Home » RDBMS Server » Performance Tuning » Very slow in Executing
icon8.gif  Very slow in Executing [message #184259] Tue, 25 July 2006 22:48 Go to next message
bagulia_pilla
Messages: 25
Registered: July 2006
Junior Member
Plz help me to tuning this query.
This query needs so much time to execute.
Thanks in advanced

SELECT   10000 AS parentindicator, NULL AS companyname, templatetype, seqnum,
         NAME, description, beneficiaryname, trxtype, companyid AS companyid,
         user_id, accountid, lastupdatedate
    FROM templates
   WHERE nvl(accountid,-1) = -1
   		 AND companyid = '20020423000007'
		 AND templatetype IN ('2')		 
UNION ALL
SELECT  (SELECT COUNT (*)
            FROM company com
           WHERE a.accountid IS NOT NULL
             AND a.accountid = b.bankaccountid
             AND b.companyid = com.parentcompanyid) AS parentindicator,
         c.companyname AS companyname, templatetype, seqnum, NAME,		
         description, beneficiaryname, trxtype, a.companyid AS companyid,
         user_id, accountid, lastupdatedate
    FROM templates a, bankaccount b, company c
   WHERE a.accountid is not null
     AND a.accountid = b.bankaccountid
	 AND b.companyid = c.companyid      
     AND a.companyid = c.companyid      
     AND a.companyid = '20020423000007' 
     AND a.templatetype IN ('2')
ORDER BY parentindicator DESC, companyname, lastupdatedate DESC;



=================================================================================
Explain Plan
==============
Operation				Object Name		Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
-----------------------------------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=CHOOSE				3 K	 	338  	 	      	             	 
  SORT ORDER BY							3 K	309 K	253.3  	 	      	             	 
    UNION-ALL		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	EBN.TEMPLATES		3 K	306 K	66.3  	 	      	             	 
        BITMAP CONVERSION TO ROWIDS		  	 	 	 	      	             	 
          BITMAP AND		  	 	 	 	      	             	 
            BITMAP CONVERSION FROM ROWIDS		  	 	 	 	      	             	 
              INDEX RANGE SCAN		EBN.IDX_TEMP_ACCOUNT	3 K	 	10  	 	      	             	 
            BITMAP CONVERSION FROM ROWIDS		  	 	 	 	      	             	 
              INDEX RANGE SCAN		EBN.IDX_CMP_TTYPE	3 K	 	69  	 	      	             	 
      NESTED LOOPS						21  	3 K	187  	 	      	             	 
        NESTED LOOPS						21  	2 K	166  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	EBN.TEMPLATES		21  	2 K	145  	 	      	             	 
            INDEX RANGE SCAN		EBN.IDX_CMP_TTYPE	3 K	 	69  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	EBN.BANKACCOUNT		1  	16  	1  	 	      	             	 
            INDEX UNIQUE SCAN		EBN.PK_BANKACCOUNT	1  	 	 	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	EBN.COMPANY		1  	32  	1  	 	      	             	 
          INDEX UNIQUE SCAN		EBN.PK_COMPANY		1  	 	 	 	      	             	 
 	 	 	      	             	 
 	 	 	      	             	 

Re: Very slow in Executing [message #184584 is a reply to message #184259] Thu, 27 July 2006 02:54 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. Verify that you have an index on COMPANY table with leading
column PARENTCOMPANYID.

2. ALTER SESSION SET star_transformation_enabled = FALES;
-- You may need to do it later in INIT.ORA as well

3. Try rewriting your query as:

SELECT 10000 AS parentindicator, NULL AS companyname,
templatetype, seqnum,
NAME, description, beneficiaryname, trxtype,
companyid AS companyid,
user_id, accountid, lastupdatedate
FROM templates
WHERE accountid IS NULL AND companyid = '20020423000007'
AND templatetype || '' IN ('2')
UNION ALL
SELECT
(SELECT COUNT (*)
FROM company com
WHERE b.companyid = com.parentcompanyid) AS parentindicator,
c.companyname AS companyname, templatetype, seqnum, NAME,
description, beneficiaryname, trxtype,
a.companyid AS companyid,
user_id, accountid, lastupdatedate
FROM templates a, bankaccount b, company c
WHERE a.accountid is not null
AND a.accountid = b.bankaccountid
AND a.companyid = c.companyid
AND a.companyid = '20020423000007'
AND a.templatetype || '' IN ('2')
ORDER BY parentindicator DESC, companyname, lastupdatedate DESC

HTH.
Michael
Re: Very slow in Executing [message #184588 is a reply to message #184584] Thu, 27 July 2006 02:57 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Correction:

ALTER SESSION ... = FALSE;

Previous Topic: Can someone help with this (image attached) query
Next Topic: how to get a query into sharable pool ??
Goto Forum:
  


Current Time: Thu Dec 08 10:39:09 CST 2016

Total time taken to generate the page: 0.08729 seconds