Home » RDBMS Server » Performance Tuning » General Question (9i)
General Question [message #352226] |
Mon, 06 October 2008 13:05 |
MrSethT
Messages: 3 Registered: October 2008 Location: Columbia, SC
|
Junior Member |
|
|
I this this is a general question that I hope is easy to answer. If I query my database with a query from 9/1/08 to 9/4/08 it takes over an hour, but if I query from 9/1/08 to 10/1/08 it takes 5 min. What could possibly cause this?
|
|
|
|
|
Re: General Question [message #352747 is a reply to message #352226] |
Thu, 09 October 2008 05:33 |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
I assumed that you posted date in dd/mm/yy format.
In that case the "long" query retrieves data for 3 monhts (about 90 days) while the "fast" one doing it for a single day.
That may impact the performance.
HTH.
|
|
|
|
|
|
Re: General Question [message #352819 is a reply to message #352226] |
Thu, 09 October 2008 14:43 |
MrSethT
Messages: 3 Registered: October 2008 Location: Columbia, SC
|
Junior Member |
|
|
ugh, it's big and ugly, I know, but I guess that means you people that know what you are doing can help even more. Also, all I knew to do with the expain plan was "SELECT * from plan_table" and it fills a 1Mb txt file. Is there a better way to get you that data? I use SQLDeveloper but I don't see any way to output the plan once it is generated.
Thanks
SELECT C.DEPT_ID,
DEPT_NAME AS DEPT,
PART_ID,
PART_NAME AS PART,
OPERATION_ID,
OPERATION_NAME AS OPERATION,
MACHINE_ID,
MACHINE_NAME AS MACHINE,
FEATURE_ID,
FEATURE_NAME AS FEATURE,
'-1' AS WS_NET,
- 1 AS WS_ADDR,
C.SPECS_ID,
WS_NET,
WS_ADDR,
ROUND(USL,12) AS USL,
ROUND(LSL,12) AS LSL,
ROUND(UCLXP,12) AS UCLXP,
ROUND(LCLXP,12) AS LCLXP,
ROUND(UCLRP,12) AS UCLRP,
ROUND(LCLRP,12) AS LCLRP,
ROUND(UCLSP,12) AS UCLSP,
ROUND(LCLSP,12) AS LCLSP,
DISTRIBUTION,
SPEC_TYPE,
SUBGROUP_SIZE,
XUSER,
COUNT(1) AS RDGS,
ROUND(AVG(MEASUREMENT),12) AS MEAN,
SUM(CASE
WHEN MEASUREMENT > USL THEN 1
ELSE 0
END) AS OOTHIGH,
SUM(CASE
WHEN MEASUREMENT < LSL THEN 1
ELSE 0
END) AS OOTLOW,
SUM(CASE
WHEN MEASUREMENT > UCLXP THEN 1
ELSE 0
END) AS OOCHIGH,
SUM(CASE
WHEN MEASUREMENT < LCLXP THEN 1
ELSE 0
END) AS OOCLOW,
ROUND(SUM(POWER(MEASUREMENT - NOMINAL,4)),12) AS X4,
ROUND(SUM(POWER(MEASUREMENT - NOMINAL,3)),12) AS X3,
ROUND(SUM(POWER(MEASUREMENT - NOMINAL,2)),12) AS X2,
ROUND(SUM(MEASUREMENT - NOMINAL),12) AS X,
SUM(CASE
WHEN RJ IS NOT NULL THEN 1
ELSE 0
END) AS SUBG_CNT
FROM FACT_DATA C
JOIN (SELECT SPECS_ID,
MAX(TIMESTARTED) AS SELECTEDTIME
FROM TRANS_SPECS
WHERE SPECS_ID IN (SELECT SPECS_ID
FROM V_SPECPARAM
WHERE ((DEPT_ID = 2)))
AND (NVL(TIMEENDED,SYSDATE) >= TO_DATE('10/7/2008 12:00:00 AM','MM/DD/YYYY HH:MI:SS AM')
AND TIMESTARTED <= TO_DATE('10/7/2008 11:59:59 PM','MM/DD/YYYY HH:MI:SS AM'))
GROUP BY SPECS_ID) B
ON C.SPECS_ID = B.SPECS_ID
JOIN (SELECT *
FROM V_SPECPARAM
WHERE ((DEPT_ID = 2))) A
ON A.SPECS_ID = C.SPECS_ID
AND A.TIMESTARTED = B.SELECTEDTIME
WHERE STIME >= TO_DATE('10/7/2008 12:00:00 AM','MM/DD/YYYY HH:MI:SS AM')
AND STIME <= TO_DATE('10/7/2008 11:59:59 PM','MM/DD/YYYY HH:MI:SS AM')
AND ((C.DEPT_ID = 2))
AND ((MEASUREMENT >= (LSL - ((USL - LSL) *. 5 * 99.99))
AND MEASUREMENT <= (USL + ((USL - LSL) *. 5 * 99.99))))
GROUP BY C.DEPT_ID,
DEPT_NAME,
PART_ID,
PART_NAME,
OPERATION_ID,
OPERATION_NAME,
MACHINE_ID,
MACHINE_NAME,
FEATURE_ID,
FEATURE_NAME,
C.SPECS_ID,
WS_NET,
WS_ADDR,
USL,
LSL,
UCLXP,
LCLXP,
UCLRP,
LCLRP,
UCLSP,
LCLSP,
DISTRIBUTION,
SUBGROUP_SIZE,
XUSER,
SPEC_TYPE
|
|
|
Re: General Question [message #352856 is a reply to message #352819] |
Thu, 09 October 2008 22:58 |
|
Michel Cadot
Messages: 68716 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Should be less if you read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Tue Nov 26 09:28:11 CST 2024
|