| No data in v$sql_plan for SELECT [message #159186] |
Thu, 16 February 2006 10:42 |
piotrgrz
Messages: 1
Registered: February 2006
|
Junior Member |
|
|
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)...
For clearity, sequence was:
1. few (or more) weeks we gathered data from v$sql_plan,
2. last week we drop some indexes (ID_FK too),
3. in Sunday job was started,
4. in Monday we see that job didn't quit execution,
5. after (4) we kill session for job,
6. in Monday later we recreate index and gathered statistics,
7. today we start again this job - after short time job ended execution,
8. we looked at v$sqltext and v$sql_plan for "SELECT ..." and "ID_FK" and we didn't see anything...
Thanks very much,
Piotr Grzegrzolka
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
