| |
 |
|
Plan9i.sql script to display full-table scans
Don Burleson
|
Here
is a great script to show SQL access patterns, grouped by full-table
scans, index range scans and index unique scans.
 |
For complete details on tracking full-table scans with scripts, see my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
Full table scans and counts
Note that "K" indicates in the table is in the KEEP pool.
OWNER NAME NUM_ROWS C K BLOCKS
NBR_FTS
-------------- ------------------------ --------- - -
-------- --------
SYS DUAL N 2
97,237
SYSTEM SQLPLUS_PRODUCT_PROFILE N K 2
16,178
DONALD PAGE 3,450,209 N 932,120
9,999
DONALD RWU_PAGE 434 N 8
7,355
DONALD PAGE_IMAGE 18,067 N 1,104
5,368
DONALD SUBSCRIPTION 476 N K 192
2,087
DONALD PRINT_PAGE_RANGE 10 N K 32
874
--**************************************************************
-- Object Access script report
--
-- © 2001 by Donald K. Burleson
--
-- No part of this SQL script may be copied. Sold or distributed
-- without the express consent of Donald K. Burleson
--**************************************************************
-- ********************************************************
-- Report section
-- ********************************************************
spool plan.lst
set echo off
set feedback on
set pages 999;
column nbr_FTS format 9,999,999
column num_rows format 999,999,999
column blocks format 999,999
column owner format a14;
column name format a24;
column ch format a1;
column object_owner heading "Owner" format a12;
column ct heading "# of SQL selects" format 999,999;
select
object_owner,
count(*) ct
from
v$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
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) nbr_FTS
from
dba_tables t,
dba_segments s,
v$sqlarea a,
(select distinct
address,
object_owner owner,
object_name name
from
v$sql_plan
where
operation = 'TABLE ACCESS'
and
options = 'FULL') p
where
a.address = p.address
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) > 1
group by
p.owner, p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks
order by
sum(a.executions) desc;
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
p.owner,
p.name,
t.num_rows,
sum(s.executions) nbr_RID
from
dba_tables t,
v$sqlarea s,
(select distinct
address,
object_owner owner,
object_name name
from
v$sql_plan
where
operation = 'TABLE ACCESS'
and
options = 'BY ROWID') p
where
s.address = p.address
and
t.table_name = p.name
and
t.owner = p.owner
having
sum(s.executions) > 9
group by
p.owner, p.name, t.num_rows
order by
sum(s.executions) desc;
--*************************************************
-- 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
p.owner,
d.table_name,
p.name index_name,
seg.blocks tbl_blocks,
sum(s.executions) nbr_scans
from
dba_segments seg,
v$sqlarea s,
dba_indexes d,
(select distinct
address,
object_owner owner,
object_name name
from
v$sql_plan
where
operation = 'INDEX'
and
options = 'FULL SCAN') p
where
d.index_name = p.name
and
s.address = p.address
and
d.table_name = seg.segment_name
and
seg.owner = p.owner
having
sum(s.executions) > 9
group by
p.owner, d.table_name, p.name, seg.blocks
order by
sum(s.executions) desc;
ttitle 'Index range scans and counts'
select
p.owner,
d.table_name,
p.name index_name,
seg.blocks tbl_blocks,
sum(s.executions) nbr_scans
from
dba_segments seg,
v$sqlarea s,
dba_indexes d,
(select distinct
address,
object_owner owner,
object_name name
from
v$sql_plan
where
operation = 'INDEX'
and
options = 'RANGE SCAN') p
where
d.index_name = p.name
and
s.address = p.address
and
d.table_name = seg.segment_name
and
seg.owner = p.owner
having
sum(s.executions) > 9
group by
p.owner, d.table_name, p.name, seg.blocks
order by
sum(s.executions) desc;
ttitle 'Index unique scans and counts'
select
p.owner,
d.table_name,
p.name index_name,
sum(s.executions) nbr_scans
from
v$sqlarea s,
dba_indexes d,
(select distinct
address,
object_owner owner,
object_name name
from
v$sql_plan
where
operation = 'INDEX'
and
options = 'UNIQUE SCAN') p
where
d.index_name = p.name
and
s.address = p.address
having
sum(s.executions) > 9
group by
p.owner, d.table_name, p.name
order by
sum(s.executions) desc;
spool off
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
