OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » tkprof question (10gr2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

tkprof question [message #460538]

Fri, 11 June 2010 19:09

faq_dba
Messages: 8
Registered: April 2009

Junior Member

How can i differentiate between system issued sql's and user issued sql's in the tkprof report ?

Report message to a moderator

Re: tkprof question [message #460539 is a reply to message #460538]

Fri, 11 June 2010 19:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>How can i differentiate between system issued sql's and user issued sql's in the tkprof report ?
Please realize that as a general rule TKPROF processes a trace file from a single session/user.
As such there is no "system issued sql" involved.
I assume you did not mean SQL initiated by the SYSTEM user.
I am not clear exactly what "system issued SQL" actually is.
Are you referring to SQL invoked by Oracle background sessions?
Please clarify.

Report message to a moderator

Re: tkprof question [message #460541 is a reply to message #460539]

Fri, 11 June 2010 19:56

faq_dba
Messages: 8
Registered: April 2009

Junior Member

Swan,
Sorry abt the confusion..
At the end of the report it say's
19 user SQL statements in trace file.
69 internal SQL statements in trace file.
How can i differentiate between internal/user issued sql statements ?

Report message to a moderator

Re: tkprof question [message #460544 is a reply to message #460541]

Fri, 11 June 2010 20:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

If you used COPY & PASTE then we could know exactly what you are looking at instead of trying to guess or speculate.

Do you know what RECURSIVE SQL is?
I suspect it is the same as "internal SQL"

Let me give you a simple example.
Let's say I issue the following SQL statement.

SELECT * FROM EXPLAIN_PLAN;

Now "EXPLAIN PLAN" is really a SYNONYM.
So the SQL Engine must issue another query to lookup to what the synonym refers. Then try to issue the substituted SQL.
Similar activity occurs when querying a VIEW.
Every SQL within the TKPROF report is SQL either directly issued by you or other SQL that needs to occur to produce the results you requested.
You can't tune or control the internal or recursive SQL.
You can only attempt to tune the SQL directly issued by you.

Report message to a moderator

Re: tkprof question [message #460551 is a reply to message #460544]

Fri, 11 June 2010 22:41

faq_dba
Messages: 8
Registered: April 2009

Junior Member

Blackswan,

I think you havent got my question.
Let me put in another way.
Suppose i am tracing a sys session for watever reason..now sys can also execute queries on x$ and other internal tables.

Now i just want to get queries which were executed by sys ONLY and not the queries which got internally to get the results.
How can i do that ?

Thanks for taking time in answering my question..!

Report message to a moderator

Re: tkprof question [message #460553 is a reply to message #460551]

Fri, 11 June 2010 22:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I think you havent got my question.
I apologize for being dense.

Do NOT tell, describe or explain. SHOW us.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

If you used COPY & PASTE then we could know exactly what you are looking at instead of trying to guess or speculate.

Report message to a moderator

Previous Topic:	Rule based tips still valid in 11g
Next Topic:	Merge Performance (wide open question here)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 07:01:32 CST 2024