Re: SQL query clarification

Jonathan,

I flushed out the shared pool before running that sql first time - maybe that caused 30K rec calls? I will try what you suggested later in the evening. Thanks a lot to everyone who answered.

Ram

On Thu, Jan 5, 2017 at 5:08 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> 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