We help IT Professionals succeed at work.

Question on Tkprof Output

chanikya
chanikya asked
on
Hi Experts,

I have attached the tkprof output of the following SQL statements.

I have few questions on this.

1) The explain plan provided in tkprof is actual plan( from v$sql_plan) or estimated plan?
2) I have seen the following out in the explain plan. what is meant by  CR,PR,PW etc..

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL TAB_TEST (cr=331 pr=0 pw=0 time=4843 us)
      1   SORT AGGREGATE (cr=165 pr=0 pw=0 time=2864 us)
  12633    TABLE ACCESS FULL TAB_TEST (cr=165 pr=0 pw=0 time=10 us)
3) hr.tab_test is created from dba_objects . I would like to know the recently created object. Such scenarios which is the best query out of these three.

SQL> select * from (select * from hr.tab_test  order by created desc) where rown
um<=1;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------

SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
--------- --------- ------------------- ------- - - -
HR
TAB_TEST
                                    16416          16416 TABLE
06-DEC-11 06-DEC-11 2011-12-06:10:48:59 VALID   N N N


SQL> select * from hr.tab_test where created=(select max(created) from hr.tab_te
st);

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------

SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
--------- --------- ------------------- ------- - - -
HR
TAB_TEST
                                    16416          16416 TABLE
06-DEC-11 06-DEC-11 2011-12-06:10:48:59 VALID   N N N


SQL> select * from hr.tab_test a where 1=(select count(1) from hr.tab_test b whe
re b.created>=a.created);

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------

SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
--------- --------- ------------------- ------- - - -
HR
TAB_TEST
                                    16416          16416 TABLE
06-DEC-11 06-DEC-11 2011-12-06:10:48:59 VALID   N N N


SQL>

Open in new window


 query.txt

Thanks In Advance.

Thanks,
Chanikya.
Comment
Watch Question

>>>1) The explain plan provided in tkprof is actual plan( from v$sql_plan) or estimated plan?
This is the actual plan which is in your trace file .

>>>2) ....

I think you are looking into the raw file of the trace , you need to format it first for more information you can look into the below links:

Author

Commented:
Hi slobaray,

It is not raw trace file.

The output is generated from the following query.

C:\Users\test>tkprof C:\oraclexe\app\oracle\admin\XE\udump\orcl_ora_6780
.trc   d:\query.txt explain=sys/oracle as sysdba sys=no

So, it is defnitely not raw file.

In those three links i did n't get the information for my second question.

Probably, i may need to add few more parameters to the tkprof to convert it into a more readable form. I am sorry, i did not see any thing related to my 2nd question.



Thanks,
Rama.
You try to convert it again as per my first post and the meaning for your 2nd question are as follows:

cr = Consistent Reads
pr = Physical Reads
pw = Physical Writes
time = time taken by this step in microseconds
cost = cost incured by the step
size = represent the size of data in that step
card = cardinality

You can get details on explain plan and trace file and there use from OTN.
For your 3rd question :

You 2nd query is the best one as per me. For more details you can check the attached file based on the explain plan.

Best Query:

SQL> select * from hr.tab_test where created=(select max(created) from hr.tab_test);

 Query-plan.docx

Author

Commented:
Now i understand it