Re: Slow performance/response
- From: Leroy Kemnitz <lkemnitz@xxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 29 Jun 2004 15:56:49 -0500
Here is the plan:
SELECT STATEMENT Cost = 3414
SORT ORDER BY
SORT GROUP BY
VIEW
WINDOW BUFFER
SORT GROUP BY
FILTER
TABLE ACCESS BY INDEX ROWID STUDENT_ANALYTICAL_BASE
NESTED LOOPS
VIEW
SORT GROUP BY
FILTER
TABLE ACCESS BY INDEX ROWID STUDENT_ANALYTICAL_BASE
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID STUDENT_ANALYTICAL_BASE
INDEX RANGE SCAN PK_STUDENT_ANALYTICAL
INDEX RANGE SCAN PK_STUDENT_ANALYTICAL
FILTER
TABLE ACCESS FULL DUAL
FILTER
TABLE ACCESS FULL DUAL
FILTER
TABLE ACCESS FULL DUAL
FILTER
TABLE ACCESS FULL DUAL
FILTER
TABLE ACCESS FULL DUAL
FILTER
TABLE ACCESS FULL DUAL
FILTER
TABLE ACCESS FULL DUAL
FILTER
TABLE ACCESS FULL DUAL
INDEX RANGE SCAN PK_STUDENT_ANALYTICAL
FILTER
TABLE ACCESS FULL DUAL
-----------------------------------
Nelson, Allan wrote:
> Sounds like you should be able to read the whole table in the original
> 30 minutes. How about a query plan=3F
>
> Allan
>
> -----Original Message-----
> =46rom: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Leroy Kemnitz
> Sent: Tuesday, June 29, 2004 1:51 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Slow performance/response
>
>
> All -
>
> I have not done tuning a great deal so bear with me. I currently have a
>
> table that consists of 10 million rows - warehouse. The largest table I
>
> have. A few months ago a query took around 30 minutes to run against=20
> this table. This was acceptable. Now the query takes almost 5 hours!!!
>
> The db is 9.2.0.4 running on aix 5.2. I have stats that I ran on the
>
> db during the query run and it shows the db working but not maxed out.=20
> The tablespace is at 75% used, not seeing waits. There is alot of disk=20
> reads. No disk sorts. I am seeing a high 'Physical Blks per Read %'=20
> but I am attrbiuting that to the datafiles being on one disk. I do plan
>
> on rearranging them after the query finishes and hope this helps.
>
> I am looking for other ideas of what could be wrong with the table or=20
> places to look. I will continue to search for ideas at metalink and in=20
> the books.
>
>
> Lee
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> =46AQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>
> ______________________________________________________________________________
> This email is intended solely for the person or entity to which it is
> addressed and may contain confidential and/or privileged information.
> Copying, forwarding or distributing this message by persons or entities other
> than the addressee is prohibited. If you have received this email in error,
> please contact the sender immediately and delete the material from any
> computer. This email may have been monitored for policy compliance. [021216]
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
--
LeRoy Kemnitz
UW System Administration
Database Administrator
780 Regent Street, #246
Madison, WI 53714
Phone: (608) 265 -5775
Fax: (608) 265 - 2090
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
