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 |
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 |
John Watson
Messages: 8935 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 #637705 is a reply to message #637679] |
Thu, 21 May 2015 21:52 |
|
Kevin Meade
Messages: 2103 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
|
|
|
|
Goto Forum:
Current Time: Tue May 28 09:37:43 CDT 2024
|