Home » RDBMS Server » Performance Tuning » Statspack question (9.2.0.8 on Solaris.)
Statspack question [message #281565] Sun, 18 November 2007 07:04 Go to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi,

I need some help on analysing statspack. In my statspack report, there is following sql reported:

SQL ordered by Parse Calls for DB: DB1  Instance: Ia  Snaps: 10181 -1018
-> End Parse Calls Threshold:      1000

                           % Total
 Parse Calls  Executions   Parses  Hash Value
------------ ------------ -------- ----------

SELECT name, item, itemType, price
FROM name_table WHERE name=:1

      12,408       12,408     1.29 1599154530



I do not know why there are as many parse calls as there are executions? - should not there be just one parse call and several executions? And if yes (there should be one parse call for several executions), then what could lead to this type of sql? What should be changed to rectify?

Thanks,
Nirav
Re: Statspack question [message #281585 is a reply to message #281565] Sun, 18 November 2007 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is as much parse as execution because your application asks it for. Oracle only parse when you ask it.

Quote:

What should be changed to rectify?

The application: it should not release the cursor after executing it.
Instead of (algorithm not PL/SQL code):
loop
  open cursor
  bind variable
  execute
  close cursor
end loop

It should be:
open cursor
loop
  bind variable
  execute
end loop
close

Setting session_cached_cursors to a value > 0 will help.

Regards
Michel
Re: Statspack question [message #281748 is a reply to message #281585] Mon, 19 November 2007 03:23 Go to previous message
orausern
Messages: 826
Registered: December 2005
Senior Member
Thank you very much Michel.
Previous Topic: consistent gets > segment size during FTS
Next Topic: Maximum number of oracle processes
Goto Forum:
  


Current Time: Fri Jun 28 11:03:38 CDT 2024