Home » RDBMS Server » Performance Tuning » challenge to Sr.DBA (Oracle 8i)
challenge to Sr.DBA [message #320282] Wed, 14 May 2008 09:15 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Counting whole table?
Next Topic: Number of table partitions
Goto Forum:
  


Current Time: Fri Dec 02 21:02:33 CST 2016

Total time taken to generate the page: 0.20298 seconds