Author: Mike Ault

With every passing year Oracle database has grown and changed a lot. Many new features are added and old ones removed or changed. Although many things have changed but still some things remain the same in many respects. For example still we need to index properly, set memory settings correctly and specify proper hardware.

There are certain areas that need special attention as they can have profound impact on the ability of an Oracle database to function properly. Tuning larger databases is a great target as the rules for database tuning change almost as fast as you can derive them. In this article I will discuss the major database performance issues.

Signature generation algorithm:

The biggest problem in many applications is the non-use of bind variables. Oracle uses a signature generation algorithm to assign a hash value to each SQL statement based on the characters in the SQL statement. Any change in a statement will result in a new hash and thus Oracle assumes it is a new statement. Each new statement must be verified, parsed and have an execution plan generated and stored.

Bind Variables:

A bind variable is a variable inserted into the SQL code in the place of literal values. For example:

SELECT * FROM USERS WHERE first_name='ANNIE';
SELECT * FROM USERS WHERE first_name='GRINCH';

The above queries are identical until the last bit where we specify the name and therefore the Oracle query engine will treat them as two different queries. By using bind variables, as shown below, we allow Oracle to parse the statement once and reuse it many times. The colon in front of the variable “whoname” tells Oracle this is a bind variable that will be supplied at run time.   

SELECT * FROM USERS WHERE first_name=:whoname;

Parsing a statement and generating an execution plan are CPU intensive activities and generate recursive SQL against the data dictionary which may result in physical IO as well. The added statement and parse tree takes up space in the shared pool. Databases that do not use bind variables have very large shared pool usually over a gigabyte in size. Bind variables reduce this to a couple of hundred megabytes at most.

Determining Bind Variable Usage:

Code reuse indicates proper bind variable usage. Below is a method of seeing whether code is being reused is to look at the values of reusable and non-reusable memory in the shared pool.

V$SQLAREA and V$SQLTEXT views:

The V$SQLAREA and V$SQLTEXT views give us the capability to look at the current code in the shared pool and determine if it is using, or not using bind variables. Below is a simple script to determine, based on the first x characters (input when the report is executed) the number of SQL statements that are identical up to the first x characters. This shows us the repeating code in the database and helps us to track down the offending statements for correction.

set lines 140 pages 55 verify off feedback off
col num_of_times heading 'Number|Of|Repeats'
col SQL heading 'SubString - &&chars Characters'
col username format a15 heading 'User'
@title132 'Similar SQL'
spool rep_out\&db\similar_sql&&chars
select b.username,substr(a.sql_text,1,&&chars) SQL,
count(a.sql_text) num_of_times from v$sqlarea a, dba_users b
where a.parsing_user_id=b.user_id
group by b.username,substr(a.sql_text,1,&&chars) having count(a.sql_text)>&&num_repeats
order by count(a.sql_text) desc
/
spool off
undef chars
undef num_repeats
clear columns
set lines 80 pages 22 verify on feedback on
ttitle off

So, the proper fix for non-bind variable usage is to re-write the application to use bind variables. This of course can be an expensive and time consuming process, but ultimately it provides the best fix for the problem.

CURSOR_SHARING initialization variable:

Oracle has provided the CURSOR_SHARING initialization variable that will automatically replace the literals in your code with bind variables. The settings for CURSOR_SHARING are EXACT (the default), FORCE , and SIMILAR .

We usually suggest the use of the SIMILAR option for CURSOR_SHARING. You can tell if cursor sharing is set to FORCE or SIMILAR by either using the SHOW PARAMETER CURSOR_SHARING command or by looking at the code in the shared pool.

SELECT USERNAME FROM USERS WHERE first_name=:"SYS_B_0"

This tells you that CURSOR_SHARING is set to either FORCE or SIMILAR because of the replacement variable :”SYS_B_O”.

Improper Index Usage:

We have to look for full table scans and examine the table size, available indexes and other factors to determine if the CBO has made the proper choice. In most cases where improper full table scans are occurring I have generally found that missing or improper indexes were the cause, not the optimizer.

V$SQL_PLAN:

Starting with Oracle9i there is a new view that keeps the explain plans for all current SQL in the shared pool, this view, appropriately named V$SQL_PLAN allows DBAs to determine exactly what statements are using full table scans and more importantly how often the particular SQL statements are being executed. Below code can be used to get full table scan data from database.

rem fts report
rem based on V$SQL_PLAN table
col operation format a15
col object_name format a32
col object_owner format a15
col options format a20
col executions format 999,999,999
set pages 55 lines 132 trims on
@title132 'Full Table/Index Scans'
spool rep_out\&&db\fts
select a.object_owner,a.object_name, rtrim(a.operation) operation,
a.options, b.executions from v$sql_plan a, v$sqlarea b
where
and a.operation IN ('TABLE ACCESS','INDEX')
and a.options in ('FULL','FULL SCAN','FAST FULL SCAN','SKIP SCAN','SAMPLE FAST FULL SCAN')
and a.object_owner not in ('SYS','SYSTEM','PERFSTAT')
group by object_owner,object_name, operation, options
order by object_owner, operation, options, object_name
/
spool off
set pages 20
ttitle off

Instead of trying to capture the full SQL statement you can just grab the HASH value and then use it to pull the interesting SQL statements using SQL similar to

select sql_text from v$sqltext where hash_value=&hash order by piece;
Once SQL statement is seen you can use SQL similar to this to pull the table indexes:
set lines 132
col index_name form a30
col table_name form a30
col column_name format a30
select a.table_name,a.index_name,a.column_name,b.index_type
from dba_ind_columns a, dba_indexes b
where a.table_name =upper('&tab')
and a.table_name=b.table_name
and a.index_owner=b.owner
and a.index_name=b.index_name
order by a.table_name,a.index_name,a.column_position
/
set lines 80

The next step is the tuning decision if any additional indexes are needed or, if an existing index should be used. In some cases there is an existing index that could be used of the SQL where rewritten. In that case I will usually suggest the SQL be rewritten 

SQL> @get_it
Enter value for hash: 605795936

SQL_TEXT
----------------------------------------------------------------
DELETE FROM BOUNCE WHERE UPDATED_TS < SYSDATE - 21
1 row selected.

SQL> @get_tab_ind
Enter value for tab: bounce
TABLE_NAME   INDEX_NAME                 COLUMN_NAME    INDEX_TYPE
------------ -------------------------- -------------- ----------
BOUNCE       BOUNCE_MAILREPRECJOB_UNDX  MAILING_ID     NORMAL
BOUNCE       BOUNCE_MAILREPRECJOB_UNDX  RECIPIENT_ID   NORMAL
BOUNCE       BOUNCE_MAILREPRECJOB_UNDX  JOB_ID         NORMAL
BOUNCE       BOUNCE_MAILREPRECJOB_UNDX  REPORT_ID      NORMAL
BOUNCE       BOUNCE_PK                  MAILING_ID     NORMAL
BOUNCE       BOUNCE_PK                  RECIPIENT_ID   NORMAL
BOUNCE       BOUNCE_PK                  JOB_ID         NORMAL
7 rows selected.

 As you can see here there is no index on UPDATED_TS

SQL> @get_it
Enter value for hash: 3347592868

SQL_TEXT
SELECT VERSION_TS, CURRENT_MAJOR, CURRENT_MINOR, CURRENT_BUILD,
CURRENT_URL, MINIMUM_MAJOR, MINIMUM_MINOR, MINIMUM_BUILD, MINIMU
M_URL, INSTALL_RA_PATH, HELP_RA_PATH FROM CURRENT_CLIENT_VERSION
 4 rows selected.

 Here there is no WHERE clause, hence a FTS is required.

SQL> @get_it
Enter value for hash: 4278137387

SQL_TEXT
----------------------------------------------------------------
SELECT STATUS FROM DB_STATUS WHERE DB_NAME = 'ARCHIVE'
1 row selected.

SQL> @get_tab_ind
Enter value for tab: db_status
no rows selected

Even after you come up with a proposed index list you must thoroughly test them in a test environment as they may have other side-effects on other SQL statements.

Improper Memory Configuration

If you do not give enough memory to Oracle you will prevent it from reaching its full performance potential.

The Database Buffer Area:

You aren't going to get data unless you go through the buffer. There are 2, 4, 8, 16, 32 K buffer areas. Within these areas we have the consistent read, current read, free, exclusive current, and many other types of blocks that are used in Oracle's multi-block consistency model.

The V$BH view and its parent the X$BH table are the major tools used by the DBA to track block usage, however, you may find that the data in the V$BH view can be misleading unless you also tie in block size data.

rem vbh_status.sql

col dt new_value td noprint
select to_char(sysdate,'ddmmyyyyhh24miss') dt from dual;
@title80 'Status of DB Block Buffers'
spool rep_out\&db\vbh_status&&td
select status,count(*) number_buffers from v$bh group by status;
spool off
ttitle off
clear columns

Free buffers are assigned to a keep or recycle pool area and are not available for normal usage.

set pages 50
@title80 'All Buffers Status'
spool rep_out\&&db\all_vbh_status
select
  '32k '||status as status,
  count(*) as num
from
 v$bh
where file# in(
   select file_id
     from dba_data_files
     where tablespace_name in (
       select tablespace_name
        from dba_tablespaces
        where block_size=32768))
group by '32k '||status
union
select
  '16k '||status as status,
   count(*) as num
from
 v$bh
where
  file# in(
   select file_id
    from dba_data_files
    where tablespace_name in (
      select tablespace_name
       from dba_tablespaces
       where block_size=16384))
group by '16k '||status
union
select
  '8k '||status as status,
  count(*) as num
from
  v$bh
where
  file# in(
   select file_id
    from dba_data_files
    where tablespace_name in (
      select tablespace_name
       from dba_tablespaces
       where block_size=8192))
group by '8k '||status
union
select
  '4k '||status as status,
  count(*) as num
from
 v$bh
where
 file# in(
  select file_id
   from dba_data_files
   where tablespace_name in (
     select tablespace_name
     from dba_tablespaces
     where block_size=4096))
group by '4k '||status
union
select
  '2k '||status as status,
  count(*) as num
from
  v$bh
where
 file# in(
  select file_id
   from dba_data_files
   where tablespace_name in (
    select tablespace_name
    from dba_tablespaces
    where block_size=2048))
group by '2k '||status
union
select
  status,
  count(*) as num
from
  v$bh
where status='free'
group by status
order by 1
/
spool off
ttitle off

If you see buffer busy waits, db block waits and the like and you run the above report and see no free buffers it is probably a good bet you need to increase the number of available buffers for the area showing no free buffers. You should not immediately assume you need more buffers because of buffer busy waits as these can be caused by other problems such as row lock waits, itl waits and other issues. Oracle10g has made it relatively simple to determine if we have these other types of waits.

-- Crosstab of object and statistic for an owner
col "Object" format a20
set numwidth 12
set lines 132
set pages 50
@title132 'Object Wait Statistics'
spool rep_out\&&db\obj_stat_xtab
select * from(
select DECODE(GROUPING(a.object_name), 1, 'All Objects',
      a.object_name) AS "Object",
sum(case when a.statistic_name = 'ITL waits'
then a.value else null end) "ITL Waits",
sum(case when a.statistic_name = 'buffer busy waits'
then a.value else null end) "Buffer Busy Waits",
sum(case when a.statistic_name = 'row lock waits'
then a.value else null end) "Row Lock Waits",
sum(case when a.statistic_name = 'physical reads'
then a.value else null end) "Physical Reads",
sum(case when a.statistic_name = 'logical reads'
then a.value else null end) "Logical Reads"
where a.owner like upper('&owner')
group by rollup(a.object_name)) b
where (b."ITL Waits">0 or b."Buffer Busy Waits">0)
/
spool off
clear columns
ttitle off 

In situations where there are large numbers of ITL waits we need to consider the increase of the INITRANS setting for the table to remove this source of contention. If the predominant wait is row lock waits then we need to determine if we are properly using locking and cursors in our application (for example, we may be over using the SELECT…FOR UPDATE type code.) If, on the other hand all the waits are un-accounted for buffer busy waits, then we need to consider increasing the amount of database block buffers we have in our SGA.

By knowing how our buffers are being used and seeing exactly what waits are causing our buffer wait indications we can quickly determine if we need to tune objects or add buffers, making sizing buffer areas fairly easy.

Automatic Memory Manager:

Automatic Memory Manager is a powerful tool for DBAs with systems that have a predictable load profile, however if your system has rapid changes in user and memory loads then AMM is playing catch up and may deliver poor performance as a result. In the case of memory it may be better to hand the system too much rather than just enough, just in time (JIT).

As many companies have found when trying the JIT methodology in their manufacturing environment it only works if things are easily predictable.

The AMM is utilized in 10g by setting two parameters, the SGA_MAX_SIZE and the SGA_TARGET. The Oracle memory manager will size the various buffer areas as needed within the range between base settings or SGA_TARGET and SGA_MAX_SIZE using the SGA_TARGET setting as an optimal and the SGA_MAX_SIZE as a maximum with the manual settings used in some cases as a minimum size for the specific memory component.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Common Oracle Recovery mistakes
   Data Warehousing – Do or Don’t?
   Great Tips on Reusing Space after deletion of database data!!
   Efficient Recovery by Skipping Tablespaces!!