Home » RDBMS Server » Performance Tuning » General Question (9i)
General Question [message #352226] Mon, 06 October 2008 13:05 Go to next message
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?

Report message to a moderator

Re: General Question [message #352229 is a reply to message #352226] Mon, 06 October 2008 13:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without more information, no accurate answer can be given.

Regards
Michel

Report message to a moderator

Re: General Question [message #352249 is a reply to message #352229] Mon, 06 October 2008 21:42 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post the EXPLAIN PLAN for both queries

Report message to a moderator

Re: General Question [message #352747 is a reply to message #352226] Thu, 09 October 2008 05:33 Go to previous messageGo to next message
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.

Report message to a moderator

Re: General Question [message #352763 is a reply to message #352747] Thu, 09 October 2008 07:44 Go to previous messageGo to next message
MrSethT
Messages: 3
Registered: October 2008
Location: Columbia, SC
Junior Member
nah. It really is 5 days. I did the same (5 day) query the next day, and it took less than a mintute. So, it's not consistantly slow just becuase it is a shorter time span. I actually get this problem when I query close to today, so it's more like 10/5/08-10/9/08 takes long time vs 9/9/08-10/9/08 taking 3 min. Also, when I query just 5 days from a month ago, it goes fast. I am starting to think there might be some write locks going on or something strange like that.

Report message to a moderator

Re: General Question [message #352771 is a reply to message #352226] Thu, 09 October 2008 08:41 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
I don't think it's a lock problem:

In oracle SELECT does NOT do any locking.

try running 2 queries with event 10046 and post TKPROF with wait info.

Report message to a moderator

Re: General Question [message #352776 is a reply to message #352226] Thu, 09 October 2008 09:21 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Please post the query along with a query plan. Otherwise you will never get an answer.

It would also help if you post table definitions (simple DESC from sqlplus would be sufficient) so we could confirm or deny the use of date format dd/mm/rr vs. mm/dd/rr.

I am with HTH on this one so far. Most obvious reason is you are using date format dd/mm/rr and not mm/dd/rr.

Kevin

Report message to a moderator

Re: General Question [message #352819 is a reply to message #352226] Thu, 09 October 2008 14:43 Go to previous messageGo to next message
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

Report message to a moderator

Re: General Question [message #352856 is a reply to message #352819] Thu, 09 October 2008 22:58 Go to previous message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it's big and ugly

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

Report message to a moderator

Previous Topic: query tuning
Next Topic: After Defining Index
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Jun 29 04:30:35 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.