OraFAQ Forum: Performance Tuning » nvl problem i think

Home » RDBMS Server » Performance Tuning » nvl problem i think (11.2.0.2.0 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

nvl problem i think [message #519152]

Wed, 10 August 2011 01:34

shashank_kurasrediffmail
Messages: 63
Registered: July 2011
Location: hyderabad

Member

hi all ,
I have a problem,help me please
whem am trying to use nvl for one condition it is taking lot of time to execute but when am removing nvl function then the query executing in 2 min
condition is given below

(HOI2.ORG_INFORMATION1)=nvl(TO_CHAR(:p_set_of_books_id) , HOI2.ORG_INFORMATION1)

but when am using the same condition as below the querry executing in 2 min

(HOI2.ORG_INFORMATION1)=TO_CHAR(:p_set_of_books_id)

my querry given below

(SELECT   cust.customer_number cust_no, cust.customer_name customer,
                     cnv.item_no, SUM(wd.shipped_quantity) shp_qty_nos,
                    0 rtn_qty_nos,
                    ROUND(SUM(cnv.cnf * wd.shipped_quantity), 3) shp_qty_tons,
                    0 rtn_qty_tons, 0 net_shp_qty_nos, 0 net_shp_qty_tons
               FROM wsh_delivery_details wd,
                    wsh_delivery_assignments wda,
                    wsh_new_deliveries wnd,
                    ra_customers cust,
                    vis_itm_ctg_cnv_mst cnv,              
	    HR_ORGANIZATION_INFORMATION HOI2,
	    MTL_PARAMETERS  mp,					
                    oe_order_headers_all oh,
                    qp_list_headers_tl pl
              WHERE	
			    oh.header_id = wd.source_header_id			 
			    AND oh.sold_to_org_id = WND.customer_id
                AND oh.price_list_id = pl.list_header_id(+)
			    AND oh.ship_from_org_id = HOI2.organization_id
				AND oh.ship_from_org_id = mp.organization_id
				AND mp.organization_id=mp.organization_id
				AND mp.organization_id = wnd.organization_id	
				AND wd.delivery_detail_id = wda.delivery_detail_id
			 	AND wda.delivery_id = wnd.delivery_id
		 	    AND oh.sold_to_org_id = cust.customer_id
                AND oh.sold_to_org_id = wd.customer_id	
       			AND cnv.inventory_item_id = wd.inventory_item_id	
                AND pl.NAME LIKE '%' || :p_brand || '%'
                AND (HOI2.ORG_INFORMATION1) =nvl(TO_CHAR(:p_set_of_books_id),HOI2.ORG_INFORMATION1)
                AND mp.organization_code BETWEEN NVL(:p_from_org_code,
                                                      mp.organization_code)
                                              AND NVL(:p_to_org_code,
                                                      mp.organization_code)
                AND cust.customer_class_code =
                             NVL(:p_cust_class_code, cust.customer_class_code)
                AND TRUNC(wnd.initial_pickup_date) BETWEEN :p_from_date
                                                       AND :p_to_date
                AND NVL(wd.shipped_quantity, 0) <> 0
                AND wd.source_header_type_id =
                                  NVL(:p_order_type, wd.source_header_type_id)
                AND cust.customer_number BETWEEN NVL(:p_from_customer,
                                                     cust.customer_number)
                                             AND NVL(:p_to_customer,
                                                     cust.customer_number)
                AND (   cnv.item_no LIKE 'A-FG%'
                     OR cnv.item_no LIKE 'B-FG%'
                     OR cnv.item_no LIKE 'P-FG%')
           GROUP BY 
		   ROLLUP(cust.customer_name,
                    cust.customer_number,
                    cnv.item_no,
                    cnv.cnf)
		HAVING GROUPING_ID(cust.customer_name,
                    cust.customer_number,
                    cnv.item_no,
                    cnv.cnf)=0
					)

CM: Got rid of some unnecessary white space

[Updated on: Wed, 10 August 2011 04:47] by Moderator

Report message to a moderator

Re: nvl problem i think [message #519167 is a reply to message #519152]

Wed, 10 August 2011 03:04

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Do you really format your queries like that? The indentation is all over the place - makes it hard to read.

Using nvl like that generally prevents oracle from using an index on the relevant column.

Post explains plans of the query with and without the nvl.

Report message to a moderator

Re: nvl problem i think [message #519174 is a reply to message #519167]

Wed, 10 August 2011 03:43

shashank_kurasrediffmail
Messages: 63
Registered: July 2011
Location: hyderabad

Member

hi,
thanks for reply

if i use the same condition with nvl as below it is executing in 2 min

HOI2.ORG_INFORMATION1)=nvl(TO_CHAR(:p_set_of_books_id) , 0)

but when am replacing the zero in above condition with column(HOI2.ORG_INFORMATION1) it is taking a lot of time to execute

[Updated on: Wed, 10 August 2011 03:43]

Report message to a moderator

Re: nvl problem i think [message #519176 is a reply to message #519174]

Wed, 10 August 2011 03:47

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Yes. That will happen. You only have a problem with index usage if the column is referenced in the function.

Report message to a moderator

Re: nvl problem i think [message #519179 is a reply to message #519176]

Wed, 10 August 2011 04:01

shashank_kurasrediffmail
Messages: 63
Registered: July 2011
Location: hyderabad

Member

any usage of hint will makes the query to execute in less time?

Report message to a moderator

Re: nvl problem i think [message #519180 is a reply to message #519179]

Wed, 10 August 2011 04:05

shashank_kurasrediffmail
Messages: 63
Registered: July 2011
Location: hyderabad

Member

execution plan without nvl

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1216 Card=1 Bytes=74)
  FILTER
    HASH (GROUP BY) (Cost=1216 Card=1 Bytes=74)
      VIEW OF VM_NWVW_0 (VIEW) (Cost=1216 Card=1 Bytes=74)
        HASH (UNIQUE) (Cost=1216 Card=1 Bytes=527)
          CONCATENATION
            FILTER
              NESTED LOOPS (OUTER) (Cost=608 Card=1 Bytes=527)
                NESTED LOOPS (Cost=584 Card=1 Bytes=506)
                  NESTED LOOPS (Cost=583 Card=1 Bytes=466)
                    NESTED LOOPS (Cost=581 Card=1 Bytes=446)
                      NESTED LOOPS (Cost=580 Card=1 Bytes=405)
                        NESTED LOOPS (Cost=578 Card=1 Bytes=371)
                          NESTED LOOPS (Cost=575 Card=1 Bytes=348)
                            NESTED LOOPS (Cost=573 Card=1 Bytes=309)
                              NESTED LOOPS (Cost=572 Card=1 Bytes=289)
                                NESTED LOOPS (Cost=570 Card=1 Bytes=257)
                                  NESTED LOOPS (Cost=24 Card=1 Bytes=215)
                                    NESTED LOOPS (Cost=23 Card=1 Bytes=197)
                                      NESTED LOOPS (Cost=22 Card=1 Bytes=172)
                                        NESTED LOOPS (Cost=20 Card=1 Bytes=136)
                                          NESTED LOOPS (Cost=19 Card=1 Bytes=126)
                                            NESTED LOOPS (Cost=17 Card=1 Bytes=106)
                                              NESTED LOOPS (Cost=17 Card=1 Bytes=99)
                                                TABLE ACCESS (BY INDEX ROWID) OF MTL_SYSTEM_ITEMS_B (TABLE) (Cost=16 Card=1 Bytes=67)
                                                  INDEX (RANGE SCAN) OF MTL_SYSTEM_ITEMS_B_N1 (INDEX) (Cost=15 Card=1)
                                                INDEX (RANGE SCAN) OF IDX$$_514C0004 (INDEX) (Cost=1 Card=1 Bytes=32)
                                              INDEX (UNIQUE SCAN) OF IC_ITEM_MST_TL_PK (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=7)
                                            INDEX (RANGE SCAN) OF MTL_ITEM_CATEGORIES_U1 (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=20)
                                          TABLE ACCESS (BY INDEX ROWID) OF MTL_CATEGORY_SETS_B (TABLE) (Cost=1 Card=1 Bytes=10)
                                            INDEX (UNIQUE SCAN) OF MTL_CATEGORY_SETS_B_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                                        INDEX (RANGE SCAN) OF FND_LOOKUP_VALUES_U1 (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=36)
                                      TABLE ACCESS (BY INDEX ROWID) OF MTL_CATEGORY_SETS_TL (TABLE) (Cost=1 Card=1 Bytes=25)
                                        INDEX (UNIQUE SCAN) OF MTL_CATEGORY_SETS_TL_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                                    TABLE ACCESS (BY INDEX ROWID) OF MTL_CATEGORIES_B (TABLE) (Cost=1 Card=1 Bytes=18)
                                      INDEX (UNIQUE SCAN) OF MTL_CATEGORIES_B_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                                  TABLE ACCESS (BY INDEX ROWID) OF WSH_DELIVERY_DETAILS (TABLE) (Cost=545 Card=4 Bytes=168)
                                    INDEX (RANGE SCAN) OF WSH_DELIVERY_DETAILS_N9 (INDEX) (Cost=5 Card=1081)
                                TABLE ACCESS (BY INDEX ROWID) OF OE_ORDER_HEADERS_ALL (TABLE) (Cost=2 Card=1 Bytes=32)
                                  INDEX (UNIQUE SCAN) OF OE_ORDER_HEADERS_U1 (INDEX (UNIQUE)) (Cost=1 Card=1)
                              TABLE ACCESS (BY INDEX ROWID) OF MTL_PARAMETERS (TABLE) (Cost=1 Card=1 Bytes=20)
                                INDEX (UNIQUE SCAN) OF MTL_PARAMETERS_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                            TABLE ACCESS (BY INDEX ROWID) OF QP_LIST_HEADERS_TL (TABLE) (Cost=2 Card=1 Bytes=39)
                              INDEX (RANGE SCAN) OF QP_LIST_HEADERS_TL_PK (INDEX (UNIQUE)) (Cost=1 Card=1)
                          TABLE ACCESS (BY INDEX ROWID) OF WSH_DELIVERY_ASSIGNMENTS (TABLE) (Cost=3 Card=1 Bytes=23)
                            INDEX (RANGE SCAN) OF WSH_DELIVERY_ASSIGNMENTS_N3 (INDEX) (Cost=2 Card=1)
                        TABLE ACCESS (BY INDEX ROWID) OF WSH_NEW_DELIVERIES (TABLE) (Cost=2 Card=1 Bytes=34)
                          INDEX (UNIQUE SCAN) OF WSH_NEW_DELIVERIES_U1 (INDEX (UNIQUE)) (Cost=1 Card=1)
                      TABLE ACCESS (BY INDEX ROWID) OF HZ_CUST_ACCOUNTS (TABLE) (Cost=1 Card=1 Bytes=41)
                        INDEX (UNIQUE SCAN) OF HZ_CUST_ACCOUNTS_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                    TABLE ACCESS (BY INDEX ROWID) OF HR_ORGANIZATION_INFORMATION (TABLE) (Cost=2 Card=1 Bytes=20)
                      INDEX (RANGE SCAN) OF HR_ORGANIZATION_INFORMATIO_FK2 (INDEX) (Cost=1 Card=2)
                  TABLE ACCESS (BY INDEX ROWID) OF HZ_PARTIES (TABLE) (Cost=1 Card=1 Bytes=40)
                    INDEX (UNIQUE SCAN) OF HZ_PARTIES_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                TABLE ACCESS (BY INDEX ROWID) OF IC_ITEM_CNV (TABLE) (Cost=24 Card=96 Bytes=2016)
                  INDEX (RANGE SCAN) OF IC_ITEM_CNV_U1 (INDEX (UNIQUE)) (Cost=3 Card=267)
            FILTER
              NESTED LOOPS (OUTER) (Cost=608 Card=1 Bytes=527)
                NESTED LOOPS (Cost=584 Card=1 Bytes=506)
                  NESTED LOOPS (Cost=583 Card=1 Bytes=466)
                    NESTED LOOPS (Cost=581 Card=1 Bytes=446)
                      NESTED LOOPS (Cost=579 Card=1 Bytes=407)
                        NESTED LOOPS (Cost=577 Card=1 Bytes=373)
                          NESTED LOOPS (Cost=574 Card=1 Bytes=350)
                            NESTED LOOPS (Cost=573 Card=1 Bytes=309)
                              NESTED LOOPS (Cost=572 Card=1 Bytes=289)
                                NESTED LOOPS (Cost=570 Card=1 Bytes=257)
                                  NESTED LOOPS (Cost=24 Card=1 Bytes=215)
                                    NESTED LOOPS (Cost=23 Card=1 Bytes=197)
                                      NESTED LOOPS (Cost=22 Card=1 Bytes=172)
                                        NESTED LOOPS (Cost=20 Card=1 Bytes=136)
                                          NESTED LOOPS (Cost=19 Card=1 Bytes=126)
                                            NESTED LOOPS (Cost=17 Card=1 Bytes=106)
                                              NESTED LOOPS (Cost=17 Card=1 Bytes=99)
                                                TABLE ACCESS (BY INDEX ROWID) OF MTL_SYSTEM_ITEMS_B (TABLE) (Cost=16 Card=1 Bytes=67)
                                                  INDEX (RANGE SCAN) OF MTL_SYSTEM_ITEMS_B_N1 (INDEX) (Cost=15 Card=1)
                                                INDEX (RANGE SCAN) OF IDX$$_514C0004 (INDEX) (Cost=1 Card=1 Bytes=32)
                                              INDEX (UNIQUE SCAN) OF IC_ITEM_MST_TL_PK (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=7)
                                            INDEX (RANGE SCAN) OF MTL_ITEM_CATEGORIES_U1 (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=20)
                                          TABLE ACCESS (BY INDEX ROWID) OF MTL_CATEGORY_SETS_B (TABLE) (Cost=1 Card=1 Bytes=10)
                                            INDEX (UNIQUE SCAN) OF MTL_CATEGORY_SETS_B_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                                        INDEX (RANGE SCAN) OF FND_LOOKUP_VALUES_U1 (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=36)
                                      TABLE ACCESS (BY INDEX ROWID) OF MTL_CATEGORY_SETS_TL (TABLE) (Cost=1 Card=1 Bytes=25)
                                        INDEX (UNIQUE SCAN) OF MTL_CATEGORY_SETS_TL_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                                    TABLE ACCESS (BY INDEX ROWID) OF MTL_CATEGORIES_B (TABLE) (Cost=1 Card=1 Bytes=18)
                                      INDEX (UNIQUE SCAN) OF MTL_CATEGORIES_B_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                                  TABLE ACCESS (BY INDEX ROWID) OF WSH_DELIVERY_DETAILS (TABLE) (Cost=545 Card=4 Bytes=168)
                                    INDEX (RANGE SCAN) OF WSH_DELIVERY_DETAILS_N9 (INDEX) (Cost=5 Card=1081)
                                TABLE ACCESS (BY INDEX ROWID) OF OE_ORDER_HEADERS_ALL (TABLE) (Cost=2 Card=1 Bytes=32)
                                  INDEX (UNIQUE SCAN) OF OE_ORDER_HEADERS_U1 (INDEX (UNIQUE)) (Cost=1 Card=1)
                              TABLE ACCESS (BY INDEX ROWID) OF MTL_PARAMETERS (TABLE) (Cost=1 Card=1 Bytes=20)
                                INDEX (UNIQUE SCAN) OF MTL_PARAMETERS_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                            TABLE ACCESS (BY INDEX ROWID) OF HZ_CUST_ACCOUNTS (TABLE) (Cost=1 Card=1 Bytes=41)
                              INDEX (UNIQUE SCAN) OF HZ_CUST_ACCOUNTS_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                          TABLE ACCESS (BY INDEX ROWID) OF WSH_DELIVERY_ASSIGNMENTS (TABLE) (Cost=3 Card=1 Bytes=23)
                            INDEX (RANGE SCAN) OF WSH_DELIVERY_ASSIGNMENTS_N3 (INDEX) (Cost=2 Card=1)
                        TABLE ACCESS (BY INDEX ROWID) OF WSH_NEW_DELIVERIES (TABLE) (Cost=2 Card=1 Bytes=34)
                          INDEX (UNIQUE SCAN) OF WSH_NEW_DELIVERIES_U1 (INDEX (UNIQUE)) (Cost=1 Card=1)
                      TABLE ACCESS (BY INDEX ROWID) OF QP_LIST_HEADERS_TL (TABLE) (Cost=2 Card=1 Bytes=39)
                        INDEX (RANGE SCAN) OF QP_LIST_HEADERS_TL_PK (INDEX (UNIQUE)) (Cost=1 Card=1)
                    TABLE ACCESS (BY INDEX ROWID) OF HR_ORGANIZATION_INFORMATION (TABLE) (Cost=2 Card=1 Bytes=20)
                      INDEX (RANGE SCAN) OF HR_ORGANIZATION_INFORMATIO_FK2 (INDEX) (Cost=1 Card=2)
                  TABLE ACCESS (BY INDEX ROWID) OF HZ_PARTIES (TABLE) (Cost=1 Card=1 Bytes=40)
                    INDEX (UNIQUE SCAN) OF HZ_PARTIES_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                TABLE ACCESS (BY INDEX ROWID) OF IC_ITEM_CNV (TABLE) (Cost=24 Card=96 Bytes=2016)
                  INDEX (RANGE SCAN) OF IC_ITEM_CNV_U1 (INDEX (UNIQUE)) (Cost=3 Card=267)

execution plan with nvl

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=402 Card=1 Bytes=220)
  FILTER
    HASH (GROUP BY) (Cost=402 Card=1 Bytes=220)
      NESTED LOOPS
        NESTED LOOPS (Cost=401 Card=1 Bytes=220)
          NESTED LOOPS (Cost=399 Card=1 Bytes=212)
            NESTED LOOPS (Cost=396 Card=1 Bytes=201)
              NESTED LOOPS (Cost=360 Card=1 Bytes=164)
                NESTED LOOPS (Cost=355 Card=1 Bytes=134)
                  NESTED LOOPS (Cost=321 Card=1 Bytes=112)
                    NESTED LOOPS (Cost=320 Card=1 Bytes=84)
                      HASH JOIN (Cost=319 Card=1 Bytes=76)
                        NESTED LOOPS
                          NESTED LOOPS (Cost=316 Card=63 Bytes=3087)
                            TABLE ACCESS (FULL) OF HZ_CUST_ACCOUNTS (TABLE) (Cost=283 Card=1 Bytes=29)
                            INDEX (RANGE SCAN) OF OE_ORDER_HEADERS_N2 (INDEX) (Cost=2 Card=48)
                          TABLE ACCESS (BY INDEX ROWID) OF OE_ORDER_HEADERS_ALL (TABLE) (Cost=33 Card=48 Bytes=960)
                        INDEX (SKIP SCAN) OF IDX$$_514C0002 (INDEX) (Cost=3 Card=2 Bytes=54)
                      TABLE ACCESS (BY INDEX ROWID) OF MTL_PARAMETERS (TABLE) (Cost=1 Card=1 Bytes=8)
                        INDEX (UNIQUE SCAN) OF MTL_PARAMETERS_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                    TABLE ACCESS (BY INDEX ROWID) OF HZ_PARTIES (TABLE) (Cost=1 Card=1 Bytes=28)
                      INDEX (UNIQUE SCAN) OF HZ_PARTIES_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                  TABLE ACCESS (BY INDEX ROWID) OF WSH_NEW_DELIVERIES (TABLE) (Cost=34 Card=1 Bytes=22)
                    INDEX (RANGE SCAN) OF WSH_NEW_DELIVERIES_N6 (INDEX) (Cost=2 Card=49)
                TABLE ACCESS (BY INDEX ROWID) OF WSH_DELIVERY_DETAILS (TABLE) (Cost=5 Card=1 Bytes=30)
                  INDEX (RANGE SCAN) OF WSH_DELIVERY_DETAILS_N2 (INDEX) (Cost=2 Card=7)
              VIEW PUSHED PREDICATE OF VIS_ITM_CTG_CNV_MST (VIEW) (Cost=36 Card=1 Bytes=37)
                SORT (UNIQUE) (Cost=36 Card=1 Bytes=236)
                  FILTER
                    NESTED LOOPS (OUTER) (Cost=35 Card=1 Bytes=236)
                      NESTED LOOPS (Cost=11 Card=1 Bytes=215)
                        NESTED LOOPS (Cost=10 Card=1 Bytes=197)
                          NESTED LOOPS (Cost=9 Card=1 Bytes=172)
                            NESTED LOOPS (Cost=7 Card=1 Bytes=136)
                              NESTED LOOPS (Cost=6 Card=1 Bytes=126)
                                NESTED LOOPS (Cost=4 Card=1 Bytes=106)
                                  NESTED LOOPS (Cost=4 Card=1 Bytes=99)
                                    TABLE ACCESS (BY INDEX ROWID) OF MTL_SYSTEM_ITEMS_B (TABLE) (Cost=3 Card=1 Bytes=67)
                                      INDEX (UNIQUE SCAN) OF MTL_SYSTEM_ITEMS_B_U1 (INDEX (UNIQUE)) (Cost=2 Card=1)
                                    INDEX (RANGE SCAN) OF IDX$$_514C0004 (INDEX) (Cost=1 Card=5605 Bytes=179360)
                                  INDEX (UNIQUE SCAN) OF IC_ITEM_MST_TL_PK (INDEX (UNIQUE)) (Cost=0 Card=5605 Bytes=39235)
                                INDEX (RANGE SCAN) OF MTL_ITEM_CATEGORIES_U1 (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=20)
                              TABLE ACCESS (BY INDEX ROWID) OF MTL_CATEGORY_SETS_B (TABLE) (Cost=1 Card=1 Bytes=10)
                                INDEX (UNIQUE SCAN) OF MTL_CATEGORY_SETS_B_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                            INDEX (RANGE SCAN) OF FND_LOOKUP_VALUES_U1 (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=36)
                          TABLE ACCESS (BY INDEX ROWID) OF MTL_CATEGORY_SETS_TL (TABLE) (Cost=1 Card=1 Bytes=25)
                            INDEX (UNIQUE SCAN) OF MTL_CATEGORY_SETS_TL_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                        TABLE ACCESS (BY INDEX ROWID) OF MTL_CATEGORIES_B (TABLE) (Cost=1 Card=1 Bytes=18)
                          INDEX (UNIQUE SCAN) OF MTL_CATEGORIES_B_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                      TABLE ACCESS (BY INDEX ROWID) OF IC_ITEM_CNV (TABLE) (Cost=24 Card=96 Bytes=2016)
                        INDEX (RANGE SCAN) OF IC_ITEM_CNV_U1 (INDEX (UNIQUE)) (Cost=3 Card=267)
            TABLE ACCESS (BY INDEX ROWID) OF WSH_DELIVERY_ASSIGNMENTS (TABLE) (Cost=3 Card=1 Bytes=11)
              INDEX (RANGE SCAN) OF WSH_DELIVERY_ASSIGNMENTS_N3 (INDEX) (Cost=2 Card=1)
          INDEX (RANGE SCAN) OF HR_ORGANIZATION_INFORMATIO_FK2 (INDEX) (Cost=1 Card=2)
        TABLE ACCESS (BY INDEX ROWID) OF HR_ORGANIZATION_INFORMATION (TABLE) (Cost=2 Card=1 Bytes=8)

Report message to a moderator

Re: nvl problem i think [message #519185 is a reply to message #519180]

Wed, 10 August 2011 04:30

shashank_kurasrediffmail
Messages: 63
Registered: July 2011
Location: hyderabad

Member

and when i use the same condition in other sub querry its working .the querry execting in same amount of time for both conditions ..

(HOI2.ORG_INFORMATION1)=nvl(TO_CHAR(:p_set_of_books_id) , HOI2.ORG_INFORMATION1)

AND (HOI2.ORG_INFORMATION1) =TO_CHAR(:p_set_of_books_id)

so i think its not problem of index

[Updated on: Wed, 10 August 2011 04:33]

Report message to a moderator

Re: nvl problem i think [message #519188 is a reply to message #519185]

Wed, 10 August 2011 04:45

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

According to those explain plans the one with the nvl should be faster. So either:
1) your statistics aren't up to date
2) You ran the explain plans on a different DB to the one you're getting the performance problem on.

So you need to fix that.

As for your last post, all that proves is that the sub-query didn't use the index in the first place.
However the query you posted above doesn't contain a sub-query so I'm not sure what you are refering to.

nvl itself is not slow. When it causes a query to slow down it is almost always because it's usage prevents oracle from using an index.

Report message to a moderator

Re: nvl problem i think [message #519191 is a reply to message #519188]

Wed, 10 August 2011 04:57

shashank_kurasrediffmail
Messages: 63
Registered: July 2011
Location: hyderabad

Member

My querry having 3 subquerries which are joined by using union all .and I have posted only one sub querry which is taking a lot of time to execute.

I have taken both execution plans from the same DB.

Report message to a moderator

Re: nvl problem i think [message #519192 is a reply to message #519191]

Wed, 10 August 2011 05:10

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

And are those executions plans from the DB that has the performance problem or another DB, like say a Development DB?
If I'm reading that plan right there is very little data in the tables.

Also are you sure that the only difference between the 2 queries explained above is the nvl? Because the 1st is doing a lot more table accesses than the second.

Report message to a moderator

Re: nvl problem i think [message #519196 is a reply to message #519192]

Wed, 10 August 2011 05:16

shashank_kurasrediffmail
Messages: 63
Registered: July 2011
Location: hyderabad

Member

yes only difference is nvl and no other change.

Report message to a moderator

Re: nvl problem i think [message #519197 is a reply to message #519196]

Wed, 10 August 2011 05:18

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

And my first question?

Report message to a moderator

Re: nvl problem i think [message #519198 is a reply to message #519197]

Wed, 10 August 2011 05:23

shashank_kurasrediffmail
Messages: 63
Registered: July 2011
Location: hyderabad

Member

twp execution plans are taken from same test db

Report message to a moderator

Re: nvl problem i think [message #519200 is a reply to message #519198]

Wed, 10 August 2011 05:26

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

And are you having the performance problem on the test DB?
If yes, then it looks like your stats are out of date - regather them for all relevant tables and try again.
If no, run the explain on the DB where you are having the performance problem.

Report message to a moderator

Re: nvl problem i think [message #519202 is a reply to message #519200]

Wed, 10 August 2011 05:37

shashank_kurasrediffmail
Messages: 63
Registered: July 2011
Location: hyderabad

Member

no there is no performance problem on the test db.and I have checked twice and am getting the same exeution plan which i posted above.
and I tried the same above condition with case as below


AND (HOI2.ORG_INFORMATION1) IN (CASE  WHEN  :p_set_of_books_id IS NOT NULL THEN :p_set_of_books_id ELSE  HOI2.ORG_INFORMATION1 END)

but still not executing ...

Report message to a moderator

Re: nvl problem i think [message #519203 is a reply to message #519202]

Wed, 10 August 2011 05:38

shashank_kurasrediffmail
Messages: 63
Registered: July 2011
Location: hyderabad

Member

this is the execution plan when am using same condition with case

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=402 Card=1 Bytes=220)
  FILTER
    HASH (GROUP BY) (Cost=402 Card=1 Bytes=220)
      NESTED LOOPS
        NESTED LOOPS (Cost=401 Card=1 Bytes=220)
          NESTED LOOPS (Cost=399 Card=1 Bytes=212)
            NESTED LOOPS (Cost=396 Card=1 Bytes=201)
              NESTED LOOPS (Cost=360 Card=1 Bytes=164)
                NESTED LOOPS (Cost=355 Card=1 Bytes=134)
                  NESTED LOOPS (Cost=321 Card=1 Bytes=112)
                    NESTED LOOPS (Cost=320 Card=1 Bytes=84)
                      HASH JOIN (Cost=319 Card=1 Bytes=76)
                        NESTED LOOPS
                          NESTED LOOPS (Cost=316 Card=63 Bytes=3087)
                            TABLE ACCESS (FULL) OF HZ_CUST_ACCOUNTS (TABLE) (Cost=283 Card=1 Bytes=29)
                            INDEX (RANGE SCAN) OF OE_ORDER_HEADERS_N2 (INDEX) (Cost=2 Card=48)
                          TABLE ACCESS (BY INDEX ROWID) OF OE_ORDER_HEADERS_ALL (TABLE) (Cost=33 Card=48 Bytes=960)
                        INDEX (SKIP SCAN) OF IDX$$_514C0002 (INDEX) (Cost=3 Card=2 Bytes=54)
                      TABLE ACCESS (BY INDEX ROWID) OF MTL_PARAMETERS (TABLE) (Cost=1 Card=1 Bytes=8)
                        INDEX (UNIQUE SCAN) OF MTL_PARAMETERS_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                    TABLE ACCESS (BY INDEX ROWID) OF HZ_PARTIES (TABLE) (Cost=1 Card=1 Bytes=28)
                      INDEX (UNIQUE SCAN) OF HZ_PARTIES_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                  TABLE ACCESS (BY INDEX ROWID) OF WSH_NEW_DELIVERIES (TABLE) (Cost=34 Card=1 Bytes=22)
                    INDEX (RANGE SCAN) OF WSH_NEW_DELIVERIES_N6 (INDEX) (Cost=2 Card=49)
                TABLE ACCESS (BY INDEX ROWID) OF WSH_DELIVERY_DETAILS (TABLE) (Cost=5 Card=1 Bytes=30)
                  INDEX (RANGE SCAN) OF WSH_DELIVERY_DETAILS_N2 (INDEX) (Cost=2 Card=7)
              VIEW PUSHED PREDICATE OF VIS_ITM_CTG_CNV_MST (VIEW) (Cost=36 Card=1 Bytes=37)
                SORT (UNIQUE) (Cost=36 Card=1 Bytes=236)
                  FILTER
                    NESTED LOOPS (OUTER) (Cost=35 Card=1 Bytes=236)
                      NESTED LOOPS (Cost=11 Card=1 Bytes=215)
                        NESTED LOOPS (Cost=10 Card=1 Bytes=197)
                          NESTED LOOPS (Cost=9 Card=1 Bytes=172)
                            NESTED LOOPS (Cost=7 Card=1 Bytes=136)
                              NESTED LOOPS (Cost=6 Card=1 Bytes=126)
                                NESTED LOOPS (Cost=4 Card=1 Bytes=106)
                                  NESTED LOOPS (Cost=4 Card=1 Bytes=99)
                                    TABLE ACCESS (BY INDEX ROWID) OF MTL_SYSTEM_ITEMS_B (TABLE) (Cost=3 Card=1 Bytes=67)
                                      INDEX (UNIQUE SCAN) OF MTL_SYSTEM_ITEMS_B_U1 (INDEX (UNIQUE)) (Cost=2 Card=1)
                                    INDEX (RANGE SCAN) OF IDX$$_514C0004 (INDEX) (Cost=1 Card=5605 Bytes=179360)
                                  INDEX (UNIQUE SCAN) OF IC_ITEM_MST_TL_PK (INDEX (UNIQUE)) (Cost=0 Card=5605 Bytes=39235)
                                INDEX (RANGE SCAN) OF MTL_ITEM_CATEGORIES_U1 (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=20)
                              TABLE ACCESS (BY INDEX ROWID) OF MTL_CATEGORY_SETS_B (TABLE) (Cost=1 Card=1 Bytes=10)
                                INDEX (UNIQUE SCAN) OF MTL_CATEGORY_SETS_B_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                            INDEX (RANGE SCAN) OF FND_LOOKUP_VALUES_U1 (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=36)
                          TABLE ACCESS (BY INDEX ROWID) OF MTL_CATEGORY_SETS_TL (TABLE) (Cost=1 Card=1 Bytes=25)
                            INDEX (UNIQUE SCAN) OF MTL_CATEGORY_SETS_TL_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                        TABLE ACCESS (BY INDEX ROWID) OF MTL_CATEGORIES_B (TABLE) (Cost=1 Card=1 Bytes=18)
                          INDEX (UNIQUE SCAN) OF MTL_CATEGORIES_B_U1 (INDEX (UNIQUE)) (Cost=0 Card=1)
                      TABLE ACCESS (BY INDEX ROWID) OF IC_ITEM_CNV (TABLE) (Cost=24 Card=96 Bytes=2016)
                        INDEX (RANGE SCAN) OF IC_ITEM_CNV_U1 (INDEX (UNIQUE)) (Cost=3 Card=267)
            TABLE ACCESS (BY INDEX ROWID) OF WSH_DELIVERY_ASSIGNMENTS (TABLE) (Cost=3 Card=1 Bytes=11)
              INDEX (RANGE SCAN) OF WSH_DELIVERY_ASSIGNMENTS_N3 (INDEX) (Cost=2 Card=1)
          INDEX (RANGE SCAN) OF HR_ORGANIZATION_INFORMATIO_FK2 (INDEX) (Cost=1 Card=1)
        TABLE ACCESS (BY INDEX ROWID) OF HR_ORGANIZATION_INFORMATION (TABLE) (Cost=2 Card=1 Bytes=8)

Report message to a moderator

Re: nvl problem i think [message #519204 is a reply to message #519203]

Wed, 10 August 2011 05:44

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Have you regathered statistics on all the tables?

Report message to a moderator

Re: nvl problem i think [message #519208 is a reply to message #519204]

Wed, 10 August 2011 05:57

shashank_kurasrediffmail
Messages: 63
Registered: July 2011
Location: hyderabad

Member

statistics means execution plans of all tables?

[Updated on: Wed, 10 August 2011 06:03]

Report message to a moderator

Re: nvl problem i think [message #519209 is a reply to message #519208]

Wed, 10 August 2011 06:16

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

statistics means statistics of all tables. Execution plans are generated using statistics.
Read up on dbms_stats.

Report message to a moderator

Re: nvl problem i think [message #519213 is a reply to message #519209]

Wed, 10 August 2011 06:50

shashank_kurasrediffmail
Messages: 63
Registered: July 2011
Location: hyderabad

Member

when am trying this am getting error


SQL> dbms_stats.gather_table_stats(user,emp);
unknown command beginning "dbms_stats..." - rest of line ignored.

is right way to gather statistics?

Report message to a moderator

Re: nvl problem i think [message #519214 is a reply to message #519213]

Wed, 10 August 2011 07:02

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

To run procedures in sqplus you need to either wrap them in an anonymous block (BEGIN...code...END) or use exec.
The table name needs to be wrapped in quotes and be in upper case.

Report message to a moderator

Re: nvl problem i think [message #519223 is a reply to message #519214]

Wed, 10 August 2011 07:48

shashank_kurasrediffmail
Messages: 63
Registered: July 2011
Location: hyderabad

Member

sry I dont have priiliges to view the statistics of the tables

Report message to a moderator

Re: nvl problem i think [message #519230 is a reply to message #519223]

Wed, 10 August 2011 08:08

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Then talk to someone who does. You can't fix a performance problem if the statistics are wrong.

Report message to a moderator

Re: nvl problem i think [message #519232 is a reply to message #519223]

Wed, 10 August 2011 08:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>sry I dont have priiliges to view the statistics of the tables
I bet you do have privileges.

SQL> DESC USER_TABLES

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: nvl problem i think [message #519234 is a reply to message #519223]

Wed, 10 August 2011 08:12

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you can query (and explain) a table then you can view the statistics.

Please read OraFAQ Forum Guide and don't use IM/SMS speak.

Regards
Michel

Report message to a moderator

Re: nvl problem i think [message #519241 is a reply to message #519234]

Wed, 10 August 2011 09:01

shashank_kurasrediffmail
Messages: 63
Registered: July 2011
Location: hyderabad

Member

hi blackswan ,
thanks for reply and for sending url.
when am trying this am getting following error

SQL> exec dbms_stats.gather_table_stats(user,'WSH_DELIVERY_DETAILS');
BEGIN dbms_stats.gather_table_stats(user,'WSH_DELIVERY_DETAILS'); END;

*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "APPS"."WSH_DELIVERY_DETAILS", insufficient privileges or does
not exist
ORA-06512: at "SYS.DBMS_STATS", line 23143
ORA-06512: at "SYS.DBMS_STATS", line 23205
ORA-06512: at line 1

Report message to a moderator

Re: nvl problem i think [message #519246 is a reply to message #519241]

Wed, 10 August 2011 09:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>ORA-20000: Unable to analyze TABLE "APPS"."WSH_DELIVERY_DETAILS", insufficient privileges or does
"local" error & NOT Oracle default
contact local DBA for assistance.
I suspect SYNONYM exists directing invocation to local code for "DBMS_STATS"

Report message to a moderator

Re: nvl problem i think [message #519252 is a reply to message #519246]

Wed, 10 August 2011 09:26

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Not local error, Oracle one, they do sometimes use raise_application_error you know:

SQL> exec dbms_stats.gather_table_stats(user,'made_up_table');
BEGIN dbms_stats.gather_table_stats(user,'made_up_table'); END;

*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "OPS$GALAGA"."MADE_UP_TABLE", insufficient
privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 13149
ORA-06512: at "SYS.DBMS_STATS", line 13179
ORA-06512: at line 1

Report message to a moderator

Re: nvl problem i think [message #519253 is a reply to message #519246]

Wed, 10 August 2011 09:30

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Hi - be careful running dbms_stats like that on an EBS database.
You are meant to go through the FND_STATS package. It has certain configured defaults (particularly for histograms, I think) that are supposed to be optimal for EBS.
John.

[update: typo]

[Updated on: Wed, 10 August 2011 09:31]

Report message to a moderator

Re: nvl problem i think [message #519261 is a reply to message #519253]

Wed, 10 August 2011 10:01

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

That begs the question of whether this thread should be moved to one of the apps forums.

Report message to a moderator

Re: nvl problem i think [message #519305 is a reply to message #519261]

Wed, 10 August 2011 22:45

shashank_kurasrediffmail
Messages: 63
Registered: July 2011
Location: hyderabad

Member

hi all
thanks for reply and for giving some valuable information.
Present my problem is solved and I used the condition as below

(HOI2.ORG_INFORMATION1)=TO_CHAR(:p_set_of_books_id)

so it is ececuting in 2 minutes and am trying to reduce some more time .

Report message to a moderator

Previous Topic:	SGA_MAX_SIZE to more than 2G?
Next Topic:	New Trace File

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 01:10:11 CST 2024