Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Performance Problems

RE: Performance Problems

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Thu, 23 Mar 2006 12:58:32 -0500
Message-ID: <AA29A27627F842409E1D18FB19CDCF270745C269@AABO-EXCHANGE02.bos.il.pqe>


Well, certainly, there is a risk. However, due to the large size of you db and large number of data files, I think you're a candidate for stats collection on the data dictionary. See MetaLink Doc ID 245051.1 before proceeding. Also, it may be worth opening a tar before proceeding.

Finally, remember, the good news is, to collect stats, all you need to do is:
exec dbms_stats.gather_schema_stats('SYS');

And if it's a total disaster, you can quickly get right back to where you are now by just doing:
exec dbms_stats.delete_schema_stats('SYS');

Note that per the note I referenced above, ANALYZE is not recommended or supported for gathering data dictionary stats. Use DBMS_STATS procedure.

Hope that helps,

-Mark

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988

-----Original Message-----

From: Ebadi, Abdul [mailto:Abdul.Ebadi_at_Level3.com] Sent: Thursday, March 23, 2006 12:50 PM
To: Bobak, Mark; oracle-l_at_freelists.org
Subject: RE: Performance Problems

Tried the RULE hint and did not help. This is a large production DB, so any gotchas/cons I need to worry about when collecting stats on SYS? Don't want to make things worse...

Also, have done the 10046 trace and seeing bunch of "db file sequential read" and "buffer busy wait" waits.

Thanks!

-----Original Message-----

From: Bobak, Mark [mailto:Mark.Bobak_at_il.proquest.com] Sent: Thursday, March 23, 2006 10:39 AM
To: Ebadi, Abdul; oracle-l_at_freelists.org Subject: RE: Performance Problems

Try the RULE hint, does that speed things up? If that doesn't do it, try collecting stats for SYS.

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988

-----Original Message-----

From: Ebadi, Abdul [mailto:Abdul.Ebadi_at_Level3.com] Sent: Thursday, March 23, 2006 12:29 PM
To: Bobak, Mark; oracle-l_at_freelists.org
Subject: RE: Performance Problems

Optimizer mode is CHOOSE. We have not gathered stats on the data dictionary. Last_analyzed is null for all SYS/SYSTEM-owned tables.

Thanks!

-----Original Message-----

From: Bobak, Mark [mailto:Mark.Bobak_at_il.proquest.com] Sent: Thursday, March 23, 2006 10:24 AM
To: Ebadi, Abdul; oracle-l_at_freelists.org Subject: RE: Performance Problems

What is optimizer_mode set to?

Have you tried the query with a /*+ RULE */ hint?

Have you considered gathering statistics on the data dictionary?

-Mark

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ebadi, Abdul Sent: Thursday, March 23, 2006 12:20 PM
To: oracle-l_at_freelists.org
Subject: Performance Problems

We have a large datawarehouse database on Oracle and are encountering strange performance problems. A query such as "select count(*) from dba_data_files;" will run in a few seconds or as long as 15 minutes depending on when this is run. We've narrowed it down to where it is not even directly related to load on the DB. A few days ago, I ran this query right after we started the DB (no activity in the DB at all) and it took 6 minutes! The waits we were seeing were "db file sequential read" and "buffer busy wait" for this query. Keep in mind our DB is ~27TB with ~30,000 datafiles!

Oracle 9.2.0.4 on Solaris 10.

Any help will be appreciated.

Thanks!
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 23 2006 - 11:58:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US