Home » RDBMS Server » Performance Tuning » SQL execution takes long time (Oracle 11.2.0.3, Linux x86 64)
SQL execution takes long time [message #637638] Thu, 21 May 2015 04:42 Go to next message
hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA
Member
Hi,

When I execute the following SQL script, it takes long time (approximately 45 minutes) to execute, so would request you to please help me tune the SQL script.

SELECT 
	SUM (AB.S_QTY) AS TITLE1,
	MAX (AB.R_QTY) AS TITLE2,
	SUM (AB.N_KG) AS TITLE3,
	MAX (AB.F_QTY) AS TITLE4,
	MAX (AB.C_QTY) AS TITLE5,
	CONCAT (CONCAT (CD.NAME, '-'), CD.TITLE) AS TITLE6,
	MN.YEAR AS TITLE7,
	MN.FULL_DATE AS TITLE8,
	CONCAT (CONCAT (GH.NAME, ' - '), GH.DESCRIPTION) AS TITLE9,
	OP.NAME AS TITLE10,
	OP.DESCRIPTION AS TITLE11,
	EF.TITLE AS TITLE12,
	EF.NAME AS TITLE13,
	EF.DESCRIPTION AS TITLE14,
	CONCAT (CONCAT (QR.NAME, ' - '), QR.DESCRIPTION) AS TITLE15,
	OP.COUNTRY AS TITLE16,
	OP.L_ENT AS TITLE17,
	KL.NAME AS TITLE18,
	KL.DESCRIPTION AS TITLE19,
	KL.COUNTRY AS TITLE20,
	KL.L_ENT AS TITLE21,
	MN.D_ID AS TITLE22
FROM 
		PRO_D EF,
		F_C_D GH,
		S_USER_FAF IJ,
		SI_D KL,
		D_MONTH MN,
		CON_D CD,
		SI_D OP,
		R_MAT_D QR,
		PL_MVP_C_F AB,
		SE_USR_A_F ST
	WHERE 
			(    CD.C_SID = AB.C_SID
			AND QR.RM_SID = AB.RM_SID
			AND QR.RM_SID = ST.P_SID
			AND OP.S_SID = AB.RS_SID
			AND GH.FC_SID = AB.FC_SID
			AND GH.FC_SID = IJ.P_SID
			AND MN.D_ID = AB.D_ID
			AND MN.YEAR = 2015
			AND EF.PSID = AB.PSID
			AND AB.SUP_S_SID = KL.S_SID
			AND IJ.NAME = 'ABC'
			AND ST.NAME = 'ABC')
	GROUP BY OP.COUNTRY,
		OP.DESCRIPTION,
		OP.L_ENT,
		OP.NAME,
		MN.D_ID,
		MN.FULL_DATE,
		MN.YEAR,
		EF.DESCRIPTION,
		EF.NAME,
		EF.TITLE,
		KL.COUNTRY,
		KL.DESCRIPTION,
		KL.L_ENT,
		KL.NAME,
		CONCAT (CONCAT (CD.NAME, '-'), CD.TITLE),
		CONCAT (CONCAT (QR.NAME, ' - '), QR.DESCRIPTION),
		CONCAT (CONCAT (GH.NAME, ' - '), GH.DESCRIPTION);


Also, for information - I have check the tables / indexes status, its analyzed yesterday.

Please let me know for any queries.
Many thanks in advance.

Re: SQL execution takes long time [message #637640 is a reply to message #637638] Thu, 21 May 2015 04:52 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
You need to provide the informstion listed in the sticky at the top of the forum As a minimum,
set lines 150
set pages 1000
alter session set statistics_level=all;

run your query....

select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
Re: SQL execution takes long time [message #637649 is a reply to message #637640] Thu, 21 May 2015 06:37 Go to previous messageGo to next message
hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA
Member
Hi,

Please find attached requested execution plan.
Thanks in advance.
  • Attachment: exp_plan.txt
    (Size: 10.84KB, Downloaded 214 times)
Re: SQL execution takes long time [message #637659 is a reply to message #637649] Thu, 21 May 2015 07:06 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
hitesh.bhatt wrote on Thu, 21 May 2015 12:37
Hi,

Please find attached requested execution plan.
Thanks in advance.
That is not what I requested.
Re: SQL execution takes long time [message #637677 is a reply to message #637659] Thu, 21 May 2015 09:08 Go to previous messageGo to next message
hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA
Member
oh sorry for inconvenience, as it sql execution took long time so directly generated the execution plan with statistics_level=all;
Re: SQL execution takes long time [message #637679 is a reply to message #637677] Thu, 21 May 2015 09:10 Go to previous messageGo to next message
BlackSwan
Messages: 26204
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: SQL execution takes long time [message #637705 is a reply to message #637679] Thu, 21 May 2015 21:52 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
Please refer to the page BlackSwan has noted. I have duplicated here part of that page but your should review the full page anyway.

Quote:
I am attaching here the promotional first chapter of my book on SQL Tuning, and the scripts file for people to find. These are free to use and share. As always, there is no warranty express or implied and use at your own risk.

At the suggestion of BlackSwan, I have also attached the latest document which describes the information needed to tune a SQL statement and how you can get it using the scripts also available here. If you are going to ask for help in tuning a SQL statement, please review this document first. You will at some point be asked to produce this information so if you can have it in your original post, better results will follow. I suggest providing the BASIC information first, then follow up with the ADVANCED information when asked.

Enjoy. Kevin


As indicated, here are the attachments. There is a minimum amount of information anyone here needs to help you, which is why everyone is asking for it. All who have responded to your request for help can help you tune your query, if you provide the information. They are all experts in Oracle which I know because I have conversed with them all over many years.

As to your basic question about performance; as was noted there is not enough to make any valid statements. Still, as a guess, your cardinality estimates are wrong and on the low side (they all say 1), and this has caused your query to use NESTED LOOPS JOIN to join a large number of rows instead of HASH JOIN. It is also quite possible your JOIN ORDER is wrong, causing you to push substantially more rows into the execution process than is necessary and thus doing more work than necessary. These things can be determined if you provide the information people are requesting. Also, if you read the chapter of the book provided, you will come to learn part of the reasoning behind these two guesses. It should only take you an hour, maybe two, to read it and surely you can invest an hour or two to learn something so important right?

Good luck, post back when you have the requested info, or at least the BASIC info noted in the attachment. At some point you may become interested in the book itself. Follow the link. The book is at its lowest price ever.

Kevin

Re: SQL execution takes long time [message #637711 is a reply to message #637705] Fri, 22 May 2015 01:19 Go to previous message
hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA
Member
Many Thanks for information
Previous Topic: Select statement taking Long time to execute
Next Topic: Materialized view option (suggestion)
Goto Forum:
  


Current Time: Sun Oct 21 05:03:26 CDT 2018