Re: Need help with Explain Plan and tkprof..
Date: Tue, 28 Mar 2000 21:53:17 -0700
Message-ID: <10gE4.261$3i1.12397_at_wormhole.dimensional.com>
I've been away for a week and am just catching up to the group. I'm surprised nobody answered this (on the other hand I can't fully answer it either). Your autotrace shows that (as expected) a full scan is looking at 99,999 rows. That is the number you should be seeing in the tkprof explain plan as well. You have everything set up correctly that I can remember. The only other thing to watch out for is to issue the tkprof command passing in the same user as the one that ran the statement being traced. So if you log into SQL*Plus as scott/tiger and do your two alter sessions, then issue the SQL statement(s) and then exit the session, your statements should be traced. You go to the udump directory as issue the statement like this:
tkprof ######.trc mytrace.txt explain=scott/tiger sys=no
That should do it. If that's not it, then the best I can suggest is doing a deja news search of usenet archives and maybe speak with Oracle support.
You mentioned that you analyzed the indexes, but there can't possibly be an index on the "state" column since it would have been used when you asked for the rows where state = 'ok'. Not only that, but even with a full table scan in that instance, it should have brought back that one row. Something's not right. The data seems to be there (99,999 rows processed), but I'm not sure the value in state is actually 'ok', it might be 'OK' or something you weren't expecting.
The only other thing I can see is that you're allowing the CHOOSE optimizer option. I seem to remember that there a various scenarios and maybe even init.ora settings that negate the CBO being used. You might try issuing your SQL statements with a CBO-forcing hint, like "select /*+ FIRST_ROWS */ ..."
Hope I'm not missing something obvious.
best 'o luck,
- bill c.
"news.verio.net" <kennylim_at_techie.com> wrote in message
news:yCBC4.19319$AT6.24278_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
> ------- ------ -------- ---------- ---------- ---------- ---------- ----
--Received on Wed Mar 29 2000 - 06:53:17 CEST
> ----
> 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-
>
>
>
>
>
>
>
>
>