Re: Need help with explain plan and tkrprof..

A copy of this was sent to "news.verio.net" <kennylim_at_techie.com> (if that email address didn't require changing) On Thu, 23 Mar 2000 19:51:40 -0800, you wrote:

>Hi All,
>
>I am just a beginner and are in the process of trying figure out in how to
>use/understand the explain plan and tkrpof in Oracle 8.15.
>
>I had performed a very simple case scenario to test it and so far, had not
>received any rows fetched from the tkrprof results generated. Can anyone
>give me some pointers as to why I am not receiving the numbers of rows
>being fetched when I generated the result of the explain plan using tkprof ?
>Is the result generated correct or I am not interpreting the result
>generated correctly ?
>
>Enclosed here is my environment setting for my instance, session level and
>the sample test.
>
>init.ora parameter file :
>
>audit_trail = true
>timed_statistics = true
>max_dump_file_size = 10240
>user_dump_dest = C:\Servers\Oracle\admin\KENNYL\udump
>
>Session level :
>
>Alter session set sql_trace = true;
>Alter session set timed_statistics = true;
>
>
>I had also analyzed (Compute statistics) all the table and indexes before
>running the test.
>
>Sample Test :
>
>skewed table :-
>
>Name Null? Type
>State varchar2(2)
>
>(This table comprise of 10000 rows of data)
>
>99999 is 'tx'
>1 is 'ok'
>
>If I run a simple select statement on both the table, it will yield the
>following result.
>
>select * from skewed where state = 'tx' --this will return 99999 rows of
>data--
>
>select * from skewed where state = 'ok' --this will return 1 rows of data--
>
>
>Test 1 :
>
>explain plan for
>select * from skewed
>where state = 'tx'
>
>tkprof ora00283.trc ora00283.txt (Changed the output file to *.txt extension
>instead of *.prc)
>
>This are the following result that I am receiving.., shouldn't I receive
>99999 numbers of rows
>being fetched in the result instead of 0 ?
>
>

No -- sql_trace "watches" statements for you and tells you how they performed. In this case, sql_trace told you about the performance of the EXPLAIN PLAN sql statement. EXPLAIN doesn't actually RUN the query -- it just develops a query plan and lets you see it.

What you want to do is:

SQL> alter session set sql_trace=true;
SQL> alter session sql timed_statistics=true;
SQL> select * frm skewed where state = 'tx';
SQL> exit

and then run tkprof on the resulting trace file. that way sql_trace was watching a SELECT, not an EXPLAIN...
>
>Kenny-
>
>
>
>
>
>
>
>

-- 
http://osi.oracle.com/~tkyte/
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation