Home » RDBMS Server » Performance Tuning » Analyze tables
Analyze tables [message #202922] Mon, 13 November 2006 05:26 Go to next message
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.
Re: Analyze tables [message #202952 is a reply to message #202922] Mon, 13 November 2006 07:17 Go to previous messageGo to next message
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.
Re: Analyze tables [message #203109 is a reply to message #202952] Mon, 13 November 2006 22:45 Go to previous messageGo to next message
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 ?

Re: Analyze tables [message #203147 is a reply to message #203109] Tue, 14 November 2006 01:50 Go to previous messageGo to next message
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.


Re: Analyze tables [message #203154 is a reply to message #203147] Tue, 14 November 2006 02:23 Go to previous messageGo to next message
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.



Re: Analyze tables [message #203172 is a reply to message #203154] Tue, 14 November 2006 03:24 Go to previous messageGo to next message
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.


Re: Analyze tables [message #203221 is a reply to message #203172] Tue, 14 November 2006 06:40 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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 Go to previous message
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.

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


Current Time: Tue Dec 06 12:24:21 CST 2016

Total time taken to generate the page: 0.05758 seconds