SQL Performance (MOSC)

MOSC Banner

how to monitor a sql statement's plan changed?

edited Sep 7, 2016 5:00AM in SQL Performance (MOSC) 3 commentsAnswered

i want to monitor a sql statement's execution plan changed.  do like these:

V$SQL_SHARED_CURSOR

V$SQL_SHARED_CURSOR explains why a particular child cursor is not shared with

existing child cursors. Each column identifies a specific reason why the cursor cannot

be shared.

seems good.

do the test:

scott:

create testing table

     SQL> create table t1 as select * from emp;

     SQL> exec dbms_stats.gather_table_stats(user,'t1');

     SQL> set autotrace on

     SQL> select empno,ename,job,sal from t1 where deptno=30;

          TABLE ACCESS FULL| T1   # table full scan for t1

then create index:

     SQL> create index ind_t1 on t1(deptno)

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center