Oracle Top Sql
col sql_text format a65 heading 'SQL Text'
col sharable_mem format 999999 heading 'SHARED|MEMORY'
col persistent_mem format 999999 heading 'PERSIST|MEMORY'
col runtime_mem format 999999 heading 'RUNTIME|MEMORY'
col loads format 9999 heading 'LOADS'
col invalidations format 9999 heading 'INVALID'
col parse_calls format 999999 heading 'PARSE|CALLS'
col executions format 999999 heading 'EXECUTE'
col log_phy format 9999 heading 'LOG/|PHY'
col disk_reads format 9999999 heading 'DISK|READS'
col phy_exe format 999999 heading 'PHY/|EXE'
col buffer_gets format 999999999 heading 'BUFFER|GETS'
col log_exe format 9999999 heading 'LOG/|EXE'
col sorts format 9999 heading 'S'
col rows_processed format 99999999 heading 'ROWS|PROCESSED'
col rows_exe format 9999999 heading 'ROWS/|EXE'
select
loads,
optimizer_mode,
rows_processed,
sorts,
parse_calls,
executions,
disk_reads,
buffer_gets,
sql_id,
sql_text
from gv$sqlarea
where parse_calls >= 0
and executions >= 2
and rows_processed >= 5
and disk_reads >= 100
and buffer_gets >= 1000
order by buffer_gets asc;
ROWS PARSE DISK BUFFER
LOADS OPTIMIZER_ PROCESSED S CALLS EXECUTE READS GETS SQL_ID SQL Text
----- ---------- --------- ----- ------- ------- -------- ---------- ------------- -----------------------------------------------------------------
9 ALL_ROWS 119 0 2 26 427 1023 9wygvu6cx2npy DELETE FROM WRI$_ADV_MESSAGE_GROUPS A WHERE A.TASK_ID = :B2 AND (
:B1 IS NULL OR :B1 = A.EXEC_NAME)
12 CHOOSE 592 0 232 592 458 1851 g3wrkmxkxzhf2 select cols,audit$,textlength,intcols,property,flags,rowid from v
iew$ where obj#=:1
8 CHOOSE 702 0 702 702 330 2163 b1wc53ddd6h3p select audit$,options from procedure$ where obj#=:1
2 ALL_ROWS 9 0 9 9 132 2850 2nszajb0qbyvp DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; b
roken BOOLEAN := FALSE; BEGIN wwv_flow_mail.push_queue(wwv_flow_p
latform.get_preference('SMTP_HOST_ADDRESS'),wwv_flow_platform.get
_preference('SMTP_HOST_PORT')); :mydate := next_date; IF broken T
HEN :b := 1; ELSE :b := 0; END IF; END;
7 CHOOSE 688 0 1245 1245 696 4762 c6awqs517jpj0 select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece f
rom idl_char$ where obj#=:1 and part=:2 and version=:3 order by p
iece#
7 ALL_ROWS 396 16 2 2 801 4866 g5m0bnvyy37b1 select sql_id, plan_hash_value, bucket_id, begin_snap, end
_snap from (select dbid, sql_id, bucket_id, plan_hash_value,
begin_snap, end_snap, cpu_plus_io from (select dbid
, sql_id, bucket_id, plan_hash_value, begin_sna
p, end_snap, cpu_plus_io, row_number() over (pa
rtition by bucket_id order b
y cpu_plus_io desc) as within_bucket_rnk
from (select dbid, sql_id, bucket_id,
max(plan_hash_value) keep (dense_rank last
order by cpu_plus_io) plan_hash_value,
max(begin_snap) keep (dense_rank last
order by cpu_plus_io) begin_snap,
max(end_snap) keep (dense_rank last
order by cpu_plus_io) end_snap,
max(cpu_plus_io) cpu_plus_io
from (select dbi
4 ALL_ROWS 60 0 60 60 177 5679 ga6ja2d04ycbm DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIM
E ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(3
0) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_
owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME
ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE
:= :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE
:= :window_start; window_end TIMESTAMP WITH TIME ZONE := :window
_end; chain_id VARCHAR2(14) := :chainid; credential_owner varc
har2(30) := :credown; credential_name varchar2(30) := :crednam;
destination_owner varchar2(30) := :destown; destination_name v
archar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid;
BEGIN begin dbms_rlmgr_dr.execschdactions('RLM$SCHDNEGACTION');
end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0;
END IF; END;
4 CHOOSE 2881 0 278 278 122 6316 bgjhtnqhr5u9h select procedure#,entrypoint# from procedureplsql$ where obj#=:1
order by procedure#
10 CHOOSE 2469 0 19 19 115 10348 2mp99nzd9u1qp delete from histgrm$ where obj# = :1
1 CHOOSE 2304 0 1677 1677 2519 10429 39m4sx9k63ba2 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece fro
m idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piec
e#
1 CHOOSE 2406 0 1677 1677 1292 10660 ga9j9xk5cy9s0 select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece fro
m idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piec
e#
5 CHOOSE 5238 0 254 254 350 11126 dcstr36r0vz0d select procedure#,procedurename,properties,itypeobj# from procedu
reinfo$ where obj#=:1 order by procedurename desc, overload# desc
6 CHOOSE 3404 1162 1162 1162 508 11389 3ktacv9r56b51 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_o
bj#, nvl(property,0),subname,type#,d_attrs from dependency$ d, ob
j$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
1 CHOOSE 549 0 549 549 234 13172 9ctt1scmwbmbg begin dbsnmp.bsln_internal.maintain_thresholds; end;
18 RULE 67066 4333 142 4333 461 13213 db78fxqxwxt7r select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$
where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
12 CHOOSE 2605 6831 2257 6831 296 15610 2q93zsrvbdw48 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#
,0) order by grantee#
1 CHOOSE 3985 0 1677 1677 4666 16132 cvn54b7yz0s8u select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece fro
m idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piec
e#
2 CHOOSE 8083 0 3800 3800 1414 23798 8swypbbr0m372 select order#,columns,types from access$ where d_obj#=:1
5 CHOOSE 12 0 14612 14612 144 29294 20vv6ttajyjzq delete from access$ where d_obj#=:1
4 ALL_ROWS 1593 58 58 58 882 30606 gnux0zb3sxduk SELECT TIMEPOINT, DELTA_SPACE_USAGE, DELTA_SPACE_ALLOC, TOTAL_SPA
CE_USAGE, TOTAL_SPACE_ALLOC, INSTANCE_NUMBER, OBJN FROM TABLE(DBM
S_SPACE.OBJECT_GROWTH_TREND_SWRF(:B1 , :B2 , :B3 , :B4 )) ORDER B
Y TIMEPOINT
3 FIRST_ROWS 364 728 2 364 109 33379 424h0nf7bhqzd SELECT sqlset_row(sql_id, force_matching_signature, sq
l_text, object_list, bind_data, parsing_schema_name, mo
dule, action, elapsed_time, cpu_time, buffer_gets, disk
_reads, direct_writes, rows_processed, fetches, executi
ons, end_of_fetch_count, optimizer_cost, optimizer_env,
priority, command_type, first_load_time, stat_period,
active_stat_period, other, plan_hash_value, sql_plan, b
ind_list) FROM ( SELECT /*+ first_rows(1) */ sql_id, force_match
ing_signature, sql_text, cast(NULL as SQL_OBJECTS) object_list, b
ind_data, parsing_schema_name, module, action, elapsed_time, cpu_
time, buffer_gets, disk_reads, direct_writes,rows_processed, fetc
hes, executions, end_of_fetch_count, optimizer_cost, optimizer_en
v,NULL priority, command_type, NULL first_load_time, null stat_pe
riod, null active_stat_period, xmlelement(
"other_attrs", xmlelement("parsing
_user_id",
3 CHOOSE 17848 0 17848 17848 567 35777 grwydz59pu6mc select text from view$ where rowid=:1
7 CHOOSE 28 0 14462 14462 157 43742 1gfaj4z5hn1kf delete from dependency$ where d_obj#=:1
4 CHOOSE 24529 0 254 5238 649 64917 32hbap2vtmf53 select position#,sequence#,level#,argument,type#,charsetid,charse
tform,properties,nvl(length, 0), nvl(precision#, 0),nvl(scale, 0)
,nvl(radix, 0), type_owner,type_name,type_subname,type_linkname,p
ls_type from argument$ where obj#=:1 and procedure#=:2 order by s
equence# desc
9 RULE 25858 0 641 28937 430 84837 96g93hntrzjtr select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, time
stamp#, sample_size, minimum, maximum, distcnt, lowval, hival, de
nsity, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1
and intcol#=:2
4 ALL_ROWS 2088 0 58 58 123112 157671 8szmwam7fysa3 insert into wri$_adv_objspace_trend_data select timepoint, space
_usage, space_alloc, quality from table(dbms_space.object_growth
_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE'))
11 ALL_ROWS 130 10 2 2 126 160747 2tr12b1b8uj71 MERGE /*+ dynamic_sampling(ST 4) dynamic_sampling_est_cdn(ST) */
INTO STATS_TARGET$ ST USING (SELECT STALENESS, OSIZE, OBJ#, TYPE#
, CASE WHEN STALENESS > LOG(0.01, NVL(LOC_STALE_PCT, :B1 )/100) T
HEN 128 ELSE 0 END + AFLAGS AFLAGS, STATUS, SID, SERIAL#, PART#,
BO# FROM ( SELECT /*+ no_expand dynamic_sampling(4) dynamic_sampl
ing_est_cdn */ DECODE(BITAND(T.FLAGS,16), 16, ROUND( LOG(0.01, NV
L( LEAST( 100, GREATEST( 0.01, (DECODE(BITAND(M.FLAGS, 1), 1, GRE
ATEST(T.ROWCNT, M.INSERTS), LEAST((M.INSERTS + M.DELETES + M.UPDA
TES), GREATEST(T.ROWCNT, (T.ROWCNT + M.INSERTS - M.DELETES)))) /
(T.ROWCNT + 0.01)))), 0.01)), 1), -100.0) STALENESS, CASE WHEN T.
FILE# = 0 THEN DBMS_STATS_INTERNAL.GET_TABLE_BLOCK_COUNT(U.NAME,
O.NAME, NULL, NULL, 'TRUE') WHEN S.TYPE# = 5 THEN DBMS_STATS_INTE
RNAL.SEGMENT_NUMBER_BLOCKS(T.TS#, T.FILE#, T.BLOCK#, S.TYPE#, S.C
ACHEHINT, NVL(S.SPARE1,0), O.DATAOBJ#, S.BLOCKS, 'TRUE') ELSE NUL
L END * NVL(TS.BLOCKSIZE, :B6 ) OSIZE, O.OBJ# OBJ#, O.TYPE# TYPE#
, 32 AFLAGS, 0 STATUS, :B
13 CHOOSE 103829 0 1933 38518 120 284702 5n1fs4m2n2y0r select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ wher
e obj#=:1
1 CHOOSE 24 0 24 24 50024 35848714 59v4zh1ac3v2a DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIM
E ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(3
0) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_
owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME
ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE
:= :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE
:= :window_start; window_end TIMESTAMP WITH TIME ZONE := :window
_end; chain_id VARCHAR2(14) := :chainid; credential_owner varc
har2(30) := :credown; credential_name varchar2(30) := :crednam;
destination_owner varchar2(30) := :destown; destination_name v
archar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid;
BEGIN DECLARE ename VARCHAR2(30); BEGIN
ename := dbms_sqltune.execute_tuning_task( '
SYS_AUTO_SQL_TUNING_TASK'); END; :mydate := next_date; IF
broken THEN :b := 1; ELSE :b := 0; END IF; END;
29 rows selected.
Sunday 27 November 2011
Oracle Top SQL
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment