-
yes indeed; like:
Code:
alter session set timed_statistics = true;
alter session set sql_trace = true;
set autotrace on explain
alter session set events='10046 trace name context forever, level 12';
++ EXECUTE SQL ++
alter session set sql_trace = false;
alter session set timed_statistics = false;
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Hi
I am thinking this could be due to histograms.
If you run the following on sqlplus you should have the full table scan back
with bind varaibles instead of hard coded literals.
Code:
explain plan for
SELECT MANDT , OBJNR , STAT ,
CHGNR , USNAM , UDATE ,
UTIME ,
TCODE , CDTCODE ,
INACT , CHIND
FROM JCDS
WHERE MANDT = :A0
AND OBJNR BETWEEN :A1 AND :A2
AND INACT = :A3
select * from table(dbms_xplan.display)
You might want to try and delete the histograms on the table and collect stats using dbms_stats and see what happens.
regards
-
Hi,
There are no histograms on that table columns.
SELECT column_name, num_distinct, num_buckets, histogram FROM USER_TAB_COL_STATISTICS WHERE table_name = 'JCDS'
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
MANDT 1 1 NONE
OBJNR 18262124 1 NONE
STAT 406 1 NONE
CHGNR 670 1 NONE
USNAM 22186 1 NONE
UDATE 4065 1 NONE
UTIME 86400 1 NONE
TCODE 125 1 NONE
CDTCODE 271 1 NONE
INACT 2 1 NONE
CHIND 2 1 NONE
Regards
Jay
-
Hi
Would you mind showing me the output of the statements that i have posted below.
Code:
explain plan for
SELECT MANDT , OBJNR , STAT ,
CHGNR , USNAM , UDATE ,
UTIME ,
TCODE , CDTCODE ,
INACT , CHIND
FROM JCDS
WHERE MANDT = :A0
AND OBJNR BETWEEN :A1 AND :A2
AND INACT = :A3
select * from table(dbms_xplan.display)
and also the plan with this
Code:
SELECT /*+ gather_plan_statistics */ * FROM
JCDS
WHERE
MANDT = 100
AND OBJNR BETWEEN 'VB*' AND 'VB99999999999999999999'
AND INACT = ' ';
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
Last edited by hrishy; 11-04-2009 at 03:27 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|