| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: index not used on x$ v$ tables
Ok, the deal is
Ok here is the deal (I spent a day on this so maybe the solution will
help someone else), if the bind variable is character then the index
is not used. (I'm assuming that the monitoring tool was using didn't
define its bind variables as numeric, thus creating the problem)
variable cn varchar2(3);
execute :cn:='11';
SELECT s.indx,s.ksusestn ,s.ksusestv FROM x$ksusesta s WHERE
s.indx=:cn;
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
10800 FIXED TABLE (FULL) OF 'X$KSUSESTA'
variable n number
Rows Row Source Operation
------- ---------------------------------------------------
240 FIXED TABLE FIXED INDEX #1 X$KSUSESTA A good indication that this is going on is that the "row source" and the explain plan in the tkprof output are different. This doesn't seem to be a problem on a normal table like emp(empno)
SELECT * FROM emp WHERE empno=:cn
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID EMP
1 INDEX RANGE SCAN (object id 48929)
SELECT * FROM emp WHERE empno=:n
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID EMP
1 INDEX RANGE SCAN (object id 48929)
On Thu, 15 Nov 2001 15:48:35 GMT, oraperf_at_yahoo.com (Kyle Hailey) wrote:
>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
Best Wishes
Kyle Hailey
http://oraperf.sourceforge.net
 |
 |