Home » RDBMS Server » Performance Tuning » SQL execution takes long time (Oracle 220.127.116.11, Linux x86 64)
|SQL execution takes long time [message #637638]
||Thu, 21 May 2015 04:42
Registered: February 2014
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.
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
( 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,
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 #637705 is a reply to message #637679]
||Thu, 21 May 2015 21:52
Registered: December 1999
Location: Connecticut USA
Please refer to the page BlackSwan has noted. I have duplicated here part of that page but your should review the full page anyway.|
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.
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.
Current Time: Sun Oct 21 05:03:26 CDT 2018