Dear Readers, In this article, we will see the Autonomous Health Framework. AHF includes number of diagnostic tools in a single bundle and helps us to gather various types og information like upgrading info. , oracheck….. AHF combines Trace file analyzer(TFA), orachk etc.., which oracle… Read More
Dear Readers, In this article, we will see the following Free Buffer Wait . Buffer Cache Management : 👉 Locating free blocks (Data to write) 👉 Finding data blocks (Data to read) 👉 Managing LRU lists (Flushing least recently used blocks) 👉 Cleaning dirty buffers… Read More
Dear Readers, In this article, we will see the following Internal mechanism of Buffer Cache. Buffer cache management : 👉 Locating free blocks (Data to write) 👉 Finding data blocks (Data to read) 👉 Managing LRU lists (Flushing least recently used blocks) 👉 Cleaning dirty… Read More
Dear Readers, In this article, we will see Performance Investigation(Database / SQL) . UNDERSTANDING: Database running slow or application running slow. These are the basic and practical issue come in day today activity. Before Investigation have below answers from application or client or users 👉… Read More
Dear Readers, In this article, we will see DB scattered Read Wait Event. DB scattered Read wait event Adjacent blocks are read from disk allowing Oracle to read many blocks by performing large I/Os. Thus, from the storage perspective, db file scattered read is a… Read More
Dear Readers, In this article, we will see Direct Path ReadWait Event. Direct Path Read Wait event: The Direct Path Read Temp wait events are an access path in which multiple Oracle blocks are read directly to the Oracle process memory without being read into… Read More
Dear Readers, In this article, we will see AWR Customization . AWR Customization: Oracle init parameter “statistics_level” enables AWR to collect and maintain performance data. Values for parameter are :: BASIC : Doesn’t collect AWR or any metric stats TYPICAL: (Default), Collects typical advisories and… Read More
Dear Readers, In this article, we will see the following Top CPU & ELAPSED Queries. Top CPU Queries :
|
1 2 3 4 5 6 7 8 9 10 |
select * from (SELECT dhst.sql_id,SQL_TEXT,X.CPU_TIME/1000000 CPU_Time_inSec,X.Num_Execs,round(((X.CPU_TIME/1000000)/X.Num_Execs),3) CPUPEREXEC, X.num_rows FROM DBA_HIST_SQLTEXT DHST, (SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME ,sum(executions_delta) as Num_Execs,sum(ROWS_PROCESSED_delta) as num_rows FROM DBA_HIST_SQLSTAT DHSS WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT WHERE begin_interval_time > =TO_DATE('25/APR/2019 20:50','DD-MON-YYYY HH24:MI') AND END_INTERVAL_TIME<=TO_DATE('26/APR/2019 06:30','DD-MON-YYYY HH24:MI') ) GROUP BY DHSS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.CPU_TIME DESC FETCH FIRST 50 ROWS ONLY)y order by y.cpuperexec desc |
Top ELAPSED Queries :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
select sub.sql_id, txt.sql_text, parsing_schema_name, sub.seconds_since_date as TOTALELAPSEDTIME, sub.execs_since_date as NUmberofExecs, sub.gets_rows_date as RowsProcessed, round(sub.seconds_since_date / (sub.execs_since_date + 0.01), 3) avg_query_time from ( -- sub to sort before top N filter select sql_id, g.parsing_schema_name, round(sum(ELAPSED_TIME_delta) / 1000000) as seconds_since_date, sum(executions_delta) as execs_since_date, sum(buffer_gets_delta) as gets_since_date, sum(ROWS_PROCESSED_delta) as gets_rows_date, row_number() over (order by round(sum(elapsed_time_delta) / 1000000) desc) r from dba_hist_snapshot natural join dba_hist_sqlstat g where begin_interval_time > =TO_DATE('02/APR/2019 07:50','DD-MON-YYYY HH24:MI') AND END_INTERVAL_TIME<=TO_DATE('02/APR/2019 08:20','DD-MON-YYYY HH24:MI') and parsing_schema_name = 'SCOTT' group by sql_id, g.parsing_schema_name) sub join dba_hist_sqltext txt on sub.sql_id = txt.sql_id where r < 10 order by avg_query_time desc |
TOP Elapsed Queries with Text :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
select sub.sql_id, txt.sql_text, parsing_schema_name, sub.seconds_since_date as TOTALELAPSEDTIME, sub.execs_since_date as NUmberofExecs, sub.gets_rows_date as RowsProcessed, round(sub.seconds_since_date / (sub.execs_since_date + 0.01), 3) avg_query_time from ( -- sub to sort before top N filter select sql_id, g.parsing_schema_name, round(sum(ELAPSED_TIME_delta) / 1000000) as seconds_since_date, sum(executions_delta) as execs_since_date, sum(buffer_gets_delta) as gets_since_date, sum(ROWS_PROCESSED_delta) as gets_rows_date, row_number() over (order by round(sum(elapsed_time_delta) / 1000000) desc) r from dba_hist_snapshot natural join dba_hist_sqlstat g where begin_interval_time > =TO_DATE('22/OCT/2019 10:40','DD-MON-YYYY HH24:MI') AND END_INTERVAL_TIME<=TO_DATE('22/OCT/2019 14:00','DD-MON-YYYY HH24:MI') and parsing_schema_name = 'SCOTT' group by sql_id, g.parsing_schema_name) sub join dba_hist_sqltext txt on sub.sql_id = txt.sql_id and SQL_TEXT like 'update%' --where r < 50 order by avg_query_time desc |
Thank you for giving your valuable time to read the above information.… Read More
Dear Readers, In this article we will focus one of the most remarkable feature that has been introduced in Oracle 18C that is “now you can cancel SQL statement without killing session”. Prior 18C, DBA’s had to kill a DB sessions to get rid of… Read More
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 / |
