Reorganization of table in Oracle Database

Hi All,


As we all know reorganization help in better disk space management and performance management in oracle Database,so this task is often done in companies for Tables,Tablespaces and Databases.It helps to reduce the fragmentation( which affects the performance of the Oracle).

Below are the Steps I follow for doing reorganization:

Step 1:Check the last analyzed, tables size before Reorganization:


-- Query 1:Check the last Analyzed and number of rows:
SQL> select name from v$database;

NAME
---------
TESTDB

SQL> SELECT  OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS  FROM DBA_TABLES WHERE TABLE_NAME='ORDER_TAB';

OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 06-JUN-14 NO 71374990


--Query 2 :Check the size of the table

SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024||' '||BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='ORDER_TAB';

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024||''||BLOCKS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 41643 5330304


Note: top -c enter 1 to get the list of cpus =>8 we can give parallel upto 16


ALTER INDEX APPS.XHL_ORDER_TAB_NU5 REBUILD NOLOGGING PARALLEL 12


Note:CUSTOM_SCHEMA,APPS and ORD_SCHEMA are the important schemas used.

Step 2: Get the list of all the INDEXES associated with the tables used for Reorganization.

set lines 2000
set linesize 2000
select 'alter index ORD_SCHEMA.'||index_name||' rebuild NOLOGGING parallel 12;' from dba_indexes where table_name='ORDER_TAB';


If db is up:
=================

select 'alter index ORD_SCHEMA.'||index_name||' rebuild online NOLOGGING parallel 12;' from dba_indexes where table_name='ORDER_TAB';


INDEXES NEEDS TO BE REBUILDED:
=================================

--alter index ORD_SCHEMA.ORDER_TAB_NU17 rebuild online parallel 12;
--alter index ORD_SCHEMA.ORDER_TAB_F2 rebuild online parallel 12;
--alter index ORD_SCHEMA.ORDER_TAB_N18 rebuild online parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N10 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N11 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N12 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N6 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U2 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N9 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N1 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N7 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N8 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_R1 rebuild parallel 12;



Step 3:Make sure to make it noparallel and  logging after indexes are rebuild:

Make it no parallel to release the CPU channels

select 'alter index ORD_SCHEMA.'||index_name||' noparallel logging;' from dba_indexes where table_name='ORDER_TAB';


Step 3:Move the tables to reduce fragmentation.

SQL> spool move_table_10June2014.out
SQL> ALTER TABLE ORD_SCHEMA.ORDER_TAB MOVE;
SQL> spool off

Table altered.



Step 4: Rebuild indexes online using VNC session(vncserver)

We need to rebuild indexes using VNC session or run it in the form of shell scripts using nohup as this operation takes time.

spool rebuild_10Jun2014.out
alter index ORD_SCHEMA.ORDER_TAB_NU17 rebuild parallel 12;
alter index ORD_SCHEMA.ORDER_TAB_F2 rebuild parallel 12;
alter index ORD_SCHEMA.ORDER_TAB_N18 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N10 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N11 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N12 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N6 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U2 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N9 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N1 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N7 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N8 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_R1 rebuild parallel 12;
spool off


Step 5:Check index status again after rebuild

Check index status again after rebuild,if they are in unusable status for some reason,please rebuild them again and make sure
it comes to VALID Status.



SQL> select owner, index_name,STATUS from dba_indexes where status='UNUSABLE' and table_name='ORDER_TAB';

OWNER                          INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
APPS                           ORDER_TAB_NU17      UNUSABLE
ORD_SCHEMA                            ORDER_TAB_F2        UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU3   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N10           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N11           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N12           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_U1            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N6            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_U2            UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU1   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N9            UNUSABLE

OWNER                          INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
CUSTOM_SCHEMA                           XHL_ORDER_TAB_NU4   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N1            UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU2   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N7            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N8            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N2            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N3            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N4            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N5            UNUSABLE
APPS                           XHL_ORDER_TAB_NU5   UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_R1    UNUSABLE

22 rows selected.


To check unusable indexes for table:
==========================================

SQL> select owner, index_name,STATUS from dba_indexes where status='UNUSABLE' and table_name='ORDER_TAB';

no rows selected

SQL>




Step 6:Make it no parallel and release the channels:

select 'alter index ORD_SCHEMA.'||index_name||' noparallel logging;' from dba_indexes where table_name='ORDER_TAB';





Step 7:Check the status of INDEX after rebuild
Check the status of INDEX after rebuild,make sure it is in valid state or else rebuild the INDEX again.
SELECT INDEX_NAME||' '||INDEX_TYPE||' '||STATUS
FROM USER_INDEXES WHERE TABLE_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');


Step 8:Run the gather stats for the tables in VNC session:

We run gather statistics to choose the best posible execution plan.

spool gather_stats_tables_TESTDB.out



sqlplus "/as sysdba"

SQL>

Begin DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'ORD_SCHEMA',
tabname => 'ORDER_TAB',
degree => 10,
cascade => TRUE,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/


Step 9:Verify the number of rows,statistics ,tables size after reorganization

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> SELECT  OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS  FROM DBA_TABLES WHERE TABLE_NAME='ORDER_TAB';

OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 11-JUN-14 NO 71348700

SQL> SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024||' '||BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='ORDER_TAB';

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024||''||BLOCKS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 42534 5444352




Hope it helps.Enjoy DBA tasks...



Thanks,

Rafi

How to tune oracle Database?

Collecting and analyzing the AWR reports for Oracle Database:


Hi,
Tuning the Database is very much essential not only for better execution of SQL statement but also for applications running well using that Database.AWR(Automatic workload repository) report gives us clear picture in Deciding and tuning Database as well as SQL statement.As Enterprise Manager we can see it easily but Enterprise manager needs license(additional cost) so some companies does not want to use Oracle Enterprise Manager.Oracle gives us flexible option in which we can generated awr report in html and plain format.
It is better to generate in html format for seeing it clearly by using any web browser.Since performance tuning is a deep ocean I will be updating this thread based on my problems faced and the method which work fine for tuning purpose.


Collecting awr report from SQL prompt:

Login to the Database as 'sys' user(SYSDBA) and make sure Database is up & running and Oracle Environment for the particular Database
is set.we can gather the awr report using 'awrrpt.sql'.

Note:If we want the awr report in Oracle RAC environment than we have to use 'awrgrpt.sql' script,as there you have to gather the report for multiple instances running on various nodes.

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> @?/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
3628069655 TESTDB 1 TESTDB


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html

Type Specified: html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 3628069655 1 TESTDB TESTDB TESTNODE1.comp.com



Using 3628069655 for database Id
Using 1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
TESTDB TESTDB 5590 24 Nov 2011 00:30 1
5591 24 Nov 2011 01:30 1
5592 24 Nov 2011 02:30 1
5593 24 Nov 2011 03:30 1
5594 24 Nov 2011 04:30 1
5595 24 Nov 2011 05:30 1
5596 24 Nov 2011 06:30 1
5597 24 Nov 2011 07:30 1
5598 24 Nov 2011 08:30 1
5599 24 Nov 2011 09:30 1
5600 24 Nov 2011 10:30 1
5601 24 Nov 2011 11:30 1
5602 24 Nov 2011 12:30 1
5603 24 Nov 2011 13:30 1
5604 24 Nov 2011 14:30 1
5605 24 Nov 2011 15:30 1


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 5604
Begin Snapshot Id specified: 5604

Enter value for end_snap: 5605
End Snapshot Id specified: 5605


Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_5604_5605.html. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: awrrpt_NOV24_2011_2_30_3_30_PM.html


SQL> exit
We will see the html format of the awr report in the current operating system path.

[oracle@TESTNODE1 ~]$ ls -altr awr*
-rw-r--r-- 1 oracle dba 458371 Nov 24 14:02 awrrpt_1_5590_5603.html
-rw-r--r-- 1 oracle dba 390564 Nov 24 16:31 awrrpt_NOV24_2011_2_30_3_30_PM.html

We can copy this html file using copying tool(winscp or ftp) to our machine and review using web browsers(mozilla or IE supported
versions).


Analzing the awr report and suggesting possible recommendations:
Once we obtain the awr report our main motive is to analyze the awr report and come up with possible recommendations.Depending
on the size of our Production Database we can come up with possible recommendations.This recommendation should be first implemented in
test environment and after successful results should be adopted in production environments.

1) Redo logs:

We need to make sure our redo logs are large enough.Check the number of log switches, one every twenty minutes is ideal, more than this is too high and you should make them larger to reduce the number of switches.

We can find the log switches in the Instance Activity Stats part of the awr report.

Example:

Instance Activity Stats - Thread Activity

* Statistics identified by '(derived)' come from sources other than SYSSTAT

Statistic Total per Hour
log switches (derived) 2 2.00

We can see in this system there are 2 log swtiches per hourly basis,which is good.So this tells us the redo logs are large enough.

2)Parsing:

Check the hard parsing amount.It should be zero.If it is not, this indicates that our SGA is probably too small,increase the size of SGA and test again.
Hard parsing is caused by use of literals in SQL (as opposed to bind variables).If the queries in question are our own,we should change them to use bind variables.

We can find this information on the first page.


Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
...
Parses: 33.9 7.2
Hard parses: 0.5 0.1
...

We can see in this system the hard parses is almost zero, which is good.

Now coming to the SGA we can focus on the below considerations:

3)Buffer hit and Library hit percentages:

Check the buffer hit and library hit percentages. We want them to be 100%, if not we should increase the size of SGA.This is also on the first page:

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.82 Redo NoWait %: 100.00
Buffer Hit %: 99.52 In-memory Sort %: 100.00
Library Hit %: 98.63 Soft Parse %: 98.60
Execute to Parse %: 50.96 Latch Hit %: 98.16
Parse CPU to Parse Elapsd %: 66.67 % Non-Parse CPU: 97.75

In this case they are also good.

4)Top 5 Timed Foreground Events:

Check the average wait times.Anything over 5ms indicates a problem.If we see database CPU events in the Top 5, this indicates that SGA is too small.We may also be missing indexes.Check the optimizer statistics.

Here are the Top 5 from my environment:

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 15 59.9
log file sync 1,592 8 5 32.3 Commit
sort segment request 1 1 1001 4.0 Configurat
db file sequential read 216 1 4 3.6 User I/O
db file scattered read 64 0 6 1.5 User I/O

we can see here that the major issue is DB CPU, which generally indicates SGA is too small. However, in this case it is high because this report was run on a VM with the database and BPM sharing the CPU and disk.

Database file sequential/scattered read

These indicate time spent doing table scans and index scans (respectively).If these are high (over 5ms),We should consider moving your data files to reduce disk I/O contention, or move them to faster disks.

5)Enqueue high watermark:

This indicates hardware contention that occurs when there are multiple users inserting into LOB segments at once while the database is trying to reclaim unused space.
We should consider enabling secure files to improve LOB performance (SECURE_FILES=ALWAYS).

We cannot see these in my example report, because this was not a problem in my environment, so it did not make it into the Top 5.If it did, you would see an event called:enq: HW - contention

Other things to be aware of…

We will also check our database configuration.

6)MEMORY_TARGET:

Do not use this setting.We should have our DBA tune the memory manually instead.This will result in a better tuned database.We should start with 60% of physical memory allocated to SGA and 20% to PGA.


7)AUDIT_TRAIL:

Usually we do not use this setting much for tuning.But auditing on Database level
can be overhead to the Database.




Happy Tuning Oracle Databases ...


Best regards,

Rafi.

Cost based optimizer approach

Hi,
Performance tuning of Database and queries is very important from DBA's point of view it is no-doubt very vast and many people say it is a deep ocean and tuning can
be done to unlimited extent when we really know our data very well but many times
developers and designers will be having details of data in a better way so tuning as per me is a combined effort where DBA's,developers and Database Designers work together to make a well tune system.In the below example I'm starting with the basics
but in my future posts I will surely put some real time tuning techniques in a detailed way.
We all know how important is the 'optimizer' as depending on setting
of the optimizer and statistics the query will execute better.We all know there are two types of optimizers
1)Rule-based optimizer
2)cost-based optimizer

Let us start with the below example:

For checking the optimizer mode we can use the below approach:

SELECT * FROM V$PARAMETER WHERE NAME = 'optimizer_mode';
(OR)
SQL> show parameter %OPTIMIZER%

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS

This will tell us the mode for the database. If the value is 'CHOOSE' than the
database is in Cost-Based.However,if there are no statistics on the tables
that a query is based upon, then it is in RULE mode.

To tell for a particular query,we will need to do an EXPLAIN PLAN on the
query. If we see costs associated with the query, then the query is in
COST-BASED mode. Otherwise it is in RULE-BASED mode.

Note 1: we can use the RULE hint to force rule-based mode on any query.
Note 2:
1) Possible values for optimizer_mode = choose/ all_rows/ first_rows/ first_rows[n]
2) The ALL_ROWS optimizer mode uses a cost-based approach to determine the best access path to access your tables.

I'm not focusing on rule-based much as it is not suggested much to use.

Eg:

SQL> conn schema_test
Enter password:
Connected.
SQL> select *from TTTT;

no rows selected

SQL> SET AUTOTRACE TRACEONLY;
SQL> select *from TTTT;

no rows selected


Execution Plan
----------------------------------------------------------

----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 |
| 1 | TABLE ACCESS FULL| TTTT | 1 | 13 | 2 |
----------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
330 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed



Hope it helps...

Happy Oracle Performance tuning learning...

Best regards,

Rafi.

BUFFER BUSY WAITS AND REMEDIES

UNDERSTANDING BUFFER BUSY WAITS AND REMEDIES


Hi ,
Buffer busy wait comes in top 5 wait events.Below is the detail of dealing with them.

The Buffer Busy Waits Oracle metric occur when an Oracle session needs to access a block in the buffer cache, but cannot because the buffer copy of the data block is locked. This buffer busy wait condition can happen for either of the following reasons:

The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete.

Another session has the buffer block locked in a mode that is incompatible with the waiting session's request.
Because buffer busy waits are due to contention between particular blocks, there's nothing you can do until you know which blocks are in conflict and why the conflicts are occurring. Tuning therefore involves identifying and eliminating the cause of the block contention.

One of the most confounding problems with Oracle is the resolution of buffer busy wait events. Buffer busy waits are common in an I/O-bound Oracle system, as evidenced by any system with read (sequential/scattered) waits in the top-five waits in the Oracle STATSPACK report, like this:
Top 5 Timed Events
% Total
Event Waits Time (s) Ela Time
--------------------------- ------------ ----------- -----------
db file sequential read 2,598 7,146 48.54
db file scattered read 25,519 3,246 22.04
library cache load lock 673 1,363 9.26
CPU time 2,154 934 7.83
log file parallel write 19,157 837 5.68

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). Even if we have a huge db_cache_size, we may still see buffer busy waits, and increasing the buffer size won't help.

The resolution of a "buffer busy wait" events is one of the most confounding problems with Oracle. In an I/O-bound Oracle system, buffer busy waits are common, as evidenced by any system with read (sequential/scattered) waits in the top-five waits.

Reducing buffer busy waits reduces the total I/O on the system. This can be accomplished by tuning the SQL to access rows with fewer block reads by adding indexes, adjusting the database writer or adding freelists to tables and indexes. Even if there is a huge db_cache_size , the DBA may still see buffer busy waits and, in this case, increasing the buffer size will not help.

The most common remedies for high buffer busy waits include database writer (DBWR) contention tuning, adding freelists (or ASSM), and adding missing indexes

Best regards,

Rafi.

My Performance tuning practices

Generating Automatic Workload Repository Report:

Generating Automatic Workload Repository through Enterprise Manager Grid control:

Step 1:Login to Enterprise Manager grid control Account with the user havin dba role or grant that user
 dba role.
user:ralvi
passwd:***********

Step 2:Choose the Database whose AWR report You want to generate.In my case I'm generating awr report for our development Database AUCD between 3pm and 4pm(i.e For the interval of 1 hour)

Step 3:Choose the order as given below:
Choose :performance->Snaphosts->Automatic Workload Repository->Run AWR Report

Step 4:Select the range of time for which you want the AWR report:

Select Beginning Snapshot
Go to time 2
12
10 3pm

->Click Go
->Click Ok

Select Ending Snapshot
Go to time 2
12
10 4pm

->Click Go
->Click Ok

You will be seeing on the screen
Processing AWR report.....
This Will give you the
WORKLOAD REPOSITORY report for your Database

Note:Please choose all the above process carefully
 
 
 
Best regards,
 
Rafi.