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

 

 


 

 

 

 

 

 

 

The Self-Join

Oracle Tips by Burleson Consulting

A self-join is a condition where a table is joined against itself. This is commonly done when you want to compare two time-based values within a table to see the differences between the two times (Figure 16-3).

Figure 3: A self-join for a STATSPACK table

The STATSPACK tables are an excellent example of time-based tables. Each snapshot has a date column, and you can write STATSPACK queries to compare values between STATSPACK snapshots. In the following example, we join the stats$sysstat table against itself to display times where we have more than 100 disk sorts per hour:

prompt
prompt
prompt ***********************************************************
prompt  When there are high disk sorts, you should investigate
prompt  increasing sort_area_size, or adding indexes to force
prompt  index_full scans.
prompt
prompt ***********************************************************


column sorts_memory  format 999,999,999
column sorts_disk    format 999,999,999
column ratio format .9999999999999

select
   to_char(snap_time,'dd Mon HH24:mi:ss') mydate,
   newmem.value-oldmem.value sorts_memory,
   newdsk.value-olddsk.value sorts_disk,
   (newdsk.value-olddsk.value)/(newmem.value-oldmem.value) ratio
from
   perfstat.stats$sysstat oldmem,
   perfstat.stats$sysstat newmem,
   perfstat.stats$sysstat newdsk,
   perfstat.stats$sysstat olddsk,
   perfstat.stats$snapshot   sn
where
-- Where there are more than 100 disk sorts per hour
   newdsk.value-olddsk.value > 100
and
   sn.snap_id = (select max(snap_id) from stats$snapshot)
and
   newdsk.snap_id = sn.snap_id
and
   olddsk.snap_id = sn.snap_id-1
and
   newmem.snap_id = sn.snap_id
and
   oldmem.snap_id = sn.snap_id-1
and
   oldmem.name = 'sorts (memory)'
and
   newmem.name = 'sorts (memory)'
and
   olddsk.name = 'sorts (disk)'
and
   newdsk.name = 'sorts (disk)'
;

This report is commonly used to show periods of high sort activity to alert the Remote DBA to check the SQL in the stats$sql_summary table for the period of high disk sorts. Here is an actual sample from a production database.

TO_CHAR(SNAP_ SORTS_MEMORY   SORTS_DISK   RATIO
------------- ------------ ------------ ---------------
2001-04-09 09     60,404     141  .0023342824978
2001-04-09 14     23,520     251  .0106717687075
2001-04-09 15     17,980     402  .0223581757508
2001-04-10 11     26,451     149  .0056330573513
2001-04-10 14     36,313   2,794  .0769421419326
2001-04-10 16     17,995     132  .0073353709364
2001-04-11 13     22,057     177  .0080246633722
2001-04-11 15     20,274     170  .0083851238039
2001-04-12 14     21,540   2,056  .0954503249768

When joining a table against itself, Oracle normally invokes a nested loops access. Here is the execution plan for the self-join. Note the use of the NESTED LOOPS table access method for each self reference.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
                                                                    25
  NESTED LOOPS
                                                                     1
    NESTED LOOPS
                                                                     1
      NESTED LOOPS
                                                                     1
        NESTED LOOPS
                                                                     1
          TABLE ACCESS
BY INDEX ROWID                 STATS$SNAPSHOT                        1
            INDEX
RANGE SCAN                     STATS$SNAPSHOT_PK                     1
              SORT
AGGREGATE                                                            1
                INDEX
FULL SCAN (MIN/MAX)            STATS$SNAPSHOT_PK                     1
          TABLE ACCESS

BY INDEX ROWID                 STATS$SYSSTAT                         2
            INDEX
RANGE SCAN                     STATS$SYSSTAT_PK                      1
        TABLE ACCESS
BY INDEX ROWID                 STATS$SYSSTAT                         2
          INDEX
RANGE SCAN                     STATS$SYSSTAT_PK                      1
      TABLE ACCESS
BY INDEX ROWID                 STATS$SYSSTAT                         2
        INDEX
RANGE SCAN                     STATS$SYSSTAT_PK                      1
    TABLE ACCESS
BY INDEX ROWID                 STATS$SYSSTAT                         2
      INDEX
RANGE SCAN                     STATS$SYSSTAT_PK                      1

Despite the complex look of this execution plan, this query runs quite quickly. Also, note the relatively rare INDEX FULL SCAN (MIN/MAX) table access method. In this query, the (MIN/MAX) is used because we are scanning all snapshots within the stats$snapshot table.

Next, let's look at a special case of a table join where we use a non-correlated subquery to filter  the selection criteria for the main table. This type is query is known as the anti-join.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


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