COSS update takes very long time

[former_member358875]

Hi all,

for COSS table there is index COSS_{1 standard index (used fields are subset of the primary index). Why when I execute update query using all primary index fields in where condition, the primary index is not used and in explain I can see index range scan using secondary index COSS}1? And the query takes up to one second (with cca. 4 million records in COSS). If I delete COSS~1, everything works fine.

Thanks,

Michal

[fidel_vales]

Hi,

It is imposible to provide an answer to your query with the (NO) information you have provided.

Would you mind to post here

1) the complete statement and explain plan (use the code markup)

2) all available indexes and their statistics (also the table statistics)

3) are your parameters properly set?

4) what oracle optimizer merge fix patch have you installed?

with that information it would be possible to start thinking about giving you REAL explanations instead of shooting bullets and hoping any of them will help you. (look at [this|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/17662] [original link is broken] [original link is broken] [original link is broken]😉

Thanks

Fidel

[former_member358875]

Hi Fidel,

COSS table in SAP has 3 standard indexes. Statement and explain:

SQL Statement
----------------------------------------------------------------------------------------------------------------------
SELECT
/*+
  FIRST_ROWS (500)
*/
  "MANDT" , "LEDNR" , "OBJNR" , "GJAHR" , "WRTTP" , "VERSN" , "KSTAR" , "HRKFT" , "VRGNG" ,
  "PAROB" , "USPOB" , "BEKNZ" , "TWAER" , "PERBL" , "MEINH" , "WTG001" , "WTG002" , "WTG003" ,
  "WTG004" , "WTG005" , "WTG006" , "WTG007" , "WTG008" , "WTG009" , "WTG010" , "WTG011" ,
  "WTG012" , "WTG013" , "WTG014" , "WTG015" , "WTG016" , "WOG001" , "WOG002" , "WOG003" ,
  "WOG004" , "WOG005" , "WOG006" , "WOG007" , "WOG008" , "WOG009" , "WOG010" , "WOG011" ,
  "WOG012" , "WOG013" , "WOG014" , "WOG015" , "WOG016" , "WKG001" , "WKG002" , "WKG003" ,
  "WKG004" , "WKG005" , "WKG006" , "WKG007" , "WKG008" , "WKG009" , "WKG010" , "WKG011" ,
  "WKG012" , "WKG013" , "WKG014" , "WKG015" , "WKG016" , "WKF001" , "WKF002" , "WKF003" ,
  "WKF004" , "WKF005" , "WKF006" , "WKF007" , "WKF008" , "WKF009" , "WKF010" , "WKF011" ,
  "WKF012" , "WKF013" , "WKF014" , "WKF015" , "WKF016" , "PAG001" , "PAG002" , "PAG003" ,
  "PAG004" , "PAG005" , "PAG006" , "PAG007" , "PAG008" , "PAG009" , "PAG010" , "PAG011" ,
  "PAG012" , "PAG013" , "PAG014" , "PAG015" , "PAG016" , "PAF001" , "PAF002" , "PAF003" ,
  "PAF004" , "PAF005" , "PAF006" , "PAF007" , "PAF008" , "PAF009" , "PAF010" , "PAF011" ,
  "PAF012" , "PAF013" , "PAF014" , "PAF015" , "PAF016" , "MEG001" , "MEG002" , "MEG003" ,
  "MEG004" , "MEG005" , "MEG006" , "MEG007" , "MEG008" , "MEG009" , "MEG010" , "MEG011" ,
  "MEG012" , "MEG013" , "MEG014" , "MEG015" , "MEG016" , "MEF001" , "MEF002" , "MEF003" ,
  "MEF004" , "MEF005" , "MEF006" , "MEF007" , "MEF008" , "MEF009" , "MEF010" , "MEF011" ,
  "MEF012" , "MEF013" , "MEF014" , "MEF015" , "MEF016" , "MUV001" , "MUV002" , "MUV003" ,
  "MUV004" , "MUV005" , "MUV006" , "MUV007" , "MUV008" , "MUV009" , "MUV010" , "MUV011" ,
  "MUV012" , "MUV013" , "MUV014" , "MUV015" , "MUV016" , "BELTP" , "TIMESTMP" , "BUKRS" , "FKBER" ,
  "SEGMENT" , "GEBER" , "GRANT_NBR"
FROM
  "COSS"
WHERE
  "MANDT" = :A0 AND "LEDNR" = :A1 AND "OBJNR" = :A2 AND "GJAHR" = :A3 AND "WRTTP" = :A4 AND "VERSN"
  = :A5 AND "KSTAR" = :A6 AND "HRKFT" = :A7 AND "VRGNG" = :A8 AND "PAROB" = :A9 AND "USPOB" = :A10
  AND "BEKNZ" = :A11 AND "TWAER" = :A12 AND "PERBL" = :A13 AND ROWNUM <= :A14


Execution Plan

----------------------------------------------------------------------------------------------------------------------
System: SD9

----------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |   413 |     2   (0)|
|*  1 |  COUNT STOPKEY               |        |       |       |            |
|*  2 |   TABLE ACCESS BY INDEX ROWID| COSS   |     1 |   413 |     2   (0)|
|*  3 |    INDEX RANGE SCAN          | COSS~1 |     1 |       |     1   (0)|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=TO_NUMBER(:A14))
   2 - filter("PAROB"=:A9 AND "KSTAR"=:A6 AND "USPOB"=:A10 AND
              "BEKNZ"=:A11 AND "TWAER"=:A12 AND "HRKFT"=:A7 AND "PERBL"=:A13)
   3 - access("OBJNR"=:A2 AND "MANDT"=:A0 AND "LEDNR"=:A1 AND
              "VERSN"=:A5 AND "WRTTP"=:A4 AND "GJAHR"=:A3 AND "VRGNG"=:A8)

[fidel_vales]

Hi,

Thanks for the information.

I can see that there is a "HINT" which you have not mentioned in our initial post.

Nevertheless, I still miss the answer to the other 3 questions.

You have "standard" indexes (so I am obliged to connect to my system and look at the index definition, but I'm 100% sure that you do not have the same omount of row that I have and the clustering factor is different and ... )

Again, with the current information I can only GUESS what could be the problem.

If you answer the second one, we can start thinking about a proper answer

[Former Member]

Hi,

Try executing database update statistics through the TA DB13 ?

-Varadhu...