on 11-25-2008 6:14 PM
Hi,
We have a performance problem. We have 2 systems. PRD and QAS (QAS is a copy of PRD as of September 2nd)
SQL request is identical.
table structures is identical.
indexes are identical.
views are identical
DB stats have all been recalculated on both systems
initSID.ora values are almost identical. only memory related parameters (and SID) are different.
Obviously, data is different
For you info, view ZBW_VIEW_EKPO fetched its info from tables EIKP, LFA1, EKKO and EKPO.
Starting on September 15th, a query that used to take 10 minutes started taking over 120 minutes.
I compared explain plans on both system and they are really different:
SQL request:
SELECT
"MANDT" , "EBELN" , "EBELP" , "SAISO" , "SAISJ" , "AEDAT" , "AUREL" , "LOEKZ" , "INCO2" ,
"ZZTRANSPORT" , "PRODA" , "ZZPRDHA" , "ZZMEM_DATE" , "KDATE" , "ZZHERKL" , "KNUMV" , "KTOKK"
FROM
"ZBW_VIEW_EKPO"
WHERE
"MANDT" = :A0#
Explain plan for PRD:
SELECT STATEMENT ( Estimated Costs = 300,452 , Estimated #Rows = 0 )
8 HASH JOIN
( Estim. Costs = 300,451 , Estim. #Rows = 4,592,525 )
Estim. CPU-Costs = 9,619,870,571 Estim. IO-Costs = 299,921
Access Predicates
1 TABLE ACCESS FULL EIKP
( Estim. Costs = 353 , Estim. #Rows = 54,830 )
Estim. CPU-Costs = 49,504,995 Estim. IO-Costs = 350
Filter Predicates
7 HASH JOIN
( Estim. Costs = 300,072 , Estim. #Rows = 4,592,525 )
Estim. CPU-Costs = 9,093,820,218 Estim. IO-Costs = 299,571
Access Predicates
2 TABLE ACCESS FULL LFA1
( Estim. Costs = 63 , Estim. #Rows = 812 )
Estim. CPU-Costs = 7,478,316 Estim. IO-Costs = 63
Filter Predicates
6 HASH JOIN
( Estim. Costs = 299,983 , Estim. #Rows = 4,592,525 )
Estim. CPU-Costs = 8,617,899,244 Estim. IO-Costs = 299,508
Access Predicates
3 TABLE ACCESS FULL EKKO
( Estim. Costs = 2,209 , Estim. #Rows = 271,200 )
Estim. CPU-Costs = 561,938,609 Estim. IO-Costs = 2,178
Filter Predicates
5 TABLE ACCESS BY INDEX ROWID EKPO
( Estim. Costs = 290,522 , Estim. #Rows = 4,592,525 )
Estim. CPU-Costs = 6,913,020,784 Estim. IO-Costs = 290,141
4 INDEX SKIP SCAN EKPO~Z02
( Estim. Costs = 5,144 , Estim. #Rows = 4,592,525 )
Search Columns: 2
Estim. CPU-Costs = 789,224,817 Estim. IO-Costs = 5,101
Access Predicates Filter Predicates
Explain plan for QAS:
SELECT STATEMENT ( Estimated Costs = 263,249 , Estimated #Rows = 13,842,540 )
7 HASH JOIN
( Estim. Costs = 263,249 , Estim. #Rows = 13,842,540 )
Estim. CPU-Costs = 59,041,893,935 Estim. IO-Costs = 260,190
Access Predicates
1 TABLE ACCESS FULL LFA1
( Estim. Costs = 63 , Estim. #Rows = 812 )
Estim. CPU-Costs = 7,478,316 Estim. IO-Costs = 63
Filter Predicates
6 HASH JOIN
( Estim. Costs = 263,113 , Estim. #Rows = 13,842,540 )
Estim. CPU-Costs = 57,640,387,953 Estim. IO-Costs = 260,127
Access Predicates
4 HASH JOIN
( Estim. Costs = 2,127 , Estim. #Rows = 194,660 )
Estim. CPU-Costs = 513,706,489 Estim. IO-Costs = 2,100
Access Predicates
2 TABLE ACCESS FULL EIKP
( Estim. Costs = 351 , Estim. #Rows = 54,830 )
Estim. CPU-Costs = 49,504,995 Estim. IO-Costs = 348
Filter Predicates
3 TABLE ACCESS FULL EKKO
( Estim. Costs = 1,534 , Estim. #Rows = 194,660 )
Estim. CPU-Costs = 401,526,622 Estim. IO-Costs = 1,513
Filter Predicates
5 TABLE ACCESS FULL EKPO
( Estim. Costs = 255,339 , Estim. #Rows = 3,631,800 )
Estim. CPU-Costs = 55,204,047,516 Estim. IO-Costs = 252,479
Filter Predicates
One more bit of information, PRD was copied to TST about a month ago and this one is also slow.
I did almost anything I could think of.
> DB stats have all been recalculated on both systems
> initSID.ora values are almost identical. only memory related parameters (and SID) are different.
> Obviously, data is different
Ok, so you say: the parameters are different, the data is different and the statistics are different.
I'm surprised that you still expect the plans to be the same...
> For you info, view ZBW_VIEW_EKPO fetched its info from tables EIKP, LFA1, EKKO and EKPO.
We will need to see the view definition !
> Starting on September 15th, a query that used to take 10 minutes started taking over 120 minutes.
Oh - Sep. 15th - that explains it ... just kiddin'.
Ok, so it appears to be obvious that from that day on, the execution plan for the query was changed.
If you're on Oracle 10g you may look it up again and also recall the CBO stats that had been used back then.
> I compared explain plans on both system and they are really different:
>
> SQL request:
>
SELECT
> "MANDT" , "EBELN" , "EBELP" , "SAISO" , "SAISJ" , "AEDAT" , "AUREL" , "LOEKZ" , "INCO2" ,
> "ZZTRANSPORT" , "PRODA" , "ZZPRDHA" , "ZZMEM_DATE" , "KDATE" , "ZZHERKL" , "KNUMV" , "KTOKK"
> FROM
> "ZBW_VIEW_EKPO"
> WHERE
> "MANDT" = :A0#
Ok - basically you fetch all rows from this view as MANDT is usually not a selection criteria at all.
> Explain plan for PRD:
>
SELECT STATEMENT ( Estimated Costs = 300,452 , Estimated #Rows = 0 )
>
> 8 HASH JOIN
> ( Estim. Costs = 300,451 , Estim. #Rows = 4,592,525 )
> Estim. CPU-Costs = 9,619,870,571 Estim. IO-Costs = 299,921
> Access Predicates
>
> 1 TABLE ACCESS FULL EIKP
> ( Estim. Costs = 353 , Estim. #Rows = 54,830 )
> Estim. CPU-Costs = 49,504,995 Estim. IO-Costs = 350
> Filter Predicates
> 7 HASH JOIN
> ( Estim. Costs = 300,072 , Estim. #Rows = 4,592,525 )
> Estim. CPU-Costs = 9,093,820,218 Estim. IO-Costs = 299,571
> Access Predicates
>
> 2 TABLE ACCESS FULL LFA1
> ( Estim. Costs = 63 , Estim. #Rows = 812 )
> Estim. CPU-Costs = 7,478,316 Estim. IO-Costs = 63
> Filter Predicates
> 6 HASH JOIN
> ( Estim. Costs = 299,983 , Estim. #Rows = 4,592,525 )
> Estim. CPU-Costs = 8,617,899,244 Estim. IO-Costs = 299,508
> Access Predicates
>
> 3 TABLE ACCESS FULL EKKO
> ( Estim. Costs = 2,209 , Estim. #Rows = 271,200 )
> Estim. CPU-Costs = 561,938,609 Estim. IO-Costs = 2,178
> Filter Predicates
> 5 TABLE ACCESS BY INDEX ROWID EKPO
> ( Estim. Costs = 290,522 , Estim. #Rows = 4,592,525 )
> Estim. CPU-Costs = 6,913,020,784 Estim. IO-Costs = 290,141
>
> 4 INDEX SKIP SCAN EKPO~Z02
> ( Estim. Costs = 5,144 , Estim. #Rows = 4,592,525 )
> Search Columns: 2
> Estim. CPU-Costs = 789,224,817 Estim. IO-Costs = 5,101
> Access Predicates Filter Predicates
Ok, we've no restriction to the data, so Oracle chooses the access methods it thinks are best for large volumes of data - Full table scans and HASH JOINS. The index skip scan is quite odd - maybe this is due to one of the join conditions.
> Explain plan for QAS:
>
SELECT STATEMENT ( Estimated Costs = 263,249 , Estimated #Rows = 13,842,540 )
>
> 7 HASH JOIN
> ( Estim. Costs = 263,249 , Estim. #Rows = 13,842,540 )
> Estim. CPU-Costs = 59,041,893,935 Estim. IO-Costs = 260,190
> Access Predicates
>
> 1 TABLE ACCESS FULL LFA1
> ( Estim. Costs = 63 , Estim. #Rows = 812 )
> Estim. CPU-Costs = 7,478,316 Estim. IO-Costs = 63
> Filter Predicates
> 6 HASH JOIN
> ( Estim. Costs = 263,113 , Estim. #Rows = 13,842,540 )
> Estim. CPU-Costs = 57,640,387,953 Estim. IO-Costs = 260,127
> Access Predicates
>
> 4 HASH JOIN
> ( Estim. Costs = 2,127 , Estim. #Rows = 194,660 )
> Estim. CPU-Costs = 513,706,489 Estim. IO-Costs = 2,100
> Access Predicates
>
> 2 TABLE ACCESS FULL EIKP
> ( Estim. Costs = 351 , Estim. #Rows = 54,830 )
> Estim. CPU-Costs = 49,504,995 Estim. IO-Costs = 348
> Filter Predicates
> 3 TABLE ACCESS FULL EKKO
> ( Estim. Costs = 1,534 , Estim. #Rows = 194,660 )
> Estim. CPU-Costs = 401,526,622 Estim. IO-Costs = 1,513
> Filter Predicates
>
> 5 TABLE ACCESS FULL EKPO
> ( Estim. Costs = 255,339 , Estim. #Rows = 3,631,800 )
> Estim. CPU-Costs = 55,204,047,516 Estim. IO-Costs = 252,479
> Filter Predicates
Ok, we see significantly different table sizes here, but at least this second plan leaves out the superfluous Index Skip Scan.
How to move on from here?
1. Check whether you've installed all the current patches. Not all bugs that are in the system are hit all the time, so it may very well be that after new CBO stats were calculated you just begin to hit one of it.
2. Make sure that all parameter recommendations are implemented on the systems. This is crucial for the CBO.
3. Provide a description of the Indexes and the view definition.
The easiest would be: perform an Oracle CBO trace and provide a download link to it.
regards,
Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
thanks for the reply.
Yes, data is not identical. PRD has more data than QAS. I was just surprised about the "INDEX SKIP SCAN EKPO~Z02" on PRD.
1 and 2. Both systems are using the exact same Oracle version (10.2.0.4) with minipatch 8. We are on Windows and using SAP ECC 6.0. So, both use the same CBO calculation.
3. ZBW_VIEW_EKPO definition:
MANDT CLNT 3 Client
EBELN CHAR 10 Purchasing Document Number
EBELP NUMC 5 Item Number of Purchasing Document
SAISO CHAR 4 Season Category
SAISJ CHAR 4 Season Year
AEDAT DATS 8 Purchasing Document Item Change Date
AUREL CHAR 1 Relevant to Allocation Table
LOEKZ CHAR 1 Deletion Indicator in Purchasing Document
INCO2 CHAR 28 Incoterms (Part 2)
ZZTRANSPORT CHAR 8 Article freight group
PRODA DATS 8 Invoice: Date of invoice document for import processing
ZZPRDHA CHAR 18 PO Product hierarchy
ZZMEM_DATE DATS 8 Mem Date used in PO
KDATE DATS 8 End of Validity Period
ZZHERKL CHAR 3 Country of origin of the material
KNUMV CHAR 10 Number of the document condition
KTOKK CHAR 4 Vendor account group
table/join conditions:
EKPO MANDT = EKKO MANDT
EKPO EBELN = EKKO EBELN
LFA1 LIFNR = EKKO LIFNR
LFA1 MANDT = EKKO MANDT
EIKP EXNUM = EKKO EXNUM
EIKP MANDT = EKKO MANDT
Selections/conditions:
EKPO ATTYP EQ '02' AND
LFA1 KTOKK EQ 'ZVII'
You say: If you're on Oracle 10g you may look it up again and also recall the CBO stats that had been used back then.
I would like to know how to recall these stats and test this out.
Thanks.
Hi Eric,
you may want to have a look into the Oracle Documentation [ Restoring Previous Versions of Statistics|http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#i41814].
1. Find out for if the old statistics are still available:
select dbms_stats.GET_STATS_HISTORY_AVAILABILITY() from dual;
2. If the statistics are still there (which is likely to be not the case here, as the default retention is set to 31 days) **, you may use
begin
dbms_stats.restore_table_stats (
ownname => 'SAP<SID>',
tabname => '<table_name>',
as_of_timestamp => to_timestamp ('15.09.2008', 'DD.MM.YYYY'),
force => true,
no_invalidate => false
);
end;
for each of the involved tables.
The procedure also restores the index statistics - so you don't have to care about them.
Anyhow, it would still be a better idea to try to figure out, why the plan changes to a bad one when the statistics are up to date. Maybe a bigger sample size would help here.
I propose you open a support message for this and have the CBO trace analysed.
I know some primary supporters that are capable of that ...
**)
you may double-check this for your system via
select dbms_stats.GET_STATS_HISTORY_RETENTION() from dual;
regards,
Lars
SAP has not answered yet, but problem is fixed.
1. I ran these scriptsw:
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');
@?/rdbms/admin/prvtbpw.plb
@?/rdbms/admin/catdph.sql
@?/rdbms/admin/catdpb.sql
@?/rdbms/admin/prvtstat.plb
2. I rebuilt indexes of necessary tables
3. changed analysis method to CX in DB21 and recalculated statistics.
I believe that step 3 fixed the problem.
thanks Lars.
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.