Home » RDBMS Server » Server Administration » Finding Skip Scan (Oracle)
Finding Skip Scan [message #673566] Sat, 24 November 2018 10:11 Go to next message
moonjellies4
Messages: 2
Registered: November 2018
Junior Member
set verify off
set lines 400
set pages 100
col sql_text format a40
col FORCE_MATCHING_SIGNATURE format a32

undef btime
undef etime
undef bsnap
undef esnap
undef tvalue

prompt
prompt ENTER BEGIN TIME FORMAT MMDD HH24:MI:
prompt
define btime = '&&btime'
prompt
prompt ENTER END TIME FORMAT MMDD HH24:MI:
prompt
define etime = '&&etime'
prompt
prompt ENTER TOP N VALUE:
prompt
define tvalue ='&&tvalue'

set termout off
define bsnap=idle
define esnap=idle
column bsnapid new_value bsnap
column esnapid new_value esnap

select min(snap_id) bsnapid,max(snap_id) esnapid from dba_hist_snapshot
where
begin_interval_time >= TO_DATE('&btime','MMDD HH24:MI') and
begin_interval_time <= TO_DATE('&etime','MMDD HH24:MI');


set termout on

COLUMN curr_datee FORMAT A45
COLUMN instance_namee FORMAT A45 NEWLINE
COLUMN HOST_NAMEE FORMAT A45 NEWLINE
COLUMN USER_NAMEE FORMAT A45 NEWLINE

set echo off
set heading off
set feedback off

SELECT 'Current Date: '||to_char(sysdate, 'DD-MON-YYYY HH:MI PM') curr_datee,
'Instance Name: '||instance_name instance_namee,
'Host Name: '||HOST_NAME HOST_NAMEE,
'User: '||User User_NAMEE
FROM v$instance;

set heading on
set feedback on

prompt SNAP ID &bsnap AND &esnap
prompt PERIOD &btime AND &etime
prompt Top &tvalue RECORDS

prompt
prompt Top &tvalue SQLID ordered by Gets
prompt =======================================
prompt

select * from (
select instance_number,sql_id,parsing_schema_name,module,PARSE_CALLS,EXECUTIONS,BUFFER_GETS, GETS_PER_EXEC, DISK_READS, READS_PER_EXEC,round(ELAPSED_TIME/1000000,2) ELAPSED_TIME,
cost,main.FORCE_MATCHING_SIGNATURE||'('||nvl(sub.count,1)||')' FORCE_MATCHING_SIGNATURE,substr(trim(sql_text),1,40) sql_text
from (
select a.instance_number,a.sql_id, substr(PARSING_SCHEMA_NAME,1,20) parsing_schema_name,substr(module,1,30) module,DBMS_LOB.SUBSTR(trim(sql_text),40,1) sql_text, a.FORCE_MATCHING_SIGNATURE,
sum(PARSE_CALLS_DELTA) as PARSE_CALLS,sum(EXECUTIONS_DELTA) as EXECUTIONS, sum(BUFFER_GETS_DELTA) as BUFFER_GETS,(sum(BUFFER_GETS_DELTA) / sum(DECODE(EXECUTIONS_DELTA,0,1,EXECUTIONS_DELTA))) GETS_PER_EXEC,
sum(DISK_READS_DELTA) as DISK_READS, (sum(DISK_READS_DELTA) / sum(DECODE(EXECUTIONS_DELTA,0,1,EXECUTIONS_DELTA))) READS_PER_EXEC,
sum(ELAPSED_TIME_DELTA) as ELAPSED_TIME,sum(EXECUTIONS_DELTA) as CPU_TIME,sum(optimizer_cost) as cost,
rank() over (order by sum(BUFFER_GETS_DELTA) DESC) rnk
from DBA_HIST_SQLSTAT a,DBA_HIST_SQLTEXT b
where
a.snap_id >= &bsnap and a.snap_id <= &esnap and
PARSING_SCHEMA_NAME not in ('SYS','SYSTEM') and
PARSING_SCHEMA_NAME NOT LIKE 'DB_%' and
a.sql_id=b.sql_id and
a.sql_id in (
(SELECT sql_id
FROM dba_hist_sql_plan
WHERE timestamp >= TO_DATE ('&btime', 'MMDD HH24:MI')
AND timestamp <= TO_DATE ('&etime', 'MMDD HH24:MI')
AND ((options IN ('SKIP SCAN','SKIP SCAN DESCENDING') AND operation in ('INDEX') and CARDINALITY > 15000) OR TIME > 120 OR COST > 10000)))
and substr(module,1,30) not like 'SQL%Developer%' and substr(module,1,30) not like 'TOAD%' and
upper(b.sql_text) not like 'CALL%' and upper(b.sql_text) not like 'BEGIN%' and upper(b.sql_text) not like 'DEC%'
group by a.instance_number,a.sql_id,PARSING_SCHEMA_NAME,module,DBMS_LOB.SUBSTR(trim(sql_text),40,1),a.FORCE_MATCHING_SIGNATURE
) main,
(select FORCE_MATCHING_SIGNATURE,count(*) count from gv$sqlarea d where d.FORCE_MATCHING_SIGNATURE <> d.EXACT_MATCHING_SIGNATURE group by d.FORCE_MATCHING_SIGNATURE) sub
where main.FORCE_MATCHING_SIGNATURE=sub.FORCE_MATCHING_SIGNATURE(+) order by BUFFER_GETS desc
) where rownum <= &tvalue order by buffer_gets desc
/

undef btime
undef etime
undef bsnap
undef esnap
undef tvalue



Re: Finding Skip Scan [message #673567 is a reply to message #673566] Sat, 24 November 2018 10:15 Go to previous messageGo to next message
BlackSwan
Messages: 26538
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Realize that since we don't have your tables or data, we can't run, test, debug, or improve posted code.

So why did you post above?
Re: Finding Skip Scan [message #673568 is a reply to message #673567] Sat, 24 November 2018 10:19 Go to previous messageGo to next message
BlackSwan
Messages: 26538
Registered: January 2009
Location: SoCal
Senior Member
SELECT * 
FROM   (SELECT instance_number, 
               sql_id, 
               parsing_schema_name, 
               MODULE, 
               parse_calls, 
               executions, 
               buffer_gets, 
               gets_per_exec, 
               disk_reads, 
               reads_per_exec, 
               Round(elapsed_time / 1000000, 2) ELAPSED_TIME, 
               cost, 
               main.force_matching_signature 
               ||'(' 
               ||Nvl(sub.count, 1) 
               ||')'                            FORCE_MATCHING_SIGNATURE, 
               Substr(Trim(sql_text), 1, 40)    sql_text 
        FROM   (SELECT a.instance_number, 
                       a.sql_id, 
                       Substr(parsing_schema_name, 1, 20) 
                       parsing_schema_name, 
                       Substr(MODULE, 1, 30)                     MODULE, 
                       dbms_lob.Substr(Trim(sql_text), 40, 1)    sql_text, 
                       a.force_matching_signature, 
                       SUM(parse_calls_delta)                    AS PARSE_CALLS, 
                       SUM(executions_delta)                     AS EXECUTIONS, 
                       SUM(buffer_gets_delta)                    AS BUFFER_GETS, 
                       ( SUM(buffer_gets_delta) / SUM( 
                         Decode(executions_delta, 0, 1, 
                                                  executions_delta 
                         )) ) 
                                    GETS_PER_EXEC, 
                       SUM(disk_reads_delta)                     AS DISK_READS, 
                       ( SUM(disk_reads_delta) / SUM( 
                         Decode(executions_delta, 0, 1, 
                                                  executions_delta) 
                                                 ) ) 
                                                                 READS_PER_EXEC, 
                       SUM(elapsed_time_delta)                   AS ELAPSED_TIME 
                       , 
                       SUM(executions_delta) 
                       AS CPU_TIME, 
                       SUM(optimizer_cost)                       AS cost, 
                       Rank() 
                         over ( 
                           ORDER BY SUM(buffer_gets_delta) DESC) rnk 
                FROM   dba_hist_sqlstat a, 
                       dba_hist_sqltext b 
                WHERE  a.snap_id >= &bsnap 
                       AND a.snap_id <= &esnap 
                       AND parsing_schema_name NOT IN ( 'SYS', 'SYSTEM' ) 
                       AND parsing_schema_name NOT LIKE 'DB_%' 
                       AND a.sql_id = b.sql_id 
                       AND a.sql_id IN ((SELECT sql_id 
                                         FROM   dba_hist_sql_plan 
                                         WHERE  timestamp >= To_date ('&btime', 
                                                             'MMDD HH24:MI') 
                                                AND timestamp <= To_date ( 
                                                    '&etime', 
                                                                 'MMDD HH24:MI') 
                                                AND ( ( 
                                       options IN ( 'SKIP SCAN', 
                                                    'SKIP SCAN DESCENDING' ) 
                                       AND operation IN ( 'INDEX' ) 
                                       AND cardinality > 15000 ) 
                                                       OR TIME > 120 
                                                       OR cost > 10000 ))) 
                       AND Substr(MODULE, 1, 30) NOT LIKE 'SQL%Developer%' 
                       AND Substr(MODULE, 1, 30) NOT LIKE 'TOAD%' 
                       AND Upper(b.sql_text) NOT LIKE 'CALL%' 
                       AND Upper(b.sql_text) NOT LIKE 'BEGIN%' 
                       AND Upper(b.sql_text) NOT LIKE 'DEC%' 
                GROUP  BY a.instance_number, 
                          a.sql_id, 
                          parsing_schema_name, 
                          MODULE, 
                          dbms_lob.Substr(Trim(sql_text), 40, 1), 
                          a.force_matching_signature) main, 
               (SELECT force_matching_signature, 
                       Count(*) count 
                FROM   gv$sqlarea d 
                WHERE  d.force_matching_signature <> d.exact_matching_signature 
                GROUP  BY d.force_matching_signature) sub 
        WHERE  main.force_matching_signature = sub.force_matching_signature(+) 
        ORDER  BY buffer_gets DESC) 
WHERE  ROWNUM <= &tvalue 
ORDER  BY buffer_gets DESC 

/ 
Re: Finding Skip Scan [message #673569] Sat, 24 November 2018 10:20 Go to previous messageGo to next message
moonjellies4
Messages: 2
Registered: November 2018
Junior Member
Hi
anyone can run their code in their oracle env to show skip scan related queries detail. it fetches detail from awr. does not require any tables or prerequiste to run.
Re: Finding Skip Scan [message #673570 is a reply to message #673569] Sat, 24 November 2018 10:40 Go to previous messageGo to next message
BlackSwan
Messages: 26538
Registered: January 2009
Location: SoCal
Senior Member
I was unaware that this was a problem that needed to be solved.

http://www.orafaq.com/tuningguide/index%20skip%20scan.html
Re: Finding Skip Scan [message #673571 is a reply to message #673569] Sat, 24 November 2018 12:11 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why "parsing_schema_name NOT LIKE 'DB_%' "?

Re: Finding Skip Scan [message #673577 is a reply to message #673569] Sun, 25 November 2018 01:33 Go to previous message
John Watson
Messages: 7933
Registered: January 2010
Location: Global Village
Senior Member
What, exactly, are you providing for &etime and &btime?

And why these filters,
AND Substr(MODULE, 1, 30) NOT LIKE 'SQL%Developer%' 
                       AND Substr(MODULE, 1, 30) NOT LIKE 'TOAD%'
Previous Topic: Determine which Standard Edition is installed
Next Topic: Table Partitioning
Goto Forum:
  


Current Time: Mon Jun 17 14:20:30 CDT 2019