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  |
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 #575524 is a reply to message #575522] |
Wed, 23 January 2013 17:15  |
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 #575531 is a reply to message #575529] |
Wed, 23 January 2013 20:46 |
 |
Kevin Meade
Messages: 2103
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 #575610 is a reply to message #575608] |
Thu, 24 January 2013 13:19 |
William Robertson
Messages: 1643
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 #575617 is a reply to message #575519] |
Thu, 24 January 2013 15:28 |
|
Kevin Meade
Messages: 2103
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 |
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 #575621 is a reply to message #575620] |
Thu, 24 January 2013 16:17 |
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 |
|
Kevin Meade
Messages: 2103
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 |
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 |
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 #575708 is a reply to message #575625] |
Fri, 25 January 2013 13:02 |
William Robertson
Messages: 1643
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 |
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 |
William Robertson
Messages: 1643
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 #577535 is a reply to message #577524] |
Mon, 18 February 2013 04:36 |
michael_bialik
Messages: 621
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 |
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;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 09:40:14 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
