Home » RDBMS Server » Performance Tuning » SQL Tuning Help (Oracle 10g)
SQL Tuning Help [message #397062] |
Wed, 08 April 2009 14:17 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
crazykuda
Messages: 1 Registered: April 2009
|
Junior Member |
|
|
Hello Gurus,
Need some help in tuning a peice of code. I am more of an administrator and my SQL skills are limited. This is query on Datawarehousing tables. I managed to bring down the cost of execution to 50% by creating a bitmap index on /BI0/9AADEMCRT00 (FISCPER). Need some help in tuning the join and group by clause.
Appreciate your help.
Code :
SELECT
"O1"."CALWEEK" AS "0CALWEEK" ,"O1"."FISCPER" AS "0FISCPER" ,
"O1"."FISCVARNT" AS "0FISCVARNT" ,
"O1"."/BI0/9APRODUCT" AS "9APRODUCT" ,
"O1"."/BI0/9ASTOCKING" AS "9ASTOCKING" ,
"O1"."/BI0/9ADEM_CAT" AS "9ADEM_CAT" ,"O1"."BASE_UOM" AS "0BASE_UOM" ,
"O1"."/BI0/9AFACING" AS "9AFACING" ,"O1"."CALMONTH" AS "0CALMONTH" ,
"O1"."/BI0/9ATPOP" AS "9ATPOP" ,"O1"."/BI0/9AVCP_FA" AS "9AVCP_FA" ,
"O1"."/BI0/9AVCP_ST" AS "9AVCP_ST" ,
"O1"."/BI0/9AEMER_ORD" AS "9AEMER_ORD" ,
"P1"."/BI0/9AFCSTABLE" AS "S001_9ADEM_CAT" ,
SUM ( "O1"."/BI0/9ADEM_QTY" ) AS "9ADEM_QTY" ,
SUM ( "O1"."/BI0/9AORD_LINE" ) AS "9AORD_LINE" ,
SUM ( "O1"."/BI0/9AFI_EN_M" ) AS "9AFI_EN_M" ,
SUM ( "O1"."/BI0/9AFI_EN_P" ) AS "9AFI_EN_P" ,
SUM ( "O1"."/BI0/9AFI_EN_W" ) AS "9AFI_EN_W" ,
SUM ( "O1"."/BI0/9AFI_QTY_M" ) AS "9AFI_QTY_M" ,
SUM ( "O1"."/BI0/9AFI_QTY_P" ) AS "9AFI_QTY_P" ,
SUM ( "O1"."/BI0/9AFI_QTY_W" ) AS "9AFI_QTY_W" ,
SUM ( "O1"."/BI0/9ASC_QTY_P" ) AS "9ASC_QTY_P" ,
SUM ( "O1"."/BI0/9ASC_EN_P" ) AS "9ASC_EN_P"
FROM
( "/BI0/9AADEMCRT00" "O1" ) LEFT OUTER JOIN "/BI0/9APDEM_CAT" "P1" ON
"O1"."/BI0/9ADEM_CAT" = "P1"."/BI0/9ADEM_CAT" AND ( "P1"."OBJVERS" =
'A' )
WHERE
( ( ( ( "O1"."FISCPER" BETWEEN '2008002' AND '2008002' ) ) ) )
GROUP BY
"O1"."CALWEEK" ,"O1"."FISCPER" ,"O1"."FISCVARNT" ,
"O1"."/BI0/9APRODUCT" ,"O1"."/BI0/9ASTOCKING" ,"O1"."/BI0/9ADEM_CAT" ,
"O1"."BASE_UOM" ,"O1"."/BI0/9AFACING" ,"O1"."CALMONTH" ,
"O1"."/BI0/9ATPOP" ,"O1"."/BI0/9AVCP_FA" ,"O1"."/BI0/9AVCP_ST" ,
"O1"."/BI0/9AEMER_ORD" ,"P1"."/BI0/9AFCSTABLE"
================================================================
Execution Plan
----------------------------------------------------------------------------------------------------------------------
System: APZ
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4892K| 545M| | 227K (1)|
| 1 | SORT GROUP BY | | 4892K| 545M| 1213M| 227K (1)|
|* 2 | HASH JOIN RIGHT OUTER | | 4892K| 545M| | 119K (1)|
|* 3 | TABLE ACCESS FULL | /BI0/9APDEM_CAT | 63 | 567 | | 4 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID | /BI0/9AADEMCRT00 | 4892K| 503M| | 119K (1)|
| 5 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 6 | BITMAP INDEX SINGLE VALUE | /BI0/9AADEMCRT00Z0 | | | | |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O1"."/BI0/9ADEM_CAT"="P1"."/BI0/9ADEM_CAT"(+))
3 - filter("P1"."OBJVERS"(+)='A')
6 - access("O1"."FISCPER"='2008002')
=================================================================
|
|
|
|
Goto Forum:
Current Time: Tue Sep 10 15:01:33 CDT 2024
|