Performance Tuning - Part 2

7. Identifying missing indexes

To find the top SQL statements that have caused most block buffer reads:

Select buffer_gets, sql_text
from v$sqlarea
where buffer_gets > 15000
order by buffer_gets desc;
 If this returns a large number of rows then increase the number of ‘buffer_gets’ required, if it returns no rows then decrease this threshold.
Typically, most of these will be select statements of some sort. Considering each in turn, identify what indexes would help with the query and then check that those indexes exist. Create them if necessary.

To find the most frequently executed SQL:

Select executions, buffer_gets, sql_text from v$sqlarea where executions > 10000 order by executions desc;
If this returns a large number of rows then increase the number of ‘executions’ required. If it returns no rows then decrease the number of executions required.

8. Identify index fragmentation

analyze index <index_name> validate structure;
This populates the table ‘index_stats’. It should be noted that this table contains only one row and therefore only one index can be analysed at a time.
An index should be considered for rebuilding under any of the following conditions:

• The percentage of deleted rows exceeds 30% of the total, i.e. if
  del_lf_rows / lf_rows > 0.3.
• If the ‘HEIGHT’ is greater than 4.

select HEIGHT, decode (LF_ROWS,0,1,LF_ROWS), DEL_LF_ROWS from sys.index_stats;

• If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.

9. Identifying free list contention

To identify the percentage of requests that resulted in a wait for a free block run the following query:

select round( (sum(decode(w.class,'free list',count,0))/ (sum(decode(name,'db block gets', value, 0))
+ sum(decode(name,'consistent gets', value, 0)))) * 100,2)
from v$waitstat w, v$sysstat;
This should be less than 1%. To reduce contention for a table’s free list the table must be recreated with a larger value in the FREELISTS storage parameter

10.  Identify significant reparsing of SQL

The shared-pool contains (amongst other things) previously parsed SQL, and this allows Oracle to avoid re-parsing SQL unnecessarily.
The following SQL identifies those SQL statements that have needed to be re-parsed numerous times:

select executions, t.sql_text
from v$sqlarea a, v$sqltext t
where parse_calls >1 and parse_calls = executions and a.address=t.address and executions > 10000
order by executions desc;
If this returns a large number of rows then increase the number of ‘executions’ required. If it returns no rows then perhaps decrease the number of executions required.
If there is SQL that is being repeatedly reparsed then consider increasing the value of SHARED_POOL_SIZE.

11.  Reducing database fragmentation

Excessively fragmented tables or indexes can adversely affect performance. Use the following SQL to identify those database objects that have over 10 extents allocated:

select * from dba_segments where extents > 10;
In general, if a table or index has more than 10 extents then rebuild it to fit into one extent.
A table can only be rebuilt by exporting and then importing it. The database will be unavailable for use by applications during this time. The steps to accomplish this are:

1. Export the table with COMPRESS=Y
2. Drop the table
3. Import the table.

An index can be rebuilt without preventing others from still using it. Firstly change the storage parameters to make the ‘next’ storage parameter larger (perhaps double it). The initial storage value cannot be changed. Then rebuild the index. Refer http://oraclemamukutti.blogspot.com/2011/03/rebuild-index.html

I hope this might be an useful document for a beginner Oracle Database Performance Tuning Admin...

Performance Tuning - Part 1

Performance Tuning is a vast area in Oracle Database Administration. Tuning is divided into 3 parts as Oracle database is considered. 1. Performance Planning 2. Instance Tuning 3. SQL Tuning

In this post let's see about some Instance tuning basics which might help many DBAs who are new to performance tuning tasks.

1. Tuning the cache hit ratio

Select sum(getmisses) / sum(gets) "Miss ratio"

From v$rowcache;

If ratio < 15% --> DB fine

If ratio > 15% --> increase shared_pool_size

2. Tuning the library cache

Select sum(pinhits) / sum(pins) "Hit Ratio",
sum(reloads) / sum(pins) "Reload percent"
From v$librarycache
Where namespace in
('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER');

The hit ratio should be at least 85% (i.e. 0.85). The reload percent should be very low, 2% (i.e. 0.02) or less. If this is not the case, increase the initialisation parameter SHARED_POOL_SIZE. Although less likely, the init.ora parameter OPEN_CURSORS may also need to increased

3. Tuning the log buffer

To tune the value for LOG_BUFFER first determine the space request ratio, this is the ratio of redo log space requests to redo log requests:

Select Round(e.value/s.value,5) "Redo Log Ratio"
From v$sysstat s, v$sysstat e
Where s.name = 'redo log space requests'
and e.name = 'redo entries';

If the ratio of "redo log space requests" to "redo entries" is less than 5000:1 (i.e. less than 5000 in the above), then increase the size of the redo log buffer until the space request ratio stops falling.

Alternately, if memory is not a constraint then try to reduce the number of times that a process had to wait for the log cache to be flushed:

Select name, value from v$sysstat
Where name = 'redo log space requests';

The number of waits should always be zero. If not, increase the size of LOG_BUFFER, until the number returns to zero. Typically, there is no advantage in setting this beyond 1M.

If you want to know how long processes had to wait as well as the number of times then try the following script instead:

Select name, value from v$sysstat
Where name in ('redo log space requests', 'redo log space wait time');

This shows the time in units of 10 milliseconds. Be ware that because of the time granularity, 'redo log space requests' may be greater than zero whilst 'redo log space wait time' is zero. This happens if none of the waits were for 10ms or longer. Unless you have a busy server having 'redo log space wait time' of (or near) zero may indicate an acceptable level of tuning.

4. Tuning the buffer cache hit ratio

select 100*(1 - (v3.value / (v1.value + v2.value))) "Cache Hit Ratio [%]"
from v$sysstat v1, v$sysstat v2, v$sysstat v3
where
v1.name = 'db block gets' and
v2.name = 'consistent gets' and
v3.name = 'physical reads';

If the cache-hit ratio goes below 90% then:

• For Oracle 8 and earlier: increase the initialisation parameter DB_BLOCK_BUFFERS.
• For Oracle 9 onwards: increate the initialisation parameter DB_CACHE_SIZE.

5. Tuning sorts

Select name, value from v$sysstat
where name in ('sorts (memory)', 'sorts (disk)');

If a large number of sorts require I/O to disk, increase the initialisation parameter SORT_AREA_SIZE. As a guide less than 1% of the sorts being to disk is optimum.

If more than 1% of sorts are to disk then increase SORT_AREA_SIZE and then restart Oracle.

6. Tuning rollback segments

To identify contention for rollback segments first find out the number of times that processes had to wait for the rollback segment header and blocks. The V$WAITSTAT view contains this information:

select class, count from v$waitstat
where class in ('system undo header', 'system undo block', 'undo header', 'undo block');

The number of waits for any class should be compared with the number of logical reads over the same period of time. This information can be found in V$SYSSTAT:

select sum(value) from v$sysstat
where name in ('db block gets', 'consistent gets');

If the number of waits for any class of waits is greater than 1% of the total number of logical reads then add more rollback segments.

The following query gives the percentage of times that a request for data resulted in a wait for a rollback segment:

select round(sum(waits)/sum(gets),2) from v$rollstat;

If the percentage is greater than 1% then create more rollback segments.

Rollback segments should be isolated as much as possible by placing them in their own tablespace, preferably on a separate disk from other active tablespaces. The OPTIMAL parameter is used to cause rollback segments to shrink back to an optimal size after they have dynamically extended. The V$ROLLSTAT table can help in determining proper sizing of rollback segments:

Select segment_name, shrinks, aveshrink, aveactive "Avg.Active"
from v$rollstat v, dba_rollback_segs d
where v.usn = d.segment_id;

The following table shows how to interpret these results:

Cumulative number of shrinks	Average size of shrink	Recommendation
Low	Low	If the value for “Avg.Active” is close to OPTIMAL, the settings are correct. If not, then OPTIMAL is too large. (Note: Be aware that it is sometimes better to have a larger optimal value - depending on the nature of the applications running, reducing it towards “Avg.Active” may cause some applications to start experiencing ORA-01555.)
Low	High	Excellent – few, large shrinks.
High	Low	Too many shrinks – OPTIMAL is too small.
High	High	Increase OPTIMAL until the number of shrinks is lower.

Continue to Part 2 for more information... Refer http://oraclemamukutti.blogspot.com/2011/03/performance-tuning-part-2.html 

Rebuild an index

The simplest way to rebuild an index is:

Alter index <index-name> rebuild tablespace <index-tablespace>;

This also has the advantage of not preventing the index from being used whilst it is being rebuilt. The tablespace must be specified otherwise the index will be rebuilt in the current users default tablespace.
Rebuilding the index also allows new storage parameters to be assigned to the index.
If the index is corrupt, it must be dropped and recreated.

1. Firstly, identify the original creation parameters:

SELECT COLUMN_NAME FROM USER_IND_COLUMNS
WHERE INDEX_NAME = 'index_name';
SELECT TABLE_NAME, UNIQUENESS, TABLESPACE_NAME,INITIAL_EXENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS,PCT_INCREASE, PCT_FREE
FROM USER_INDEXES WHERE INDEX_NAME = 'index_name';

The above will give you the columns on which the index is based.

2. And then drop the index and recreate it:

Drop index <index-name>;

Create [UNIQUE] index <index-name>
On <table-name>
( <column-1> [ , <column-n> ] )
tablespace <tablespace>
PCTFREE <pct-free>
STORAGE (
NEXT <size>K
INITIAL <size>K
PCTINCREASE <pct_increase>
MIN_EXTENTS <min_extents>
MAX_EXTENTS <max_extents> );

Note:

1. In order to reduce the number of extents, when recreating the index it would be advisable to set the initial extent to the current index size. The current index size can be found by:
Select sum(bytes) from dba_extents
where segment_name=’<index-name>’;

2. Alternately see Obtain information about an index for a way of determining the exact index size.

3. The primary key cannot be dropped or recreated in this way.

-------------------------------------------------------------------------------------------------------------

Obtain information about an index

General details about the index can also be found by:

Analyze index <index-name> compute statistics;Select * from user_indexes
where index_name= ‘<index-name>’;
 To obtain further detail about an index:

Analyze index <index-name> validate structure;
The command:

Validate index <index-name>; 

performs the same function.

This places detailed information about the index in the table INDEX_STATS. This table can only contain one row, describing only the one index. This SQL also verifies the integrity of each data block in the index and checks for block corruption.

For example, to get the size of an index:
validate index <index_name>;

select name "INDEX NAME", blocks * 8192 "BYTES ALLOCATED",
btree_space "BYTES USED",
(btree_space / (blocks * 8192))*100 "PERCENT USED"
from index_stats;
 This assumes a block size of 8K (i.e. 8192 bytes). It shows the number of bytes allocated to the index and the number of bytes actually used.
Note that it does not confirm that each row in the table has an index entry or that each index entry points to a row in the table. To check this:

Analyze table <table-name> validate structure cascade;
-------------------------------------------------------------------------------------------------------------
 Rebuilding indexes

Periodically, and typically after large deletes or inserts, it is worth rebuilding indexes. The SQL for this is:

Alter index <index_name> rebuild;Alternatively, the following performs the same, but avoids writing to the redo logs and thus speeds up the index rebuild:

Alter index <index_name> rebuild unrecoverable;Note: If performing this under Oracle 7.3 then be sure to specify the destination tablespace, ie:

Alter index <index_name> rebuild tablespace <tablespace>;
Otherwise the index will be moved to the temporary tablespace.

Memory Information of different OS platforms

As a DB admin, sometimes it's necessary to check the memory information of the server. So here I provide some simple commands that would give you the information of the memory in the server for different OS platforms...
Note: All the commands are standalone and gives output in a different variety

AIX

lsattr -El sys0 | grep realmem
prtconf

LINUX

free
cat /proc/meminfo
dmesg | grep Memory

HP-UX

vmstat 5 5
swapinfo -tam 
glance # hit m for memory info

SOLARIS

/usr/sbin/prtconf | grep Memory

WINDOWS

Click Start --> run --> enter "dxdiag" to find system information.

Please add comments if I miss something...

Oracle Software Installation steps

This document briefs about the step by step procedure of installing Oracle software (Database/Client).

Step 1: Decide the Oracle database software (version and release) to be installed on the server.

Step 2: Go to download.oracle.com and sign in with your username/password and download the Oracle database software.

Step 3: Transfer the downloaded file to the desired server in which the installation has to be made. (ftp/sftp/scp)

Step 4: Unzip the file.

            Note: If the file is in cpio.gz format, use the following command to unzip

            $ cpio -icvdu < 10gr2_aix5l64_client.cpio.gz where 10gr2_aix5l64_client.cpio.gz is the file name

Step 5: For Unix based servers do the following before installing the software.

            1. Download mocha X server freeware software from the link

http://www.mochasoft.dk/freeware/x11.htm (Optional if already installed)

            2. Install mocha X server on your windows based OS (Optional if already installed)

            3. Run mocha X server from your windows based OS

            4. Open putty tool and configure putty for the sync of mocha X server and putty session.

                        a. Enter host name

                        b. click ssh under connections tab

                        c. click x11

                        d. click on the 'enable x11 forwarding' check box

                        e. Enter ip address of your windows based OS in the 'X display location' tab.

    e.g. 135.117.1.20:0.0

                        f. save settings and open putty session for the desired server

Step 6: Export the display by following command

            $ export DISPLAY=135.117.1.20:0.0

            Note: To test display type 'xclock'

Step 7: Check for the pre-requisites has been completed for Oracle software installation.

• Logging In to the System as root
• Checking the Hardware Requirements
• Checking the Software Requirements
• Installation Fixup Scripts
• Enabling Core File Creation
• Installing the cvuqdisk Package for Linux
• Checking the Network Setup
• Creating Required Operating System Groups and Users
• Check Resource Limits for the Oracle Software Installation Users
• Configuring Kernel Parameters for Linux
• Identifying Required Software Directories
• Identifying or Creating an Oracle Base Directory
• Choosing a Storage Option for Oracle Database and Recovery Files
• Creating Directories for Oracle Database or Recovery Files
• Configuring Storage for Oracle Database Files Using Block Devices
• Configuring Disk Devices for Oracle Database
• Stopping Existing Oracle Processes
• Configuring the oracle User's Environment

Note: For detailed explanation of the above, please refer to Oracle installation guide for different platforms from Oracle documentation.

Step 8: After all the pre-requisites has been done, start the Oracle universal installer.

            1. Go to the specific directory where the database software is present

            2. $ ./runInstaller

            Note: For AIX based servers run rootpre.sh before invoking OUI.

            3. GUI window will be popped up for installation.

            4. Follow the instructions to complete the installation.

Step 9: Edit the .profile/.bash_profile for setting Oracle environment.

            For e.g. ORACLE_BASE=/oracle/app; export ORACLE_BASE

                                 ORACLE_HOME=$ORACLE_BASE/product/11.2.0; export ORACLE_HOME

                                 PATH=/usr/sbin:$PATH; export PATH

                                 PATH=$ORACLE_HOME/bin:$PATH; export PATH

                                 LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

                                CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;

export CLASSPATH

Step 10: Test the installation by doing the following

1.      $ .oraenv

2.      ORACLE_SID = [oracle] ? (click enter)

3.      ORACLE_HOME = [/home/oracle] ? (Your oracle home as per installation)

4.      $ sqlplus

Following these steps would lead to a successful Oracle Database/Client Software installation on the server.

Check Database User privileges and Roles

You can check the particular user privileges and roles (system and object) by the use of following script.
This script gives all the privileges and roles granted to a particular user by setting usercheck environment.

set lines 110 pages 1000 ver off
col role for a16
col pv for a75 hea 'PRIVILEGE OR ROLE'
bre on role on type skip 1

define usercheck = 'USERNAME'

select grantee, 'ROL' type, granted_role pv 
from dba_role_privs where grantee = '&usercheck' union 
select grantee, 'PRV' type, privilege pv 
from dba_sys_privs where grantee = '&usercheck' union
select grantee, 'OBJ' type, 
max(decode(privilege,'WRITE','WRITE,'))||max(decode(privilege,'READ','READ'))||
max(decode(privilege,'EXECUTE','EXECUTE'))||max(decode(privilege,'SELECT','SELECT'))||
max(decode(privilege,'DELETE',',DELETE'))||max(decode(privilege,'UPDATE',',UPDATE'))||
max(decode(privilege,'INSERT',',INSERT'))||' ON '||object_type||' "'||a.owner||'.'||table_name||'"' pv 
from dba_tab_privs a, dba_objects b 
where a.owner=b.owner and a.table_name = b.object_name and a.grantee='&usercheck'
group by a.owner,table_name,object_type,grantee union
select username grantee, '---' type, 'empty user ---' pv from dba_users 
where not username in (select distinct grantee from dba_role_privs) and
not username in (select distinct grantee from dba_sys_privs) and 
not username in (select distinct grantee from dba_tab_privs) and username like '%&usercheck%'
group by username
order by grantee, type, pv;