by 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-