Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

No data in v$sql_plan for SELECT

490568Feb 14 2006 — edited Feb 14 2006
Hello,
I have that problem: by few (or more) weeks was gather data from view v$sql_plan (to show using objects). Last week we drop some indexes (unused => no rows in v$sql_plan for this object_name) in our Oracle 9.2.0.5. But in Sunday some job was started (from account SYS) and it was doing something by more than 24 hours (normal time for this it is about 1-3 hours). Before we killed that session, we saw this select (hash_value was getting from v$session and select from v$sqltext in time of execution):
select /*+ all_rows */ count(1) from "SOME_USER"."SOME_TABLE" where "ID" = :1
On this column was index "ID_FK", which we drop in last week.
And strange - we didn't see using this index in our historic v$sql_plan (we gather this
day by day in other table).
Yesterday this index was recreated, statistics was computed, and this job was started once again. And we didn't see data in v_sql_plan and even "select ..." in v_$sqltext...

I want to know what this mean and how to say (100% true) that indexes was (or not use) in our database (not by MONITORING clause)...

Thanks very much,

Piotr Grzegrzolka
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 14 2006
Added on Feb 14 2006
3 comments
466 views