Re: Result cache hint not being used

From: Chris Dunscombe <cdunscombe_at_yahoo.com>
Date: Mon, 11 Jun 2012 11:44:56 +0100 (BST)
Message-ID: <1339411496.43161.YahooMailNeo_at_web29703.mail.ird.yahoo.com>

See inline

---

 From: Dominic Brooks <dombrooks_at_hotmail.com> To: cdunscombe_at_yahoo.com; oracle-l_at_freelists.org Sent: Monday, 11 June 2012, 10:07
Subject: RE: Result cache hint not being used    

Can you clarify?
1. There's a SQL statement in a PLSQL proc and the SQL statement is hinted with the result cache hint?

Yes

2. When the proc is executed from the command line and it works, is this executed as the procedure owner or as the proxy user?

Executed via another user. At the moment haven't done it via the proxy user as it's a global user and I don't know the syntax to connect using SQLPlus, I'm hunting around to find the syntax.

3. When the proc is executed from the app as this proxy user, you can see the result_cache hint in the source code? but there's no result cache operation in the execution plan?

Correct the hint is in the SQLText but doesn't appear in the exection plan.

4. When you execute the procedure from the command line, is that from a user where OLS restrictions will be applied? In the same way as the proxy user?

Yes it's from a user with OLS restrictions, and the proc is called with the same parameter values etc.

5. Have you tried simplifying the scenario and executing the procedure from the proxy user with OLS not in the picture, i.e. disabled?

Not yet, from what I've done so far I don't think that it's OLS on its own that's the issue.  
 Thanks,

Chris

> Date: Mon, 11 Jun 2012 09:09:13 +0100
> From: cdunscombe_at_yahoo.com
> Subject: Re: Result cache hint not being used
> To: niall.litchfield_at_gmail.com
> CC: oracle-l_at_freelists.org
>
> Nice informative article but I'm afraid it didn't help. The result_cache hint is used when calling the PL/SQL procs from the command line and the OLS bits work OK. However when calling via the app using a proxy user the hint doesn't appear in the execution plan.
> Thanks,
>
> Chris
>
>
>
> ________________________________
> From: Niall Litchfield <niall.litchfield_at_gmail.com>
> To: cdunscombe_at_yahoo.com
> Cc: Oracle-L Freelists <oracle-l_at_freelists.org>
> Sent: Friday, 8 June 2012, 16:26
> Subject: Re: Result cache hint not being used
>
>
> Does Dom's article on VPD and Result cache help?  http://orastory.wordpress.com/2009/02/19/a-bit-of-vpd-and-a-dollop-of-result-cache/  
>
> Just a little bit extra to try to avoid the overquoting sin
>
>
> On Fri, Jun 8, 2012 at 4:07 PM, Chris Dunscombe <cdunscombe_at_yahoo.com> wrote:
>
> Hi,
> >We have a stored procedure with a cursor. This cursor has the result_cache hint. 
> >
>  
> The app is Oracle App Server 10.1.3 and connects using application connect pools to the database via a proxy user with role information etc coming from OID.
> >OLS is being used.
> >
> >
>
>  
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jun 11 2012 - 05:44:56 CDT