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 support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
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

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:

We begin with an index range scan to get the maximum date in the stats$tab_stats table.
Next, we perform an index range scan to get the sysdate–7 value.
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.

��