Oracle Database Discussions

hi

We are getting the following sql statement dump, and i am not able to understand what should be done here, would anyone please advise



Registered qb: SEL$1 0xc4c8a150 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 objn=272221 hint_alias="BSAD"@"SEL$1"

SPM: statement not found in SMB
SPM: statement not a candidate for auto-capture

**************************
Automatic degree of parallelism (ADOP)
**************************
Automatic degree of parallelism is disabled: Parameter.

PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
PM: PM bypassed: disabled due to star transformation.
OPTIMIZER INFORMATION

******************************************
----- Current SQL Statement for this session (sql_id=82mdu08rbcnyy) -----
EXPLAIN PLAN SET STATEMENT_ID='SAP_STMT' FOR /*+ 142235 */ SELECT "MANDT" , "BUKRS" , "KUNNR" , "UMSKS" , "UMSKZ" , "AUGDT" , "AUGBL" , "ZUONR"
, "GJAHR" , "BELNR" , "BUZEI" , "BUDAT" , "BLDAT" , "CPUDT" , "WAERS" , "XBLNR" , "BLART" , "MONAT" , "BSCHL" , "ZUMSK" , "SHKZG" , "GSBER" ,
"MWSKZ" , "DMBTR" , "WRBTR" , "MWSTS" , "WMWST" , "BDIFF" , "BDIF2" , "SGTXT" , "PROJN" , "AUFNR" , "ANLN1" , "ANLN2" , "SAKNR" , "HKONT" , "FK
ONT" , "FILKD" , "ZFBDT" , "ZTERM" , "ZBD1T" , "ZBD2T" , "ZBD3T" , "ZBD1P" , "ZBD2P" , "SKFBT" , "SKNTO" , "WSKTO" , "ZLSCH" , "ZLSPR" , "ZBFIX
" , "HBKID" , "BVTYP" , "REBZG" , "REBZJ" , "REBZZ" , "SAMNR" , "ANFBN" , "ANFBJ" , "ANFBU" , "ANFAE" , "MANSP" , "MSCHL" , "MADAT" , "MANST" ,
"MABER" , "XNETB" , "XANET" , "XCPDD" , "XINVE" , "XZAHL" , "MWSK1" , "DMBT1" , "WRBT1" , "MWSK2" , "DMBT2" , "WRBT2" , "MWSK3" , "DMBT3" , "W
RBT3" , "BSTAT" , "VBUND" , "VBELN" , "REBZT" , "INFAE" , "STCEG" , "EGBLD" , "EGLLD" , "RSTGR" , "XNOZA" , "VERTT" , "VERTN" , "VBEWA" , "WVER
W" , "PROJK" , "FIPOS" , "NPLNR" , "AUFPL" , "APLZL" , "XEGDR" , "DMBE2" , "DMBE3" , "DMB21" , "DMB22" , "DMB23" , "DMB31" , "DMB32" , "DMB33"
, "BDIF3" , "XRAGL" , "UZAWE" , "XSTOV" , "MWST2" , "MWST3" , "SKNT2" , "SKNT3" , "XREF1" , "XREF2" , "XARCH" , "PSWSL" , "PSWBT" , "LZBKZ" , "
LANDL" , "IMKEY" , "VBEL2" , "VPOS2" , "POSN2" , "ETEN2" , "FISTL" , "GEBER" , "DABRZ" , "XNEGP" , "KOSTL" , "RFZEI" , "KKBER" , "EMPFB" , "PRC
TR" , "XREF3" , "QSSKZ" , "ZINKZ" , "DTWS1" , "DTWS2" , "DTWS3" , "DTWS4" , "XPYPR" , "KIDNO" , "ABSBT" , "CCBTC" , "PYCUR" , "PYAMT" , "BUPLA"
, "SECCO" , "CESSION_KZ" , "PPDIFF" , "PPDIF2" , "PPDIF3" , "KBLNR" , "KBLPOS" , "GRANT_NBR" , "GMVKZ" , "SRTYPE" , "LOTKZ" , "FKBER" , "INTRE
NO" , "PPRCT" , "BUZID" , "AUGGJ" , "HKTID" , "BUDGET_PD" , "KONTT" , "KONTL" , "UEBGDAT" , "VNAME" , "EGRUP" , "BTYPE" , "PROPMANO" , "YYSTORE
" , "YYDELGRP" , "YYBOLNR" FROM "BSAD" WHERE "MANDT" = :A0 AND "KUNNR" IN ( :A1 , :A2 , :A3 , :A4 , :A5 ) AND "BUKRS" = :A6 AND "AUGDT" = :A7
*******************************************


user_id=27 user_name=SAPSR3 module=RSXPLORA action=7093
sql_id=82mdu08rbcnyy plan_hash_value=1162638466 problem_type=3
----- Current SQL Statement for this session (sql_id=82mdu08rbcnyy) -----
EXPLAIN PLAN SET STATEMENT_ID='SAP_STMT' FOR /*+ 142235 */ SELECT "MANDT" , "BUKRS" , "KUNNR" , "UMSKS" , "UMSKZ" , "AUGDT" , "AUGBL" , "ZUONR"
, "GJAHR" , "BELNR" , "BUZEI" , "BUDAT" , "BLDAT" , "CPUDT" , "WAERS" , "XBLNR" , "BLART" , "MONAT" , "BSCHL" , "ZUMSK" , "SHKZG" , "GSBER" ,
"MWSKZ" , "DMBTR" , "WRBTR" , "MWSTS" , "WMWST" , "BDIFF" , "BDIF2" , "SGTXT" , "PROJN" , "AUFNR" , "ANLN1" , "ANLN2" , "SAKNR" , "HKONT" , "FK
ONT" , "FILKD" , "ZFBDT" , "ZTERM" , "ZBD1T" , "ZBD2T" , "ZBD3T" , "ZBD1P" , "ZBD2P" , "SKFBT" , "SKNTO" , "WSKTO" , "ZLSCH" , "ZLSPR" , "ZBFIX
" , "HBKID" , "BVTYP" , "REBZG" , "REBZJ" , "REBZZ" , "SAMNR" , "ANFBN" , "ANFBJ" , "ANFBU" , "ANFAE" , "MANSP" , "MSCHL" , "MADAT" , "MANST" ,
"MABER" , "XNETB" , "XANET" , "XCPDD" , "XINVE" , "XZAHL" , "MWSK1" , "DMBT1" , "WRBT1" , "MWSK2" , "DMBT2" , "WRBT2" , "MWSK3" , "DMBT3" , "W
RBT3" , "BSTAT" , "VBUND" , "VBELN" , "REBZT" , "INFAE" , "STCEG" , "EGBLD" , "EGLLD" , "RSTGR" , "XNOZA" , "VERTT" , "VERTN" , "VBEWA" , "WVER
W" , "PROJK" , "FIPOS" , "NPLNR" , "AUFPL" , "APLZL" , "XEGDR" , "DMBE2" , "DMBE3" , "DMB21" , "DMB22" , "DMB23" , "DMB31" , "DMB32" , "DMB33"
, "BDIF3" , "XRAGL" , "UZAWE" , "XSTOV" , "MWST2" , "MWST3" , "SKNT2" , "SKNT3" , "XREF1" , "XREF2" , "XARCH" , "PSWSL" , "PSWBT" , "LZBKZ" , "
LANDL" , "IMKEY" , "VBEL2" , "VPOS2" , "POSN2" , "ETEN2" , "FISTL" , "GEBER" , "DABRZ" , "XNEGP" , "KOSTL" , "RFZEI" , "KKBER" , "EMPFB" , "PRC
TR" , "XREF3" , "QSSKZ" , "ZINKZ" , "DTWS1" , "DTWS2" , "DTWS3" , "DTWS4" , "XPYPR" , "KIDNO" , "ABSBT" , "CCBTC" , "PYCUR" , "PYAMT" , "BUPLA"
, "SECCO" , "CESSION_KZ" , "PPDIFF" , "PPDIF2" , "PPDIF3" , "KBLNR" , "KBLPOS" , "GRANT_NBR" , "GMVKZ" , "SRTYPE" , "LOTKZ" , "FKBER" , "INTRE
NO" , "PPRCT" , "BUZID" , "AUGGJ" , "HKTID" , "BUDGET_PD" , "KONTT" , "KONTL" , "UEBGDAT" , "VNAME" , "EGRUP" , "BTYPE" , "PROPMANO" , "YYSTORE
" , "YYDELGRP" , "YYBOLNR" FROM "BSAD" WHERE "MANDT" = :A0 AND "KUNNR" IN ( :A1 , :A2 , :A3 , :A4 , :A5 ) AND "BUKRS" = :A6 AND "AUGDT" = :A7
sql_text_length=2001

----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 | |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | BSAD | 1 | 601 | 2 | 00:00:01 |
| 3 | INDEX RANGE SCAN | BSAD~1 | 1 | | 1 | 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information:
----------------------
3 - access("MANDT"=:A0 AND (("KUNNR"=:A1 OR "KUNNR"=:A2 OR "KUNNR"=:A3 OR "KUNNR"=:A4 OR "KUNNR"=:A5)) AND "BUKRS"=:A6 AND "AUGDT"=:A7)

Content of other_xml column
===========================
db_version : 11.2.0.1
parse_schema : SAPSR3
plan_hash : 1162638466
plan_hash_2 : 3684135643
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
OPT_PARAM('query_rewrite_enabled' 'false')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('star_transformation_enabled' 'true')
OPT_PARAM('optimizer_index_cost_adj' 20)
OPT_PARAM('_fix_control' '4728348:0 5483301:0 5705630:1 6626018:1 6440977:1 6972291:1 6399597:1 6430500:1 5099019:1 9196440:1 9495669:1 8
937971:1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "BSAD"@"SEL$1" ("BSAD"."MANDT" "BSAD"."KUNNR" "BSAD"."BUKRS" "BSAD"."AUGDT" "BSAD"."AUGBL" "BSAD"."GJAHR" "BSAD"."B
ELNR" "BSAD"."BUZEI" "BSAD"."BUDAT"))
END_OUTLINE_DATA
*/

Query Block Registry:
SEL$1 0xc4c8a150 (PARSER) [FINAL]

:
call(in-use=194136, alloc=229336), compile(in-use=233336, alloc=338904), execution(in-use=540368, alloc=544176)

End of Optimizer State Dump
Dumping Hints
=============
====================== END SQL Statement Dump ======================

thx
Jonu Joy