OracleBlog

Alternate title: Keeping Tables Small, Revisited

In an earlier article I spoke about how removing old data can help speed up table scans:

http://thinkoracle.blogspot.com/2005/08/keeping-tables-small.html

During a test in that article, I seemed to detect that querying a view composed of the 90/10 split of a large table was much faster than querying that table directly.

I was only trying to demonstrate that it wouldn't be much slower, I did not expect for it to be faster. I didn't pursue it at the time, but reproduced those results in 2 separate tests later on.

Incidentally, David Aldridge, who inspired my original article, has a theory on this:
http://oraclesponge.blogspot.com/2005/08/more-on-partition-not-quite-pruning.html

So the greater question was:
"How do you determine why a query is faster (or slower) than you expected?"

The first step is to use SQL Trace and TKProf:
http://download-west.oracle.com/
docs/cd/B10501_01/server.920/a96533/sqltrace.htm#1018

Note: there are MANY sources of information on this. Apart from the Oracle documentation, I also used articled by Tom Kyte, as well as his book "Expert One-on-One Oracle."

Here was my test.

1. Set some variables:

ALTER SESSION SET TIMED_STATISTICS=true;
ALTER SYSTEM SET MAX_DUMP_FILE_SIZE=1000;
ALTER SYSTEM SET USER_DUMP_DEST="C:/temp/trace";

2. Create the ReallyBigTable

CREATE TABLE ReallyBigTable AS SELECT * FROM ALL_OBJECTS;

3. Turn on tracing

ALTER SESSION SET SQL_TRACE = TRUE;

4. Run the query

SELECT SUM(object_id) FROM ReallyBigTable
WHERE object_id * 2 NOT IN
(SELECT object_id FROM ReallyBigTable);

Note: 00:44:50.07

5. Turn off tracing

ALTER SESSION SET SQL_TRACE = FALSE;

6. Run TKPROF (separate window)

TKPROF robert_ora_3236.trc robert_ora_3236.prf explain='sys/******** as sysdba'
- Save that file somewhere (it will be overwritten later)

7. Create Archive and Active tables.

CREATE TABLE ReallyBigTable_Archive AS SELECT * FROM ReallyBigTable
WHERE object_id < 40000;

CREATE TABLE ReallyBigTable_Active AS SELECT * FROM ReallyBigTable
WHERE object_id >= 40000;

8. Drop ReallyBigTable

DROP TABLE ReallyBigTable;

9. Create the view

CREATE VIEW ReallyBigTable AS
SELECT * FROM ReallyBigTable_Archive
UNION ALL
SELECT * FROM ReallyBigTable_Active;

10. Turn on Tracing

ALTER SESSION SET SQL_TRACE = TRUE;

11. Run the query again

SELECT SUM(object_id) FROM ReallyBigTable
WHERE object_id * 2 NOT IN
(SELECT object_id FROM ReallyBigTable);

Elapsed: 00:45:21.04

12. Turn off tracing

ALTER SESSION SET SQL_TRACE = FALSE;

13. Run TKPROF (separate window)

TKPROF robert_ora_3236.trc robert_ora_3236.prf explain='sys/******** as sysdba'

Conclusion:

I repeated the test 3 times with tracing on, and each time I could not reproduce the results. I saw virtually no difference in time elapsed between querying a big table, and querying a big table

So I guess we're left in the dark as to why querying the view was so much faster during my earlier tests. Perhaps we can apply Occam's Razor and the safest conclusion was simply that I goofed.

Either way, it made for an interesting article of how to generate performance data and query plans. I will leave you with an excerpt from the TKPROF output:

SELECT SUM(object_id) FROM ReallyBigTable
WHERE object_id * 2 NOT IN
(SELECT object_id FROM ReallyBigTable)

call     count       cpu    elapsed       disk      query
------- ------  -------- ---------- ---------- ----------
Parse        1      0.01       0.00          0          0
Execute      1      0.00       0.00          0          0
Fetch        2    612.03    2690.60   16168915   17030020
------- ------  -------- ---------- ---------- ----------
total        4    612.04    2690.60   16168915   17030020

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE 
  20495   FILTER  
  40764    TABLE ACCESS FULL REALLYBIGTABLE 
  20269    TABLE ACCESS FULL REALLYBIGTABLE 


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   SORT (AGGREGATE)
  20495    FILTER
  40764     TABLE ACCESS (FULL) OF 'REALLYBIGTABLE'
  20269     TABLE ACCESS (FULL) OF 'REALLYBIGTABLE'