Home » RDBMS Server » Performance Tuning » challenge to Sr.DBA (Oracle 8i)
| challenge to Sr.DBA [message #320282] |
Wed, 14 May 2008 09:15  |
chinmayikkalki
Messages: 11
Registered: April 2008
|
Junior Member |
|
|
Dear experts,
You are the great peoples in all over the world!!!!
Please help me in tuning the following sql statements.
As a DBA We need to decrease the elapsed time per query.
Regards,
Chinmay
SELECT ENTITY.POR_ID, DET.NARRATION,
HEAD.TRANSACTION_DATE,DET.DR_CR_CODE,
DET.TRANSACTION_TYPE_ID,
DET.TRANSACTION_ID,
DET.SERIAL_NO,
DET.LCY_AMOUNT,
ACC.LCY_OPEN_BALANCE,
ACC.ACCT_ID,
HEAD.SMS.STATUS,
TO_DATE('1-03-2008','DD-MM-YYYY') as cm_from,
:ad_to as cm_to,
GL.OPEN_BAL AS open_bal,
ACC.LEVEL1,
ACC.LEVEL2,
ACC.LEVEL3,
ACC.LEVEL4,
ACC.DESCRIPTION
FROM FUL.DFT_ACCN_ENTITY ENTITY ,
FUL.DFT_JOURNAL_ENTRY_DETAIL DET,
FUL.DFT_JOURNAL_ENTRY_HEADER HEAD,
FUL.DFT_GL_ALL_ACCTS ACC,
FUL.DFT_ACC_GEN_LEDGER GL
WHERE ( ENTITY.ACCN_ENTITY_ID = HEAD.ACCN_ENTITY_ID ) and
( ENTITY.POR_ID <> GET_BLOCK_GLOBAL('GLOBAL') ) AND
( DET.TRANSACTION_ID = HEAD.TRANSACTION_ID ) and
( DET.TRANSACTION_TYPE_ID = HEAD.TRANSACTION_TYPE_ID ) and ( DET.ACCN_ENTITY_ID = HEAD.ACCN_ENTITY_ID ) and ( ACC.ACCT_ID = GL.ACCT_ID) AND
( ENTITY.POR_ID like :as_POR_id ) AND
( HEAD.TRANSACTION_DATE >= TO_DATE((TO_CHAR(:ad_from,'DD-MON-YY')||' 00:00:00'),'DD-MON-YY HH24:MI:SS')) AND
( HEAD.TRANSACTION_DATE <= TO_DATE((TO_CHAR(:ad_to,'DD-MON-YY')||' 23:59:59'),'DD-MON-YY HH24:MI:SS')) AND
( HEAD.SMS.STATUS in ('A', 'U')) AND
( ACC.BRANCH_ID = 1 ) AND
( ACC.POR_ID like :as_POR_id ) AND
( Decode(ACC.LEVEL1, 'BANK', ACC.LEVEL3, ACC.LEVEL1 ) LIKE :as_level ) AND
( ACC.POR_ID, Decode(ACC.LEVEL1, 'BANK', ACC.LEVEL3, ACC.LEVEL1 )) IN
( Select POR_ID, Decode(ACC.LEVEL1, 'BANK', aa.LEVEL3, aa.LEVEL1 ) from FUL.dft_gl_all_ACCts aa
Where ACCt_id = DET.ACCT_ID)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.20 0.22 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 29 14.29 23.80 32541 803405 1427 9013
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 33 14.49 24.02 32541 803405 1427 9013
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 ()
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 NESTED LOOPS
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'DFT_JOURNAL_ENTRY_HEADER'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IDX_TRAN_DATE'
(NON-UNIQUE)
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'DFT_ACC_GEN_LEDGER'
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'DFT_GL_ALL_ACCTS'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_DFT_GL_ALL_ACCTS' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'DFT_GL_ALL_ACCTS'
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'DFT_JOURNAL_ENTRY_DETAIL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IDX_ACCT_ID'
(NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'DFT_ACCN_ENTITY'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_DFT_ACCN_ENTITY' (UNIQUE)
********************************************************************************
SELECT SHORT_NAME, POR_NAME, POR_ID, nvl(POR_SCOPE, 'A') as
POR_SCOPE
FROM
FUL.DCT_POR WHERE BLOCK_PORT = 'N' AND
FUL.DQF_GET_APPLICATION_DATE(BRANCH_ID) between
FUL.DCT_POR.VALID_START_DATE and
nvl(FUL.DCT_POR.VALID_END_DATE, to_date('10/10/2999','dd/mm/yyyy')
) ORDER BY SHORT_NAME
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 3.51 3.77 2 728 2868 252
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 3.51 3.77 2 728 2868 252
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 ()
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (ORDER BY)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'DCT_POR'
********************************************************************************
SELECT "FUL"."DCT_POR"."POR_NAME",
"FUL"."DCT_POR"."POR_ID",
"FUL"."DCT_POR"."POR_TYPE",
"FUL"."DCT_POR"."SHORT_NAME",
"FUL"."DCT_POR"."BRANCH_ID"
FROM
"FUL"."DCT_POR" WHERE FUL."DCT_POR"."STATUS" not
in ('N', 'D', 'C') AND (PARENT_ID is null or POR_SCOPE = 'C')
AND FUL.DQF_GET_APPLICATION_DATE(BRANCH_ID) between
FUL.DCT_POR.VALID_START_DATE and
nvl(FUL.DCT_POR.VALID_END_DATE, to_date('10/10/2999','dd/mm/yyyy')
) AND POR_ID NOT IN (GET_BLOCK_GLOBAL('BLOCK')) ORDER BY
SHORT_NAME
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.02 0.02 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 1.80 1.88 0 400 1512 102
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 1.82 1.90 0 400 1512 102
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 ()
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (ORDER BY)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'DCT_POR'
********************************************************************************
SELECT "FUL"."DFT_GL_ALL_ACCTS"."ACCT_ID",
"FUL"."DFT_GL_ALL_ACCTS"."LEVEL1",
"FUL"."DFT_GL_ALL_ACCTS"."LEVEL3",
"FUL"."DFT_GL_ALL_ACCTS"."LEVEL2",
"FUL"."DFT_GL_ALL_ACCTS"."POR_ID",
"FUL"."DFT_GL_ALL_ACCTS"."DESCRIPTION"
FROM "FUL"."DFT_LEDGER_HIERARCHY",
"FUL"."DFT_GL_ALL_ACCTS"
WHERE ( "FUL"."DFT_LEDGER_HIERARCHY"."LEVEL1" = "FUL"."DFT_GL_ALL_ACCTS"."LEVEL1") and
( "FUL"."DFT_GL_ALL_ACCTS"."POR_ID" = "FUL"."DFT_LEDGER_HIERARCHY"."POR_ID") and
( "FUL"."DFT_LEDGER_HIERARCHY"."BRANCH_ID" = "FUL"."DFT_GL_ALL_ACCTS"."BRANCH_ID" ) and
( "FUL"."DFT_LEDGER_HIERARCHY"."ACCT_TYPE" like :as_type) and
("FUL"."DFT_GL_ALL_ACCTS"."LAST_LEVEL_FLAG" ) = 'Y'
ORDER BY "FUL"."DFT_GL_ALL_ACCTS"."DESCRIPTION" ASC
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.14 0.14 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 212 0.68 1.67 3473 5552 224 67676
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 220 0.82 1.81 3473 5552 224 67676
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 ()
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (ORDER BY)
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'DFT_LEDGER_HIERARCHY'
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'DFT_GL_ALL_ACCTS'
************************************************************************
BEGIN FUL.Dfp_entries_ledger( '0000000214' , TO_DATE('1-03-2008',
'DD-MM-YYYY') , 'LEVEL1' , null , 0 ); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.20 1.24 2551 16854 8842 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.20 1.25 2551 16854 8842 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 ()
************************************************************************
SELECT "FUL"."DCT_POR"."POR_ID" ,
"FUL"."DCT_POR"."POR_NAME"
FROM
"FUL"."DCT_POR" WHERE ( FUL."DCT_POR"."STATUS" =
'A' )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.02 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 3 0
Fetch 2 0.00 1.14 0 0 0 347
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.02 1.15 0 0 3 347
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 ()
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'DCT_POR'
[:Q10352000]
SELECT /*+ Q10352000 NO_EXPAND ROWID(A1) */ A1."POR_ID", A1."POR_NAME" FROM "FUL"."DCT_POR" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A1 WHERE A1."STATUS"='A'
|
|
|
|
| Re: challenge to Sr.DBA [message #320331 is a reply to message #320282] |
Wed, 14 May 2008 13:24  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
On a couple of these, you are selecting from a single table.
And doing a FTS on that table, implying
In your first query, it looks like you are doing a FTS of the 'DFT_GL_ALL_ACCTS' table repeatedly inside a nested loop.
(if I'm not messing up on the indentation of your explain plan).
What are you asking here?
What have you tried?
|
|
|
|
| Re: challenge to Sr.DBA [message #320393 is a reply to message #320331] |
Thu, 15 May 2008 00:00 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
( ACC.POR_ID, Decode(ACC.LEVEL1, 'BANK', ACC.LEVEL3, ACC.LEVEL1 )) IN
( Select POR_ID, Decode(ACC.LEVEL1, 'BANK', aa.LEVEL3, aa.LEVEL1 ) from FUL.dft_gl_all_ACCts aa
Where ACCt_id = DET.ACCT_ID)
This line is a problem. You are trying to do a conditional join/lookup: if LEVEL1 is BANK, lookup on LEVEL3, otherwise lookup on LEVEL1.
This is a really bad data model. There might not be a good solution. You could try:
AND EXISTS (
SELECT 1
FROM FUL.dft_gl_all_ACCts aa
WHERE aa.por_id = acc.por_id
AND acc.level1 = 'BANK'
AND aa.level3 = acc.level3
UNION ALL
SELECT 1
FROM FUL.dft_gl_all_ACCts aa
WHERE aa.por_id = acc.por_id
AND acc.level1 != 'BANK'
AND aa.level1 = acc.level1
)
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Thu Jun 27 19:55:26 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
