Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Tuning SQL with Global Temporary Tables

Don Burleson
 

Now that we have covered basic SQL tuning with temporary tables, let's look at tuning aggregate queries with temporary tables. These types of queries that summarize and compare ranges of values within temporary tables will run far faster if we create intermediate tables for the query.

For tuning the underlying temporary tables in SQL, see these important notes on tuning execution plans for global temporary table and WITH clause materializations.

12c note:  Starting in 12c, Oracle will allow you to invoke session-level dbms_stats to gather statistics specific to your own global temporary table.  Prior to 12c, statistics were shared from a master copy of the CBO statistics.

See here for a global temporary table example.

Tuning Aggregation Queries with Temporary Tables

In addition to data dictionary queries, temporary table can dramatically improve the performance of certain SQL self-join queries that summarize data values.

For example, consider a query that examines the stats$tab_stats STATSPACK extension table. Rows for the stats$tab_stats table are collected weekly, and one row in this table exists for each table in the schema. This STATSPACK extension table is loaded by collecting the table_name and the bytes consumed by the table (from the dba_segments view), and we want to use this table-level detail data to summarize our overall size change per week.

Since each row of this table contains a date (snap_time), a table name (table_name), and the number of bytes, we need a query that sums up the total size for all tables for one week, and then compares that value to the overall size for the following week.

Essentially, we could formulate this comparison of summaries as a single query that summarizes each range or rows.

select distinct
   to_char(old_size.snap_time,'yyyy-mm-dd'), -- The old snapshot date
   sum(old_size.bytes),
   sum(new_size.bytes),
   sum(new_size.bytes) - sum(old_size.bytes)
from
   stats$tab_stats old_size,
   stats$tab_stats new_size
where
   -- This is the highest date in the table
   new_size.snap_time = (select max(snap_time) from stats$tab_stats)
and
   -- This is the prior weeks snapshot
   old_size.snap_time = (select min(snap_time)-7 from stats$tab_stats)
group by
   to_char(old_size.snap_time,'yyyy-mm-dd')
;

Here is the execution plan for this query. Because we are summing and comparing ranges of values within the same table, we see the dreaded MERGE JOIN CARTESIAN access method. As you know, a Cartesian merge join can run for hours because the Cartesian products of the tables must be derived.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
                                                                     5
  SORT
GROUP BY                                                             1
    MERGE JOIN
CARTESIAN                                                            1
      TABLE ACCESS
BY INDEX ROWID                 STATS$TAB_STATS                       1
        INDEX
RANGE SCAN                     TAB_STAT_DATE_IDX                     1
          SORT
AGGREGATE                                                            1
            INDEX
FULL SCAN (MIN/MAX)            TAB_STAT_DATE_IDX                     1
      SORT
JOIN                                                                 2
        TABLE ACCESS
BY INDEX ROWID                 STATS$TAB_STATS                       1
          INDEX
RANGE SCAN                     TAB_STAT_DATE_IDX                     1
            SORT
AGGREGATE                                                            1
              INDEX
FULL SCAN (MIN/MAX)            TAB_STAT_DATE_IDX                     1

 

Take a close look at the execution plan for this query, and carefully review the steps:

  1. We begin with an index range scan to get the maximum date in the stats$tab_stats table.
  2. Next, we perform an index range scan to get the sysdate–7 value.
  3. Once we have the target data values, we must perform a Cartesian merge join to access the rows and resolve the query.

This query gets tricky where we must compare a range of common date with another range of common dates. As you can see, these range comparison queries are hard to formulate and hard for Oracle to optimize.

To see an alternative query, let's look at an equivalent query that utilizes temporary tables. This report uses the STATSPACK extension tables for objects to prepare weekly growth reports. The rpt_object_stats.sql script is a very useful STATSPACK report that approximates the overall growth of the database over the past week. The DBA can quickly compare table and index counts, and see the total growth for table and indexes over the past week.

Let's take a closer look at each section of the report and then see how to formulate the query.

Elapsed-Time Section of the Date Range Report

The first section of the report identifies the snapshots that are used in the comparison. The script identifies the most recent snapshot and compares it to the n–1 snapshot.

The next section shows the total counts of tables and indexes in the database. This is a very useful report for the DBA to ensure that no new objects have migrated into the production environment. We also see the total bytes for all tables and indexes and the size change over the past week. Here is the section of the report that shows the total growth of tables and indexes for the past week:

Mon Apr 22                                                  page    1
                  Most recent database object counts and sizes
 
DB_NAME TAB_COUNT IDX_COUNT       TAB_BYTES        IDX_BYTES
---------------- --------- ---------------- ----------------
prodzz1      451       674      330,219,520      242,204,672
        -------- --------- ---------------- ----------------
Total        451       674      330,219,520      242,204,672
 
Mon Jan 22                                                  page    1
                              Database size change
                    comparing the most recent snapshot dates
 
DB_NAME       OLD_BYTES        NEW_BYTES           CHANGE
--------- ------------- ---------------- ----------------
prodzz1     467,419,136      572,424,192      105,005,056
          ------------- ---------------- ----------------
Total       467,419,136      572,424,192      105,005,056

This is a very sophisticated DBA report, and one that can run for many hours without the use of temporary tables because of Oracle's use of the CARTESIAN access method. However, with the use of temporary tables, the table and index counts can be summarized and saved in the temp tables for fast analysis. We also use the same technique to sum the number of bytes in all tables and indexes into temporary tables, and then quickly interrogate the summary tables for total sizes of our database.

The Report Generation SQL Script

Here is the section of code that computes the date ranges and computes the total table and index counts and bytes. While this query is more verbose than our original query, it runs more than 100 times faster than our first query.

rpt_object.sql

 
set lines 80;
set pages 999;
set feedback off;
set verify off;
set echo off;
 
--*********************************************************
-- This report compares the max(snap_time) to the second-highest date
--*********************************************************
 
--*********************************************************
-- First we need to get the second-highest date in tab_stats
--*********************************************************
drop table d1;
 
create table d1 as
select distinct
   to_char(snap_time,'YYYY-MM-DD') mydate
from
   stats$tab_stats
where
   to_char(snap_time,'YYYY-MM-DD') <
    (select max(to_char(snap_time,'YYYY-MM-DD')) from stats$tab_stats)
;
 
--*********************************************************
-- The second highest date is select max(mydate) from d1;
--*********************************************************
 
set heading off;
 
prompt '*********************************************'
select '  Most recent date '||
          max(to_char(snap_time,'YYYY-MM-DD'))
from stats$tab_stats;
select '  Older date '||
          max(mydate)
from d1;
prompt '*********************************************'
 
set heading on;
 
drop table t1;
drop table t2;
drop table t3;
drop table t4;
 
--  ******************************************************************
--  Summarize the counts of all tables for the most recent snapshot
--  ******************************************************************
 
create table t1 as
select db_name, count(*) tab_count, snap_time from stats$tab_stats
where    to_char(snap_time, 'YYYY-MM-DD') =
           (select max(to_char(snap_time,'YYYY-MM-DD'))
               from stats$tab_stats)
group by db_name, snap_time;
 
--  *****************************************************************
--  Summarize the counts of all indexes for the most recent snapshot
--  *****************************************************************
 
create table t2 as
select db_name, count(*) idx_count, snap_time from stats$idx_stats
where    to_char(snap_time, 'YYYY-MM-DD') =
           (select max(to_char(snap_time,'YYYY-MM-DD'))
                from stats$idx_stats)
group by db_name, snap_time;
 
--  *****************************************************************
--  Summarize sum of bytes of all tables for the 2nd highest snapshot
--  *****************************************************************
 
create table t3 as
select db_name, sum(bytes) tab_bytes, snap_time from stats$tab_stats
where    to_char(snap_time, 'YYYY-MM-DD') =
           (select max(to_char(snap_time,'YYYY-MM-DD'))
                from stats$tab_stats)
group by db_name, snap_time;
 
--  *****************************************************************
--  Summarize sum of bytes of all indexes for the 2nd highest snapshot
--  *****************************************************************
 
create table t4 as
select db_name, sum(bytes) idx_bytes, snap_time from stats$idx_stats
where    to_char(snap_time, 'YYYY-MM-DD') =
           (select max(to_char(snap_time,'YYYY-MM-DD'))
              from stats$idx_stats)
group by db_name, snap_time;
 
--*********************************************************
-- This report displays the most recent counts & size totals
--*********************************************************
 
column tab_bytes format 999,999,999,999
column idx_bytes format 999,999,999,999
column tab_count format 99,999
column idx_count format 99,999
 
clear computes;
compute sum label "Total" of tab_count on report;
compute sum label "Total" of idx_count on report;
compute sum label "Total" of tab_bytes on report;
compute sum label "Total" of idx_bytes on report;
 
break on report;
 
ttitle 'Most recent database object counts and sizes'
 
select
   a.db_name,
   tab_count,
   idx_count,
   tab_bytes,
   idx_bytes
from
   perfstat.t1 a,  -- table counts
   perfstat.t2 b,  -- index counts
   perfstat.t3 c,  -- all table bytes
   perfstat.t4 d   -- all index bytes
where
   a.db_name = b.db_name
and
   a.db_name = c.db_name
and
   a.db_name = d.db_name
;
 
 
 
--*********************************************************
-- These temp tables will compare size growth since last snap
--*********************************************************
drop table t1;
drop table t2;
drop table t3;
drop table t4;
 
create table t1 as
select db_name, sum(bytes) new_tab_bytes, snap_time
     from stats$tab_stats
where    to_char(snap_time, 'YYYY-MM-DD') =
           (select max(to_char(snap_time,'YYYY-MM-DD'))
               from stats$tab_stats)
group by db_name, snap_time;
 
create table t2 as
select db_name, sum(bytes) new_idx_bytes, snap_time
      from stats$idx_stats
where    to_char(snap_time, 'YYYY-MM-DD') =
           (select max(to_char(snap_time,'YYYY-MM-DD'))
               from stats$idx_stats)
group by db_name, snap_time;
 
create table t3 as
select db_name, sum(bytes) old_tab_bytes, snap_time
      from stats$tab_stats
where    to_char(snap_time, 'YYYY-MM-DD') =
           (select max(mydate) from d1)
group by db_name, snap_time;
 
create table t4 as
select db_name, sum(bytes) old_idx_bytes, snap_time
       from stats$idx_stats
where    to_char(snap_time, 'YYYY-MM-DD') =
           (select max(mydate) from d1)
group by db_name, snap_time;
 
 
 
--*********************************************************
-- This is the size comparison report
--*********************************************************
column old_bytes format 999,999,999,999
column new_bytes format 999,999,999,999
column change    format 999,999,999,999
 
compute sum label "Total" of old_bytes on report;
compute sum label "Total" of new_bytes on report;
compute sum label "Total" of change    on report;
 
break on report;
ttitle 'Database size change|comparing most recent snapshot dates';
 
 
select
   a.db_name,
   old_tab_bytes+old_idx_bytes old_bytes,
   new_tab_bytes+new_idx_bytes new_bytes,
   (new_tab_bytes+new_idx_bytes)-(old_tab_bytes+old_idx_bytes) change
from
   perfstat.t1 a,
   perfstat.t2 b,
   perfstat.t3 c,
   perfstat.t4 d
where
   a.db_name = b.db_name
and
   a.db_name = c.db_name
and
   a.db_name = d.db_name
;

Upon close examination, we see that we create temporary tables to hold the total counts, and we also create two temporary tables to hold the sum of bytes for each table and index. Once the sums are precalculated, it becomes fast and easy for Oracle SQL to compute the total bytes for the whole database.

The popular Ion tool is the easiest way to analyze Oracle table behavior (average RAM concurrency wait time as shown above)  and Ion allows you to spot hidden table-related performance trends.  Ion is our favorite Oracle tuning tool, and the only 3rd party tool that we use.

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational