Home » RDBMS Server » Performance Tuning » urgent- help me  () 1 Vote
icon13.gif  urgent- help me [message #183249] Thu, 20 July 2006 02:32 Go to next message
bagulia_pilla
Messages: 25
Registered: July 2006
Junior Member
PLz help me to optimise this query . I am new to optimising the query..

SELECT   trx.transactionid, trx.status, trx.batchid, trx.referencecode,
         trx.transactiontype, trx.originatoruid, trx.description,
         trx.createtime, trx.VERSION, trx.accountid, trx.amount, trx.currency,
         trx.beneficiaryname, trx.executetime, trx.ebankingrefno, trx.bankid,
         trx.companyid, trx.makerid, trx.servicereqid, trx.vertime,
         trx.relatedrefcode, trx.statuscode, MEMBER.first_name,
         MEMBER.last_name, co.companyname
    FROM TRANSACTION trx,
         companymember MEMBER,
         company co,
         transactionhistory hist,
         bankaccount ba
   WHERE trx.accountid = ba.bankaccountid
     AND ba.companyid = co.companyid
     AND trx.makerid = MEMBER.user_id
     AND hist.transactionid = trx.transactionid
     AND hist.historyid =
            (SELECT MAX (historyid)
               FROM transactionhistory ih
              WHERE trx.transactionid = ih.transactionid
                AND ih.operationdesc <> 'Copy')
     AND trx.transactiontype NOT IN ('LTAV', 'UTAV', 'TDPV', 'FCFDPV', 'LAV', 'LTPAV')
     AND trx.status IN ('Entry', 'Pending', 'P-Signed', 'F-Signed')
     AND trx.VERSION > 0
     AND (   hist.touid = '20051107084392'  OR (hist.fromuid = '20051107084392' AND hist.touid IS NULL) )
     AND co.parentcompanyid = '20031127012134' 
ORDER BY co.parentcompanyid, 
	  	 co.companyname, 
		 trx.ebankingrefno,
         trx.createtime



Plz find the explain plan for the above..


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

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	17  	 	      	             	 
  FILTER		  	 	 	 	      	             	 
    SORT GROUP BY		1  	616  	17  	 	      	             	 
      NESTED LOOPS		1  	616  	16  	 	      	             	 
        NESTED LOOPS		1  	557  	15  	 	      	             	 
          NESTED LOOPS		2  	1014  	13  	 	      	             	 
            NESTED LOOPS		2  	968  	11  	 	      	             	 
              NESTED LOOPS		2  	868  	7  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	EBN.TRANSACTIONHISTORY	3  	120  	3.25455776495451  	 	      	             	 
                  BITMAP CONVERSION TO ROWIDS		  	 	 	 	      	             	 
                    BITMAP OR		  	 	 	 	      	             	 
                      BITMAP CONVERSION FROM ROWIDS		  	 	 	 	      	             	 
                        INDEX RANGE SCAN	EBN.IDX_TRXHIS_TOUID	  	 	1  	 	      	             	 
                      BITMAP CONVERSION FROM ROWIDS		  	 	 	 	      	             	 
                        INDEX RANGE SCAN	EBN.IDX_TRXHIS_FROMUID	  	 	1  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	EBN.TRANSACTION	1  	394  	1  	 	      	             	 
                  INDEX UNIQUE SCAN	EBN.PK_EBTRANSACTION	265  	 	 	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	EBN.TRANSACTIONHISTORY	1  	50  	2  	 	      	             	 
                INDEX RANGE SCAN	EBN.IDX_HISTORY_TRXID	2  	 	1  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	EBN.BANKACCOUNT	1  	23  	1  	 	      	             	 
              INDEX UNIQUE SCAN	EBN.PK_BANKACCOUNT	1  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	EBN.COMPANY	1  	50  	1  	 	      	             	 
            INDEX UNIQUE SCAN	EBN.PK_COMPANY	14 K	 	 	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	EBN.COMPANYMEMBER	1  	59  	1  	 	      	             	 
          INDEX UNIQUE SCAN	EBN.PK_CMPMEMBER	1  	 	 	 	      	             	 




[Updated on: Thu, 20 July 2006 02:36]

Report message to a moderator

Re: urgent- help me [message #183263 is a reply to message #183249] Thu, 20 July 2006 03:28 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How many rows are in TRANSACTION?
How many rows are in transactionhistory?
How many rows does the SQL return?
How long does it currently take?

Give us the results of this query:
SELECT count(*)
, avg(cnt)
, max(cnt)
, stddev(cnt)
FROM (
   SELECT transactionid, count(*) AS cnt
   FROM transactionhistory hist
   WHERE hist.touid = '20051107084392'
   OR (hist.fromuid = '20051107084392' AND hist.touid IS NULL)
)


Ross Leishman
Re: urgent- help me [message #183268 is a reply to message #183263] Thu, 20 July 2006 03:51 Go to previous messageGo to next message
bagulia_pilla
Messages: 25
Registered: July 2006
Junior Member
Modified for input data with added group by transactionid and get the result

SELECT count(*),avg(cnt), max(cnt), stddev(cnt)
FROM (
   SELECT transactionid , count(*) AS cnt
   FROM transactionhistory hist
   WHERE 0=0
   AND  hist.touid = '20030325000537'
   OR (hist.fromuid = '20030325000538' AND hist.touid IS NULL)
   group by transactionid)


Result :

count(*)  | 	avg(cnt) | max(cnt) | stddev(cnt)
4.00      |     1.75	 |   3.00   |  0.96

[Updated on: Thu, 20 July 2006 03:52]

Report message to a moderator

Re: urgent- help me [message #183350 is a reply to message #183249] Thu, 20 July 2006 08:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Eliminate transactionhistory hist & bankaccount ba out of the FROM clause & subordinate them in the WHERE clause.
Neither table contributes any column to the SELECT clause & therefore not needed in the FROM clause
Re: urgent- help me [message #183432 is a reply to message #183263] Thu, 20 July 2006 22:15 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rleishman wrote on Thu, 20 July 2006 18:28

How many rows are in TRANSACTION?
How many rows are in transactionhistory?
How many rows does the SQL return?
How long does it currently take?



So there are 4 different TRANSACTIONIDs in the History table, one with three rows, the rest with one row each.

These 6 rows should be efficiently found by the indexes IDX_TRXHIS_TOUID and IDX_TRXHIS_FROMUID, uniquely joined to all remaining tables, and then 2 of 6 rows filtered by an efficient index scan on transactionhistory to return 4 rows.

The whole thing should take less than 0.5 seconds.

If not, answer the questions above. If it does take less than half a second, what's your problem? How fast do you want it to run.

Of course, there's always the possibility that you've given us Development stats, and the problem is in Production. I'll let you be the judge of what to do next in that case.


Ross Leishman
Re: urgent- help me [message #183794 is a reply to message #183432] Sun, 23 July 2006 22:00 Go to previous messageGo to next message
bagulia_pilla
Messages: 25
Registered: July 2006
Junior Member
No rows are in TRANSACTION  = 26
No rows are in transactionhistory= 16
No rows the SQL return =  around 125 ( based on the data)
currently take         = 156 msc
Re: urgent- help me [message #183853 is a reply to message #183794] Mon, 24 July 2006 02:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Based on this execution time, why exactly do you think this query needs optimising?
Re: urgent- help me [message #183855 is a reply to message #183794] Mon, 24 July 2006 03:03 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Perhaps I'm just getting old, but I thought 156ms was pretty snappy. Just how much faster than one-sixth of a second were you hoping to make this?

Rather than trying to tune this SQL directly, you would be much better served by investing heavily in Quantum Computer Research, which is your only real hope of order-of-magnitude improvement.

Ross Leishman
Re: urgent- help me [message #183882 is a reply to message #183249] Mon, 24 July 2006 04:17 Go to previous messageGo to next message
bagulia_pilla
Messages: 25
Registered: July 2006
Junior Member
It was tested on local PC but in production,, it is taking much more time. but we can not access prod data for security.
Re: urgent- help me [message #183897 is a reply to message #183882] Mon, 24 July 2006 05:10 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think Ross' comment neds repeating at this point.
Quote:

Of course, there's always the possibility that you've given us Development stats, and the problem is in Production.
I'll let you be the judge of what to do next in that case.



Can you not run the query that Ross gave you against the production data, so we can get a better understanding of the data distribution?

Can you provide us with an explain plan from Production?

If you can't give us the information we need to understand why the query is perfroming poorly, what exactly do you expect us to do?

[Updated on: Mon, 24 July 2006 05:11]

Report message to a moderator

Previous Topic: Bitmap Index and Histogram
Next Topic: Data Type Mismatch
Goto Forum:
  


Current Time: Thu Mar 28 07:43:24 CDT 2024