| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Slow performance/response
Lee,
Since you know the query causing the problem, you have made a good start. I have 5 magic digits for you...10046. This will show you everything (in excrutiating, but enlightening detail) that the query is doing. I recommend metalink and www.hotsos.com for more information as to what some of the information means. You can also post some of the info to this list (judicious editing of the large trace file is appreciated).
Basics:
alter session set timed_statistics=true;
alter session set max_dump_file_size = unlimited;
alter session set tracefile_identifier = 'BIGQUERY'
alter session set events '10046 trace name context forever, level 12';
<run query>
select * from dual; -- insure the cursor is closed (probably redundant, but I do it just to make sure) exit
There will be a file with 'BIGQUERY' as part of the name in the user_dump_destination. The key lines to look for are WAIT and STAT. WAIT tells you what the timed event was and its duration. The STAT lines are the actual execution plan.
Regards,
Daniel Fink
Leroy Kemnitz wrote:
> 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
> 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.
> The tablespace is at 75% used, not seeing waits. There is alot of disk
> reads. No disk sorts. I am seeing a high 'Physical Blks per Read %'
> 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
> places to look. I will continue to search for ideas at metalink and in
> the books.
>
>
> Lee
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Jun 29 2004 - 14:05:53 CDT
 |
 |