OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » SQL Tuning Help (Oracle 10g)

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

SQL Tuning Help [message #397062]

Wed, 08 April 2009 14:17

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')
=================================================================

Report message to a moderator

Re: SQL Tuning Help [message #397075 is a reply to message #397062]

Wed, 08 April 2009 15:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/84315/74940/
which have you tried & what were the results

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.

[Updated on: Wed, 08 April 2009 15:09]

Report message to a moderator

Previous Topic:	ORA-00604: error occurred at recursive SQL level x
Next Topic:	how to optimize sql for query big table? (merged 3)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 17:58:53 CST 2024