Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Need help with explain plan and tkrprof..

Need help with explain plan and tkrprof..

From: news.verio.net <kennylim_at_techie.com>
Date: 2000/03/23
Message-ID: <iCBC4.19318$AT6.23987@dfw-read.news.verio.net>#1/1

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 Thu Mar 23 2000 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US