| how to get a query into sharable pool ?? [message #184175] |
Tue, 25 July 2006 11:11  |
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  |
michael_bialik
Messages: 621
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
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
