Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> index not used on x$ v$ tables
I have two different execution paths by the same user on the same
database and the same enviroment (as far as I can tell) but diffent
tools. One user is sqlplus and the other is a database monitoring
tool. The query in question is:
SELECT /*+ ORDERED */ s.sid,n.name,s.value FROM v$sesstat s,
v$statname n
WHERE s.sid=:n
AND s.statistic#=n.statistic#
This query takes about .01 CPU seconds in sqlplus and 2.51 in the database monitoring tool.
Now when I trace the two uses I do get different explain plans from tkprof. The good one is
GOOD (sqlplus):
Rows Row Source Operation
------- ---------------------------------------------------
226 FILTER
227 NESTED LOOPS
241 FIXED TABLE FIXED INDEX #1 X$KSUSESTA
466 FIXED TABLE FIXED INDEX #2 X$KSUSD
2 SORT AGGREGATE
226 FIXED TABLE FULL X$KSUSD
BAD (tool):
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE 0 FILTER 0 NESTED LOOPS 0 FIXED TABLE (FULL) OF 'X$KSUSESTA' 0 FIXED TABLE (FIXED INDEX #2) OF 'X$KSUSD' 0 SORT (AGGREGATE) 0 FIXED TABLE (FULL) OF 'X$KSUSD'
But I can't figure out why sqlplus has a different explain plan than the tool. Acutally if I run explain plan in the sqlplus session I ge the same explain plan as the tool, but when I actually run the statement and trace it, then the trace file contains the good explain plan.
The problem can be simplified by reducing the query to
SELECT s.indx,s.ksusestn ,s.ksusestv FROM x$ksusesta s WHERE s.indx=:n
When I run the query in explain plan I get
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE 240 FIXED TABLE (FULL) OF 'X$KSUSESTA' but when I run it in sqlplus and set sql_trace=true and then run tkprof I get
all count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
240 FIXED TABLE FIXED INDEX #1 X$KSUSESTA
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE 240 FIXED TABLE (FULL) OF 'X$KSUSESTA' The actual path taken is the first path not the second. The row source means its the actual path found in the trace file whereas the second path is what tkprof would have suspected.
I actually tried
SELECT /*+ FULL(x$ksusesta) */ s.indx,s.ksusestn ,s.ksusestv
FROM x$ksusesta s
WHERE s.indx=:n
/
but this still gives the FIXED INDEX explain plan even though I'm trying to force it to do the full scan just for kicks.
I can force the FULL TABLE scan by doing
SELECT s.indx,s.ksusestn ,s.ksusestv
FROM x$ksusesta s
WHERE abs(s.indx)=:n
but actually, of course, what I'd like would be to force the index usage, but since the index doesn't even have a name, how would I do that.
THe monitoring tool sets sort_area_size, hash_area_size and array size but I also set all of these in sqlplus to the same as the tool and I still get the good plan. I have tried changing the optimizer goal from rule to choose to all_rows to first_rows but I always get teh good plan. If I could actually reproduce the bad plan in sqlplus that might give me an indication of how I could force the good path in the monitoring tool.
I did run a 10053 trace on the sqlplus process but didn't get any output. This is version 8.1.6. I wonder if the event has been changed.
The same monitoring too attacking a v7.3.4 database seems to run the
good explain plan, but has the problem on 8.0.6 and 8.1.6 and 8.1.7
Best Wishes
Kyle Hailey
http://oraperf.sourceforge.net