Hi There,
We have a situation where one of the INSERT statement is running fast in QA and slow in PROD. Both are of same database versions - Oracle 10.2.0.4 running on HP Unix 11.31. To avoid the cause of databases running in different server, we copied our PROD database to same server where QA database is running and started with PROD init.ora which has 7GB SGA_MAX_SIZE and 6GB SGA_TARGET. For QA database, SGA_MAX_SIZE is 700MB and SGA_TARGET is 600MB. Both are running on same server and with same data. We refreshed QA with PROD data. If we start QA database with PROD init.ora, QA is also behaving the same way PROD does.
This problem is only with specific insert statement. Here is the tkprof output of that specific statement. Can someone please interpret this for me? I am poor in SQL tuning :-( Why is the statement behaving ODD with PROD SGA size? Generally we would think larger SGA should give better performance.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 56710.39 56067.75 7343 311186373 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 56710.39 56067.76 7343 311186373 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 27 (TEST)
Rows Row Source Operation
------- ---------------------------------------------------
0 SEQUENCE CRDETAIL (cr=0 pr=0 pw=0 time=29 us)
0 VIEW (cr=0 pr=0 pw=0 time=21 us)
0 SORT GROUP BY (cr=0 pr=0 pw=0 time=20 us)
401 HASH JOIN RIGHT SEMI (cr=23299915 pr=7343 pw=0 time=93982966 us)
237 TABLE ACCESS BY INDEX ROWID CR_STRUCTURE_VALUES2 (cr=96 pr=0 pw=0 time=504 us)
253 INDEX RANGE SCAN CR_STRUCTURE_VALUES2_PK (cr=4 pr=0 pw=0 time=278 us)(object id 1467582)
841 TABLE ACCESS BY INDEX ROWID CR_COST_REPOSITORY (cr=23306003 pr=7343 pw=0 time=94546465 us)
1317368058 NESTED LOOPS (cr=79721182 pr=7343 pw=0 time=18565176955 us)
26912 VIEW (cr=9874 pr=7343 pw=0 time=5269231 us)
26912 MINUS (cr=9874 pr=7343 pw=0 time=5242317 us)
27462 SORT UNIQUE (cr=9627 pr=7329 pw=0 time=5040815 us)
271564 TABLE ACCESS FULL CR_STRUCTURE_VALUES2 (cr=9627 pr=7329 pw=0 time=1357961 us)
568 SORT UNIQUE (cr=247 pr=14 pw=0 time=43467 us)
2357 TABLE ACCESS BY INDEX ROWID CR_STRUCTURE_VALUES2 (cr=247 pr=14 pw=0 time=14751 us)
2357 INDEX RANGE SCAN CR_STRUCTURE_VALUES2_PK (cr=11 pr=14 pw=0 time=10028 us)(object id 1467582)
1317341146 INDEX RANGE SCAN CRCR_MN_IX (cr=79711308 pr=0 pw=0 time=50420511 us)(object id 1469401)
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT MODE: CHOOSE
0 SEQUENCE OF 'CRDETAIL' (SEQUENCE)
0 VIEW
0 SORT (GROUP BY)
401 HASH JOIN (RIGHT SEMI)
237 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'CR_STRUCTURE_VALUES2' (TABLE)
253 INDEX MODE: ANALYZED (RANGE SCAN) OF
'CR_STRUCTURE_VALUES2_PK' (INDEX (UNIQUE))
841 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'CR_COST_REPOSITORY' (TABLE)
1317368058 NESTED LOOPS
26912 VIEW
26912 MINUS
27462 SORT (UNIQUE)
271564 TABLE ACCESS MODE: ANALYZED (FULL) OF
'CR_STRUCTURE_VALUES2' (TABLE)
568 SORT (UNIQUE)
2357 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'CR_STRUCTURE_VALUES2' (TABLE)
2357 INDEX MODE: ANALYZED (RANGE SCAN) OF
'CR_STRUCTURE_VALUES2_PK' (INDEX (UNIQUE))
1317341146 INDEX MODE: ANALYZED (RANGE SCAN) OF 'CRCR_MN_IX'
(INDEX)
********************************************************************************
And here is the statement in question:
INSERT
INTO cr_allocations_stg
(
"ID",
"COMPANY",
"GL_ACCOUNT",
"COST_CENTER",
"COST_ELEMENT",
"PROFIT_CENTER",
"MASTER_ORDER",
"ORDER_NUMBER",
" FUNDING_PROJECT",
"POSTING_ORDER",
"POSTING_COST_CENTER",
"ORIG_COST_ELEMENT",
"ORIG_COST_CENTER",
"ORIG_PROFIT_CENTER",
"TRADING_PARTNER",
"WORK_ORDER_NUMBER",
"DR_CR_ID",
"LEDGER_SIGN",
"QUANTITY",
"AMOUNT",
"MONTH_NUMBER",
"MONTH_PERIOD",
"GL_JOURNAL_CATEGORY",
"AMOUNT_TYPE",
"ALLOCATION_ID",
"TARGET_CREDIT",
"CROSS_CHARGE_COMPANY"
)
SELECT crdetail.nextval,
"COMPANY",
"GL_ACCOUNT",
"COST_CENTER",
'5253000',
"PROFIT_CENTER" ,
"MASTER_ORDER",
"ORDER_NUMBER",
"FUNDING_PROJECT",
' ',
"POSTING_COST_CENTER",
"ORIG_COST_ELEMENT",
"ORIG_COST_CENTER",
"ORIG_PROFIT_CENTER",
" TRADING_PARTNER",
"WORK_ORDER_NUMBER",
CASE
WHEN amount > 0
THEN 1
ELSE -1
END,
1,0,
ROUND(amount * 0.0574000000, 2),
month_number,
0,
'593',
1 ,
7,
'TARGET',
' '
FROM
(SELECT "COMPANY",
"GL_ACCOUNT",
"COST_CENTER",
"PROFIT_CENTER",
"MASTER_ORDER",
"FUNDING_PROJECT",
"POSTING_COST_CENTER",
"ORDER_NUMBER",
"ORIG_COST_ELEMENT",
"ORIG_COST_CENTER",
"ORIG_PROFIT_CENTER",
"TRADING_PARTNER",
"WORK_ORDER_NUMBER",
month_n umber,
0,
SUM(amount) amount,
SUM(quantity) quantity
FROM CR_COST_REPOSITORY
WHERE (amount_type = 1 )
AND (month_number = 201404)
AND ( "MASTER_ORDER" IN MASTER_ORDER
AND EXISTS
(SELECT 1
FROM
(SELECT SUBSTR(ELEMENT_VALUE, 1, DECODE(INSTR(ELEMENT_VALUE, ':'), 0, L ENGTH(ELEMENT_VALUE) + 1, INSTR(ELEMENT_VALUE, ':')) - 1) AS ELEMENT
FROM CR_STRUCTURE_VALUES2
WHERE STRUCTURE_ID = 2
AND DETAIL_BUDGET = 1
AND STATUS = 1
AND UPPER(PARENT_VALUE) IN ('ELECTRIC ALL OTHER','ELECTRIC COR')
MINUS
SELECT SUBSTR(ELEMENT_VALUE, 1, DECODE(INSTR(ELEMENT_VALUE, ':'), 0, LENGTH(ELEMENT_VALUE) + 1, INSTR(ELEMENT_VALUE, ':')) - 1) AS ELEME NT
FROM CR_STRUCTURE_VALUES2
WHERE STRUCTURE_ID = 9
AND DETAIL_BUDGET = 1
AND STATUS = 1
AND UPPER(PARENT_VALUE) = 'A&G OH ORDER EXCLUSION'
) Z
WHERE Z.ELEMENT = MASTER_ORDER
)
AND "GL_ACCOUNT" <> '91081001'
AND "COST_ELEMENT" IN COST_ELEMENT
AND EXISTS
(SELECT 1
FROM CR_ST RUCTURE_VALUES2 A
WHERE A.STRUCTURE_ID = 5
AND A.DETAIL_BUDGET =1
AND A.STATUS = 1
AND COST_ELEMENT = A.ELEMENT_VALUE
)
AND "GL_ACCOUNT" NOT IN ('5100000','5325000','5327000')
AND "SOURCE_ID" <> '7' )
GROUP BY "COMPANY",
"GL_ACCOUNT",
"COST_CENTER",
"PROFIT_CENTER",
"MASTER_ORDER",
"FUNDING_PROJECT",
"POSTING_COST_CENTER",
"ORDER_NUMBER",
"ORIG_COST_ELEMENT",
"ORIG_COST_CENTER",
"ORIG_PROFIT_CENTER",
"TRADING_PARTNER",
"WORK_ORDER_NUMBER",
month_number
)
Appreciate your earliest response on this.
Thanks & Regards,
Murali