Home » RDBMS Server » Performance Tuning » Help in Performance issue problem
Help in Performance issue problem [message #184322] Wed, 26 July 2006 02:42 Go to next message
juliee_jitu
Messages: 8
Registered: July 2006
Junior Member
plz help..
explan plan attached
SELECT COUNT (*)
  FROM TRANSACTION trx, companymember MEMBER, company co, bankaccount ba
 WHERE trx.accountid = ba.bankaccountid
   AND trx.accountid IN
          (-1,
           90040906031374,
           90040906031373,
           90050304036368,
           90050304036366,
           90050304036367
          )
   AND ba.companyid = co.companyid
   AND trx.makerid = MEMBER.user_id
   AND co.parentcompanyid = '20040906017310'
   AND trx.transactionid =
          (SELECT   hist.transactionid
               FROM transactionhistory hist
              WHERE hist.transactionid = trx.transactionid
                AND trx.transactiontype IN
                       ('',
                        '',
                        'STOPCHQ',
                        'INFDTRFSAMECURRO',
                        'INFDTRFSAMECURRT',
                        'INFDTRFDIFFCURRO',
                        'INFDTRFDIFFCURRT',
                        'INFDTRFSAMECURROP',
                        'INFDTRFSAMECURRTP',
                        'INFDTRFSAMECURROP_DSENT',
                        'INFDTRFSAMECURRTP_DSENT',
                        'IBG',
                        'IBGP',
                        'IBGP_DSENT',
                        'BP',
                        'FCFDP',
                        'CO',
                        'TT',
                        'DD'
                       )
                AND trx.status IN
                       ('Entry',
                        'Pending',
                        'P-Signed',
                        'F-Signed',
                        'Sent',
                        'In-Process',
                        'Rejected',
                        'Issued',
                        'Completed',
                        'Void',
                        'ScheduleToSend',
                        'Accepted',
                        'Sent2JMS',
                        'Sent2Host',
                        'Stopped',
                        'StopRequestSent'
                       )
                AND trx.VERSION > 0
                AND hist.timeoccured >= sysdate
                AND hist.fromuid = '20040906054080'
           GROUP BY hist.transactionid)




Operation			Object Name		Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE			1  	 	11  	 	      	             	 
  SORT AGGREGATE					1  	77  	 	 	      	             	 
    FILTER		  	 	 	 	      	             	 
      NESTED LOOPS					1  	77  	9  	 	      	             	 
        NESTED LOOPS					1  	69  	9  	 	      	             	 
          NESTED LOOPS					1  	32  	4  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	 COMPANY	1  	16  	2  	 	      	             	 
              INDEX RANGE SCAN		IDX_COMPANY1	1  	 	1  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	BANKACCOUNT	6  	96  	2  	 	      	             	 
              INDEX RANGE SCAN		IDX_COMPANYID	2  	 	1  	 	      	             	 
          INLIST ITERATOR		  	 	 	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	TRANSACTION	1  	37  	5  	 	      	             	 
              INDEX RANGE SCAN		IDX_TRX_ACCTID	15  	 	1  	 	      	             	 
        INDEX UNIQUE SCAN		PK_CMPMEMBER	1 K	9 K	 	 	      	             	 
      SORT GROUP BY NOSORT		1  	28  	2  	 	      	             	 
        FILTER		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID RANSACTIONHISTORY	1  	28  	2  	 	      	             	 
            INDEX RANGE SCAN	IDX_HISTORY_TRXID	2  	 	1  	 	      	             	 

Re: Help in Performance issue problem [message #184338 is a reply to message #184322] Wed, 26 July 2006 03:53 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
see
http://www.orafaq.com/forum/t/66862/68488/
Re: Help in Performance issue problem [message #184342 is a reply to message #184322] Wed, 26 July 2006 04:23 Go to previous messageGo to next message
juliee_jitu
Messages: 8
Registered: July 2006
Junior Member
Yes,, i read those steps but dnot know what to do ?
plz help me
Re: Help in Performance issue problem [message #184346 is a reply to message #184342] Wed, 26 July 2006 04:46 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
OK, having read the steps, did you perform any of them.

[Updated on: Wed, 26 July 2006 04:46]

Report message to a moderator

Re: Help in Performance issue problem [message #184347 is a reply to message #184342] Wed, 26 July 2006 04:47 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That plan looks fine. So good, that I wonder if this is the plan from the Dev box, while the problem occurrs on the Production box.

How long is the query taking to execute?

Do be aware that if you have replaced the list of columns with a COUNT(*), then this will generally drastically change the Plan.

Is that what you've done?
Previous Topic: Deleting duplicate rows without subquery
Next Topic: Can someone help with this (image attached) query
Goto Forum:
  


Current Time: Wed Dec 07 22:10:05 CST 2016

Total time taken to generate the page: 0.06091 seconds