1

I have a realy strange problem.

I'm using Oracle 11g.

There is a query executed by Business Objects tool, which the optimizer generate different plans for different users.

When my customer run the BO report it's realy slowly, but when I run it, it's fast.

According to the fact that there is a great plan (take seconds), I tried to enforced the optimizer to use that plan.

The problem is that it's not work.

I tried with baseline and sqlsets but the query used bind variables with different values each time, so it not realy help when the query change.

Is there a way to disable a plan for all sql executions? This is 1 bad plan.. but can come with a lot of queries becasue of the bind variables.

More, I found in the net information about optimizer_secure_view_merging parameter that could cause such problem.. but I have few users that got the good plan , not only the owner.. Is that still can be that ?

source:

https://oracledb.wordpress.com/2007/04/10/execution-plans-differents-with-different-users/

If there is another idea what to do..

user2671057
  • 1,411
  • 2
  • 25
  • 43

1 Answers1

0

I'd not call this problem a realy strange.

There is a lot of possible causes that diferent user get different behaviour for the same query.

On trivial cause is to query a non-qualified table.

select * from TAB

This query will access different tables for different users.

The next possibility are different Optimizer Initiation Parameters that could cause that the optimizer for one user may use features that are prohibited for other user.

I'd recommend as a simplest way for troubelshooting to perform the Oracle 10053 trace for both queries.

The trace file contains the complete list of the used paramaters and a simple diff could provide a usefull hint.

If the parameters are not the cause, you'll see in the trace the details why different access paths in the execution plan was taken. (A good introduction to understand 10053 trace is the paper of Wolfgang Breitling I linked above).

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • Thanks for your answer, I will apply trace.. The problem doesnt related to aliases.. I know it. I think the link for the trace is broken – user2671057 Jan 31 '18 at 15:44
  • @user2671057 well I'd be surprised if the problem would be caused by (different) *unqualified tables* - I mention it only for completeness. The link works for me, but simply search for `Oracle trace 10053` or `A look under the hood cbo the 10053 event`. Let me know if you were sucessfull. – Marmite Bomber Jan 31 '18 at 16:52