Home » RDBMS Server » Performance Tuning » optimisation of queries (oracle 10g)
optimisation of queries [message #393776] Tue, 24 March 2009 05:40 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Hi,

i am having two queries which are taking a lot of time to retrive the records.can anyone try for better optimisation on these queries for better results.
---------------Query to featch the encounter list----------------------
SELECT * FROM ( 
         SELECT  ENCOUNTER_ID,DATA,CREATED_DATE,ENCOUNTER_TYPE,FEELING_COMMENTS,CONSULTANT_ID 
         FROM emrVWEncounterDetails WHERE PATIENT_ID = '1526' 
         AND ENCOUNTER_ID NOT IN (SELECT PARALLEL_ENCOUNTER_ID FROM
EMRTRACKPARALLELENCOUNTERS 
         WHERE PARALLEL_ENCOUNTER_ID=ENCOUNTER_ID )
         UNION
         SELECT ENCOUNTER_ID,DATA,CREATED_DATE,ENCOUNTER_TYPE,FEELING_COMMENTS,CONSULTANT_ID 
         FROM emrVWEncounterDetails VW WHERE PATIENT_ID = '1526'  
         AND EXISTS (SELECT PARENT_ENCOUNTER_ID FROM EMRTRACKPARALLELENCOUNTERS  
         INNER JOIN EMREncounterDetails EDS on PARENT_ENCOUNTER_ID=EDS.ENCOUNTER_ID   
         WHERE EDS.PATIENT_ID  = '1526' 
         AND EDS.IS_SAVE_FOR_LATER=1 AND PARALLEL_ENCOUNTER_ID =VW.ENCOUNTER_ID ) 
         ) TEMP ORDER BY CREATED_DATE DESC
         

----------------Query to featch the parllel encounters ----------------------         
             
SELECT ENCOUNTER_NARRATION AS NARRATION , USER_NAME  AS CONSULTANT FROM
EMREncounterData 
 INNER JOIN EMRENCOUNTERDETAILS on
EMREncounterData.ENCOUNTER_ID=EMREncounterDetails.ENCOUNTER_ID
 INNER JOIN USERS ON CONSULTANT_ID=USER_LOGIN            
     WHERE IS_SAVE_FOR_LATER=0 AND ( EMREncounterData.ENCOUNTER_ID = '1557'        
     OR EMREncounterData.ENCOUNTER_ID IN (SELECT PARALLEL_ENCOUNTER_ID FROM
EMRTRACKPARALLELENCOUNTERS 
     WHERE PARENT_ENCOUNTER_ID= '1557'        )
     OR EMREncounterData.ENCOUNTER_ID IN (
       SELECT PARALLEL_ENCOUNTER_ID FROM EMRTRACKPARALLELENCOUNTERS 
      START WITH PARENT_ENCOUNTER_ID= '1557'         CONNECT BY PRIOR
PARALLEL_ENCOUNTER_ID=PARENT_ENCOUNTER_ID))
      order by EMREncounterData.ENCOUNTER_ID         
Re: optimisation of queries [message #393783 is a reply to message #393776] Tue, 24 March 2009 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: optimisation of queries [message #393790 is a reply to message #393783] Tue, 24 March 2009 06:17 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
for first query:

SELECT STATEMENT	ALL_ROWS	49	6	12366					
SORT(ORDER BY)		49	6	12366					
VIEW		48	6	12366					
SORT(UNIQUE)		48	6	12421					
UNION-ALL									
HASH JOIN(ANTI)		24	5	10325				"PARALLEL_ENCOUNTER_ID"="ENCOUNTER_ID"	
VIEW EZEMRXPUB_QC.EMRVWENCOUNTERDETAILS		16	5	10305					
UNION-ALL									
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDATA	ANALYZED	2	1	9					
NESTED LOOPS		7	2	96					
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDETAILS	ANALYZED	4	2	78					"EED"."ENCOUNTER_TYPE"='ENCOUNTER' AND "EED"."IS_SAVE_FOR_LATER"=0
INDEX(RANGE SCAN) EZEMRXPUB_QC.EMRENCOUNTERDETAILS_IX	ANALYZED	1	5					"EED"."PATIENT_ID"='1526'	
INDEX(RANGE SCAN) EZEMRXPUB_QC.EMRENCOUNTERDATA_EIX	ANALYZED	1	1					"EED"."ENCOUNTER_ID"="EEDE"."ENCOUNTER_ID"	
NESTED LOOPS		6	2	86					
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDETAILS	ANALYZED	4	2	78					"EED"."ENCOUNTER_TYPE"='DIABETES' AND "EED"."IS_SAVE_FOR_LATER"=0
INDEX(RANGE SCAN) EZEMRXPUB_QC.EMRENCOUNTERDETAILS_IX	ANALYZED	1	5					"EED"."PATIENT_ID"='1526'	
INDEX(RANGE SCAN) EZEMRXPUB_QC.EMRROUTINEDIABETESENC_EIX	ANALYZED	1	1	4				"EED"."ENCOUNTER_ID"="ERDE"."ENCOUNTER_ID"	
NESTED LOOPS		3	1	2090					
NESTED LOOPS		2	1	2054					
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRTRANSCRIPTIONDATA	ANALYZED	2	1	2028					"ETD"."IS_VERIFIED"=1
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRTRANSCRIPTIONMAPPING	ANALYZED	0	1	26					
INDEX(UNIQUE SCAN) EZEMRXPUB_QC.PK_EMRTranscriptionMapping	ANALYZED	0	1					"ETM"."TRANSCRIPTION_ID"="ETD"."TRANSCRIPTION_ID"	
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDETAILS	ANALYZED	1	1	36					"EED"."PATIENT_ID"='1526' AND "EED"."ENCOUNTER_TYPE"='ENCOUNTER'
INDEX(UNIQUE SCAN) EZEMRXPUB_QC.PK_encounterdetails	ANALYZED	0	1					"EED"."ENCOUNTER_ID"="ETM"."ENCOUNTER_ID"	
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRTRACKPARALLELENCOUNTERS	ANALYZED	7	673	2692					"PARALLEL_ENCOUNTER_ID" IS NOT NULL
NESTED LOOPS		23	1	2096					
HASH JOIN		12	1	27				"PARENT_ENCOUNTER_ID"="EDS"."ENCOUNTER_ID"	
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDETAILS	ANALYZED	4	1	12					"EDS"."IS_SAVE_FOR_LATER"=1
INDEX(RANGE SCAN) EZEMRXPUB_QC.EMRENCOUNTERDETAILS_IX	ANALYZED	1	5					"EDS"."PATIENT_ID"='1526'	
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRTRACKPARALLELENCOUNTERS	ANALYZED	7	915	13725					
VIEW EZEMRXPUB_QC.EMRVWENCOUNTERDETAILS		11	1	2069					
UNION ALL PUSHED PREDICATE									
NESTED LOOPS		4	1	52					
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDETAILS	ANALYZED	2	1	43					"EED"."PATIENT_ID"='1526' AND "EED"."ENCOUNTER_TYPE"='ENCOUNTER' AND "EED"."IS_SAVE_FOR_LATER"=0
INDEX(UNIQUE SCAN) EZEMRXPUB_QC.PK_encounterdetails	ANALYZED	1	1					"EED"."ENCOUNTER_ID"="EMRTRACKPARALLELENCOUNTERS"."PARALLEL_ENCOUNTER_ID"	
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDATA	ANALYZED	2	1	9					
INDEX(RANGE SCAN) EZEMRXPUB_QC.EMRENCOUNTERDATA_EIX	ANALYZED	1	1					"EEDE"."ENCOUNTER_ID"="EMRTRACKPARALLELENCOUNTERS"."PARALLEL_ENCOUNTER_ID"	
NESTED LOOPS		3	1	47					
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDETAILS	ANALYZED	2	1	43					"EED"."PATIENT_ID"='1526' AND "EED"."ENCOUNTER_TYPE"='DIABETES' AND "EED"."IS_SAVE_FOR_LATER"=0
INDEX(UNIQUE SCAN) EZEMRXPUB_QC.PK_encounterdetails	ANALYZED	1	1					"EED"."ENCOUNTER_ID"="EMRTRACKPARALLELENCOUNTERS"."PARALLEL_ENCOUNTER_ID"	
INDEX(RANGE SCAN) EZEMRXPUB_QC.EMRROUTINEDIABETESENC_EIX	ANALYZED	1	1	4				"ERDE"."ENCOUNTER_ID"="EMRTRACKPARALLELENCOUNTERS"."PARALLEL_ENCOUNTER_ID"	
NESTED LOOPS		4	1	2094					
NESTED LOOPS		4	1	2068					
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDETAILS	ANALYZED	2	1	40					"EED"."PATIENT_ID"='1526' AND "EED"."ENCOUNTER_TYPE"='ENCOUNTER'
INDEX(UNIQUE SCAN) EZEMRXPUB_QC.PK_encounterdetails	ANALYZED	1	1					"EED"."ENCOUNTER_ID"="EMRTRACKPARALLELENCOUNTERS"."PARALLEL_ENCOUNTER_ID"	
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRTRANSCRIPTIONDATA	ANALYZED	2	1	2028					"ETD"."IS_VERIFIED"=1
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRTRANSCRIPTIONMAPPING	ANALYZED	0	1	26					"ETM"."ENCOUNTER_ID"="EMRTRACKPARALLELENCOUNTERS"."PARALLEL_ENCOUNTER_ID"
INDEX(UNIQUE SCAN) EZEMRXPUB_QC.PK_EMRTranscriptionMapping	ANALYZED	0	1					"ETM"."TRANSCRIPTION_ID"="ETD"."TRANSCRIPTION_ID"	
for second query:

SELECT STATEMENT	ALL_ROWS	129	163	19886					
FILTER									"EMRENCOUNTERDATA"."ENCOUNTER_ID"=1557 OR  EXISTS (SELECT /*+ */ 0 FROM "EMRTRACKPARALLELENCOUNTERS" "EMRTRACKPARALLELENCOUNTERS" WHERE "PARALLEL_ENCOUNTER_ID"=:B1 AND "PARENT_ENCOUNTER_ID"=1557) OR  EXISTS (SELECT /*+ */ 0 FROM "EMRTRACKPARALLELENCOUNTERS" "EMRTRACKPARALLELENCOUNTERS" AND ("PARALLEL_ENCOUNTER_ID"=:B2))
MERGE JOIN		129	1667	203374					
SORT(JOIN)		23	2609	88706					
HASH JOIN		22	2609	88706				"EMRENCOUNTERDETAILS"."CONSULTANT_ID"="USER_LOGIN"	
TABLE ACCESS(FULL) EZEMRXPUB_QC.USERS	ANALYZED	7	433	7794					
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRENCOUNTERDETAILS	ANALYZED	14	2603	41648					"EMRENCOUNTERDETAILS"."IS_SAVE_FOR_LATER"=0
SORT(JOIN)		106	1663	146344				"EMRENCOUNTERDATA"."ENCOUNTER_ID"="EMRENCOUNTERDETAILS"."ENCOUNTER_ID"	"EMRENCOUNTERDATA"."ENCOUNTER_ID"="EMRENCOUNTERDETAILS"."ENCOUNTER_ID"
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRENCOUNTERDATA	ANALYZED	68	1663	146344					
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRTRACKPARALLELENCOUNTERS	ANALYZED	7	1	8					"PARALLEL_ENCOUNTER_ID"=:B1 AND "PARENT_ENCOUNTER_ID"=1557
FILTER									"PARALLEL_ENCOUNTER_ID"=:B1
CONNECT BY(WITH FILTERING)									"PARENT_ENCOUNTER_ID"=1557
FILTER									"PARENT_ENCOUNTER_ID"=1557
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRTRACKPARALLELENCOUNTERS	ANALYZED	2	1	8					
HASH JOIN								"PARENT_ENCOUNTER_ID"=NULL	
CONNECT BY PUMP									
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRTRACKPARALLELENCOUNTERS	ANALYZED	2	1	8					
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRTRACKPARALLELENCOUNTERS	ANALYZED	2	1	8					
Re: optimisation of queries [message #393793 is a reply to message #393776] Tue, 24 March 2009 06:25 Go to previous message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's unreadable.
Use:
EXPLAIN PLAN FOR <your select statement>
SELECT * FROM table(dbms_xplan.display);


to get a readable explain plan.
Previous Topic: rownum slowing query
Next Topic: Optimizing the query
Goto Forum:
  


Current Time: Mon Dec 05 21:36:38 CST 2016

Total time taken to generate the page: 0.15166 seconds