Re: View Mechanics

  • From: "Tim Gorman" <tim@xxxxxxxxx>
  • To: "Lange, Kevin G" <kevin.lange@xxxxxxxxxx>, "Oracle-L List" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 02 Sep 2011 17:32:48 +0000

Kevin,
Well, it was worth the long-shot... :-)

Each of the parameters you mentioned are alterable at the session-level. Have 
you tried changing them to see if the behavior changes?

Just grasping at straws...

-Tim


-----Original Message-----
From: Lange, Kevin G [mailto:kevin.lange@xxxxxxxxxx]
Sent: Friday, September 2, 2011 11:21 AM
To: tim@xxxxxxxxx, 'Oracle-L List'
Subject: RE: View Mechanics

Thanks for the query to list the parms Tim.

We have no undocumented parms set on the database (i.e. no underbar parms).

The ones you mentioned below were set as follows by default

_pre_rewrite_push_pred TRUE
_push_join_predicate TRUE
_push_join_union_view TRUE _push_join_union_view2 TRUE



------------------------------------------------------------
From: Tim Gorman [mailto:tim@xxxxxxxxx] 
Sent: Friday, September 02, 2011 12:08 PM
To: Lange, Kevin G; Oracle-L List
Subject: Re: View Mechanics



Kevin,

If you can connect to SQL*Plus as SYSDBA (or know someone who can), perhaps you 
can run this SQL*Plus script ("http://www.evdbt.com/tools.htm#nondefparm";) that 
displays all parameters set to non-default values, including the un-documented 
ones. There are several un-documented parameters that can affect whether 
predicates get pushed into a view, specifically "_push_join_predicate" or 
"_push_join_union_view" or "_pre_rewrite_push_pred" or others.

Hope this helps...

-Tim

-----Original Message-----
From: Lange, Kevin G [mailto:kevin.lange@xxxxxxxxxx]
Sent: Friday, September 2, 2011 10:49 AM
To: 'Oracle-L List'
Subject: RE: View Mechanics

Its repeatable over and over and over on different databases. Here are the only 
settings that we use that I imagine would have any affect on it 
optimizer_index_caching = 90 optimizer_index_cost_adj = 10 optimizer_mode = 
CHOOSE query_rewrite_enabled = TRUE -----Original Message----- From: 
oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf 
Of Powell, Mark Sent: Friday, September 02, 2011 11:36 AM To: Oracle-L List 
Subject: RE: View Mechanics That is strange. Oracle pushing the wehre field = 
'123' into the view text and driving off that table would be what I would 
normally expect. The resulting query ran in sqlplus should not run better. Do 
you have an session level settings changes that need to be taken into account? 
Have you made any database parameter setting changes for an of the parameters 
that effect the optimizer or set any underbar paramters? Are any events set in 
this database? -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx 
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Lange, Kevin G Sent: 
Friday, September 02, 2011 11:38 AM To: Oracle-L List Subject: RE: View 
Mechanics What we have seen is this: In the application, running a select from 
the view in the form of Select * from app_view where field_a = '123'; Run a lot 
slower than running the EXACT code that makes up the view adding the line and 
field_a = '123' In the underlying tables field_a is an indexed column. And the 
columns that join Table_1 to Table_2 to Table_3 are all indexed. Therefore I 
would expect the view to use the same path the sql behind the view when 
selecting from it. What we saw when explaining the select from the view is that 
all three tables are doing Full Table Scans. When running the sql outside the 
view, the appropriate indexes are being used. I just thought that, all things 
equal, the view would use the same methods to get the data that the SQl behind 
the view did. I had not seen this drastic a difference before. -----Original 
Message----- From: oracle-l-bounce@xxxxxxxxxxxxx 
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Powell, Mark Sent: Friday, 
September 02, 2011 7:48 AM To: Oracle-L List Subject: RE: View Mechanics I am 
not sure I understand your wording but it is not uncommon to create a view 
whose SQL query would be a multiple table join of all rows and if you run 
select * from the view the performance would be pretty bad. However, when used 
in the application or by the end-user tool a where clause using an indexed 
column is provided in the query against the view which in turn when merged into 
the view code by the CBO results in a much more selective plan/performance. 
-----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx 
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Lange, Kevin G Sent: 
Thursday, September 01, 2011 6:12 PM To: Oracle-L List Subject: View Mechanics 
I am just curious if its common for an Explain Plan of a view to have a totally 
different response than an explain plan of the SQL behind the view. Reason 
being, we have a very simple view made of of 3 tables that, when you explain 
it, it shows full table scans on all three tables. But, in the mean time, if 
you explain plan the SQL behind the view, you get index range scans on all 
three tables. Just makes no sense to me (which seems to be happneing more and 
more these days). System: Oracle 10.2.0.4 on a Solaris 10 machine. This e-mail, 
including attachments, may include confidential and/or proprietary information, 
and may be used only by the person or entity to which it is addressed. If the 
reader of this e-mail is not the intended recipient or his or her authorized 
agent, the reader is hereby notified that any dissemination, distribution or 
copying of this e-mail is prohibited. If you have received this e-mail in 
error, please notify the sender by replying to this message and delete this 
e-mail immediately. -- //www.freelists.org/webpage/oracle-l -- 
//www.freelists.org/webpage/oracle-l This e-mail, including attachments, 
may include confidential and/or proprietary information, and may be used only 
by the person or entity to which it is addressed. If the reader of this e-mail 
is not the intended recipient or his or her authorized agent, the reader is 
hereby notified that any dissemination, distribution or copying of this e-mail 
is prohibited. If you have received this e-mail in error, please notify the 
sender by replying to this message and delete this e-mail immediately. -- 
//www.freelists.org/webpage/oracle-l -- 
//www.freelists.org/webpage/oracle-l This e-mail, including attachments, 
may include confidential and/or proprietary information, and may be used only 
by the person or entity to which it is addressed. If the reader of this e-mail 
is not the intended recipient or his or her authorized agent, the reader is 
hereby notified that any dissemination, distribution or copying of this e-mail 
is prohibited. If you have received this e-mail in error, please notify the 
sender by replying to this message and delete this e-mail immediately. -- 
//www.freelists.org/webpage/oracle-l 
This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this e-mail is
prohibited. If you have received this e-mail in error, please notify the
sender by replying to this message and delete this e-mail immediately.

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


Other related posts: