Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Need help with explain plan and tkrprof..
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
------- ------ -------- ---------- ---------- ---------- ---------- ------
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
------- ------ -------- ---------- ---------- ---------- ---------- ------
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