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:

 
SQL Tuning with temporary tables
 
One of the shortcomings of relational databases is their inability to create intermediate result sets.  One technique that is used within all relational databases is the creation of interim (temporary) tables to improve the speed of their SQL queries. 
 
The Oracle database codified this approach starting and Oracle8i with their global temporary tables construct and we will be doing an exercise later in this tutorial so that you can understand firsthand how you can replace complex subqueries with temporary tables in order to improve the speed and readability of certain queries.
 
Temporary tables are generally used as a mechanism to pre-join tables, or pre-aggregate intermediate data.  As such, the use of temporary tables is more effective in highly normalized databases.  A data warehouse with lot’s of denormalization may not benefit from queries that use temporary tables.
 
The prudent use of temporary tables can dramatically improve Oracle SQL performance. To illustrate the concept, consider the following example from the DBA world. In the query that follows, we want to identify all users who exist within Oracle who have not been granted a role. We could formulate the query as an anti-join with a noncorrelated subquery as shown here:
 
select
   username
from
   dba_users
where
   username NOT IN
      (select grantee from dba_role_privs);
 
On a large database, this query runs in about 18 seconds. Now, we rewrite the same query to utilize temporary tables by selecting the distinct values from each table.
 
drop table temp1;
drop table temp2;

create table
   temp1
as
  select
      username
   from
      dba_users;

create table
   temp2
as
  select distinct
      grantee
   from
      dba_role_privs;

select
   username
from
   temp1
where
   username not in
      (select grantee from temp2);
 
With the addition of temporary tables to hold the intermediate results, this query runs in less than three seconds, a 6-fold performance increase. Again, it is not easy to quantify the reason for this speed increase, since the DBA views do not map directly to Oracle tables, but it is clear that temporary table show promise for improving the execution speed of certain types of Oracle SQL queries.
 
Temporary tables are also useful in cases where we need to compare two ranges of dates in a single table.  For example, consider the following STATSPACK query.
 
 
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
 
 
By the way, this is a very important Oracle query because it can be used to show the database size change.  Here is an example of the output:
 
                              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
 
 
However, this report is not terribly useful if the SQL takes 20 hours to complete! The savvy database professional will use temporary tables to extract the summaries of the data ranges and make our SQL run up to 30 times faster.
 
In sum, the use of temporary tables to extract intermediate row sets can make a dramatic difference in SQL execution times.


 

     

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.