Re: SQL query clarification

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 5 Jan 2017 11:08:58 +0000
Message-ID: <MMXP123MB091136E61B2DAC93A074743AA5600_at_MMXP123MB0911.GBRP123.PROD.OUTLOOK.COM>


The most curious thing about the autotrace output for the first plan is the 30,000 recursive calls.

The Rows estimates could have come from something as simple as the statistics on some of the tables being very misleading - as Stefan pointed out the optimizer can display numbers that show a dramatic difference between the number table estimates and index estimates for very odd reasons - but the recursive calls suggests to me that autotrace is not telling you about the actual execution plan, and the 31,000 values are a clue that Oracle is doing something completely different.

Have you tried running the update with statistics_level set to all, serveroutput off, and then issuing select * from table(dbms_xplan.display_cursor(null,null,'allstats last');

Couple of other thoughts - are any of the columns virtual columns hiding functions ? What are the column types of the date/time columns being compared ? Having 4 indexes that start with the primary key column is a design error - more so since two of them overlap on the first 3 columns - such things help the optimizer find "rare" code paths that may have avoided bug testing for years.

Regards
Jonathan Lewis



From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Ram Raman <veeeraman_at_gmail.com> Sent: 05 January 2017 04:25:29
To: ORACLE-L
Subject: SQL query clarification

List

I was trying to tune a query in 11g EE. Here is the link: https://drive.google.com/open?id=0B9YC82qZ8_3eemF5b3kzTlh0eVU

I was looking at the plan and one thing i could not understand was ids 8 and 9 in the second plan (phv 1362929457). After getting the employeeid from the TIMESHEEITEM table, the optimizer selects the matching row from the jaids table. jaids table has PERSONID as primary key. Why would the optimizer say that it would (or really did) access all the 31.5K rows of JAIDS. Same question with PERSON table.

Ram.

--

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jan 05 2017 - 12:08:58 CET

Original text of this message