SQL tuning -- high number of "Bytes" in explain plan

Question

Could you give any thoughts on what are possibly causing 8.2 is having high number of "Bytes"? Thanks in advance.

   8.2 INDEX UNIQUE SCAN S_ORG_EXT_P1 -T146208  (Cost = 1 Rows = 9390061 Bytes = 112680732 )Acc=>"A1"."ROW_ID"="A4"."PR_DEPT_OU_ID"

Full explain plan

 SELECT STATEMENT    Cost =37 (Cost = 37 Rows =  Bytes =  )
1.1 NESTED LOOPS     (Cost = 37 Rows = 1 Bytes = 361 )
 2.1 NESTED LOOPS     (Cost = 34 Rows = 1 Bytes = 317 )
  3.1 NESTED LOOPS     (Cost = 32 Rows = 1 Bytes = 273 )
   4.1 NESTED LOOPS     (Cost = 21 Rows = 1 Bytes = 245 )
    5.1 NESTED LOOPS     (Cost = 19 Rows = 1 Bytes = 201 )
     6.1 NESTED LOOPS     (Cost = 8 Rows = 1 Bytes = 179 )
      7.1 NESTED LOOPS     (Cost = 5 Rows = 1 Bytes = 135 )
       8.1 NESTED LOOPS     (Cost = 4 Rows = 1 Bytes = 123 )
        9.1 TABLE ACCESS BY INDEX ROWID S_OPTY -T203229  (Cost = 3 Rows = 1 Bytes = 29 ) Fil => "A4"."PR_DEPT_OU_ID" IS NOT NULL
         10.1 INDEX UNIQUE SCAN S_OPTY_P1 -T203252  (Cost = 2 Rows = 1 Bytes =  )Acc=>"A4"."ROW_ID"=:V1
        9.2 TABLE ACCESS BY INDEX ROWID S_SALES_METHOD -T134134  (Cost = 1 Rows = 327 Bytes = 30738 )
         10.1 INDEX UNIQUE SCAN S_SALES_METHOD_P1 -T143711  (Cost = 0 Rows = 1 Bytes =  )Acc=>"A5"."ROW_ID"="A4"."SALES_METHOD_ID"
       8.2 INDEX UNIQUE SCAN S_ORG_EXT_P1 -T146208  (Cost = 1 Rows = 9390061 Bytes = 112680732 )Acc=>"A1"."ROW_ID"="A4"."PR_DEPT_OU_ID"
      7.2 INDEX RANGE SCAN S_ORG_EXT_XM_BUCS1 -T246873  (Cost = 3 Rows = 1 Bytes = 44 )Acc=>"A2"."PAR_ROW_ID"="A1"."ROW_ID" AND "A2"."TYPE"='ACCOUNT_TIER' Fil => ("A2"."ATTRIB_03" IS NOT NULL AND "A2"."ATTRIB_04" IS NOT NULL)
     6.2 TABLE ACCESS BY INDEX ROWID S_POS_SKILL_IT -T133800  (Cost = 11 Rows = 10 Bytes = 220 )
      7.1 INDEX RANGE SCAN S_POS_SKILL_IT_F2 -T246871  (Cost = 2 Rows = 10 Bytes =  )Acc=>"A9"."LO_CHAR1"="A5"."NAME"
    5.2 TABLE ACCESS BY INDEX ROWID S_POS_SKILL -T133799  (Cost = 2 Rows = 1 Bytes = 44 ) Fil => "A7"."ASGN_IT_TYPE_NAME"='HPQ Opportunity Sales Method'
     6.1 INDEX UNIQUE SCAN S_POS_SKILL_P1 -T142149  (Cost = 1 Rows = 1 Bytes =  )Acc=>"A7"."ROW_ID"="A9"."POSTN_SKILL_ID"
   4.2 TABLE ACCESS BY INDEX ROWID S_POS_SKILL_IT -T133800  (Cost = 11 Rows = 1 Bytes = 28 ) Fil => ("A8"."LO_CHAR3" IS NOT NULL AND "A8"."LO_CHAR2" IS NOT NULL AND "A8"."LO_CHAR2"="A2"."ATTRIB_04")
    5.1 INDEX RANGE SCAN S_POS_SKILL_IT_F2 -T246871  (Cost = 2 Rows = 10 Bytes =  )Acc=>"A8"."LO_CHAR1"="A2"."ATTRIB_03"
  3.2 TABLE ACCESS BY INDEX ROWID S_POS_SKILL -T133799  (Cost = 2 Rows = 1 Bytes = 44 ) Fil => ("A6"."ASGN_IT_TYPE_NAME"='HPQ BU Tier BUCS' AND "A6"."POSTN_ID"="A7"."POSTN_ID")
   4.1 INDEX UNIQUE SCAN S_POS_SKILL_P1 -T142149  (Cost = 1 Rows = 1 Bytes =  )Acc=>"A6"."ROW_ID"="A8"."POSTN_SKILL_ID"
 2.2 INDEX RANGE SCAN S_ORG_EXT_XM_BUCS1 -T246873  (Cost = 3 Rows = 1 Bytes = 44 )Acc=>"A3"."PAR_ROW_ID"="A1"."ROW_ID" AND "A3"."TYPE"='BU_CUSTOMER_SEGMENT' AND "A8"."LO_CHAR3"."A3"."ATTR Fil" => ("A3"."ATTRIB_03" IS NOT NULL AND "A3"."ATTRIB_04" IS NOT NULL)

score 0 · Answer 1 · answered Dec 10 '11 at 06:05

0

It's doing an index scan on over 9 million rows, where each row is just 12 bytes.

So, it's the high number of rows that is causing the high number of bytes. If you don't want it to access all that data, you should try to avoid the index scan.

answered Dec 10 '11 at 06:05

Guffa

687,336
108
737
1,005

hi Guffa, thanks for sharing your insight. Could you help to relate your answer with the outcome of step 9.x and 10.x? Actually from 9.x and 10.x it should return only 1 record, thus in step 8 -- i expect it would not scan the whole table. – iwan Dec 13 '11 at 14:22

SQL tuning -- high number of "Bytes" in explain plan

1 Answers1