Home » RDBMS Server » Server Administration » Finding Skip Scan (Oracle)
| Finding Skip Scan [message #673566] |
Sat, 24 November 2018 10:11  |
 |
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 #673568 is a reply to message #673567] |
Sat, 24 November 2018 10:19  |
 |
BlackSwan
Messages: 26766
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 #673577 is a reply to message #673569] |
Sun, 25 November 2018 01:33 |
John Watson
Messages: 8960
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%' |
|
|
|
Goto Forum:
Current Time: Thu Nov 28 08:33:07 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
