Dear All,
In this post i am sharing few useful SQL's and How to find the top 10 sql's and Find How much PGA usage in Oracle 19C DB.
1) To find corrupt Object/Segments using file_id and block_id
select segment_name,block_id,file_id from dba_extents where FILE_ID=&1 and &2 between block_id and block_id+blocks-1
/
Indexes created on Table_name
set lin 10000
col INDEX_NAME for a30
col TABLE_NAME for a30
col index_type for a25
col status for a10
--col LAST_ANALYZED for a19 wrap
col column_name for a20
col column_expression for a25
select a.INDEX_NAME ,b.column_name, column_expression,a.TABLE_NAME,STATUS,INDEX_TYPE
from user_indexes a,user_ind_columns b, user_ind_expressions c
where a.table_name = upper(trim('&1'))
and a.TABLE_NAME=b.TABLE_NAME
and a.INDEX_NAME=b.INDEX_NAME
and a.INDEX_NAME=c.INDEX_NAME (+)
and a.INDEX_NAME=c.INDEX_NAME (+)
/
Current running query
set lin 10000
col sid for 999999999
col serial# for 9999999999
col osuer for a20
col SQL_TEXT for a70 wrap
col osuser for a10
col status for a10
--select sid,serial#, status, osuser,b.EXECUTIONS,b.DISK_READS, b.SQL_TEXT from v$session a, v$sqlarea b
--where a.SQL_ADDRESS=b.ADDRESS
--and b.HASH_VALUE = a.SQL_HASH_VALUE
select sid,serial#, status, osuser,b.EXECUTIONS,b.DISK_READS, b.SQL_TEXT from v$session a, v$sql b
where a.SQL_ADDRESS=b.ADDRESS
and b.HASH_VALUE = a.SQL_HASH_VALUE
order by 6 desc,5 desc
/
To find query how much memory (PGA)
SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid FORMAT 999 HEADING 'SID'
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN session_program FORMAT a18 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a18 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory'
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max'
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory'
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX'
SELECT
s.sid sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,8) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory') session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory max') session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory') session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory max') session_uga_memory_max
FROM
v$session s
ORDER BY session_pga_memory DESC
/
SID Oracle User O/S User Session Program Machine PGA Memory PGA Memory Max UGA Memory UGA Memory MAX
---- ------------ --------- ------------------ ------------------ -------------- -------------- -------------- --------------
20 oracle oracle@ggnode1 (M0 ggnode1 32,850,736 59,786,032 23,623,584 29,389,984
18 oracle oracle@ggnode1 (MM ggnode1 23,875,304 26,562,280 4,287,472 21,663,552
252 oracle oracle@ggnode1 (TT ggnode1 20,333,360 20,333,360 367,072 367,072
274 oracle oracle@ggnode1 (J0 ggnode1 11,682,608 104,809,264 367,072 367,072
246 oracle oracle@ggnode1 (DB ggnode1 8,953,728 10,478,112 367,072 367,072
42 oracle oracle@ggnode1 (M0 ggnode1 5,718,832 0 65,488 628,864
262 oracle oracle@ggnode1 (M0 ggnode1 5,522,224 8,274,736 3,650,952 5,102,328
41 SYS oracle sqlplus@ggnode1 (T ggnode1 5,006,336 19,489,792 1,611,368 2,893,104
9 oracle oracle@ggnode1 (DI ggnode1 4,844,408 4,844,408 367,072 367,072
27 oracle oracle@ggnode1 (CJ ggnode1 4,411,112 7,294,696 2,462,592 5,014,824
28 oracle oracle@ggnode1 (W0 ggnode1 3,556,144 4,408,112 2,070,120 2,633,496
7 oracle oracle@ggnode1 (DB ggnode1 3,533,688 4,123,512 1,742,320 1,873,296
275 oracle oracle@ggnode1 (W0 ggnode1 3,425,072 4,408,112 1,929,584 3,006,712
251 oracle oracle@ggnode1 (LR ggnode1 3,354,136 3,681,816 970,864 1,152,952
250 oracle oracle@ggnode1 (RE ggnode1 2,764,312 3,026,456 970,856 1,280,040
12 oracle oracle@ggnode1 (SM ggnode1 2,698,776 3,419,672 970,864 1,458,232
6 SYS oracle oracle@ggnode1 (OF ggnode1 2,567,704 2,567,704 825,488 890,976
38 oracle oracle@ggnode1 (Q0 ggnode1 2,442,032 2,900,784 1,036,344 1,341,656
10 oracle oracle@ggnode1 (LG ggnode1 2,379,496 2,379,496 367,072 367,072
11 oracle oracle@ggnode1 (CK ggnode1 2,282,496 2,282,496 367,072 367,072
4 oracle oracle@ggnode1 (W0 ggnode1 2,245,424 2,573,104 839,888 1,210,680
3 oracle oracle@ggnode1 (GE ggnode1 2,174,488 2,174,488 367,072 367,072
263 oracle oracle@ggnode1 (Q0 ggnode1 2,114,352 2,114,352 498,048 498,048
259 oracle oracle@ggnode1 (W0 ggnode1 2,114,352 6,964,016 498,048 498,048
256 oracle oracle@ggnode1 (AQ ggnode1 2,043,416 2,043,416 367,072 367,072
13 oracle oracle@ggnode1 (W0 ggnode1 2,031,760 7,291,696 629,048 1,283,976
15 oracle oracle@ggnode1 (W0 ggnode1 1,917,744 2,442,032 498,056 1,152,952
8 oracle oracle@ggnode1 (SV ggnode1 1,912,344 1,912,344 367,072 367,072
253 oracle oracle@ggnode1 (MM ggnode1 1,912,344 1,912,344 367,072 432,560
245 oracle oracle@ggnode1 (PM ggnode1 1,846,808 1,846,808 367,072 367,072
244 oracle oracle@ggnode1 (VK ggnode1 1,846,808 1,846,808 367,072 367,072
242 oracle oracle@ggnode1 (DI ggnode1 1,846,808 1,846,808 367,072 367,072
241 oracle oracle@ggnode1 (GE ggnode1 1,846,808 1,846,808 367,072 367,072
240 oracle oracle@ggnode1 (MM ggnode1 1,846,808 1,846,808 367,072 367,072
19 oracle oracle@ggnode1 (TM ggnode1 1,846,808 1,846,808 367,072 367,072
17 oracle oracle@ggnode1 (PX ggnode1 1,846,808 1,846,808 367,072 367,072
239 oracle oracle@ggnode1 (VK ggnode1 1,846,808 1,846,808 367,072 367,072
2 oracle oracle@ggnode1 (PS ggnode1 1,846,808 1,846,808 367,072 367,072
249 oracle oracle@ggnode1 (SM ggnode1 1,846,808 1,846,808 367,072 367,072
238 oracle oracle@ggnode1 (CL ggnode1 1,797,728 1,797,728 367,072 367,072
1 oracle oracle@ggnode1 (PM ggnode1 1,797,728 1,797,728 367,072 367,072
255 oracle oracle@ggnode1 (Q0 ggnode1 1,786,672 1,786,672 432,560 432,560
24 oracle oracle@ggnode1 (TT ggnode1 1,655,600 1,655,600 367,072 367,072
22 oracle oracle@ggnode1 (QM ggnode1 1,655,600 1,655,600 367,072 367,072
5 oracle oracle@ggnode1 (SC ggnode1 1,655,600 1,655,600 367,072 367,072
269 oracle oracle@ggnode1 (W0 ggnode1 1,655,600 1,655,600 367,072 367,072
248 oracle oracle@ggnode1 (LG ggnode1 1,655,600 1,655,600 367,072 367,072
247 oracle oracle@ggnode1 (LG ggnode1 1,655,600 1,655,600 367,072 367,072
237 oracle oracle@ggnode1 (TT ggnode1 1,655,600 1,655,600 367,072 367,072
243 oracle oracle@ggnode1 (SC ggnode1 1,655,600 1,655,600 367,072 367,072
35 oracle oracle@ggnode1 (W0 ggnode1 1,655,600 1,655,600 367,072 367,072
51 rows selected.
Find Active SQL old versions :::
SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
SID,
MACHINE,
REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,
ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
|| ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
|| ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09')) RUNT
FROM V$SESSION SES,
V$SQLtext_with_newlines SQL
where SES.STATUS = 'ACTIVE'
and SES.USERNAME is not null
and SES.SQL_ADDRESS = SQL.ADDRESS
and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
and Ses.AUDSID <> userenv('SESSIONID')
order by runt desc, 1,sql.piece;
Thanks,
Srini
No comments:
Post a Comment
No one has ever become poor by giving