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

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

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.

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
slightwv (䄆 Netminder) Commented:
V$SQL has the following columns:
PARSE_CALLS - Number of parse calls for this child cursor
EXECUTIONS - Number of executions that took place on this object since it was brought into the library cache

Not sure what you mean by 'test'?

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
OranewAuthor Commented:
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?
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.

slightwv (䄆 Netminder) Commented:
>>How can I prove this in my development environment?

Look at the columns I specified above in the V$SQL view.
Geert GOracle dbaCommented:
there is no need to prove that
it's a fact that's the way oracle works

you are using bind variables ?

if it's a complex query, it's possible the optimizer generates multiple plans
part of the parse is to find such a plan

if you find the querries are suffering from a lot of parsing, then you probably have a problem

during the parse, plan_hash_value of the query = 0
if it takes a long time to parse ... 1 of the causes can be histograms on an indexed column with lots of unique values
OranewAuthor Commented:
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 ?
slightwv (䄆 Netminder) Commented:
>>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.
you can look at session stats and see which sessions are doing a lot of parsing

  FROM v$sesstat s, v$statname sn
 WHERE s.statistic# = sn.statistic# AND name = 'parse count (hard)';

you'll probably want to capture that multiple times and check the difference to get an accurate picture.

A session running for 6 months might have accumulated tens of thousands of parses simply because of uptime.
Whereas a session that logged in just 2 minutes ago might have several hundred and indicate a problem.
Geert GOracle dbaCommented:
an external dba has told a few stories about peoplesoft

it's usually about his amazement at how bad a design some programs have
and his fascination of how brains of developers are so cool at inventing logic processes
when having a complete lack of understanding of any oracle process or of any business process
and with total incomprehension of the time, space and resource usage

probably one of the best stories was of how to define "dynamic constants"
create view vw_constant_3 as select 3 X from dual;

i forgot what the trouble was with that peoplesoft ...
probably the usual excessive amount of loop for a single fetch
after he tuned it a little, they awarded him a certificate "peoplesoft expert"

parsing is probably just a symptom
i'm guessing you need to look at the program flow also
OranewAuthor Commented:
I've requested that this question be deleted for the following reason:

I don't see any response yet
slightwv (䄆 Netminder) Commented:
>>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.
OranewAuthor Commented:
My bad.  I will take it back
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.