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@xxxxxxxxxxxxxxxxxx>
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Ram Raman <veeeraman@xxxxxxxxx>
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.
--
