Home » RDBMS Server » Performance Tuning » how to get a query into sharable pool ??  () 1 Vote
how to get a query into sharable pool ?? [message #184175] Tue, 25 July 2006 11:11 Go to next message
santhoshml
Messages: 2
Registered: July 2006
Junior Member
SOrry for NOT using very descriptive subject.

My problem is, some of the quires used in the application is not falling into sharable pool. For example, when I got the statspack from DBA, as one below

285 285 2.12 2318367539
Module: JDBC Thin Client
SELECT A.ACCOUNT_ID, A.SELECT_IN, S.TICKER_SYM_NM, S.TICKER_SYM_
DESC FROM ALERTS A, SECURITIES S WHERE A.ACCOUNT_ID = :B1 AND A.
SECURITY_ID = S.SECURITY_ID ORDER BY S.TICKER_SYM_NM

It shows the above query is parsed 285 times and executed 285 times. While there are some quires and procedure while are parsed few number of times (10-20) and executed thousands of times.

How can I get this query done the same way i.e. get parsed few number of times and let it be executed as many times as it wants.

I am sure this will have a performance hit.

My collegue speculates the problem may lie is how we are calling the procedure

for example
1. cstmt = conn.prepareCall("begin inbox_web_pkg.select_inbox_notifications(?, ?); end;"

2. cstmt = conn.prepareCall("call inbox_web_pkg.select_inbox_notifications(?, ?);"

In the second statement we are not using begin and end, so IS THAT THE REASON ???

*******OR***********
we are returning cursor back from the procedure to the java, so is that the reason.

Please suggest me on this.

thanks in advance.

Sunny.
Re: how to get a query into sharable pool ?? [message #184592 is a reply to message #184175] Thu, 27 July 2006 03:07 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
IMHO there are 2 possible reaseons:

1. The statement is issued by different Oracle sessions/connections.
-- In that case each session can NOT use cached cursors and
-- must search for a statement in the shared pool (performs
-- SOFT parse).

2. You a calling a stored procedure a number of times in the SAME session, but your application CLOSES the cursor, forcing Oracle to search shared pool again (soft parse).
-- If it's the case - I recommend to OPEN Java handle once for
-- session and to reuse it inside a loop.

HTH.
Michael
Previous Topic: Very slow in Executing
Next Topic: urgent .. i need help
Goto Forum:
  


Current Time: Fri Dec 09 11:35:44 CST 2016

Total time taken to generate the page: 0.07848 seconds