high parse ratio in Toad

[loaded1]

Hi there

I have been using Toad for some of my DBA work. I have started to look at server statistics and get high ratios :

High Parse to execute ratio, this is always about 50%. The database is shutdown at night for backups.

What is the best method of reducing this.

Any help would be appreciated.

Thanks

[marist89]

Since your db is shutdown at night, it is hard to compute an accurate parse ratio. Basically, everything that was executed yesterday has to be re-parsed today.

You may want to check your parse ratio between two defined periods later in the day. Maybe using a tool like statspack http://technet.oracle.com/deploy/per.../statspack.pdf and check the use of bind variables http://technet.oracle.com/deploy/per...pdf/cursor.pdf

[sambavan]

You have to be very much concerned, if it were to be of hard parses. As Jeff said that it could be because every thing has to be reparsed because of a fresh shared_pool. But one other thing could be that it could be an ill formed sql that uses the literal replacement instead of bind variables. as a result every sql requests of this form has to hard parsed.

Sam

[loaded1]

Thanks for that. As I'm only the DBA/sys admin and dont get to be a code monkey I'll have to tell them and see if they can design their sql better. I'm certain they dont use bind variables so will see if that could be a solution! Thanks for the answers. Also how would I tell if they were hard parses?