Re: View Mechanics
- From: "Tim Gorman" <tim@xxxxxxxxx>
- To: kevin.lange@xxxxxxxxxx, "Oracle-L List" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 02 Sep 2011 17:07:46 +0000
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 anyaffect on it
optimizer_index_caching = 90optimizer_index_cost_adj = 10optimizer_mode =
CHOOSEquery_rewrite_enabled = TRUE-----Original Message-----From:
oracle-l-bounce@xxxxxxxxxxxxx[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf
Of Powell, MarkSent: Friday, September 02, 2011 11:36 AMTo: Oracle-L
ListSubject: RE: View MechanicsThat is strange. Oracle pushing the wehre field
= '123' into the viewtext 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 intoaccount?Have
you made any database parameter setting changes for an of theparameters 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 GSent: Friday, September 02, 2011 11:38 AMTo: Oracle-L
ListSubject: RE: View MechanicsWhat 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
viewadding the line and field_a = '123'In the underlying tables field_a is an
indexed column. And the columnsthat join Table_1 to Table_2 to Table_3 are all
indexed. Therefore Iwould expect the view to use the same path the sql behind
the view whenselecting from it.What we saw when explaining the select from the
view is that all threetables are doing Full Table Scans. When running the sql
outside theview, the appropriate indexes are being used. I just thought that,
all things equal, the view would use the samemethods to get the data that the
SQl behind the view did. I had notseen this drastic a difference before.
-----Original Message-----From:
oracle-l-bounce@xxxxxxxxxxxxx[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf
Of Powell, MarkSent: Friday, September 02, 2011 7:48 AMTo: Oracle-L
ListSubject: RE: View Mechanics I am not sure I understand your wording but it
is not uncommon to createa view whose SQL query would be a multiple table join
of all rows and ifyou run select * from the view the performance would be
pretty bad.However, when used in the application or by the end-user tool a
whereclause using an indexed column is provided in the query against the
viewwhich in turn when merged into the view code by the CBO results in amuch
more selective plan/performance.-----Original Message-----From:
oracle-l-bounce@xxxxxxxxxxxxx[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf
Of Lange, Kevin GSent: Thursday, September 01, 2011 6:12 PMTo: Oracle-L
ListSubject: View MechanicsI am just curious if its common for an Explain Plan
of a view to have atotally different response than an explain plan of the SQL
behind theview. Reason being, we have a very simple view made of of 3 tables
that, whenyou explain it, it shows full table scans on all three tables. But,
inthe mean time, if you explain plan the SQL behind the view, you getindex
range scans on all three tables. Just makes no sense to me (which seems to be
happneing more and morethese days). System: Oracle 10.2.0.4 on a Solaris 10
machine.This e-mail, including attachments, may include confidential
and/orproprietary information, and may be used only by the person or entity
towhich it is addressed. If the reader of this e-mail is not the
intendedrecipient or his or her authorized agent, the reader is hereby
notifiedthat any dissemination, distribution or copying of this e-mail
isprohibited. If you have received this e-mail in error, please notify
thesender by replying to this message and delete this e-mail
immediately.--//www.freelists.org/webpage/oracle-l--http://www.freelists.org/webpage/oracle-lThis
e-mail, including attachments, may include confidential and/orproprietary
information, and may be used only by the person or entity towhich it is
addressed. If the reader of this e-mail is not the intendedrecipient or his or
her authorized agent, the reader is hereby notifiedthat any dissemination,
distribution or copying of this e-mail isprohibited. If you have received this
e-mail in error, please notify thesender by replying to this message and delete
this e-mail
immediately.--//www.freelists.org/webpage/oracle-l--http://www.freelists.org/webpage/oracle-lThis
e-mail, including attachments, may include confidential and/orproprietary
information, and may be used only by the person or entityto which it is
addressed. If the reader of this e-mail is not the intendedrecipient or his or
her authorized agent, the reader is hereby notifiedthat any dissemination,
distribution or copying of this e-mail isprohibited. If you have received this
e-mail in error, please notify thesender by replying to this message and delete
this e-mail immediately.--//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
