Re: View Mechanics
Date: Fri, 02 Sep 2011 17:07:46 +0000
Message-ID: <W829262693750741314983266_at_webmail57>
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_at_ppoone.com]
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_at_freelists.org[mailto:oracle-l-bounce_at_freelists.org] 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_at_freelists.org[mailto:oracle-l-bounce@ freelists.org] 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- -bounce_at_freelists.org[mailto:oracle-l-bounce_at_freelists.org] 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_at_freelists.org[mailto:oracle-l-bounce_at_freelists.org] 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.--http://www.freelists.org/webpage/oracle-l--http://www.freelists.org/webpage/oracle-lThis e-mail, including attachments, may inclu de 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.--http://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 del ete this e-mail immediately.--http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Sep 02 2011 - 12:07:46 CDT