Link to home
Start Free TrialLog in
Avatar of Oranew
Oranew

asked on

Oracle execute vs Parse

I like to test the number of executions to the number of parses for a sql statement.
I like to test how long the sql will remain in the cache(sga) and it executed without hard parse.

How can I test this in 11g rel 2?
Avatar of Sean Stuber
Sean Stuber

there is no hard timing rule for a query to remain in the shared pool.

if you never run another query, it could stay in there forever.

if you parse many queries, it could get aged out in a few minutes.


Look in v$sql to monitor
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Oranew

ASKER

Hi Slightwv,
Thanks for your comments. Here test mean my sql query where I can use it for a test scenario.
I will have a sql statement say "select * from ........where ....." ..Let us called as a test SQL.
and I will run the same statement for my test several times say like 10 times.
Now, I like to prove that this statement (test SQL ) executed 10 times but parsed only once.
How can I prove this in my development environment?
>>How can I prove this in my development environment?

Look at the columns I specified above in the V$SQL view.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Oranew

ASKER

Hi Geert,
Very good question if I am using binds or not.  I have cursor_sharing=exact (init.ora) and Peoplesoft application should have logic to use bind variables but my parse to execute ratio in the awr report is always less than 5%. Very frustrating and never seen going above 10%.
What other parameters I can check ?
>>What other parameters I can check ?

I would look at the SQL that has a similar parse and execute counts to see what they are.

Since it is PeopleSoft, you can't change anything in how the SQL is coded.

Look at the shared pool.  That might help a little but probably not that much.

I haven't been around PeopleSoft for many years.  It might just be normal behavior to have a low ratio.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Oranew

ASKER

I've requested that this question be deleted for the following reason:

I don't see any response yet
>>I don't see any response yet

What response were you expecting?

The question on number of executions to the number of parses was answered.  I think we also answered the question about the cache.
Avatar of Oranew

ASKER

My bad.  I will take it back