Sv: Need help with explain plan and tkrprof..

Hi, It seems to me you are running the trace on the "explain plan" statement in stead of on the actual sql statement.

Regards,
Hans Henrik

news.verio.net <kennylim_at_techie.com> skrev i en nyhedsmeddelelse:iCBC4.19318$AT6.23987_at_dfw-read.news.verio.net...
> 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 ?
>
>
> explain plan for
> select * from skewed
> where state = 'tx'
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----
 --
> ----
> Parse 1 0.00 0.01 0 0 0
> 0
> Execute 1 0.01 0.02 0 0 0
> 0
> Fetch 0 0.00 0.00 0 0 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ---------- ----
 --
> ----
> total 2 0.01 0.03 0 0 0
> 0
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 34
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 0 TABLE ACCESS FULL SKEWED
>
>
> Test 2:
>
> This problem is also the same on my second test, shouldn't this at least
> fetch 1 row of data instead of 0 ?
>
> explain plan for
> select * from skewed
> where state = 'ok'
>
>
> --test 2--
>
> explain plan for
> select * from skewed
> where state = 'ok'
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----
 --
> ----
> Parse 1 0.01 0.02 0 0 0
> 0
> Execute 1 0.01 0.01 0 0 0
> 0
> Fetch 0 0.00 0.00 0 0 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ---------- ----
 --
> ----
> total 2 0.02 0.03 0 0 0
> 0
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 34
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 0 TABLE ACCESS FULL SKEWED
>
>
> If I set autotrace on in SQL*PLUS, I seem to receive the corrrect
> information.
>
>
> set autotrace on
>
> select * from skewed
> where state = 'tx'
>
>
> Autotrace result for 'tx':-
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=24 Card=100000 Bytes
> =600000)
>
> 1 0 TABLE ACCESS (FULL) OF 'SKEWED' (Cost=24 Card=100000 Bytes
> =600000)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 5 db block gets
> 6810 consistent gets
> 0 physical reads
> 0 redo size
> 3954287 bytes sent via SQL*Net to client
> 740601 bytes received via SQL*Net from client
> 6670 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 99999 rows processed
>
>
> I might had probably missed out integral parameter or session settings or
> did not understand the result.
>
> Any pointers to this problem would be greatly appreciated.
>
> Thanks in advance and you have a pleasant evening.
>
> Kenny-
>
>
>
>
>
>
>
>
>
Received on Fri Mar 24 2000 - 00:00:00 CST