Ganex's Technology Series: September 2009

By Mike Ault

In my 16 years of working with Oracle I have seen the database grow and change as new features are added and old ones removed or changed. However, even as things change, they remain the same in many respects. We still must index properly, set memory settings correctly and specify proper hardware.

You would think that by now everyone would have a pretty good idea what an average configuration for an Oracle small, medium and large database would be, but unfortunately these classifications also undergo mutation. It wasn’t too long ago that if you used several hundred megabytes of disks you had a large database. Now of course large databases are in the terabyte and larger size range. This makes setting tuning guidelines for Oracle databases somewhat of a moving target where the rules change almost as fast as you can derive them.

Given a changing environment for tuning Oracle is the only constant I will try to present in this paper the major issues I have found in the last few years while tuning several dozen different client databases. I will try to present a method or methods for determining set points that take into account this changing beast that is Oracle.

Major Issues of Concern

From a review of the tuning reports I have generated over the last couple of years I have come up with a list of 6 major areas that the DBA or tuning expert needs to pay attention to in an Oracle environment:

· Non-use of bind variables

· Improper Index usage

· Improper memory configuration

· Improper disk setup

· Improper initialization parameter usage

· Improper PGA setup

Each of the above areas can have a profound impact on the ability of an Oracle database to function properly. In the following sections we will cover how to measure the affect of these on your system, how to correct them once found and how to prevent them from being issues in the future. Each of the above can be, and probably is somewhere at this conference, a full hour topic, so we have a lot of ground to cover, let’s get started!

Non-Use of Bind Variables

You’ll hear it from PL/SQL tuners, you’ll hear from SQL tuners and you’ll hear from shared pool tuners, the biggest problem in many applications is the non-use of bind variables.

Why is this an issue? Well, 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 (generally speaking) 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.

The activities needed to parse a statement and generate an execution plan are CPU intensive 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. I have seen several databases where the shared pool was over a gigabyte in size (one were it was 4 gig!) until bind variables were introduced, reducing the size to a couple of hundred megabytes at most.

A quick method of seeing whether code is being reused (a key indicator of proper bind variable usage) is to look at the values of reusable and non-reusable memory in the shared pool. A SQL for determining this comparison of reusable to non-reusable code is shown in figure 1.

For complete scripts, see my complete Oracle script collection at www.dba-oracle.com/oracle_scripts.htm.

ttitle 'Shared Pool Utilization'

spool sql_garbage

select 1 nopr,

to_char(a.inst_id) inst_id,

a.users users,

to_char(a.garbage,'9,999,999,999') garbage,

to_char(b.good,'9,999,999,999') good,

to_char((b.good/(b.good+a.garbage))*100,'9,999,999.999') good_percent

from (select

a.inst_id,

b.username users,

sum(a.sharable_mem+a.persistent_mem) Garbage,

to_number(null) good

from

sys.gv_$sqlarea a,

dba_users b

where

(a.parsing_user_id = b.user_id and a.executions<=1)

group by a.inst_id, b.username

union

select distinct

c.inst_id,

b.username users,

to_number(null) garbage,

sum(c.sharable_mem+c.persistent_mem) Good

from

dba_users b,

sys.gv_$sqlarea c

where

(b.user_id=c.parsing_user_id and c.executions>1)

group by c.inst_id, b.username

) a, (select

a.inst_id,

b.username users,

sum(a.sharable_mem+a.persistent_mem) Garbage,

to_number(null) good

from

sys.gv_$sqlarea a,

dba_users b

where

(a.parsing_user_id = b.user_id and a.executions<=1)

group by a.inst_id,b.username

union

select distinct

c.inst_id,

b.username users,

to_number(null) garbage,

sum(c.sharable_mem+c.persistent_mem) Good

from

dba_users b,

sys.gv_$sqlarea c

where

(b.user_id=c.parsing_user_id and c.executions>1)

group by c.inst_id, b.username

) b

where a.users=b.users

and a.inst_id=b.inst_id

and a.garbage is not null and b.good is not null

union

select 2 nopr,

'-------' inst_id,'-------------' users,

'--------------' garbage,'--------------' good,

'--------------' good_percent from dual

union

select 3 nopr,

to_char(a.inst_id,'999999'),

to_char(count(a.users)) users,

to_char(sum(a.garbage),'9,999,999,999') garbage,

to_char(sum(b.good),'9,999,999,999') good,

to_char(((sum(b.good)/(sum(b.good)+sum(a.garbage)))*100),'9,999,999.999') good_percent

from (select

a.inst_id,

b.username users,

sum(a.sharable_mem+a.persistent_mem) Garbage,

to_number(null) good

from

sys.gv_$sqlarea a,

SEE CODE DEPOT FOR FULL SCRIPT

where

(a.parsing_user_id = b.user_id and a.executions<=1)

group by a.inst_id,b.username

union

select distinct

c.inst_id,

b.username users,

to_number(null) garbage,

sum(c.sharable_mem+c.persistent_mem) Good

from

dba_users b,

sys.gv_$sqlarea c

where

(b.user_id=c.parsing_user_id and c.executions>1)

group by c.inst_id,b.username

) a, (select

a.inst_id,

b.username users,

sum(a.sharable_mem+a.persistent_mem) Garbage,

to_number(null) good

from

sys.gv_$sqlarea a,

dba_users b

where

(a.parsing_user_id = b.user_id and a.executions<=1)

group by a.inst_id,b.username

union

select distinct

c.inst_id,

b.username users,

to_number(null) garbage,

sum(c.sharable_mem+c.persistent_mem) Good

from

dba_users b,

SEE CODE DEPOT FOR FULL SCRIPT

where

(b.user_id=c.parsing_user_id and c.executions>1)

group by c.inst_id, b.username

) b

where a.users=b.users

and a.inst_id=b.inst_id

and a.garbage is not null and b.good is not null

group by a.inst_id

order by 1,2 desc

spool off

ttitle off

set pages 22

Figure 1: SQL Code to Show Shared verses Non-shared code

An example report is shown in Figure 2 for an instance with poor code reuse characteristics. The names have been changed to protect the innocent.

Date: 03/25/05 Page: 1

Time: 17:51 PM Shared Pool Utilization SYSTEM

whoville database

users Non-Shared SQL Shared SQL Percent Shared

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

WHOAPP 532,097,982 1,775,745 .333

SYS 5,622,594 5,108,017 47.602

DBSNMP 678,616 219,775 24.463

SYSMAN 439,915 2,353,205 84.250

SYSTEM 425,586 20,674 4.633

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

5 541,308,815 9,502,046 1.725

Figure 2: Example report output for poor code reuse

As you can see from Figure 2 the majority owner in this application, WHOAPP is only showing 0.3 percent of reusable code by memory usage and is tying up an amazing 530 megabytes with non-reusable code! Let’s look at a database with good reuse statistics. Look at Figure 3.

Date: 11/13/05 Page: 1

Time: 03:15 PM Shared Pool Utilization PERFSTAT

dbaville database

users Non-Shared SQL Shared SQL Percent Shared

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

DBAVILLAGE 9,601,173 81,949,581 89.513

PERFSTAT 2,652,827 199,868 7.006

DBASTAGER 1,168,137 35,468,687 96.812

SYS 76,037 5,119,125 98.536

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

4 13,498,174 122,737,261 90.092

Figure 3: Example of Good Sharing of Code

Notice in Figure 3 how the two application owners, DBAVILLAGE and DBASTAGER show 89.513 and 96.812 reuse percentage by memory footprint for code.

So what else can we look at to see about code reusage, the above reports give us a gross indication, how about something with a bit more usability to correct the situation? 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. Look at Figure 4.

For complete scripts, see my complete Oracle script collection at www.dba-oracle.com/oracle_scripts.htm.

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

Figure 4: Similar SQL report code

Figure 4 shows a simple script to determine, based on the first x characters (input when the report is executed) the number of SQL statements that are identifical up to the first xcharacters. This shows us the repeating code in the database and helps us to track down the offending statements for correction.

As you can see from Figure 5, the SQL text is pinpointed that needs fixing. Using a substring from the above SQL the V$SQLTEXT view can be used to pull an entire listing of the code.

Some may be asking: “What is a bind variable?” simply put, a bind variable is a variable inserted into the SQL code in the place of literal values. For example:

SELECT * FROM whousers WHERE first_name=’ANNA’;

Is not using bind variables. If we issued a second query:

SELECT * FROM whousers WHERE first_name=’GRINCH’;

Even though the queries are identical until the last bit where we specify the name, the Oracle query engine would 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.

SELECT * FROM whousers WHERE first_name=:whoname;

The colon in front of the variable “whoname” tells Oracle this is a bind variable that will be supplied at run time.

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. However, what if you can’t change the code? Maybe you have time, budget or vendor constraints that prevent you from being able to do the “proper” thing. What are your options?

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

· EXACT – The statements have to match exactly to be reusable

· FORCE – Always replace literals

· SIMILAR – Perform literal peeking and replace when it makes sense

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, if you see code that looks like so:

SELECT USERNAME FROM whousers 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

In the good old days Oracle followed the rule based optimizer (RBO) and the rule based optimizer followed essentially one simple premise from which it’s rules were devised:

INDEXES GOOD! FULL TABLE SCANS BAD!

Unfortunately this simple rule basis led to many less than optimal execution plans so SQL tuners spent a lot of time doing things such as null value concatenation or 0/1 math (add zero, or multiple by 1) to eliminate index usage. Of course now we have the cost based optimizer (CBO) which always gives us the correct path…not!

In essence we now 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.

Pre-9i determining full table scans was either done live by looking for full table scan related waits and backtracking to the objects showing the waits (as shown in Figure 6) or by periodically stripping the SQL from the V$SQLTEXT or V$SQLAREA views and performing explain plan commands on them into a table. The table was then searched for the plans that showed full table accesses. Neither of these were particularly user friendly.

For complete scripts, see my complete Oracle script collection at www.dba-oracle.com/oracle_scripts.htm.

rem fts_rep.sql

rem FUNCTION: Full table scan report

rem MRA

rem

col sid format 99999

col owner format a15

col segment_name format a30

col td new_value rep_date noprint

select to_char(sysdate,'ddmonyyhh24mi') td from dual;

@title80 'Full Table Scans &rep_date'

spool rep_out\&db\fts_rep_&rep_date

SELECT DISTINCT A.SID,

C.OWNER,

C.SEGMENT_NAME

FROM SYS.V_$SESSION_WAIT A,

SYS.V_$DATAFILE B,

SYS.DBA_EXTENTS C

WHERE A.P1 = B.FILE# AND

B.FILE# = C.FILE_ID AND

A.P2 BETWEEN C.BLOCK_ID AND

(C.BLOCK_ID + C.BLOCKS) AND

A.EVENT = 'db file scattered read';

spool off

ttitle off

Figure 6: Example Realtime report to obtain object undergoing full table scans

You will be happy to know that 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. An example report against the V$SQL_PLAN table is shown in Figure 7.

For complete scripts, see my complete Oracle script collection at www.dba-oracle.com/oracle_scripts.htm.

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

SEE CODE DEPOT FOR FULL SCRIPT

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

Figure 7: Example SQL to get full table scan data from database

Notice that I didn’t limit myself to just full table scans, I also looked for expensive index scans as well. An example excerpt from this report is shown in Figure 8.

Date: 11/09/04 Page: 1

Time: 08:31 PM Full Table/Index Scans PERFSTAT

whoemail database

HASH_VALUE OWNER OBJECT_NAME OPERATION OPTIONS EXECUTIONS BYTES FTS_MEG

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

4278137387 SDM DB_STATUS TABLE ACCESS FULL 30,303 1048576 30303

1977943106 SDM DB_STATUS TABLE ACCESS FULL 1,863 1048576 1863

3391889070 SDM FORWARD_RECIPIENTS TABLE ACCESS FULL 29,785 4194304 119140

1309516963 SDM FORWARD_RECIPIENTS TABLE ACCESS FULL 3,454 4194304 13816

4017881007 SDM GLOBAL_SUPPRESSION_LIST TABLE ACCESS FULL 168,020 1048576 168020

3707567343 SDM ORGANIZATION2 TABLE ACCESS FULL 6,008 1048576 6008

1705069780 SDM SP_CAMPAIGN_MAILING TABLE ACCESS FULL 1,306 10485760 13060

1047433976 SDM SS_LAST_SENT_JOB TABLE ACCESS FULL 572,896 1048576 572896

3556187438 SDM SS_LAST_SENT_JOB TABLE ACCESS FULL 572,896 1048576 572896

3207589632 SDM SS_SEND TABLE ACCESS FULL 32,275 20971520 645500

788044291 SDM SS_SENDJOBSTATUSTYPE_NNDX INDEX FAST FULL SCAN 25,655 20971520 513100

1417625610 SDM SS_SENDJOBSTATUSTYPE_NNDX INDEX FAST FULL SCAN 11,802 20971520 236040

2719565392 SDM SS_SENDJOBSTATUSTYPE_NNDX INDEX FAST FULL SCAN 11,379 20971520 227580

1533235337 SDM SS_SENDJOBSTATUSTYPE_NNDX INDEX FAST FULL SCAN 10,981 20971520 219620

3273441234 SDM SS_SENDJOBSTATUSTYPE_NNDX INDEX FAST FULL SCAN 10,594 20971520 211880

1823729862 SDM SS_TASK_OWNER_NNDX INDEX FULL SCAN 3,992 10485760 39920

3673286081 SDM SYSTEM_SEED_LIST TABLE ACCESS FULL 81,249 1048576 81249

4712038 SDM TRACK_OPEN_MRRJ_LOCK_NNDX INDEX FULL SCAN 11,159 62914560 669540

3797121012 SDM TRACK_OPEN_MRRJ_LOCK_NNDX INDEX FULL SCAN 11,159 62914560 669540

1624438202 SDM TRACK_RAW_OPEN TABLE ACCESS FULL 29,786 31457280 893580

4232130615 SDM TRACK_RAW_OPEN TABLE ACCESS FULL 29,786 31457280 893580

3109457251 SDM TRACK_RAW_OPEN TABLE ACCESS FULL 29,785 31457280 893550

3391889070 SDM TRACK_RAW_OPEN TABLE ACCESS FULL 29,785 31457280 893550

1309516963 SDM TRACK_RAW_OPEN TABLE ACCESS FULL 3,454 31457280 103620

3685251647 SDM TR_M_TOP_DOMAINS TABLE ACCESS FULL 5,925 10485760 59250

2318926907 SDM TR_R_CLICKSTREAM TABLE ACCESS FULL 1,155 10485760 11550

804017134 SDM TR_R_OPTOUT TABLE ACCESS FULL 2,375 10485760 23750

3707567343 SDM USER_INFO TABLE ACCESS FULL 6,008 10485760 60080

3328028760 SDM VIRTUAL_MTA_LOOKUP_PK INDEX FAST FULL SCAN 43,265 1048576 43265

1150816681 SDM VIRTUAL_MTA_LOOKUP_PK INDEX FAST FULL SCAN 20,193 1048576 20193

Figure 8: Example output from FTS Report.

Notice instead of trying to capture the full SQL statement I just grab the HASH value. I can then use the hash value to pull the interesting SQL statements using SQL similar to:

select sql_text
from v$sqltext
where hash_value=&hash

order by piece;

Once I see the SQL statement I use SQL similar to this to pull the table indexes:

For complete scripts, see my complete Oracle script collection at www.dba-oracle.com/oracle_scripts.htm.

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

Once I have both the SQL and the indexes for the full scanned table I can usually quickly come to a 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. An example extract from a SQL analysis of this type is shown in Figure 9.

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

Yep, no indexes will cause a FTS everytime…

Figure 9: Example SQL Analysis

Of course 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, it would be a shame to improve the performance of one statement and shoot six others in the head.

Improper Memory Configuration

If you put too-small a carburetor on a car then even though the engine may be able to do 200 MPH, you are constraining it to much less performance. Likewise if you do not give enough memory to Oracle you will prevent it from reaching its full performance potential.

In this section we will discuss two major areas of memory, the database buffer area and the shared pool area. The PGA areas are discussed in a later section.

The Database Buffer Area

Just like the old adage you can’t fly anywhere unless you go through Atlanta, you aren’t going to get data unless you go through the buffer. Admittedly there are some direct-read scenarios, but for the most part anything that goes to users or gets into the database must go through the database buffers.

Gone are the days of a single buffer area (the default) now we have 2, 4, 8,, 16, 32 K buffer areas, keep and recycle buffer pools on top of the default area. 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 it’s 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. Look at Figure 10.

For complete scripts, see my complete Oracle script collection at www.dba-oracle.com/oracle_scripts.htm.

rem vbh_status.sql

rem

rem Mike Ault -- Burleson

rem

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

Figure 10: Simple V$BH Report

In the report in Figure 10 we see a simple version of a V$BH query. Figure 10 assumes only one buffer is in play, the default buffer, and doesn’t account for any of the multiple blocksize areas or the recycle or keep areas. By not accounting for other types of buffers that may be present the report in Figure 10 can overstate the number of free buffers available. Look at Figure 11.

STATU NUMBER_BUFFERS

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

cr 33931

free 15829

xcur 371374

Figure 11: Simple V$BH report listing

From the results in Figure 11 we would conclude we had plenty of free buffers, however we would be mistaken. Look at the report in Figure 12.

STATUS NUM

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

32k cr 2930

32k xcur 29064

8k cr 1271

8k free 3

8k read 4

8k xcur 378747

free 10371

Figure 12: Detailed V$BH Status report

As you can see, while there are free buffers, only 3 of them are available to the 8k, default area and none are available to our 32K area. The free buffers are actually assigned to a keep or recycle pool area (hence the null value for the blocksize) and are not available for normal usage. The script to generate this report is shown in Figure 13.

For complete scripts, see my complete Oracle script collection at www.dba-oracle.com/oracle_scripts.htm.

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

Figure 13: Script to get all Buffer Pool Status

As you can see, the script is wee bit more complex than the simple V$BH script. No doubt there is a clever way to simplify the script using X and K$ tables, but then we would have to use the SYS user to run it and I prefer to use lower powered users when I go to client sites.

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

Luckily Oracle10g has made it relatively simple to determine if we have these other types of waits. Look at Figure 14.

For complete scripts, see my complete Oracle script collection at www.dba-oracle.com/oracle_scripts.htm.

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

SEE CODE DEPOT FOR FULL SCRIPT

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

Figure 14: Object Statistic Crosstab Report

Figure 14 shows an object statistic cross tab report based on the V$SEGMENT_STATISTICS view. The cross tab report generates a listing showing the statistics of concern as headers across the page rather than listings going down the page and summarizes them by object. This allows us to easily compare total buffer busy waits to the number of ITL or row lock waits. This ability to compare the ITL and row lock waits to buffer busy waits lets us see what objects may be experiencing contention for ITL lists, which may be experiencing excessive locking activity and through comparisons, which are highly contended for without the row lock or ITL waits. An example of the output of the report, edited for length, is shown in Figure 15.


ITL Buffer Busy Row Lock Physical Logical
Object Waits       Waits    Waits     Reads Reads
-------------- ----- ----------- -------- ---------- -----------
BILLING 0 63636 38267 1316055 410219712
BILLING_INDX1 1 16510 55 151085 21776800
...
DELIVER_INDX1 1963 36096 32962 1952600 60809744
DELIVER_INDX2 88 16250 9029 18839481 342857488
DELIVER_PK 2676 99748 29293 15256214 416206384
DELIVER_INDX3 2856 104765 31710 8505812 467240320
...
All Objects 12613 20348859 1253057 1139977207 20947864752

243 rows selected.

Figure 15: Example Object Cross Tab Report

In the above report the BILLING_INDX1 index has a large number of buffer busy waits but we can’t account for them from the ITL or Row lock waits, this indicates that the index is being constantly read and the blocks then aged out of memory forcing waits as they are re-read for the next process. On the other hand, almost all of the buffer busy waits for the DELIVER_INDX1 index can be attributed to ITL and Row Lock waits.
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.

As you can see, this object wait cross tab report can be a powerful addition to our tuning arsenal.

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.

But what about the Automatic Memory Manager in 10g? It 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.

Please check with Manuals. Author is NOT responsible for any misinformation / incorrect information or typographical errors.

NOTE: If you would like to share any useful info, please mail to the authors.

Ganex's Technology Series

Wednesday, September 30, 2009

Removing Hyperlinks using jQuery

Common Oracle performance problems

Ganex Improves .. Headline Animator

Search This Blog

Authors Blog

Subscribe

Latest Java Developments

Blog Archive