OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Analyze tables

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Analyze tables [message #202922]

Mon, 13 November 2006 05:26

seema.taunk
Messages: 96
Registered: October 2006

Member

Hi Friends,

I have analyzed tables & indexes using compute statistics after one month, after which I am getting lot of buffer busy waits on some tables.Previously buffer busy waits were absolutely zero.
How can I resolve it?
If I delete statistics & again analyze tables & indexes,will it work?
Please advise.

Report message to a moderator

Re: Analyze tables [message #202952 is a reply to message #202922]

Mon, 13 November 2006 07:17

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

hi.

buffer busy wait is not bcoz of analyze table.

1.The main way to reduce buffer busy waits is to reduce the total I/O on the system. This can be done by tuning the SQL to access rows with fewer block reads (i.e., by adding indexes).

2.The most common remedies for high buffer busy waits include database writer (DBWR) contention tuning, adding freelists to a table and index, implementing Automatic Segment Storage Management (ASSM, a.k.a bitmap freelists), and, of course, and adding a missing index to reduce buffer touches.

I am not very experiened in PERFORMANCE TUNNING but go through below link it will help you alot.

http://www.dba-oracle.com/art_builder_bbw.htm
http://www.adp-gmbh.ch/ora/tuning/buffer_busy_waits.html

hope this helps.
Mohammad Taj.

Report message to a moderator

Re: Analyze tables [message #203109 is a reply to message #202952]

Mon, 13 November 2006 22:45

seema.taunk
Messages: 96
Registered: October 2006

Member

Hi Moahammed,

Thank you for ur reply.
Initially, I was getting buffer busy waits on some tables, then I have incresed DBWR processes & converted tablespaces to ASSM.
Then buffer busy waits reduces to nearly zero.
I have analyzed tables after one month of above activity, & I am getting buffer busy waits from next day.
Can anyone help me ?

Report message to a moderator

Re: Analyze tables [message #203147 is a reply to message #203109]

Tue, 14 November 2006 01:50

seema.taunk
Messages: 96
Registered: October 2006

Member

Hi friends,

I have deleted statistics of tables on which I was getting buffer busy waits, after which buffer busy waits decreases .
Is oracle is using RBO?

Please help.

Report message to a moderator

Re: Analyze tables [message #203154 is a reply to message #203147]

Tue, 14 November 2006 02:23

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

hi seema

Is oracle is using RBO?
According Oracle DB Ver. if 10g RBO is desupported.
"Support for the RBO will be removed in Oracle10g. The last release that supports the rule-based optimizer will be Oracle9i Release 2."

http://www.dba-oracle.com/oracle_tips_10g_cbo.htm

analyzed tables & indexes

"The ANALYZE command is available for all versions of Oracle, however to obtain faster and better statistics use the procedures supplied - in 7.3.4 and 8.0 DBMS_UTILITY.ANALYZE_SCHEMA, and in 8i and above - DBMS_STATS.GATHER_SCHEMA_STATS"

http://www.ss64.com/ora/analyze.html

you can try to analyze ur table through "dbms_stats" pkg. then see what happen with buffer busy wait.

still looking for answer... and also others opinion

hope this helps.
Mohammad Taj.

Report message to a moderator

Re: Analyze tables [message #203172 is a reply to message #203154]

Tue, 14 November 2006 03:24

seema.taunk
Messages: 96
Registered: October 2006

Member

Hi Mohammad,

Thanks 4 ur reply.
Optimizer_mode is choose & database version is 9.2
Analyze table table_name compute statistics command
internally executes

dbms_stats.gather_table_stats(ownname=> 'username', tabname=> 'tablename', partname=> NULL);
end;
But after deleting statistics for those tables, buffer busy waits reduces drastically.
I am unable to determine reason.

Report message to a moderator

Re: Analyze tables [message #203221 is a reply to message #203172]

Tue, 14 November 2006 06:40

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

You are not analying indexes at all.
use

dbms_stats.gather_table_stats(ownname=> 'username', tabname=> 'tablename', cascade=>true,method_opt=>'For all indexed columns size your_size);

>>buffer busy waits reduces drastically.
From where are you getting this?

[Updated on: Tue, 14 November 2006 06:41]

Report message to a moderator

Re: Analyze tables [message #203369 is a reply to message #203221]

Tue, 14 November 2006 22:52

seema.taunk
Messages: 96
Registered: October 2006

Member

Hi Mahesh,
Thanks for ur prompt reply.
I have analyzed tables & indexes separately using OEM.
Analyze index indexname compute statistics
Same for tables.
I have generated statspack reports before deleting stats & after deleting stats.
In that there was a major difference of buffer busy waits after deleting stats of that table & its corresponding indexes.
I have read that If stats are not present for table, then oracle uses RBO. It means it is using RBO for that single table related queries right?
But if one table's stats are persent & other table's stats are not present & query uses join of those tables, then what oracle will use? CBO or RBO?
Please clarify.

Thanx in advance.

Report message to a moderator

Previous Topic:	Regarding Autotrace execution Plan
Next Topic:	I need a listing of Oracle indexes and how often they are used, how?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 29 15:13:09 CDT 2024