Hi. Our users notice poor performance when opening a project in tx
CJ20N. I did a performance trace and notice that a sql statement is
performing very poorly on a join of AFKO and AUFK. I search in OSS and
found note 484638 which describe the exact SQL statement. The solution
is to create a histogram as per oss 335415. When I read this note, it
seems to indicate that the oracle bug is fixed in 9.2.0.6. I am now
oracle 10.2.0.2.
ST05 trace code:
SELECT STATEMENT ( Estimated Costs = 21,643 , Estimated #Rows = 19,649 )
4 HASH JOIN
( Estim. Costs = 21,643 , Estim. #Rows = 19,649 )
Estim. CPU-Costs = 1,152,644,359 Estim. IO-Costs = 21,355
2 TABLE ACCESS BY INDEX ROWID AFKO
( Estim. Costs = 5,567 , Estim. #Rows = 19,649 )
Estim. CPU-Costs = 69,195,492 Estim. IO-Costs = 5,550
1 INDEX RANGE SCAN AFKO~2
( Estim. Costs = 163 , Estim. #Rows = 19,649 )
Search Columns: 2
Estim. CPU-Costs = 5,083,673 Estim. IO-Costs = 162
3 TABLE ACCESS FULL AUFK <b>(NOT SURE WHY THIS IS DOING A FULL SCAN)</b> ( Estim. Costs = 16,071 , Estim. #Rows = 130,098 )
Estim. CPU-Costs = 1,065,488,680 Estim. IO-Costs = 15,805
Further testing from our Customers have identified that this
transaction seems to have issues only when "Networks" are included in
the Project.
Thanks in Advance for any clarification.
Kumar
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.