Wednesday, September 14, 2005

Analyzing Query Performance

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'

Comments:
max_dump_file_size seems a little "conservative" there Robert ;)

I'm enjoying using:

alter session set tracefile_identifier='some_string_or_other';

... at the moment. It seems that you could also wrap up this series of commands in a neat little procedure also.
 
The part about not being able to SQL trace a raw password value is not exactly true:

PARSING IN CURSOR #1 len=73 dep=0 uid=39 oct=47 lid=39 tim=444650244946 hv=2365598651 ad='8b806280'
begin
execute immediate 'create user feeby identified by mypass';
end;
END OF STMT
PARSE #1:c=10000,e=6157,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=444650244935
BINDS #1:
XCTEND rlbk=0, rd_only=1

Caveat emptor.
 
This comment has been removed by a blog administrator.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?