Question on Tkprof Output

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.
chanikyaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Swadhin Ray Commented:
>>>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:

chanikyaAuthor 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.
Swadhin Ray Commented:
This link might help you :

http://www.lab128.com/lab128_rg/html/explain_plan.html

Check this ..
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Swadhin Ray Commented:
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.
Swadhin Ray Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chanikyaAuthor Commented:
Now i understand it
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.