Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> index not used on x$ v$ tables

index not used on x$ v$ tables

From: Kyle Hailey <oraperf_at_yahoo.com>
Date: Thu, 15 Nov 2001 15:48:35 GMT
Message-ID: <3bf3231e.35060243@newsfeeds-goliath.1usenet.net>


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
------- ------ -------- ---------- ---------- ---------- ----------



Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 17 0.01 0.00 0 0 0 240
------- ------ -------- ---------- ---------- ---------- ----------

total 19 0.01 0.00 0 0 0 240

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



Posted Via Binaries.net = SPEED+RETENTION+COMPLETION = http://www.binaries.net Received on Thu Nov 15 2001 - 09:48:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US