| |
 |
|
Find full-table scans with AWR using plan10g.sql
Oracle Database Tips by Donald Burleson |
If you are using the extra-cost AWR option, you can
query the dba_hist_sql_plan table to quickly find all tables
that experience full-table scans. If you want to interrogate
your current library cache, you
can use the
plan9i.sql script to see full-table scans from v$sql_plan.
While full-table scans are not "bad", per se,
they are an indicator that you may have a missing index, and
excessive full-table scans on large tables should be investigated.
High full-table scans on small tables could also be checked to
ensure that they reside in your KEEP pool. Here is a sample of
the output, showing full-table scans between AWR snapshot intervals:
full table scans and
counts
Snapshot Time OWNER NAME NUM_ROWS C
K BLOCKS NBR_FTS
------------- ---------- ------------------------ ------------
- - -------- -----
12/08/04 14 APPLSYS FND_CONC_RELEASE_DISJS
39 N K 2 98,864
APPLSYS FND_CONC_RELEASE_PERIODS 39 N
K 2 98,864
APPLSYS FND_CONC_RELEASE_STATES 1 N
K 2 98,864
SYS DUAL N
K 2 63,466
APPLSYS FND_CONC_PP_ACTIONS 7,021
N 1,262 52,036
APPLSYS FND_CONC_REL_CONJ_MEMBER 0 N
K 22 50,174
12/08/04 15 APPLSYS FND_CONC_RELEASE_DISJS 39 N
K 2 33,811
APPLSYS FND_CONC_RELEASE_PERIODS 39 N
K 2 2,864
APPLSYS FND_CONC_RELEASE_STATES 1 N
K 2 32,864
SYS DUAL N
K 2 63,466
APPLSYS FND_CONC_PP_ACTIONS 7,021 N
1,262 12,033
APPLSYS FND_CONC_REL_CONJ_MEMBER 0 N
K 22 50,174
The full arsenal of Oracle tuning scripts are
in my book "Oracle
Tuning: The Definitive Reference" and
Mike Ault's Oracle script collection.
Here is my plan10g.sql script to display SQL
access signatures over time:
--
*************************************************
-- Copyright © 2005 by
Rampant TechPress
-- This script is free for
non-commercial purposes
-- with no warranties.
Use at your own risk.
-- To use this script you must be licensed to
access the AWR tables
-- by purchasing the Oracle tuning pack and
the Oracle diagnostic pack
--
-- To license this script
for a commercial purpose,
-- contact info@rampant.cc
--
*************************************************
spool plan.lst
set echo off
set feedback on
set pages 999;
column nbr_FTS format
99,999
column num_rows format
999,999
column blocks format
9,999
column owner format
a10;
column name format
a30;
column ch format a1;
column time heading
"Snapshot Time" format a15
column object_owner
heading "Owner" format a12;
column ct
heading "# of SQL selects" format 999,999;
break on time
select
object_owner,
count(*) ct
from
dba_hist_sql_plan
where
object_owner is not
null
group by
object_owner
order by
ct desc
;
--spool access.lst;
set heading on;
set feedback on;
ttitle 'full table scans
and counts| |The "K" indicates that the table is in the
KEEP Pool (Oracle8).'
select
to_char(sn.end_interval_time,'mm/dd/rr hh24') time,
p.owner,
p.name,
t.num_rows,
-- ltrim(t.cache) ch,
decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') K,
s.blocks blocks,
sum(a.executions_delta
E "executions_delta"
) nbr_FTS
from
dba_tables E "dba_tables"
t,
dba_segments E "dba_segments"
s,
dba_hist_sqlstat E "dba_hist_sqlstat"
a,
dba_hist_snapshot sn,
(select distinct
pl.sql_id,
object_owner owner,
object_name name
from
dba_hist_sql_plan pl
where
operation = 'TABLE
ACCESS'
and
options = 'FULL') p
where
a.snap_id = sn.snap_id
and
a.sql_id = p.sql_id
and
t.owner = s.owner
and
t.table_name =
s.segment_name
and
t.table_name = p.name
and
t.owner = p.owner
and
t.owner not in ('SYS','SYSTEM')
having
sum(a.executions_delta
E "executions_delta"
) > 1
group by
to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner,
p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks
order by
1 asc;
column nbr_RID format
999,999,999
column num_rows format
999,999,999
column owner format
a15;
column name format
a25;
ttitle 'Table access by
ROWID and counts'
select
to_char(sn.end_interval_time,'mm/dd/rr hh24') time,
p.owner,
p.name,
t.num_rows,
sum(a.executions_delta
E "executions_delta"
) nbr_RID
from
dba_tables E "dba_tables"
t,
dba_hist_sqlstat E "dba_hist_sqlstat"
a,
dba_hist_snapshot sn,
(select distinct
pl.sql_id,
object_owner owner,
object_name name
from
dba_hist_sql_plan pl
where
operation = 'TABLE
ACCESS'
and
options = 'BY USER
ROWID') p
where
a.snap_id = sn.snap_id
and
a.sql_id = p.sql_id
and
t.table_name = p.name
and
t.owner = p.owner
having
sum(a.executions_delta
E "executions_delta"
) > 9
group by
to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner,
p.name, t.num_rows
order by
1 asc;
--*************************************************
-- Index Report Section
--*************************************************
column nbr_scans format
999,999,999
column num_rows format
999,999,999
column tbl_blocks format
999,999,999
column owner format
a9;
column table_name format
a20;
column index_name format
a20;
ttitle 'Index full scans
and counts'
select
to_char(sn.end_interval_time,'mm/dd/rr hh24') time,
p.owner,
d.table_name,
p.name index_name,
seg.blocks tbl_blocks,
sum(s.executions_delta
E "executions_delta"
) nbr_scans
from
dba_segments E "dba_segments"
seg,
dba_indexes E "dba_indexes"
d,
dba_hist_sqlstat E "dba_hist_sqlstat"
s,
dba_hist_snapshot sn,
(select distinct
pl.sql_id,
object_owner owner,
object_name name
from
dba_hist_sql_plan pl
where
operation = 'INDEX'
and
options = 'FULL
SCAN') p
where
d.index_name = p.name
and
s.snap_id = sn.snap_id
and
s.sql_id = p.sql_id
and
d.table_name =
seg.segment_name
and
seg.owner = p.owner
having
sum(s.executions_delta
E "executions_delta"
) > 9
group by
to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner,
d.table_name, p.name, seg.blocks
order by
1 asc;
ttitle 'Index range scans
and counts'
select
to_char(sn.end_interval_time,'mm/dd/rr hh24') time,
p.owner,
d.table_name,
p.name index_name,
seg.blocks tbl_blocks,
sum(s.executions_delta
E "executions_delta"
) nbr_scans
from
dba_segments E "dba_segments"
seg,
dba_hist_sqlstat E "dba_hist_sqlstat"
s,
dba_hist_snapshot sn,
dba_indexes E "dba_indexes"
d,
(select distinct
pl.sql_id,
object_owner owner,
object_name name
from
dba_hist_sql_plan pl
where
operation = 'INDEX'
and
options = 'RANGE
SCAN') p
where
d.index_name = p.name
and
s.snap_id = sn.snap_id
and
s.sql_id = p.sql_id
and
d.table_name =
seg.segment_name
and
seg.owner = p.owner
having
sum(s.executions_delta
E "executions_delta"
) > 9
group by
to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner,
d.table_name, p.name, seg.blocks
order by
1 asc;
ttitle 'Index unique scans
and counts'
select
to_char(sn.end_interval_time,'mm/dd/rr hh24') time,
p.owner,
d.table_name,
p.name index_name,
sum(s.executions_delta
E "executions_delta"
) nbr_scans
from
dba_hist_sqlstat E "dba_hist_sqlstat"
s,
dba_hist_snapshot sn,
dba_indexes E "dba_indexes"
d,
(select distinct
pl.sql_id,
object_owner owner,
object_name name
from
dba_hist_sql_plan pl
where
operation = 'INDEX'
and
options = 'UNIQUE
SCAN') p
where
d.index_name = p.name
and
s.snap_id = sn.snap_id
and
s.sql_id = p.sql_id
having
sum(s.executions_delta
E "executions_delta"
) > 9
group by
to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner,
d.table_name, p.name
order by
1 asc;
spool off
The output is shown below, and it is the same in 9i and 10g. A good
way to start the review of the results is by looking at the counts
of full-table scans for each AWR snapshot period. This report gives
all the information needed to select candidate tables for the KEEP
pool. The database will benefit from placing small tables, less than
two percent of db_cache_size, that are subject to frequent
full-table scans in the KEEP pool. The report from an Oracle
Applications database below shows full-table scans on both large and
small tables.
The goal is to use the RECYCLE pool for segregating large tables
involved in frequent full-table scans. To locate these large-table
full-table scans, the plan9i.sql full-table scan report for a 9i
database:
full table scans and counts
Snapshot Time
OWNER NAME NUM_ROWS C K BLOCKS NBR_FTS
-------------
---------- ------------------------ ------------ - - --------
----- 12/08/04 14 APPLSYS FND_CONC_RELEASE_DISJS
39 N K 2 98,864
APPLSYS FND_CONC_RELEASE_PERIODS 39 N K 2
98,864
APPLSYS FND_CONC_RELEASE_STATES 1 N K 2
98,864
SYS DUAL N K 2
63,466
APPLSYS FND_CONC_PP_ACTIONS 7,021 N 1,262
52,036
APPLSYS FND_CONC_REL_CONJ_MEMBER 0 N K 22
50,174
12/08/04 15
APPLSYS FND_CONC_RELEASE_DISJS 39 N K 2
33,811
APPLSYS FND_CONC_RELEASE_PERIODS 39 N K 2
2,864
APPLSYS FND_CONC_RELEASE_STATES 1 N K 2
32,864
SYS DUAL N K 2
63,466
APPLSYS FND_CONC_PP_ACTIONS 7,021 N 1,262
12,033
APPLSYS FND_CONC_REL_CONJ_MEMBER 0 N K 22
50,174
|
and include the URL for the page.
