Performance Troubleshooting Steps
reference of this article from : dbaparadise.com
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
/* sessions.sql Example: @sessions.sql Copyright @2016 dbaparadise.com */ set linesize 200 set pagesize 100 clear columns col inst for 99999999 col sid for 9990 col serial# for 999990 col username for a12 col osuser for a16 col program for a10 trunc col Locked for a6 col status for a1 trunc print col "hh:mm:ss" for a8 col SQL_ID for a15 col seq# for 99990 col event heading 'Current/LastEvent' for a25 trunc col state head 'State (sec)' for a14 select inst_id inst, sid , serial# , username , ltrim(substr(osuser, greatest(instr(osuser, '\', -1, 1)+1,length(osuser)-14))) osuser , substr(program,instr(program,'/',- 1)+1,decode(instr(program,'@'),0,decode(instr(program,'.'),0,length(program),instr(program,'.')- 1),instr(program,'@')-1)) program, decode(lockwait,NULL,' ','L') locked, status, to_char(to_date(mod(last_call_et,86400), 'sssss'), 'hh24:mi:ss') "hh:mm:ss" , SQL_ID, seq# , event, decode(state,'WAITING','WAITING '||lpad(to_char(mod(SECONDS_IN_WAIT,86400),'99990'),6) ,'WAITED SHORT TIME','ON CPU','WAITED KNOWN TIME','ON CPU',state) state , substr(module,1,25) module, substr(action,1,20) action from GV$SESSION where type = 'USER' and audsid != 0 -- to exclude internal processess order by inst_id, status, last_call_et desc, sid / |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
/* sqltext.sql Example @sqltext.sql 6rv5za4tfnjs8 Copyright dbaparadise.com */ set define '&' set verify off define sqlid=&1 col sql_text for a80 word_wrapped col inst_id for 9 break on inst_id set linesize 150 select inst_id, sql_text from gv$sqltext where sql_id = '&sqlid' order by inst_id,piece / |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
/* findsql.sql Example: @findsql.sql employee_contact Copyright dbaparadise.com */ set define '&' define sql_str=&1 col sql_id for A15 col sql_text for A150 word_wrapped set linesize 170 set pagesize 300 SELECT /* findsql */ sql_id, executions, sql_text FROM gv$sql WHERE command_type IN (2,3,6,7,189) AND UPPER(sql_text) LIKE UPPER('%&sql_str%') AND UPPER(sql_text) NOT LIKE UPPER('%findsql%') / undef sql_str |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
/* sqlplan.sql Copyright dbaparadise.com Example: @sqlplan.sql 1t8v91nxtxgjr */ set define '&' define sqlid=&1 col ELAPSED for 99,990.999 col CPU for 99,990.999 col ROWS_PROC for 999,999,990 col LIO for 9,999,999,990 col PIO for 99,999,990 col EXECS for 999,990 col sql_text for a40 trunc set lines 200 set pages 300 select inst_id,sql_id,child_number child_num ,plan_hash_value, round(ELAPSED_TIME/1000000/greatest(EXECUTIONS,1),3) ELAPSED, round(CPU_TIME/1000000/greatest(EXECUTIONS,1),3) CPU,EXECUTIONS EXECS, BUFFER_GETS/greatest(EXECUTIONS,1) lio, DISK_READS/greatest(EXECUTIONS,1) pio, ROWS_PROCESSED/greatest(EXECUTIONS,1) ROWS_PROC, sql_text from gv$sql where sql_id = '&sqlid' order by inst_id, sql_id, child_number / select plan_table_output from table(dbms_xplan.display_cursor('&sqlid',NULL,'ADVANCED -PROJECTION -BYTES RUNSTATS_LAST')); |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
/* sqlplan_hist.sql Copyright dbaparadise.com Example: @sqlplan_hist.sql 1t8v91nxtxgjr WARNING! Diagnistic and Tuning Pack licensing is required to run this script!!! */ set linesize 200 set pagesize 200 set verify off set define '&' define sqlid=&1 select plan_table_output from table(dbms_xplan.display_awr('&sqlid')) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
/* historical_runs.sql Copyright dbaparadise.com Example @historical_runs.sql sql_id */ set linesize 200 set pagesize 200 set verify off set define '&' define sqlid=&1 col execs for 999,999,999 col avg_etime for 999,999.999 col avg_lio for 999,999,999.9 col begin_interval_time for a30 col node for 9999 break on plan_hash_value skip 1 select sh.snap_id, sh.instance_number inst, sh.begin_interval_time, s.sql_id, s.plan_hash_value, nvl(s.executions_delta,0) execs, (s.elapsed_time_delta/decode(nvl(s.executions_delta,0),0,1,s.executions_delta))/1000000 avg_etime, (s.buffer_gets_delta/decode(nvl(s.buffer_gets_delta,0),0,1,s.executions_delta)) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SH where s.sql_id = '&sqlid' and sh.snap_id = s.snap_id and sh.instance_number = s.instance_number order by 1, 2, 3 / |
Note: Please test scripts in Non Prod before trying in Production.
Loading...
