Performance Tuning P1

How to tune oracle Database?

Collecting and analyzing the AWR reports for Oracle Database:

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 ...

Monday, September 26, 2011

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

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

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