No data in v$sql_plan for SELECT
490568Feb 14 2006 — edited Feb 14 2006Hello,
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