index is not used after a table reorg

Printable View

10-27-2009, 10:18 AM
Nugpot

index is not used after a table reorg

Hi,

Application - SAP
Database - oracle 10.2.0.4.0
OS - Sun sparc 64 bit

The below sql statement was using the index jcds~0 but after a table reorg(using Quest's live reorg) and collecting stats using brconnect, it has started performing full table scans.

UNIQUE INDEX SAPSR3."JCDS~0" ON SAPSR3.JCDS
(MANDT, OBJNR, STAT, CHGNR)

SELECT "MANDT" , "OBJNR" , "STAT" , "CHGNR" , "USNAM" , "UDATE" , "UTIME" , "TCODE" , "CDTCODE" , "INACT" , "CHIND"
FROM "JCDS" WHERE "MANDT" = 100 AND "OBJNR" BETWEEN 'VB*' AND 'VB99999999999999999999' AND "INACT" = ' ';

When I explain that statement on oracle sqlplus level, it seems to be using the index. However, when run from SAP, it does a full table scan. Does that make sense?

I was able to reorg just the table/index on a staging env by retaining the existing stats. The select was still using the index, so that confirmed that the reorg is not causing the issue but the stats is telling the optimizer to change the execution plan.

So, I copied the stats that work into the problem database hoping it should use the index now but it was still doing full table scans when run from the application level.

Can someone help me with what else I can look at to resolve this mystery. I'm running out of options. Appreciate your help.

Regards
Jay
10-27-2009, 10:26 AM
Nugpot

The acutal select statement uses bind variables.
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;

The values for the bind variables are
MANDT-100
OBJNR = VB* and VB99999999999999999999
INACT = ' '

Regards
Jay
10-28-2009, 02:03 AM
hrishy

Hi

Can you post the explain plan with formatting for the statement here

Code:

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
10-28-2009, 08:20 AM
giteshtrivedi

Hi,

What are distinct values in same index? Did you analyze both?

Thanks and regards,
Gitesh Trivedi
http://www.dbametrix.com
10-28-2009, 10:58 AM
PAVB

Quote:

Originally Posted by giteshtrivedi

What are distinct values in same index? Did you analyze both?

... meaning?
10-29-2009, 10:20 AM
Nugpot

1 Attachment(s)

Attached are the explain plans.

the Mandt column does not have disticnt values. OBJNR has alot of disticnt values.

Regards
Jay
10-30-2009, 09:47 AM
paragp1981

I am not sure what i am seeing different in your attached file is the cause of different explain plans

In attached file SAP screen shot has query as
select * from JCDS where .........

In query which you are trying it has

SELECT "MANDT" , "OBJNR" , "STAT" , "CHGNR" , "USNAM" , "UDATE" , "UTIME" , "TCODE" , "CDTCODE" , "INACT" , "CHIND"
FROM "JCDS" WHERE .........

Can you try explain plan in sql with "select * from JCDS where ...."
10-30-2009, 03:21 PM
Nugpot

Hi,

Below is the explain for select *

SQL> explain plan set statement_id='OCT30' for
2 SELECT * FROM "JCDS" WHERE "MANDT" = 100 AND "OBJNR" BETWEEN 'VB*' AND 'VB99999999999999999999' AND "INACT" = ' ';

Explained.

Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 2K| 136K| 121 | | |
| TABLE ACCESS BY INDEX ROW|JCDS | 2K| 136K| 120 | | |
| INDEX SKIP SCAN |JCDS~0 | 316 | | 82 | | |
--------------------------------------------------------------------------------

6 rows selected.

Regards
Jay
10-30-2009, 06:08 PM
PAVB

I would stop looking at explain plans and start looking at traces.

Execute/Trace query both from SAP and sqlplus, tkprof/check trace files for the naked truth.
11-01-2009, 01:59 AM
Nugpot

Hi,

Do you mean like a 10046 trace? level 10 ?

regards
Vj
11-01-2009, 11:35 AM
PAVB

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;
11-02-2009, 02:06 AM
hrishy

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
11-03-2009, 05:29 PM
Nugpot

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
11-04-2009, 12:12 AM
hrishy

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'));