Home » RDBMS Server » Performance Tuning » Please help me in rewriting the below query in optimized way (Oracle 11g)
Please help me in rewriting the below query in optimized way [message #575519] Wed, 23 January 2013 16:06 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Please help me in rewriting the below query in optimized way.

Table A, Table C has 20 Million records each
Select *
      From  a,
              b,
               c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
      And Unitssold != 0 
      and a.Item_Cost_Amt is Null;


Execution Plan
----------------

SQL> SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1424127997
--------------------------------------------------------------------------------
| Id  | Operation              | Name                   | Rows  | Bytes |TempSpc
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                        |  4432 |   354K|
|*  1 |  HASH JOIN             |                        |  4432 |   354K|    38M
|*  2 |   HASH JOIN            |                        |   787K|    29M|
|   3 |    TABLE ACCESS FULL   |    B     |  8542 |   150K|
|*  4 |    INDEX FAST FULL SCAN|  A                   |    13M|   262M|
|*  5 |   TABLE ACCESS FULL    | C |  1489K|    61M|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C"."SKU"=TO_NUMBER("A"."SKU_ITEM_KEY") AND "A"."LOCATIONNO"="C"."
              "B"."FISCAL_MONTH_YEAR"="C"."MONTH_DATE")
   2 - access("A"."BSNS_UNIT_KEY"="B"."VOYAGENO" AND "A"."LOCATIONNO"="B"."LOCAT
   4 - filter("A"."ITEM_COST_AMT" IS NULL)
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   5 - filter("UNITSSOLD"<>0)

Update the below table with above select values

 Forall Indx In 1 .. t_Rtl_Tbl_1.Count Save Exceptions

                         
                          Execute Immediate
                          'Update A t
           Set t.Item_Cost_Amt = :1
         Where t.Sku_Item_Key = :2
           And t.Locationno = :3
           And t.Bsns_Unit_Key = :4
           And t.Item_Cost_Amt Is Null' Using t_Rtl_Tbl_1(Indx)
                         .Cost_Cal_Amt, t_Rtl_Tbl_1(Indx).Sku_Item_Key, t_Rtl_Tbl_1(Indx)
                         .Locationno, t_Rtl_Tbl_1(Indx).Bsns_Unit_Key
        ;

[Updated on: Wed, 23 January 2013 16:12]

Report message to a moderator

Re: Please help me in rewriting the below query in optimized way [message #575522 is a reply to message #575519] Wed, 23 January 2013 17:07 Go to previous messageGo to next message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) output from SQL_TRACE & tkprof

all columns in the WHERE clause should be INDEXED.
Re: Please help me in rewriting the below query in optimized way [message #575524 is a reply to message #575522] Wed, 23 January 2013 17:15 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Yes. Below are the indexes created.

Table A Index's
----------------

create index IDX1 on A (SKU_ITEM_KEY, BSNS_UNIT_KEY, LOCATIONNO, RGLR_UNIT_PRICE_AMT, ITEM_COST_AMT);

create index IDX3 on A (TRX_NBR, TRX_LINE_ITEM_SEQ_NBR);


create index IDX4 on A (BSNS_UNIT_KEY, LOCATIONNO);

Table B Index's
----------------

create index IDX2_B on B (VOYAGENO, LOCATIONNO);

create index IDX3_B on B (VOYAGENO, VOYAGEENDDATE) ;

create index IDX4_B on B (ORG_SW_VOYAGENO) ;

create index IDX5_B on B (LOCATIONNO);

create index IDX6_B on B (FISCAL_MONTH_YEAR);

create index IDX_B on B(VOYAGENO);


Table C Index's
----------------

create index IDX1_C on C (YEARNUMBER, MONTHNUMBER, SKU, LOCATIONNO);

create index IDX_C on DWH_SKULOCATIONBYMONTH (SKU, LOCATIONNO, MONTH_DATE);
Re: Please help me in rewriting the below query in optimized way [message #575526 is a reply to message #575519] Wed, 23 January 2013 20:24 Go to previous messageGo to next message
Kevin Meade
Messages: 1934
Registered: December 1999
Location: Connecticut USA
Senior Member
As far as the select goes, what makes you think it is bad?

If the rowcount estimates in the plan are accurate, seems to me FTS and HJ is the way to go for that query.
Re: Please help me in rewriting the below query in optimized way [message #575529 is a reply to message #575526] Wed, 23 January 2013 20:33 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
yes the row counts are good.
Re: Please help me in rewriting the below query in optimized way [message #575531 is a reply to message #575529] Wed, 23 January 2013 20:46 Go to previous messageGo to next message
Kevin Meade
Messages: 1934
Registered: December 1999
Location: Connecticut USA
Senior Member
Then consider the alternative join possibilities and the rowcounts they might produce. Oracle has done its job in trying to keep intermediary rowset sizes as small as possible. I cannot do the research myself since I do not have the data to work with but you can run some queries to test things out. There are only three tables and some list of columns you did not specify. But the combination of rows and row length will tell you which join order keeps the size of intermediary rowsets smallest.

a --> b --> c
a --> c --> b

b --> a --> c
b --> c --> a

c --> a --> b
c --> b --> a

If my math is correct, these are the six possible join orders.

What version of Oracle are you on? If it is 11g, use the LEADING() hint to force a join order and see what rowcounts and intermediary sizes pop out. If not then write the WHERE CLAUSE with the order noted and used the ORDERE hint instead. Post the resulting plans for us please.

For example:

explain plan for
select /*+ leading (a,b,c) */ *
from a,b,c
where...
/

explain plan for
select /*+ leading (a,c,b) */ *
from a,b,c
where...
/

... and so on

explain plan for
select /*+ leading (c,b,a) */ *
from a,b,c
where...
/

--------------------------------------------------------------------------------
| Id  | Operation              | Name                   | Rows  | Bytes |TempSpc
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                        |  4432 |   354K|
|*  1 |  HASH JOIN             |                        |  4432 |   354K|    38M
|*  2 |   HASH JOIN            |                        |   787K|    29M|
|   3 |    TABLE ACCESS FULL   |  B                     |  8542 |   150K|
|*  4 |    INDEX FAST FULL SCAN|  A                     |    13M|   262M|
|*  5 |   TABLE ACCESS FULL    |  C                     |  1489K|    61M|
--------------------------------------------------------------------------------


The query plan tells you after each step, how many rows result, how many bytes these rows consume (sum of average row width), and how much temp space is needed to complete the operation (these are all guesses of course unless you are using ACTUALS).

[Updated on: Wed, 23 January 2013 20:48]

Report message to a moderator

Re: Please help me in rewriting the below query in optimized way [message #575608 is a reply to message #575531] Thu, 24 January 2013 12:55 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Tried but it proved to be more costlier than actual plan.
Re: Please help me in rewriting the below query in optimized way [message #575610 is a reply to message #575608] Thu, 24 January 2013 13:19 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Do you mean the "cost" figure in the plan was higher than in the one you got by default? If so, yes it probably will be! It's a cost-based optimiser - it is designed to cost a range of possible execution plans and pick the one that comes out cheapest, so unless your new hinted version is something it never considered, then by definition it must have a higher cost than the default plan.
Re: Please help me in rewriting the below query in optimized way [message #575614 is a reply to message #575610] Thu, 24 January 2013 14:43 Go to previous messageGo to next message
Kevin Meade
Messages: 1934
Registered: December 1999
Location: Connecticut USA
Senior Member
that was the whole point. To show that you got the cheapest plan at least from a COST perspective.

But... show your work. I want to see all the plans for each variation shown. Don't just tell me it was more expensive, I figure that. I want people to see how the plans are different.
Re: Please help me in rewriting the below query in optimized way [message #575616 is a reply to message #575614] Thu, 24 January 2013 15:26 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
100% agree with you. I have checked different plans before me posting my question in forum.

Thanks a lot for throwing some light on this.
Re: Please help me in rewriting the below query in optimized way [message #575617 is a reply to message #575519] Thu, 24 January 2013 15:28 Go to previous messageGo to next message
Kevin Meade
Messages: 1934
Registered: December 1999
Location: Connecticut USA
Senior Member
that's nice. glad to see someone doing some work for themselves before checking in. A good change.

Now stop procrastinating and post what I want. Give us the plans so we can look at the numbers. I want others to understand what you and I already know.

explain plan for
select /*+ leading (a,b,c) */ *
from a,b,c
where...
/

explain plan for
select /*+ leading (a,c,b) */ *
from a,b,c
where...
/

... and so on

explain plan for
select /*+ leading (c,b,a) */ *
from a,b,c
where...
/


Re: Please help me in rewriting the below query in optimized way [message #575619 is a reply to message #575617] Thu, 24 January 2013 15:38 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Here is the requested different plans

Explain Plan For Select /*+ leading (a,b,c) */  *
      From A a,
           B        b,
           C    c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
      And Unitssold != 0 
      and a.Item_Cost_Amt is Null;



PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2676101487
--------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes |TempSp
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |  4432 |   939K|
|*  1 |  HASH JOIN          |                            |  4432 |   939K|    99
|*  2 |   TABLE ACCESS FULL | C				 |  1489K|    82M|
|*  3 |   HASH JOIN         |                            |   787K|   119M|  1487
|*  4 |    TABLE ACCESS FULL| A				 |    13M|  1337M|
|   5 |    TABLE ACCESS FULL| B        			 |  8543 |   433K|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C"."SKU"=TO_NUMBER("A"."SKU_ITEM_KEY") AND "A"."LOCATIONNO"="C"."
              "B"."FISCAL_MONTH_YEAR"="C"."MONTH_DATE")
   2 - filter("UNITSSOLD"<>0)
   3 - access("A"."BSNS_UNIT_KEY"="B"."VOYAGENO" AND "A"."LOCATIONNO"="B"."LOCAT
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   4 - filter("A"."ITEM_COST_AMT" IS NULL)


*************************************************************************************


Explain Plan For Select /*+ leading (a,c,b) */  *
      From A a,
           B        b,
           C    c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
      And Unitssold != 0 
      and a.Item_Cost_Amt is Null;


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2373585061
--------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes |TempSp
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            | 73775 |    15M|
|*  1 |  HASH JOIN          |                            | 73775 |    15M|
|   2 |   TABLE ACCESS FULL | B				 |  8543 |   433K|
|*  3 |   HASH JOIN         |                            |  5655K|   889M|  1487
|*  4 |    TABLE ACCESS FULL| A                          |    13M|  1337M|
|*  5 |    TABLE ACCESS FULL| C                          |  1489K|    82M|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."BSNS_UNIT_KEY"="B"."VOYAGENO" AND "A"."LOCATIONNO"="B"."LOCAT
              "B"."FISCAL_MONTH_YEAR"="C"."MONTH_DATE")
   3 - access("C"."SKU"=TO_NUMBER("A"."SKU_ITEM_KEY") AND "A"."LOCATIONNO"="C"."
   4 - filter("A"."ITEM_COST_AMT" IS NULL)
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   5 - filter("UNITSSOLD"<>0)



*************************************************************************************


Explain Plan For Select /*+ leading (c,b,a) */  *
      From A  a,
           B        b,
           C    c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
      And Unitssold != 0 
      and a.Item_Cost_Amt is Null;


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2330993626
--------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes |TempSp
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |  5818 |  1232K|
|*  1 |  HASH JOIN          |                            |  5818 |  1232K|  1487
|*  2 |   TABLE ACCESS FULL | A                          |    13M|  1337M|
|*  3 |   HASH JOIN         |                            |   166M|    17G|    99
|*  4 |    TABLE ACCESS FULL| C     			 |  1489K|    82M|
|   5 |    TABLE ACCESS FULL| B			         |  8543 |   433K|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."BSNS_UNIT_KEY"="B"."VOYAGENO" AND "A"."LOCATIONNO"="B"."LOCAT
              "C"."SKU"=TO_NUMBER("A"."SKU_ITEM_KEY") AND "A"."LOCATIONNO"="C"."
   2 - filter("A"."ITEM_COST_AMT" IS NULL)
   3 - access("B"."FISCAL_MONTH_YEAR"="C"."MONTH_DATE")
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   4 - filter("UNITSSOLD"<>0)



Re: Please help me in rewriting the below query in optimized way [message #575620 is a reply to message #575619] Thu, 24 January 2013 15:52 Go to previous messageGo to next message
Kevin Meade
Messages: 1934
Registered: December 1999
Location: Connecticut USA
Senior Member
Can you check please to see if you cut the end off each plan. Looks like the tempspace numbers of these were chopped off. For example, I find it hard to believe that the hash join that spits out 166 million rows and takes up 17GB of space requires only 99 bytes of temp space. We need to see the full plan lines to evalute.

| Id  | Operation              | Name                   | Rows  | Bytes |TempSp
--------------------------------------------------------------------------------
|*  3 |   HASH JOIN         |                           |   166M|    17G|    99

I will forgive you that you only provided three of the six requested plans.
Re: Please help me in rewriting the below query in optimized way [message #575621 is a reply to message #575620] Thu, 24 January 2013 16:17 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
I am, sorry I missed because of not setting the line size in SQLPLUS.

Below is the all 6 plans.

Explain Plan For Select /*+ leading (a,b,c) */  *
      From A a,
           B        b,
           C    c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
      And Unitssold != 0 
      and a.Item_Cost_Amt is Null;




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------
Plan hash value: 2676101487

--------------------------------------------------------------------------------
--------------------------
| Id  | Operation           | Name                       | Rows  | Bytes |TempSp
c| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
--------------------------
|   0 | SELECT STATEMENT    |                            |  4432 |   939K|
 |   256K  (2)| 00:51:13 |
|*  1 |  HASH JOIN          |                            |  4432 |   939K|    99
M|   256K  (2)| 00:51:13 |
|*  2 |   TABLE ACCESS FULL | C				  |  1489K|    82M|
 | 47157   (2)| 00:09:26 |
|*  3 |   HASH JOIN         |                            |   787K|   119M|  1487
M|   197K  (2)| 00:39:31 |
|*  4 |    TABLE ACCESS FULL| A				 |    13M|  1337M|
 |   123K  (3)| 00:24:43 |
|   5 |    TABLE ACCESS FULL| B				  |  8543 |   433K|
 |    22   (0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------------------

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

   1 - access("C"."SKU"=TO_NUMBER("A"."SKU_ITEM_KEY") AND "A"."LOCATIONNO"="C"."
LOCATIONNO" AND
              "B"."FISCAL_MONTH_YEAR"="C"."MONTH_DATE")
   2 - filter("UNITSSOLD"<>0)
   3 - access("A"."BSNS_UNIT_KEY"="B"."VOYAGENO" AND "A"."LOCATIONNO"="B"."LOCAT
IONNO")
   4 - filter("A"."ITEM_COST_AMT" IS NULL)





*************************************************************************************


Explain Plan For Select /*+ leading (a,c,b) */  *
      From A a,
           B        b,
           C    c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
      And Unitssold != 0 
      and a.Item_Cost_Amt is Null;






PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------
Plan hash value: 2373585061

--------------------------------------------------------------------------------
--------------------------
| Id  | Operation           | Name                       | Rows  | Bytes |TempSp
c| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
--------------------------
|   0 | SELECT STATEMENT    |                            | 73775 |    15M|
 |   249K  (2)| 00:49:58 |
|*  1 |  HASH JOIN          |                            | 73775 |    15M|
 |   249K  (2)| 00:49:58 |
|   2 |   TABLE ACCESS FULL | B     		         |  8543 |   433K|
 |    22   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |                            |  5655K|   889M|  1487
M|   249K  (2)| 00:49:57 |
|*  4 |    TABLE ACCESS FULL| A                          |    13M|  1337M|
 |   123K  (3)| 00:24:43 |
|*  5 |    TABLE ACCESS FULL| C                          |  1489K|    82M|
 | 47157   (2)| 00:09:26 |
--------------------------------------------------------------------------------
--------------------------

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

   1 - access("A"."BSNS_UNIT_KEY"="B"."VOYAGENO" AND "A"."LOCATIONNO"="B"."LOCAT
IONNO" AND
              "B"."FISCAL_MONTH_YEAR"="C"."MONTH_DATE")
   3 - access("C"."SKU"=TO_NUMBER("A"."SKU_ITEM_KEY") AND "A"."LOCATIONNO"="C"."
LOCATIONNO")
   4 - filter("A"."ITEM_COST_AMT" IS NULL)
   5 - filter("UNITSSOLD"<>0)

21 rows selected.

*************************************************************************************



Explain Plan For Select /*+ leading (b,a,c) */  *
      From A  a,
           B        b,
           C    c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
      And Unitssold != 0 
      and a.Item_Cost_Amt is Null;


SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------
Plan hash value: 1292568015

--------------------------------------------------------------------------------
--------------------------
| Id  | Operation           | Name                       | Rows  | Bytes |TempSp
c| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
--------------------------
|   0 | SELECT STATEMENT    |                            |  4432 |   939K|
 |   182K  (3)| 00:36:27 |
|*  1 |  HASH JOIN          |                            |  4432 |   939K|    99
M|   182K  (3)| 00:36:27 |
|*  2 |   TABLE ACCESS FULL | C				|  1489K|    82M|
 | 47157   (2)| 00:09:26 |
|*  3 |   HASH JOIN         |                            |   787K|   119M|
 |   123K  (3)| 00:24:45 |
|   4 |    TABLE ACCESS FULL| B				|  8543 |   433K|
 |    22   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| A				|    13M|  1337M|
 |   123K  (3)| 00:24:43 |
--------------------------------------------------------------------------------
--------------------------

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

   1 - access("C"."SKU"=TO_NUMBER("A"."SKU_ITEM_KEY") AND "A"."LOCATIONNO"="C"."
LOCATIONNO" AND
              "B"."FISCAL_MONTH_YEAR"="C"."MONTH_DATE")
   2 - filter("UNITSSOLD"<>0)
   3 - access("A"."BSNS_UNIT_KEY"="B"."VOYAGENO" AND "A"."LOCATIONNO"="B"."LOCAT
IONNO")
   5 - filter("A"."ITEM_COST_AMT" IS NULL)





*************************************************************************************



Explain Plan For Select /*+ leading (b,c,a) */  *
      From A  a,
           B        b,
           C    c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
      And Unitssold != 0 
      and a.Item_Cost_Amt is Null;



SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------
Plan hash value: 2079919284

--------------------------------------------------------------------------------
--------------------------
| Id  | Operation           | Name                       | Rows  | Bytes |TempSp
c| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
--------------------------
|   0 | SELECT STATEMENT    |                            |  5818 |  1232K|
 |  1207K  (1)| 04:01:36 |
|*  1 |  HASH JOIN          |                            |  5818 |  1232K|  1487
M|  1207K  (1)| 04:01:36 |
|*  2 |   TABLE ACCESS FULL | A				|    13M|  1337M|
 |   123K  (3)| 00:24:43 |
|*  3 |   HASH JOIN         |                            |   166M|    17G|
 | 48916   (6)| 00:09:47 |
|   4 |    TABLE ACCESS FULL| B				|  8543 |   433K|
 |    22   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| C				|  1489K|    82M|
 | 47157   (2)| 00:09:26 |
--------------------------------------------------------------------------------
--------------------------

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

   1 - access("A"."BSNS_UNIT_KEY"="B"."VOYAGENO" AND "A"."LOCATIONNO"="B"."LOCAT
IONNO" AND
              "C"."SKU"=TO_NUMBER("A"."SKU_ITEM_KEY") AND "A"."LOCATIONNO"="C"."
LOCATIONNO")
   2 - filter("A"."ITEM_COST_AMT" IS NULL)
   3 - access("B"."FISCAL_MONTH_YEAR"="C"."MONTH_DATE")
   5 - filter("UNITSSOLD"<>0)

21 rows selected.



*************************************************************************************



Explain Plan For Select /*+ leading (c,a,b) */  *
      From A  a,
           B        b,
           C    c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
      And Unitssold != 0 
      and a.Item_Cost_Amt is Null;






SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------
Plan hash value: 3466300653

--------------------------------------------------------------------------------
--------------------------
| Id  | Operation           | Name                       | Rows  | Bytes |TempSp
c| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
--------------------------
|   0 | SELECT STATEMENT    |                            | 73775 |    15M|
 |   249K  (2)| 00:49:58 |
|*  1 |  HASH JOIN          |                            | 73775 |    15M|
 |   249K  (2)| 00:49:58 |
|   2 |   TABLE ACCESS FULL | B				|  8543 |   433K|
 |    22   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |                            |  5655K|   889M|    99
M|   249K  (2)| 00:49:57 |
|*  4 |    TABLE ACCESS FULL| C				|  1489K|    82M|
 | 47157   (2)| 00:09:26 |
|*  5 |    TABLE ACCESS FULL| A				|    13M|  1337M|
 |   123K  (3)| 00:24:43 |
--------------------------------------------------------------------------------
--------------------------

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

   1 - access("A"."BSNS_UNIT_KEY"="B"."VOYAGENO" AND "A"."LOCATIONNO"="B"."LOCAT
IONNO" AND
              "B"."FISCAL_MONTH_YEAR"="C"."MONTH_DATE")
   3 - access("C"."SKU"=TO_NUMBER("A"."SKU_ITEM_KEY") AND "A"."LOCATIONNO"="C"."
LOCATIONNO")
   4 - filter("UNITSSOLD"<>0)
   5 - filter("A"."ITEM_COST_AMT" IS NULL)



*************************************************************************************


Explain Plan For Select /*+ leading (c,b,a) */  *
      From A  a,
           B        b,
           C    c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
      And Unitssold != 0 
      and a.Item_Cost_Amt is Null;



SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------
Plan hash value: 2330993626

--------------------------------------------------------------------------------
--------------------------
| Id  | Operation           | Name                       | Rows  | Bytes |TempSp
c| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
--------------------------
|   0 | SELECT STATEMENT    |                            |  5818 |  1232K|
 |  1212K  (1)| 04:02:36 |
|*  1 |  HASH JOIN          |                            |  5818 |  1232K|  1487
M|  1212K  (1)| 04:02:36 |
|*  2 |   TABLE ACCESS FULL | A				|    13M|  1337M|
 |   123K  (3)| 00:24:43 |
|*  3 |   HASH JOIN         |                            |   166M|    17G|    99
M| 53899   (5)| 00:10:47 |
|*  4 |    TABLE ACCESS FULL| C				|  1489K|    82M|
 | 47157   (2)| 00:09:26 |
|   5 |    TABLE ACCESS FULL| B				|  8543 |   433K|
 |    22   (0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------------------

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

   1 - access("A"."BSNS_UNIT_KEY"="B"."VOYAGENO" AND "A"."LOCATIONNO"="B"."LOCAT
IONNO" AND
              "C"."SKU"=TO_NUMBER("A"."SKU_ITEM_KEY") AND "A"."LOCATIONNO"="C"."
LOCATIONNO")
   2 - filter("A"."ITEM_COST_AMT" IS NULL)
   3 - access("B"."FISCAL_MONTH_YEAR"="C"."MONTH_DATE")
   4 - filter("UNITSSOLD"<>0)





Thanks for your time.

[Updated on: Thu, 24 January 2013 16:18]

Report message to a moderator

Re: Please help me in rewriting the below query in optimized way [message #575622 is a reply to message #575621] Thu, 24 January 2013 17:36 Go to previous messageGo to next message
Kevin Meade
Messages: 1934
Registered: December 1999
Location: Connecticut USA
Senior Member
Much better, thanks.

So looking at the results of these tests we can make several observations for what they are worth.

----------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN          |                            |  4432 |   939K|    99M|   256K  (2)| 00:51:13 |
|*  2 |   TABLE ACCESS FULL | C                          |  1489K|    82M|       | 47157   (2)| 00:09:26 |
|*  3 |   HASH JOIN         |                            |   787K|   119M|  1487M|   197K  (2)| 00:39:31 |
|*  4 |    TABLE ACCESS FULL| A                          |    13M|  1337M|       |   123K  (3)| 00:24:43 |
|   5 |    TABLE ACCESS FULL| B                          |  8543 |   433K|       |    22   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN          |                            | 73775 |    15M|       |   249K  (2)| 00:49:58 |
|   2 |   TABLE ACCESS FULL | B                          |  8543 |   433K|       |    22   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |                            |  5655K|   889M|  1487M|   249K  (2)| 00:49:57 |
|*  4 |    TABLE ACCESS FULL| A                          |    13M|  1337M|       |   123K  (3)| 00:24:43 |
|*  5 |    TABLE ACCESS FULL| C                          |  1489K|    82M|       | 47157   (2)| 00:09:26 |
----------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN          |                            |  4432 |   939K|    99M|   182K  (3)| 00:36:27 |
|*  2 |   TABLE ACCESS FULL | C                          |  1489K|    82M|       | 47157   (2)| 00:09:26 |
|*  3 |   HASH JOIN         |                            |   787K|   119M|       |   123K  (3)| 00:24:45 |
|   4 |    TABLE ACCESS FULL| B                          |  8543 |   433K|       |    22   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| A                          |    13M|  1337M|       |   123K  (3)| 00:24:43 |
----------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN          |                            |  5818 |  1232K|  1487M|  1207K  (1)| 04:01:36 |
|*  2 |   TABLE ACCESS FULL | A                          |    13M|  1337M|       |   123K  (3)| 00:24:43 |
|*  3 |   HASH JOIN         |                            |   166M|    17G|       | 48916   (6)| 00:09:47 |
|   4 |    TABLE ACCESS FULL| B                          |  8543 |   433K|       |    22   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| C                          |  1489K|    82M|       | 47157   (2)| 00:09:26 |
----------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN          |                            | 73775 |    15M|       |   249K  (2)| 00:49:58 |
|   2 |   TABLE ACCESS FULL | B                          |  8543 |   433K|       |    22   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |                            |  5655K|   889M|    99M|   249K  (2)| 00:49:57 |
|*  4 |    TABLE ACCESS FULL| C                          |  1489K|    82M|       | 47157   (2)| 00:09:26 |
|*  5 |    TABLE ACCESS FULL| A                          |    13M|  1337M|       |   123K  (3)| 00:24:43 |
----------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN          |                            |  5818 |  1232K|  1487M|  1212K  (1)| 04:02:36 |
|*  2 |   TABLE ACCESS FULL | A                          |    13M|  1337M|       |   123K  (3)| 00:24:43 |
|*  3 |   HASH JOIN         |                            |   166M|    17G|    99M| 53899   (5)| 00:10:47 |
|*  4 |    TABLE ACCESS FULL| C                          |  1489K|    82M|       | 47157   (2)| 00:09:26 |
|   5 |    TABLE ACCESS FULL| B                          |  8543 |   433K|       |    22   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------


1) we can see that in all the plans, the table scans all return the same number of rows for each table. No matter which plan we are looking at, we get these counts.

TABLE A --> 13M
TABLE B --> 8543
TABLE C --> 1489k

This is correct in that the ROWS column shows the number of rows returned from a step and since each of this is an "elemental" step so to speak meaning each table scan is a plan step with no children below it (a true leaf node), ROWS should hold the count of rows from scanning the table AFTER FILTERING. This is not the rowcount on the table, it is the rowcount after any WHERE CLAUSE FILTERING has been applied.

2) also, we can see that the BYTES column is just as consistent as the ROWS column. BYTES tells us the number of bytes required to hold the intermediary rowset for a step. So for each of these table scan steps this tells us the number of bytes needed to hold the data from all rows of the table AFTER PROJECTION which means after the optimizer throws away all the columns it does not need for the query. Since we expect the query plan to need the same columns from each tables regardless of the query plan variation we look at, this number is the same for each of the leaf node table scans. This too is as it should be. I am not sure if there are situations where Oracle is smart enough to throw away columns that were needed for a join but not in the final result set so I will test that later. For now it is enough to understand that the BYTES column is the size of the resulting intermediary rowset after the step is complete. This rowset is composed of rows that passed filtering and contains only those columns needed to do the query.

3) HOWEVER, we can see that the ROWS column and the BYTES column are different for the JOIN STEPS in the plan (HASH JOINS for this query). For example, let us look at line 3 for each plan. In all six plan variations this is the first join in the query. I have noted which pair of tables has been joined and the order they were joined in.

----------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|*  3 |   HASH JOIN         | A,B                        |   787K|   119M|  1487M|   197K  (2)| 00:39:31 |
|*  3 |   HASH JOIN         | A,C                        |  5655K|   889M|  1487M|   249K  (2)| 00:49:57 |
|*  3 |   HASH JOIN         | B,A                        |   787K|   119M|       |   123K  (3)| 00:24:45 |
|*  3 |   HASH JOIN         | B,C                        |   166M|    17G|       | 48916   (6)| 00:09:47 |
|*  3 |   HASH JOIN         | C,A                        |  5655K|   889M|    99M|   249K  (2)| 00:49:57 |
|*  3 |   HASH JOIN         | C,B                        |   166M|    17G|    99M| 53899   (5)| 00:10:47 |

We can see that

a) regardless of the order in which we join the tables, the ROWS and BYTES is the same so A,B = B,A. They both yeild ROWS = 787K and BYTES = 119M. Same with the other variations.

b) BUT... DIFFERENT pairs of tables being joined produce different intermediary rowcounts and different intermediary rowset sizes. This of course should also be expected since it A,B != A,C from a join perspective (unless B = C).

This is all actually quite obvious. But is does point out that the cost of different joins is evident first in the number of rows it produces and second in the size of the rowset that is produced and also possibly in the number of hash probes done when doing the actual row joins.

4) we also can see a difference in the cost. Let us change the ordering of line 3 so that the same table pairs are back to back.

----------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|*  3 |   HASH JOIN         | A,B                        |   787K|   119M|  1487M|   197K  (2)| 00:39:31 |
|*  3 |   HASH JOIN         | B,A                        |   787K|   119M|       |   123K  (3)| 00:24:45 |
|*  3 |   HASH JOIN         | A,C                        |  5655K|   889M|  1487M|   249K  (2)| 00:49:57 |
|*  3 |   HASH JOIN         | C,A                        |  5655K|   889M|    99M|   249K  (2)| 00:49:57 |
|*  3 |   HASH JOIN         | B,C                        |   166M|    17G|       | 48916   (6)| 00:09:47 |
|*  3 |   HASH JOIN         | C,B                        |   166M|    17G|    99M| 53899   (5)| 00:10:47 |


See here how the ROWS and BYTES are the same for equivelant table pairs, but that the TEMPSPC needed is different. This is normal and is a reflection of how HASH JOIN works. Since one of the rowsets feeding into the hash join must be pushed to memory, the cost to join can in several ways. This has to do with:

a) if the in-memory table will not fit in memory and thus must spill to disk

b) if spilling to disk is necessary, how much spills to disk and thus how many join passes are need to complete the join

c) how many total join attempts must be done (e.g. how many probes of the hashed table are needed)

From these six plan variations we can surmize that TABLE B will fit in memory and that it is the only table that will fit into memory. That is why the two join pairs that push TABLE B into memory (B,A and B,C) do not require tempspc in order to do the join. These joins are OPTIMAL (as vs. 1-pass or multi-pass). If the result of these joins is itself too big to fit into memory then these rows will be written to tempspc but that will be charged off to the next join operation.

So looking at these joins we see that the B,A join looks to be the most efficient. It keeps the intermediary rowsets smallest and requires the least amount of tempspc. Since there is only one table left C, the best join order is B,A,C. You will note that this is the same join order that the OP posted originally. The OP already has what looks to be the best plan.

5) one last thing we should point out is that although we see that the best join order is B,A,C; and this is the same join order as was original provided by the OP, the OP's original query plan is not the same as the plan shown here. The OP posted this:

--------------------------------------------------------------------------------
| Id  | Operation              | Name                   | Rows  | Bytes |TempSpc
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                        |  4432 |   354K|
|*  1 |  HASH JOIN             |                        |  4432 |   354K|    38M
|*  2 |   HASH JOIN            |                        |   787K|    29M|
|   3 |    TABLE ACCESS FULL   | B                      |  8542 |   150K|
|*  4 |    INDEX FAST FULL SCAN| A                      |    13M|   262M|
|*  5 |   TABLE ACCESS FULL    | C                      |  1489K|    61M|
--------------------------------------------------------------------------------


Notice that line #4 is not a TABLE SCAN. This is not necessarily the fault of the OP. There are several possibilities as to why the plan given in our six plan group is different that the original plan posted for the B,A,C join order.

a) the OP was looking at different tables

b) there was an index that was dropped

c) EXPLAIN PLAN is by it nature a guess based on current session settings, not the actual plan used. The OP originally posted the actual plan used because the OP used dbms_xplan.display which I am prett sure provides the plan for the last query executed and is thus the actual plan used.

So this is may be a case of EXPLAIN PLAN not giving the actual plan. Neat. Or maybe the OP can provide some other explanation.

In any event this is what I wanted people to see, that plan costs are driven by various factors and the OP had the best plan to begin with, at least based on the CARDINALITY and SPACE requirements of EXPECTED INTERMEDIARY ROWSETS.

Good luck, Kevin
Re: Please help me in rewriting the below query in optimized way [message #575624 is a reply to message #575622] Thu, 24 January 2013 17:56 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Thanks a lot Kevin for your good explanation.

Please find the plan output for original query and plan output for BAC.


Explain Plan For Select  *
      From A  a,
           B        b,
           C    c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
      And Unitssold != 0 
      and a.Item_Cost_Amt is Null;


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------
Plan hash value: 1424127997

--------------------------------------------------------------------------------
-------------------------
| Id  | Operation              | Name                   | Rows  | Bytes |TempSpc
| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
-------------------------
|   0 | SELECT STATEMENT       |                        |  6507 |   521K|
| 75567   (2)| 00:15:07 |
|*  1 |  HASH JOIN             |                        |  6507 |   521K|    56M
| 75567   (2)| 00:15:07 |
|*  2 |   HASH JOIN            |                        |  1156K|    42M|
| 21695   (3)| 00:04:21 |
|   3 |    TABLE ACCESS FULL   | B                      |  8543 |   150K|
|    22   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| A                      |    19M|   385M|
| 21471   (2)| 00:04:18 |
|*  5 |   TABLE ACCESS FULL    | C                      |  1489K|    61M|
| 47157   (2)| 00:09:26 |
--------------------------------------------------------------------------------
-------------------------

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

   1 - access("C"."SKU"=TO_NUMBER("A"."SKU_ITEM_KEY") AND "A"."LOCATIONNO"="C"."
LOCATIONNO" AND
              "B"."FISCAL_MONTH_YEAR"="C"."MONTH_DATE")
   2 - access("A"."BSNS_UNIT_KEY"="B"."VOYAGENO" AND "A"."LOCATIONNO"="B"."LOCAT
IONNO")
   5 - filter("UNITSSOLD"<>0)

20 rows selected.

********************************************************

Explain Plan For Select /*+ leading (b,a,c) */  *
      From A  a,
           B        b,
           C    c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
      And Unitssold != 0 
      and a.Item_Cost_Amt is Null;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------
Plan hash value: 1292568015

--------------------------------------------------------------------------------
--------------------------
| Id  | Operation           | Name                       | Rows  | Bytes |TempSp
c| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
--------------------------
|   0 | SELECT STATEMENT    |                            |  4432 |   939K|
 |   182K  (3)| 00:36:27 |
|*  1 |  HASH JOIN          |                            |  4432 |   939K|    99
M|   182K  (3)| 00:36:27 |
|*  2 |   TABLE ACCESS FULL | C                          |  1489K|    82M|
 | 47157   (2)| 00:09:26 |
|*  3 |   HASH JOIN         |                            |   787K|   119M|
 |   123K  (3)| 00:24:45 |
|   4 |    TABLE ACCESS FULL| B                          |  8543 |   433K|
 |    22   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| A                          |    13M|  1337M|
 |   123K  (3)| 00:24:43 |
--------------------------------------------------------------------------------
--------------------------

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

   1 - access("C"."SKU"=TO_NUMBER("A"."SKU_ITEM_KEY") AND "A"."LOCATIONNO"="C"."
LOCATIONNO" AND
              "B"."FISCAL_MONTH_YEAR"="C"."MONTH_DATE")
   2 - filter("UNITSSOLD"<>0)
   3 - access("A"."BSNS_UNIT_KEY"="B"."VOYAGENO" AND "A"."LOCATIONNO"="B"."LOCAT
IONNO")
   5 - filter("A"."ITEM_COST_AMT" IS NULL)

21 rows selected.


Re: Please help me in rewriting the below query in optimized way [message #575625 is a reply to message #575624] Thu, 24 January 2013 18:05 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
I don't understand it's giving different plan if plan output is retrieved from SQL*PLUS and PL/SQL Developer tool.

The above outputs are from SQL*PLUS.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1424127997
--------------------------------------------------------------------------------
| Id  | Operation              | Name                   | Rows  | Bytes |TempSpc
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                        |  6507 |   521K|
|*  1 |  HASH JOIN             |                        |  6507 |   521K|    56M
|*  2 |   HASH JOIN            |                        |  1156K|    42M|
|   3 |    TABLE ACCESS FULL   | B                      |  8543 |   150K|
|   4 |    INDEX FAST FULL SCAN| A                      |    19M|   385M|
|*  5 |   TABLE ACCESS FULL    | C                      |  1489K|    61M|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C"."SKU"=TO_NUMBER("A"."SKU_ITEM_KEY") AND "A"."LOCATIONNO"="C"."
              "B"."FISCAL_MONTH_YEAR"="C"."MONTH_DATE")
   2 - access("A"."BSNS_UNIT_KEY"="B"."VOYAGENO" AND "A"."LOCATIONNO"="B"."LOCAT
   5 - filter("UNITSSOLD"<>0)
 
20 rows selected


With BAC hint
*****************

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1424127997
--------------------------------------------------------------------------------
| Id  | Operation              | Name                   | Rows  | Bytes |TempSpc
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                        |  6507 |   521K|
|*  1 |  HASH JOIN             |                        |  6507 |   521K|    56M
|*  2 |   HASH JOIN            |                        |  1156K|    42M|
|   3 |    TABLE ACCESS FULL   | B                      |  8543 |   150K|
|   4 |    INDEX FAST FULL SCAN| A                     |    19M|   385M|
|*  5 |   TABLE ACCESS FULL    | C                     |  1489K|    61M|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C"."SKU"=TO_NUMBER("A"."SKU_ITEM_KEY") AND "A"."LOCATIONNO"="C"."
              "B"."FISCAL_MONTH_YEAR"="C"."MONTH_DATE")
   2 - access("A"."BSNS_UNIT_KEY"="B"."VOYAGENO" AND "A"."LOCATIONNO"="B"."LOCAT
   5 - filter("UNITSSOLD"<>0)
 
20 rows selected

Re: Please help me in rewriting the below query in optimized way [message #575626 is a reply to message #575625] Thu, 24 January 2013 20:38 Go to previous messageGo to next message
Kevin Meade
Messages: 1934
Registered: December 1999
Location: Connecticut USA
Senior Member
Plans are affected by environment settings. I could easily envision that PL/SQL DEVELOPER has changed environment settings and thus the plans can be affected accordingly. If there is a v$ses_optimizer_env view you can look at, figure out the sid of your sqlpus and developer sessions and see what is different. You will likely find one or more optimizer settings differ between the two. You can then adjust your sqlplus session to match your developer session or otherwise, and see if that makes things the same.
Re: Please help me in rewriting the below query in optimized way [message #575708 is a reply to message #575625] Fri, 25 January 2013 13:02 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Quote:
I don't understand it's giving different plan if plan output is retrieved from SQL*PLUS and PL/SQL Developer tool.


Do you mean you get different plans when the query is executed in PL/SQL Developer and SQL*Plus? Or, the query is executed once and you look at the resulting plan in two places? (Just checking.)

As Kevin suggests, check V$SES_OPTIMIZER_ENV.

Also, are you running exactly same query, or some variation e.g. a PL/SQL block with variables in one and a query with literals in the other?

[Updated on: Fri, 25 January 2013 13:18]

Report message to a moderator

Re: Please help me in rewriting the below query in optimized way [message #575719 is a reply to message #575625] Fri, 25 January 2013 19:16 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
This is the below procedure which I am trying to tune.


CREATE OR REPLACE Procedure Sample_proc Is
  Type Type_Name Is Record(
    Sku_Item_Key            AAAAA.Sku_Item_Key%Type,
    Locationno              AAAAA.Locationno%Type,
    Bsns_Unit_Key           AAAAA.Bsns_Unit_Key%Type,
    Act_Item_Cost_Amt       AAAAA.Item_Cost_Amt%Type,
    Act_Rglr_Unit_Price_Amt AAAAA.Rglr_Unit_Price_Amt%Type,
    Cost_Cal_Amt            AAAAA.Rglr_Unit_Price_Amt%Type,
    Rtl_Cal_Amt             AAAAA.Rglr_Unit_Price_Amt%Type,
    Rtl_Cal_Amt1            AAAAA.Rglr_Unit_Price_Amt%Type,
    Unitssold               CCCCC.Unitssold%Type,
    Markdownunitssold       CCCCC.Markdownunitssold%Type);
  Type Type_Name_1 Is Record(
    Trx_Nbr               AAAAA.Trx_Nbr%Type,
    Trx_Line_Item_Seq_Nbr AAAAA.Trx_Line_Item_Seq_Nbr%Type,
    Markdwn               AAAAA.Mrkdn_Amt%Type,
    Markdup               AAAAA.Mrkup_Amt%Type);
  t_Type_Name  Type_Name;
  t_Type_Name1 Type_Name_1;
  Type Rtl_Tbl Is Table Of t_Type_Name%Type Index By Pls_Integer;
  Type Rtl_Tbl_1 Is Table Of t_Type_Name1%Type Index By Pls_Integer;
  t_Rtl_Tbl_1 Rtl_Tbl;
  t_Rtl_Tbl_2 Rtl_Tbl;
  t_Rtl_Tbl_3 Rtl_Tbl;
  t_Rtl_Tbl_4 Rtl_Tbl_1;
  --l_Error_Count Number;
  Ex_Dml_Errors Exception;
  Pragma Exception_Init(Ex_Dml_Errors, -24381);
  l_Error_Count1 Number;
  l_Error_Count2 Number;
  l_Error_Count3 Number;
  l_Error_Count4 Number;
  Type Ref_Cursor Is Ref Cursor;
  C1    Ref_Cursor;
  C2    Ref_Cursor;
  C3    Ref_Cursor;
  v_Sql Varchar2(4000);

  Cursor C4 Is
    Select t.Trx_Nbr,
           t.Trx_Line_Item_Seq_Nbr,
           Case
             When t.Act_Unit_Price_Amt < t.Rglr_Unit_Price_Amt Then
              (t.Rglr_Unit_Price_Amt - t.Act_Unit_Price_Amt) * t.Qty
             Else
              Null
           End Markdwn,
           Case
             When t.Act_Unit_Price_Amt > t.Rglr_Unit_Price_Amt Then
              (t.Act_Unit_Price_Amt - t.Rglr_Unit_Price_Amt) * t.Qty
             Else
              Null
           End Markdup
      From AAAAA t;
Begin



  v_Sql := 'Select /*+ leading (b,a,c) */  a.Sku_Item_Key,
           a.Locationno,
           a.Bsns_Unit_Key,
           a.Item_Cost_Amt,
           a.Rglr_Unit_Price_Amt,
           c.Salescostdollars / c.Unitssold Cost_Cal_Amt,
           (c.Salesretaildollars - c.Markdownretaildollars) /
           (c.Unitssold - c.Markdownunitssold) Rtl_Cal_Amt,
           c.Salesretaildollars / c.Unitssold Rtl_Cal_Amt1,
           c.Unitssold,
           c.Markdownunitssold
      From AAAAA a,
           BBBBB        b,
           CCCCC    c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
       And Unitssold != 0';

  Open C1 For v_Sql || ' ' || ' and a.Item_Cost_Amt is null';
  Loop
    Begin
      Fetch C1 Bulk Collect
        Into t_Rtl_Tbl_1 Limit 10000;
      Exit When C1%Notfound;

      Forall Indx In 1 .. t_Rtl_Tbl_1.Count Save Exceptions

                      
                          Execute Immediate
                          'Update AAAAA t
           Set t.Item_Cost_Amt = :1
         Where t.Sku_Item_Key = :2
           And t.Locationno = :3
           And t.Bsns_Unit_Key = :4
           And t.Item_Cost_Amt Is Null' Using t_Rtl_Tbl_1(Indx)
                         .Cost_Cal_Amt, t_Rtl_Tbl_1(Indx).Sku_Item_Key, t_Rtl_Tbl_1(Indx)
                         .Locationno, t_Rtl_Tbl_1(Indx).Bsns_Unit_Key
        ;

    Exception
      When Ex_Dml_Errors Then
        l_Error_Count1 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count1: ' ||
                             l_Error_Count1);
        For i In 1 .. l_Error_Count1 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
    End;
    Commit;
  End Loop;
  Close C1;

 Open C2 For v_Sql || ' ' || ' and c.Unitssold > c.Markdownunitssold And  a.Rglr_Unit_Price_Amt Is Null';
  Loop
    Begin
      Fetch C2 Bulk Collect
        Into t_Rtl_Tbl_2 Limit 10000;
      Exit When C2%Notfound;

      Forall Indx In 1 .. t_Rtl_Tbl_2.Count Save Exceptions

                         
                          Execute Immediate
                          'Update AAAAA t
           Set t.Rglr_Unit_Price_Amt = :1
         Where t.Sku_Item_Key = :2
           And t.Locationno = :3
           And t.Bsns_Unit_Key = :4
           And t.Rglr_Unit_Price_Amt Is Null'
                          Using t_Rtl_Tbl_2(Indx).Rtl_Cal_Amt, t_Rtl_Tbl_2(Indx)
                         .Sku_Item_Key, t_Rtl_Tbl_2(Indx).Locationno, t_Rtl_Tbl_2(Indx)
                         .Bsns_Unit_Key
        ;

    Exception
      When Ex_Dml_Errors Then
        l_Error_Count2 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count2: ' ||
                             l_Error_Count2);
        For i In 1 .. l_Error_Count2 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
    End;
    Commit;
  End Loop;
  Close C2;

  Open C3 For v_Sql || ' ' || ' and  c.Unitssold <= c.Markdownunitssold And  a.Rglr_Unit_Price_Amt Is Null';
  Loop
    Begin
      Fetch C3 Bulk Collect
        Into t_Rtl_Tbl_3 Limit 10000;
      Exit When C3%Notfound;

      Forall Indx In 1 .. t_Rtl_Tbl_3.Count Save Exceptions

                      
                          Execute Immediate
                          'Update AAAAA t
           Set t.Rglr_Unit_Price_Amt = :1
         Where t.Sku_Item_Key = :2
           And t.Locationno = :3
           And t.Bsns_Unit_Key = :4
           And t.Rglr_Unit_Price_Amt Is Null'
                          Using t_Rtl_Tbl_3(Indx).Rtl_Cal_Amt, t_Rtl_Tbl_3(Indx)
                         .Sku_Item_Key, t_Rtl_Tbl_3(Indx).Locationno, t_Rtl_Tbl_3(Indx)
                         .Bsns_Unit_Key
        ;

    Exception
      When Ex_Dml_Errors Then
        l_Error_Count3 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count3: ' ||
                             l_Error_Count3);
        For i In 1 .. l_Error_Count3 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
    End;
    Commit;
  End Loop;
  Close C3;

  Open C4;
  Loop
    Begin
      Fetch C4 Bulk Collect
        Into t_Rtl_Tbl_4 Limit 10000;
      Exit When C4%Notfound;
 
      Forall Indx In 1 .. t_Rtl_Tbl_4.Count Save Exceptions

                          Execute Immediate
                          'Update AAAAA t
           Set t.Mrkdn_Amt = :1,
               t.Mrkup_Amt = :2
         Where t.Trx_Nbr = :3
           And t.Trx_Line_Item_Seq_Nbr = :4' Using t_Rtl_Tbl_4(Indx)
                         .Markdwn, t_Rtl_Tbl_4(Indx).Markdup, t_Rtl_Tbl_4(Indx)
                         .Trx_Nbr, t_Rtl_Tbl_4(Indx).Trx_Line_Item_Seq_Nbr
        ;

    Exception
      When Ex_Dml_Errors Then
        l_Error_Count4 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count4: ' ||
                             l_Error_Count4);
        For i In 1 .. l_Error_Count4 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
    End;
    Commit;
  End Loop;
  Close C4;/

End;





Please let me know whether it can be written in a optimized way.

Thanks in advance

[Updated on: Fri, 25 January 2013 19:17]

Report message to a moderator

Re: Please help me in rewriting the below query in optimized way [message #575735 is a reply to message #575719] Sat, 26 January 2013 04:13 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
I don't think the EXECUTE IMMEDIATE sections need to be dynamic unless I'm missing something, although I wouldn't expect a big performance gain from making them static.

However I would try to combine all of those queries into a single UPDATE statement using CASE statements for the conditional logic, so you do everything in a single pass.
Re: Please help me in rewriting the below query in optimized way [message #575842 is a reply to message #575735] Mon, 28 January 2013 06:00 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
There definitely shouldn't be any dynamic sql there.
It should probably be two updates - 1 that replicates what c1, c2 and c3 do, and a seperate one for c4 since that seems substantually different.
Re: Please help me in rewriting the below query in optimized way [message #577524 is a reply to message #575842] Mon, 18 February 2013 03:29 Go to previous messageGo to next message
TuneMyQuery
Messages: 9
Registered: February 2013
Junior Member

Hi,

Do you really need to perform a "select *" ?

Please specify only the columns you need really in the select clause. If those columns are part of indexes involved in joins, it should results in some tables scans being replaced by index scans, which is much quicker.

Regards,

Emmanuel
Re: Please help me in rewriting the below query in optimized way [message #577535 is a reply to message #577524] Mon, 18 February 2013 04:36 Go to previous messageGo to next message
michael_bialik
Messages: 601
Registered: July 2006
Senior Member
Hi.

1. You don't need any dynamic sql here. It's just unnecessary consumes CPU (and probably performs unnecessary soft parses). Change it to embedded sql.
2. You original query does not have any indexable predicates (except joins), co optimizer has to match all rows of all tables and it rightfully decides to do it
by HASH join and full table scans.
3. Post TKPROF - it's impossible to know where your proc is spending it's time.

HTH
Re: Please help me in rewriting the below query in optimized way [message #577807 is a reply to message #577535] Wed, 20 February 2013 15:38 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Below is proc. Removed all the dynamic sql.

Even though its taking so much time. Never completed successfully.


Is there anyway to rewrite the same proc with efficient sql.

Create Or Replace Procedure Sales_Hist_Update_Bkp Is
  Type Type_Name Is Record(
    Sku_Item_Key            Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Sku_Item_Key%Type,
    Locationno              Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Locationno%Type,
    Bsns_Unit_Key           Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Bsns_Unit_Key%Type,
    Act_Item_Cost_Amt       Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Item_Cost_Amt%Type,
    Act_Rglr_Unit_Price_Amt Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type,
    Cost_Cal_Amt            Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type,
    Rtl_Cal_Amt             Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type,
    Rtl_Cal_Amt1            Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type,
    Unitssold               Stage_Ordm.Dwh_Skulocationbymonth.Unitssold%Type,
    Markdownunitssold       Stage_Ordm.Dwh_Skulocationbymonth.Markdownunitssold%Type);
  Type Type_Name_1 Is Record(
    Trx_Nbr               Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Trx_Nbr%Type,
    Trx_Line_Item_Seq_Nbr Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Trx_Line_Item_Seq_Nbr%Type,
    Markdwn               Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Mrkdn_Amt%Type,
    Markdup               Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Mrkup_Amt%Type);
  t_Type_Name  Type_Name;
  t_Type_Name1 Type_Name_1;
  Type Rtl_Tbl Is Table Of t_Type_Name%Type Index By Pls_Integer;
  Type Rtl_Tbl_1 Is Table Of t_Type_Name1%Type Index By Pls_Integer;
  t_Rtl_Tbl_1 Rtl_Tbl;
  t_Rtl_Tbl_2 Rtl_Tbl;
  t_Rtl_Tbl_3 Rtl_Tbl;
  t_Rtl_Tbl_4 Rtl_Tbl_1;
  l_array_size number default 10000;
  --l_Error_Count Number;
  Ex_Dml_Errors Exception;
  Pragma Exception_Init(Ex_Dml_Errors, -24381);
  l_Error_Count1 Number;
  l_Error_Count2 Number;
  l_Error_Count3 Number;
  l_Error_Count4 Number;
  Type Ref_Cursor Is Ref Cursor;
  C1    Ref_Cursor;
  C2    Ref_Cursor;
  C3    Ref_Cursor;
  v_Sql Varchar2(4000);
  a     Varchar2(100);
  Emesg Varchar2(3000);
  Ecode Number;
  --cursor to update values for Markdwn, Markdup columns.
  Cursor C4 Is
    Select t.Trx_Nbr,
           t.Trx_Line_Item_Seq_Nbr,
           Case
             When t.Act_Unit_Price_Amt < t.Rglr_Unit_Price_Amt Then
              (t.Rglr_Unit_Price_Amt - t.Act_Unit_Price_Amt) * t.Qty
             Else
              Null
           End Markdwn,
           Case
             When t.Act_Unit_Price_Amt > t.Rglr_Unit_Price_Amt Then
              (t.Act_Unit_Price_Amt - t.Rglr_Unit_Price_Amt) * t.Qty
             Else
              Null
           End Markdup
      From Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp t;
Begin

  --Common cursor to update values for Item_Cost_Amt, Rglr_Unit_Price_Amt

  v_Sql := 'Select /*+ leading (b,a,c) */ distinct a.Sku_Item_Key,
           a.Locationno,
           a.Bsns_Unit_Key,
           a.Item_Cost_Amt,
           a.Rglr_Unit_Price_Amt,
           c.Salescostdollars / c.Unitssold Cost_Cal_Amt,
           (c.Salesretaildollars - c.Markdownretaildollars) /
           (c.Unitssold - c.Markdownunitssold) Rtl_Cal_Amt,
           c.Salesretaildollars / c.Unitssold Rtl_Cal_Amt1,
           c.Unitssold,
           c.Markdownunitssold
      From Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp a,
           Stage_Ordm.Dwh_Voyageschedule        b,
           Stage_Ordm.Dwh_Skulocationbymonth    c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
       And Unitssold != 0';

  Open C1 For v_Sql || ' ' || ' and a.Item_Cost_Amt is null';
  Loop
    Begin
      Fetch C1 Bulk Collect
        Into t_Rtl_Tbl_1 Limit l_array_size;
   Exit When t_Rtl_Tbl_1.Count=0;
      Forall Indx In 1 .. t_Rtl_Tbl_1.Count Save Exceptions
      
      --Update Dwi_Rtl_Sls_Retrn_Line_bkp for Item_Cost_Amt values from cusror where Item_Cost_Amt Is Null
      
        Update Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp t
           Set t.Item_Cost_Amt = t_Rtl_Tbl_1(Indx).Cost_Cal_Amt
         Where t.Sku_Item_Key = t_Rtl_Tbl_1(Indx).Sku_Item_Key
           And t.Locationno = t_Rtl_Tbl_1(Indx).Locationno
           And t.Bsns_Unit_Key = t_Rtl_Tbl_1(Indx).Bsns_Unit_Key
           And t.Item_Cost_Amt Is Null;
    
    Exception
      When Ex_Dml_Errors Then
        l_Error_Count1 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count1: ' ||
                             l_Error_Count1);
        For i In 1 .. l_Error_Count1 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
      When Others Then
        Emesg := Sqlerrm;
        Ecode := Sqlcode;
        Dbms_Output.Put_Line(Ecode || ' ' || Emesg);
    End;
  
    Commit;

  End Loop;
  Emesg := v_Sql || ' ' || ' and a.Item_Cost_Amt is null';
  Close C1;

  Open C2 For v_Sql || ' ' || ' and c.Unitssold > c.Markdownunitssold And  a.Rglr_Unit_Price_Amt Is Null';
  Loop
    Begin
      Fetch C2 Bulk Collect
        Into t_Rtl_Tbl_2 Limit l_array_size;
      
    Exit When t_Rtl_Tbl_2.Count=0;
      Forall Indx In 1 .. t_Rtl_Tbl_2.Count Save Exceptions
      
      --Update Dwi_Rtl_Sls_Retrn_Line_bkp for Rglr_Unit_Price_Amt values from cusror where Rglr_Unit_Price_Amt Is Null and
      --Unitssold > Markdownunitssold of Dwh_Skulocationbymonth table
      
        Update Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp t
           Set t.Rglr_Unit_Price_Amt = t_Rtl_Tbl_2(Indx).Rtl_Cal_Amt
         Where t.Sku_Item_Key = t_Rtl_Tbl_2(Indx).Sku_Item_Key
           And t.Locationno = t_Rtl_Tbl_2(Indx).Locationno
           And t.Bsns_Unit_Key = t_Rtl_Tbl_2(Indx).Bsns_Unit_Key
           And t.Rglr_Unit_Price_Amt Is Null;
    
    Exception
      When Ex_Dml_Errors Then
        l_Error_Count2 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count2: ' ||
                             l_Error_Count2);
        For i In 1 .. l_Error_Count2 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
      When Others Then
        Emesg := Sqlerrm;
        Ecode := Sqlcode;
        Dbms_Output.Put_Line(Ecode || ' ' || Emesg);
    End;
    Commit;
   
  End Loop;
  Close C2;

Open C3 For v_Sql || ' ' || ' and  c.Unitssold <= c.Markdownunitssold And  a.Rglr_Unit_Price_Amt Is Null';
  Loop
    Begin
      Fetch C3 Bulk Collect
        Into t_Rtl_Tbl_3 Limit l_array_size;
    Exit When t_Rtl_Tbl_3.Count=0;
      Forall Indx In 1 .. t_Rtl_Tbl_3.Count Save Exceptions
      
      --Update Dwi_Rtl_Sls_Retrn_Line_bkp for Rglr_Unit_Price_Amt values from cusror where Rglr_Unit_Price_Amt Is Null and
      --Unitssold <= Markdownunitssold of Dwh_Skulocationbymonth table
        Update Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp t
           Set t.Rglr_Unit_Price_Amt = t_Rtl_Tbl_3(Indx).Rtl_Cal_Amt
         Where t.Sku_Item_Key = t_Rtl_Tbl_3(Indx).Sku_Item_Key
           And t.Locationno = t_Rtl_Tbl_3(Indx).Locationno
           And t.Bsns_Unit_Key = t_Rtl_Tbl_3(Indx).Bsns_Unit_Key
           And t.Rglr_Unit_Price_Amt Is Null;
    
    Exception
      When Ex_Dml_Errors Then
        l_Error_Count3 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count3: ' ||
                             l_Error_Count3);
        For i In 1 .. l_Error_Count3 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
      When Others Then
        Emesg := Sqlerrm;
        Ecode := Sqlcode;
        Dbms_Output.Put_Line(Ecode || ' ' || Emesg);
    End;
  
    Commit;
    
  End Loop;
  Close C3;

  Open C4;
  Loop
    Begin
      Fetch C4 Bulk Collect
        Into t_Rtl_Tbl_4 Limit l_array_size;
    Exit When t_Rtl_Tbl_4.Count=0;
      --Update markdwn and markup values for table Dwi_Rtl_Sls_Retrn_Line_bkp from cursor 2.
      --This update is done after updation of Item_Cost_Amt, Rglr_Unit_Price_Amt values.
      Forall Indx In 1 .. t_Rtl_Tbl_4.Count Save Exceptions
      
        Update Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp t
           Set t.Mrkdn_Amt = t_Rtl_Tbl_4(Indx).Markdwn,
               t.Mrkup_Amt = t_Rtl_Tbl_4(Indx).Markdup
         Where t.Trx_Nbr = t_Rtl_Tbl_4(Indx).Trx_Nbr
           And t.Trx_Line_Item_Seq_Nbr = t_Rtl_Tbl_4(Indx)
              .Trx_Line_Item_Seq_Nbr;
    
    Exception
      When Ex_Dml_Errors Then
        l_Error_Count4 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count4: ' ||
                             l_Error_Count4);
        For i In 1 .. l_Error_Count4 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
      When Others Then
        Emesg := Sqlerrm;
        Ecode := Sqlcode;
        Dbms_Output.Put_Line(Ecode || ' ' || Emesg);
    End;
    Commit;
   
  End Loop;
  Close C4;

End;

Re: Please help me in rewriting the below query in optimized way [message #577809 is a reply to message #577807] Wed, 20 February 2013 17:06 Go to previous messageGo to next message
Kevin Meade
Messages: 1934
Registered: December 1999
Location: Connecticut USA
Senior Member
There are always things we can do. I will give you one idea.

consider three of your udpate statements.

  Open C1 For v_Sql || ' ' || ' and a.Item_Cost_Amt is null';

        Update Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp t
           Set t.Item_Cost_Amt = t_Rtl_Tbl_1(Indx).Cost_Cal_Amt
         Where t.Sku_Item_Key = t_Rtl_Tbl_1(Indx).Sku_Item_Key
           And t.Locationno = t_Rtl_Tbl_1(Indx).Locationno
           And t.Bsns_Unit_Key = t_Rtl_Tbl_1(Indx).Bsns_Unit_Key
           And t.Item_Cost_Amt Is Null;

  Open C2 For v_Sql || ' ' || ' and c.Unitssold > c.Markdownunitssold And  a.Rglr_Unit_Price_Amt Is Null';

        Update Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp t
           Set t.Rglr_Unit_Price_Amt = t_Rtl_Tbl_2(Indx).Rtl_Cal_Amt
         Where t.Sku_Item_Key = t_Rtl_Tbl_2(Indx).Sku_Item_Key
           And t.Locationno = t_Rtl_Tbl_2(Indx).Locationno
           And t.Bsns_Unit_Key = t_Rtl_Tbl_2(Indx).Bsns_Unit_Key
           And t.Rglr_Unit_Price_Amt Is Null;

Open C3 For v_Sql || ' ' || ' and  c.Unitssold <= c.Markdownunitssold And  a.Rglr_Unit_Price_Amt Is Null';

        Update Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp t
           Set t.Rglr_Unit_Price_Amt = t_Rtl_Tbl_3(Indx).Rtl_Cal_Amt
         Where t.Sku_Item_Key = t_Rtl_Tbl_3(Indx).Sku_Item_Key
           And t.Locationno = t_Rtl_Tbl_3(Indx).Locationno
           And t.Bsns_Unit_Key = t_Rtl_Tbl_3(Indx).Bsns_Unit_Key
           And t.Rglr_Unit_Price_Amt Is Null;

These all seem to use the same basic rowset as input. Additionally upon examination it looks like these three updates each update three different sets of rows. Even if there is overlap among them I don't think that matters because the data that is being updated is not related to the database being fetched. Thus update#1 does not affect the set of rows updated by update#2 and so forth.

with that in mind I would try to combine the three updates into one using case. Consider these lines addes to the query:

,case when a.Item_Cost_Amt is null then 1 else 0 end item_id_null
,case when c.Unitssold > c.Markdownunitssold And  a.Rglr_Unit_Price_Amt Is Null then 1 else 0 end units_gt_markdown
,case when c.Unitssold <= c.Markdownunitssold And  a.Rglr_Unit_Price_Amt Is Null then 1 else 0 end units_lt_markdown

and (
      a.item_cost_amnt is null or
      c.Unitssold > c.Markdownunitssold And  a.Rglr_Unit_Price_Amt Is Null or
      c.Unitssold <= c.Markdownunitssold And  a.Rglr_Unit_Price_Amt Is Null
    )

If you add these three columns to your base query and this where clause predicate, then you can query the data once and do a form of conditional update; something like this maybe:

Update Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp t set
(
  t.Item_Cost_Amt = case when t_Rtl_Tbl_1(Indx).item_id_null = 1 then t_Rtl_Tbl_1(Indx).Cost_Cal_Amt else t.item_cost_amt) end
, t.Rglr_Unit_Price_Amt = case when t_Rtl_Tbl_1(Indx).units_gt_markdown = 1 then t_Rtl_Tbl_1(Indx).Rtl_Cal_Amt else t.rglr_Unit_price_amt) end
, t.Rglr_Unit_Price_Amt = case when t_Rtl_Tbl_1(Indx).units_lt_markdown = 1 then t_Rtl_Tbl_1(Indx).Rtl_Cal_Amt else t.rglr_unit_price_amt) end
)
Where t.Sku_Item_Key = t_Rtl_Tbl_1(Indx).Sku_Item_Key
And t.Locationno = t_Rtl_Tbl_1(Indx).Locationno
And t.Bsns_Unit_Key = t_Rtl_Tbl_1(Indx).Bsns_Unit_Key
;

If you succeed, you have eliminated a significant amount of the work.

Kevin
Re: Please help me in rewriting the below query in optimized way [message #577810 is a reply to message #577809] Wed, 20 February 2013 17:15 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Thanks Kevin.

your solution was great.

But the scenario I have here is if the Rglr_Unit_Price_Amt is null after the C2 process only those records are picked up for C3.

Its a kind of iterative process.

Once again thanks for your help.
Re: Please help me in rewriting the below query in optimized way [message #577811 is a reply to message #577809] Wed, 20 February 2013 17:23 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Thanks Kevin I Understood. it works for me.

I made a mistake.

Re: Please help me in rewriting the below query in optimized way [message #577880 is a reply to message #577811] Thu, 21 February 2013 07:52 Go to previous messageGo to next message
Kevin Meade
Messages: 1934
Registered: December 1999
Location: Connecticut USA
Senior Member
Way to go dude. I am glad you worked at it. What kind of improvement did you get?

Also, would you mind posting the updated version of your procedure please. This will allow others to see what you did, and it will allow us to take a possible look at next steps to further improve.

Kevin

[Updated on: Thu, 21 February 2013 07:54]

Report message to a moderator

Re: Please help me in rewriting the below query in optimized way [message #577931 is a reply to message #577880] Thu, 21 February 2013 19:20 Go to previous message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi Kevin,

It took 320 minutes to successfully run this proc.

Earlier it was never completed.

Thanks for your help.

Regards,
Vikram
Previous Topic: Query Sql Server faster than Oracle
Next Topic: DELETE taking long time
Goto Forum:
  


Current Time: Tue Jul 29 23:18:59 CDT 2014

Total time taken to generate the page: 0.11533 seconds