Re: sql trace questions - help

1 This is natural, your statement and the statement results are probably in SGA the second and third time and either are retrieved faster or are not retrieved at (second parse) because the results are there You always should use tkprof to format your trace files, with the option explain=<username/password of user running the statements. This brings me to remark
2 The efficiency of a query is mainly determined by means of the EXPLAIN PLAN results gotten by your tkprof session. It will also show you how many rows are retrieved exactly in every step and whether or not all your join conditions are being used properly. Oracle can join unrelated tables. 3 I would recommend Guy Harrison Oracle SQL High-performace tuning. Prentice Hall, ISBN 0-13-614231-1. According to my copy it costs 49.95 bucks 4 If you send us some background and most important the explain plan we could have a look into it. Also specify your init.ora optimizer_mode and whether or not your tables have been analyzed. If optimizer_mode=choose and there are no statistiscs on the involved tables the rule based optimizer will be used.

Hth,

Sybrand Bakker, Oracle DBA
kal121_at_yahoo.com wrote:

> Everytime I run SQL Trace on the SAME sql statement I get back different
> results, for example see below (below paragraph below).
>
> Why is this? How can anyone accurately interpret there results??? What is the
> point? I am going crazy here. Please respond. How can I tell what is an
> efficient query vs. an inefficient one? Are there any good books on the
> subject? Please don't refer me to "Oracle Performance Tuning" by Corrigan and
> Gurry. I already own this book, it doesn't help - too vague. Or the "DBA
> Handbook" by Kevin Loney. Aside from paying a consultant $100 an hour, how
> can I learn these things on my own? I have literally wasted 8 hours today
> trying to understand this stuff, and am frustrated!
>
> Thank you.
> Please respond to my email address.
>
> First Run:
>
> call count cpu elapsed disk query current rows ------- ------
> -------- ---------- ---------- ---------- ---------- ---------- Parse 1
> 0.13 0.15 3 0 3 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00
> 0.02 5 6 0 0 ------- ------ -------- ---------- ---------- ----------
> ---------- ---------- total 3 0.13 0.17 8 6 3 0
>
> Second Run:
>
> call count cpu elapsed disk query current rows ------- ------
> -------- ---------- ---------- ---------- ---------- ---------- Parse 1
> 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 5 0.01
> 0.01 0 168 0 52 ------- ------ -------- ---------- ---------- ----------
> ---------- ---------- total 7 0.01 0.01 0 168 0 52
>
> Third run:
>
> call count cpu elapsed disk query current rows ------- ------
> -------- ---------- ---------- ---------- ---------- ---------- Parse 2
> 0.02 0.02 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 10 0.01
> 0.01 0 336 0 104 ------- ------ -------- ---------- ----------
> ---------- ---------- ---------- total 14 0.03 0.03 0 336 0 104
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Received on Tue Dec 15 1998 - 16:38:21 CST