BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Oracle index usage tracking

Oracle Tips by Burleson Consulting

 

Note:  These scripts will only track SQL that you have directed Oracle to capture via your threshold settings in AWR or STATSPACK. STATSPACK and AWR will not collect "transient SQL" that did not appear in v$sql at snapshot time.  Hence, not all SQL will appear in these reports.  See my notes here on adjusting the SQL capture thresholds.

 

The following awr_sql_index.sql script exposes the cumulative usage of database indexes:

 

awr_sql_index.sql

 

 

col c0 heading ‘Begin|Interval|time’ format a8

col c1 heading ‘Index|Name’          format a20

col c2 heading ‘Disk|Reads’          format 99,999,999

col c3 heading ‘Rows|Processed’      format 99,999,999

select

  to_char(s.begin_interval_time,'mm-dd hh24')  c0,

  p.object_name               c1,

  sum(t.disk_reads_total)     c2,

  sum(t.rows_processed_total) c3

from
SEE CODE DEPOT FOR FULL SCRIPTS

       dba_hist_sql_plan p,

       dba_hist_sqlstat  t,

       dba_hist_snapshot s

where  

       p.sql_id = t.sql_id

   and

       t.snap_id = s.snap_id   

   and

       p.object_type like '%INDEX%'

group by

       to_char(s.begin_interval_time,'mm-dd hh24'),

       p.object_name     

order by       

       c0,c1,c2 desc

;

 

The following is a sample of the output where the stress on every important index is shown over time.  This information is important for placing index blocks into the KEEP pool to reduce disk reads, and for determining the optimal setting for the important optimizer_index_caching parameter.

 

Begin

Interval Index                       Disk        Rows

time     Name                       Reads   Processed

-------- -------------------- ----------- -----------

10-14 12 I_CACHE_STATS_1                          114

10-14 12 I_COL_USAGE$                 201       8,984

10-14 12 I_FILE1                        2           0

10-14 12 I_IND1                        93         604

10-14 12 I_JOB_NEXT                     1     247,816

10-14 11 I_KOPM1                        4       2,935

10-14 11 I_MON_MODS$_OBJ               12      28,498

10-14 11 I_OBJ1                    72,852         604

10-14 11 I_PARTOBJ$                    93         604

10-14 11 I_SCHEDULER_JOB2               4           0

10-14 11 SYS_C002433                  302       4,629

10-14 11 SYS_IOT_TOP_8540               0      75,544

10-14 11 SYS_IOT_TOP_8542               1       4,629

10-14 11 WRH$_DATAFILE_PK               2           0

10-14 10 WRH$_SEG_STAT_OBJ_PK          93         604

10-14 10 WRH$_TEMPFILE_PK                           0

10-14 10 WRI$_ADV_ACTIONS_PK           38       1,760

 

The above report shows the highest impact tables.

 

The following awr_sql_index_access.sql script will summarize index access by snapshot period.

 

          awr_sql_index_access.sql

 

 

col c1 heading ‘Begin|Interval|Time’   format a20

col c2 heading ‘Index|Range|Scans’ format 999,999

col c3 heading ‘Index|Unique|Scans’ format 999,999

col c4 heading ‘Index|Full|Scans’ format 999,999

 

select

  r.c1  c1,

  r.c2  c2,

  u.c2  c3,

  f.c2  c4

from 

(

select

  to_char(sn.begin_interval_time,'yy-mm-dd hh24')  c1,

  count(1)                           c2

from

   dba_hist_sql_plan p,

   dba_hist_sqlstat s,

   dba_hist_snapshot sn

where

   p.object_owner <> 'SYS'

and

   p.operation like '%INDEX%'

and

   p.options like '%RANGE%'  

and

   p.sql_id = s.sql_id

and

   s.snap_id = sn.snap_id  

group by

  to_char(sn.begin_interval_time,'yy-mm-dd hh24')

order by

1 ) r,

(

select

  to_char(sn.begin_interval_time,'yy-mm-dd hh24')  c1,

  count(1)                           c2

from

   dba_hist_sql_plan p,

   dba_hist_sqlstat  s,

   dba_hist_snapshot sn

where

   p.object_owner <> 'SYS'

and

   p.operation like '%INDEX%'

and

   p.options like '%UNIQUE%'  

and

   p.sql_id = s.sql_id

and

   s.snap_id = sn.snap_id  

group by

  to_char(sn.begin_interval_time,'yy-mm-dd hh24')

order by

1 ) u,

(

select

  to_char(sn.begin_interval_time,'yy-mm-dd hh24')  c1,

  count(1)                           c2

from
SEE CODE DEPOT FOR FULL SCRIPTS

   dba_hist_sql_plan p,

   dba_hist_sqlstat s,

   dba_hist_snapshot sn

where

   p.object_owner <> 'SYS'

and

   p.operation like '%INDEX%'

and

   p.options like '%FULL%'  

and

   p.sql_id = s.sql_id

and

   s.snap_id = sn.snap_id  

group by

  to_char(sn.begin_interval_time,'yy-mm-dd hh24')

order by

1 ) f

where

      r.c1 = u.c1

  and

      r.c1 = f.c1

;

 

The sample output below shows those specific times when the database performs unique scans, index range scans and index fast full scans:

 

Begin                   Index    Index    Index

Interval                Range   Unique     Full

Time                    Scans    Scans    Scans

-------------------- -------- -------- --------

04-10-21 15                36       35        2

04-10-21 19                10        8        2

04-10-21 20                          8        2

04-10-21 21                          8        2

04-10-21 22                11        8        3

04-10-21 23                16       11        3

04-10-22 00                10        9        1

04-10-22 01                11        8        3

04-10-22 02                12        8        1

04-10-22 03                10        8        3

04-10-22 04                11        8        2

04-10-22 05                          8        3

04-10-22 06                          8        2

04-10-22 07                10        8        3

04-10-22 08                          8        2

04-10-22 09                          8        2

 

SQL object usage can also be summarized by day-of-the-week:

 

awr_sql_object_avg_dy.sql

 

 

col c1 heading ‘Object|Name’        format a30

col c2 heading ‘Week Day’           format a15

col c3 heading ‘Invocation|Count’   format 99,999,999

 

break on c1 skip 2

break on c2 skip 2

 

select

  decode(c2,1,'Monday',2,'Tuesday',3,'Wednesday',4,'Thursday',5,'Friday',6,'Saturday',7,'Sunday') c2,

  c1,

  c3

from

select

   p.object_name                       c1,

   to_char(sn.end_interval_time,'d')   c2,

   count(1)                            c3

from
SEE CODE DEPOT FOR FULL SCRIPTS

  dba_hist_sql_plan   p,

  dba_hist_sqlstat   s,

  dba_hist_snapshot  sn

where

  p.object_owner <> 'SYS' 

and

  p.sql_id = s.sql_id

and

  s.snap_id = sn.snap_id   

group by

   p.object_name,

   to_char(sn.end_interval_time,'d')

order by

  c2,c1

)

;

 

 

The output below shows the top objects within the database during each snapshot period.

 

 

                Object                          Invocation

Week Day        Name                                 Count

--------------- ------------------------------ -----------

Monday          CUSTOMER                                44

                CUSTOMER_ORDERS                         44

                CUSTOMER_ORDERS_PRIMARY                 44

                MGMT_CURRENT_METRICS_PK                 43

                MGMT_FAILOVER_TABLE                     47

                MGMT_JOB                               235

                MGMT_JOB_EMD_STATUS_QUEUE               91

                MGMT_JOB_EXECUTION                     235

                MGMT_JOB_EXEC_IDX01                    235

                MGMT_JOB_EXEC_SUMMARY                   94

                MGMT_JOB_EXEC_SUMM_IDX04                94

                MGMT_JOB_PK                            235

                MGMT_METRICS                            65

                MGMT_METRICS_1HOUR_PK                   43

 

Tuesday         CUSTOMER                                40

                CUSTOMER  _CHECK                         2

                CUSTOMER _PRIMARY                        1

                CUSTOMER_ORDERS                         46

                CUSTOMER_ORDERS_PRIMARY                 46

                LOGMNR_LOG$                              3

                LOGMNR_LOG$_PK                           3

                LOGSTDBY$PARAMETERS                      2

                MGMT_CURRENT_METRICS_PK                 31

                MGMT_FAILOVER_TABLE                     42

                MGMT_JOB                               200

                MGMT_JOB_EMD_STATUS_QUEUE               78

                MGMT_JOB_EXECUTION                     200

                MGMT_JOB_EXEC_IDX01                    200

                MGMT_JOB_EXEC_SUMMARY                   80

                MGMT_JOB_EXEC_SUMM_IDX04                80

                MGMT_JOB_PK                            200

                MGMT_METRICS                            48

 

Wednesday       CURRENT_SEVERITY_PRIMARY_KEY             1

                MGMT_CURRENT_METRICS_PK                 17

                MGMT_CURRENT_SEVERITY                    1

                MGMT_FAILOVER_TABLE                     24

                MGMT_JOB                               120

                MGMT_JOB_EMD_STATUS_QUEUE               46

                MGMT_JOB_EXECUTION                     120

                MGMT_JOB_EXEC_IDX01                    120

                MGMT_JOB_EXEC_SUMMARY                   48

                MGMT_JOB_EXEC_SUMM_IDX04                48

                MGMT_JOB_PK                            120

                MGMT_METRICS                            36

                MGMT_METRICS_1HOUR_PK                   14

                MGMT_METRICS_IDX_01                     24

                MGMT_METRICS_IDX_03                      1

                MGMT_METRICS_PK                         11

 

When these results are posted, the result is a well-defined signature that emerges for particular tables, access plans and SQL statements.  Most Oracle databases are remarkably predictable, with the exception of DSS and ad-hoc query systems, and the DBA can quickly track the usage of all SQL components.

 

Understanding the SQL signature can be extremely useful for determining what objects to place in the KEEP pool, and to determining the most active tables and indexes in the database.

 

 

The Ion tool is also excellent for identifying SQL to tune and it can show SQL execution over time with stunning SQL graphics.

 


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter