Re: Need help with explain plan and tkrpof ..

Kenny,

the expression is
tkprof infile outfile explain=user/password [system=yes if you eant recursive sql, too]
Make sure the explain plan table is created; run utlxplan.sql

In the traced session, you do not do an explain plan, just execute the query

--
Kind Regards,
Frank
"news.verio.net" <kennylim_at_techie.com> wrote in message news:%BBC4.19317$AT6.24303_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 - 18:50:17 CST