Hi , i was looking at the longest running sql statements during our batch run so i ran the following query:
SELECT * from
v$sqlarea
where
upper(sql_text) like '%desired_schema%'
order by
disk_reads desc;
The top statement was select ....... where table.full_date >= '01-01-2016 00:00:00'
I then looked at the explain plan that was run using for this sql_id:
select plan_table_output from table (dbms_xplan.display_cursor('sql_id',null,'typical'))
This showed it was doing a full table scan, which i thought was a bit strange.
When i tried to run the SQL statement it failed as there was no "to_date" in the statement.
I added a "to_date" and then ran an explain plan and it used the indexes i was expecting.
My question is how can v$sqlarea have an sql statement that is invalid, and why are the explain plans different?
Many thanks for your help