Tuning: How to determine an index needs to be rebuild

Summary

An Oracle server index is a schema object that can speed up the retrieval of rows by using a pointer.

You can create indexes on one or more columns of a table to speed SQL statement execution on that table. If you do not have an index on the column, then a full table scan occurs.

You can reduce disk I/O by using a rapid path access method to locate data quickly. By default, Oracle creates B-tree indexes.

After a table experiences a large number of inserts, updates, and deletes, the index can become unbalanced and fragmented and can hinder query performance.

Knowing when to Rebuild Indexes

We must first get an idea of the current state of the index by using the ANALYZE INDEX VALIDATE STRUCTURE command.

The VALIDATE STRUCTURE command can be safely executed without affecting the optimizer. The VALIDATE STRUCTURE command populates the SYS.INDEX_STATS table only. The SYS.INDEX_STATS table can be accessed with the public synonym INDEX_STATS. The INDEX_STATS table will only hold validation information for one index at a time. You will need to query this table before validating the structure of the next index.

Below is a sample output from INDEX_STATS Table.

SQL> ANALYZE INDEX IDX_GAM_ACCT VALIDATE STRUCTURE;

Statement processed.

SQL> SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM
INDEX_STATS;
NAME                      HEIGHT    LF_ROWS    LF_BLKS    DEL_LF_ROW
---------------------- -----------   ----------      ----------   ----------------
DX_GAM_ACCT           2             1                     3               6

1 row selected.

There are two rules of thumb to help determine if the index needs to be rebuilt.

1)     If the index has height greater than four, rebuild the index.

2)     The deleted leaf rows should be less than 20%.

If it is determined that the index needs to be rebuilt, this can easily be accomplished by the ALTER INDEX <INDEX_NAME> REBUILD | REBULID ONLINE command. It is not recommended, this command could be executed during normal operating hours. The alternative is to drop and re-create the index. Creating an index uses the base table as its data source that needs to put a lock on the table. The index is also unavailable during creation.

In this example, the HEIGH column is clearly showing the value 2. This is not a good candidate for rebuilding. For most indexes, the height of the index will be quite low, i.e. one or two. I have seen an index on a 2 million-row table that had height two or three. An index with height greater than four may need to be rebuilt as this might indicate a skewed tree structure. This can lead to unnecessary database block reads of the index. Let’s take another example.

SQL> ANALYZE INDEX IDX_GAM_FID VALIDATE STRUCTURE;

Statement processed.

SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)
*100 as ratio FROM INDEX_STATS;

NAME                           HEIGHT     LF_ROWS    DEL_LF_ROW RATIO    
------------------------------ ---------- ---------- ---------- -------
IDX_GAM_FID                                  1          189         62        32.80

1 row selected.

In this example, the ratio of deleted leaf rows to total leaf rows
is clearly above 20%. This is a good candidate for rebuilding.
Let’s rebuild the index and examine the results

SQL> ANALYZE INDEX IDX_GAM_FID REBUILD;

Statement processed.

SQL> ANALYZE INDEX IDX_GAM_FID VALIDATE STRUCTURE;

Statement processed.

SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*
100 as ratio FROM INDEX_STATS;

NAME                           HEIGHT     LF_ROWS    DEL_LF_ROW RATIO    
------------------------------ ---------- ---------- ---------- -------
IDX_GAM_FID                                  1          127         0        0

1 row selected.

Examining the INDEX_STATS table shows that the 62 deleted leaf rows were dropped from the index. Notice that the total number of leaf rows went from 189 to 127, which is a difference of 62 leaf rows (189-127). This index should provide better performance for the application.

 

How to adjust the high watermark in ORACLE 10g – ALTER TABLE SHRINK

Hanging around in my hotel in Chicago because it is raining, I have been cruising around in the OTN forums which inspired me to write something about the High Watermark and the Oracle 10gR1 New Feature SEGMENT SHRINKING.

The High Watermark is the maximum fill-grade a table has ever reached.
Above the high watermark are only empty blocks.
These blocks can be formatted or unformatted.

First let’s have a look at the question when space is allocated

- when you create a table at least one extent (contiguous blocks) is allocated to the table
- if you have specified MINEXTENTS the number of MINEXTENTS extents
will be allocated immedaitely to the table
- if you have not specified MINEXTENTS then exactely one extent
will be allocated (we will look at extent sizes later in another post).

Immediately after creation of the segment (table) the high watermark will be at the first block of the first extent as long as there are no inserts made.

When you insert rows into the table the high watermark will be bumped up step by step.
This is done by the server process which makes the inserts.

Now let us take a look at when space is released again from a segment like a table or index:

Let’s asume that we have filled a table with 100’0000 rows.
And let’s asume that we deleted 50’000 rows afterwards.
In this case the high watermark will have reached the level of 100’000 and will have stayed there. Which means that we have empty blocks below the high watermark now.
Oracle has a good reason this: it might occur that you delete rows and immediately this you insert rows into the same table. In this case it is good that the space was not released with the deletes, because it had to be get reallocate again for the following inserts, which would mean permanent changes to the data dictionary
(=> dba_free_space, dba_extents, dba_segements …) .
Furthermore the physical addresses of the deleted row get recycled by new rows.

These empty blocks below the high watermark can get annoying in a number of situations because they are not used by DIRECT LOADs and DIRECT PATH LOADs:

1. seriell direct load:
INSERT /*+ APPEND */
INTO hr.employees
NOLOGGING
SELECT *
FROM oe.emps;

2. parallel direct load:
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+PARALLLEL(hr.employees,2)
INTO hr.employees
NOLOGGING
SELECT *
FROM oe.emps;

3. direct path loads:
sqlldr hr/hr control=lcaselutz.ctl … direct=y (default is direct=n)

All the above actions case that the SGA is not used for the inserts but the PGA:
there wil be temporary segements filled and dumped into newly formatted blocks above the high watermark.

So we might want to get high watermark down before we load data into the table in order to use the free empty blocks for the loading.

So how can we release unused space from a table?

There are a number of possible options which are already available before Oracle 10g:
- What we always could do is export and import the segment.
After an import the table will have only one extent.
The rows will have new physical addresses and
the high watermark will be adjusted.
- Another option would be to TRUNCATE the table.
With this we would loose all rows which are in the table.
So we cannot use this if we want to keep existing records.

With Oracle 9i another possibilty was implemented:
ALTER TABLE emp MOVE TABLESPACE users;
This statement will also cause that
- the rows will have new physical addresses and
- the high watermark will be adjusted.
But for this:
- we need a full (exclusive) table lock
- the indexes will be left with the status unusable (because they contain the old rowids) and must be rebuilt.

Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark,
it is called segment shrinking and is only possible for segments which use ASSM, in other words, which are located in tablespaces which use Automatic Segement Space Management.
In such a tablespace a table does not really have a High watermark!
It uses two watermarks instead:
- the High High Watermark referred to as HHWM, above which alle blocks ar unformatted.
- the Low High Watermark referred to as LHWM below which all blocks are formatted.
We now can have unformatted blocks in the middle of a segment!

ASSM was introduced in Oracle 9iR2 and it was made the default for tablespaces in Oracle 10gR2.
With the table shrinking feature we can get Oracle
to move rows which are located in the middle or at the end of a segment
further more down to the beginning of the segment and by
this make the segment more compact.
For this we must first allow ORACLE to change the ROWIDs of these rows by issuing
ALTER TABLE emp ENABLE ROW MOVEMENT;
ROWIDs are normally assigned to a row for the life time of the row at insert time.

After we have given Oracle the permission to change the ROWIDs
we can now issue a shrink statement.
ALTER TABLE emp SHRINK SPACE;

This statement will procede in two steps:
- The first step makes the segment compact
by moving rows further down to free blocks at the beginning of the segment.
- The second step adjusts the high watermark. For this Oracle needs an exclusive table lock,
but for a very short moment only.

Table shrinking…
- will adjust the high watermark
- can be done online
- will cause only rowlocks during the operation and just a very short full table lock at the end of the operation
- indexes will be maintained and remain usable
- can be made in one go
- can be made in two steps
(this can be usefull if you cannot get a full table lock during certain hours:
you only make the first step and adjust the high watermark later
when it is more conveniant:

- ALTER TABLE emp SHRINK SPACE; – only for the emp table
- ALTER TABLE emp SHRINK SPACE CASCADE; – for all dependent objects as well

- ALTER TABLE emp SHRINK SPACE COMPACT; – only makes the first step (moves the rows)
)

How are the indexes maintained?
In the first phase Oracle scans the segment from the back to find the position of the last row.
Afterwards it scan the segment from the beginning to find the position of the first free slot in a block in this segment. In case the two positions are the same, there is nothing to shrink. In case the two positions are different Oracle deletes teh row from the back and inserts it into the free position at front of the segement. Now Oracle scan teh segement from the back and front again and again until it finds that the two positions are the same.
Since it is DML statements performed to move the rows, the indexes are maintained at the same time. Only row level locks are used for these operations in the first pase of SHRINK TABLE statement.

The following restrictions apply to table shrinking:

1.) It is only possible in tablespaces with ASSM.
2.) You cannot shrink:
- UNDO segments
- temporary segments
- clustered tables
- tables with a colmn of datatype LONG
- LOB indexes
- IOT mapping tables and IOT overflow segments
- tables with MVIEWS with ON COMMIT
- tables with MVIEWS which are based on ROWIDs

The Oracle 10g Oracle comes with a Segment Advisor utility.
The Enterprise Manager, Database Control, even has a wizzard which can search for shrink candidates.

This advisor is run automatically by an autotask job on a regular basis in the default maintainance window.

You can use the built in package DBMS_SPACE to run the advisor manually as well…

(sysdba.wordpress.com)

Tips: Tuning DB scripts

Latest addition: Reporting just the branch blocks from an index treedump.

Since many of my customers are fairly hot on security I’m often banned from plugging USB drives into local PCs or getting my laptop onto the local network or downloading from the Internet, so I’ve often had to type up ad-hoc queries whenever I’ve had a thought about some useful piece of information that I want to pull out of the database.

On the plus side this means that I usually manage to keep up to date with the information that’s available in the data dictionary and dynamic performance views – because I have to check it from time to time; on the minus side, I’ve never really got around to creating a tidy library of useful scripts – after all it only takes a few minutes to create an ad hoc query but it takes a couple of hours to write the sort of “production quality” that I would normally feel happy about publishing.

However, I’ve been asked for ideas and scraps so often that I’ve decided to start supplying some of the quick and dirty solutions that I’ve hacked together in a few minutes. Even though they’re very basic scripts, and not intended to cover all situations, you may find some of them useful.

For future reference, I’ve also added a link to this page near the top of the panel to the right.

 

Extracts from the AWR

trend_awr_stat.sql: Report a single statistic across time from the AWR history of v$sysstat.
trend_awr_os_stat.sql: Report a single statistic across time from the AWR history of v$osstat.

Simple diagnostic probes

Pga Leaks: an article that includes three queries to check memory usage for a session.
Recent SQL: an article that describes a check for SQL that has recently appeared in the SGA heap
Segment Scans: a simple script for finding objects subject to a large number of tablescans or index fast full scans
Log file switches: a script (for single instance) to report the time of, and time between, log file switches
Time to collect stats: a script to report the start time and run time of the automatic stats collection job
SGA Resizing: a script to report resizing operations (v$sga_resize_ops)
What have I done: a script to report the work done, time spent, and wait events for my session
Tracking one statement: an article describing a way of watching the work done by a critical statement as time passes
Snapshot my workload: an example of creating a package in the SYS schema to take snapshot of dynamic performance views
Hidden Parameters: Very old queries (2001) to report all parameters, including the hidden ones – session and system level

Data Diagnostics

Partition Count: an article with a script to count the number of rows in each partition of a partitioned table
tablespace usage: a script to list the extents and free space chunks in a tablespace in file and block order
Index definitions: a script to describe the indexes on a single table – column and statistical information
Oversized Indexes: a data dictionary scan for simple B-tree indexes that may be unreasonably large for the data held
Index Leaf Block scanner: a labour-intensive analysis of leaf block usage for a simple B-tree index.
Drawing an Index: An example of reading a treedump and using an analytic function to “draw a picture” of an index.
Reporting Branch blocks: Similar to “drawing an index”, but listing just the branch blocks in index order.

                                                                            (Tips from Jonathan Lewis blog)

AWR / Statspack

People frequently ask questions about interpreting statspack (and AWR) outputs. So I’ve started to collect references to examples of statspack (and AWR) outputs that have been published on the Internet with intelligent comments about interpretation. A few of the items are about related topics, rather than output and interpretation.

There are bound to be more items out there than I currently know of, so if you know of any good examples, add it to the comments list and if I like it I’ll add it to the main list.

Getting Started: if you don’t know how to install or use statspack, then the best place to start is on your server by reading the documentation at $ORACLE_HOME/rdbms/admin/spdoc.txt, which describes how to install it, how to take snapshots, and how to use the reporting facilities.

• Graham Wood: a pdf file of his presentation on ASH ( (v$active_session_history).
• Connie Dialeris Green: Diagnosing Performance Bottlenecks using Statspack and the Oracle Performance Method.
• Metalink (MOS):  Not worth reading (until further notice) Metalink note 842884.1
• Doug Burns: “Comparing current with recent history”.
• Jos Van Den Oord: Statspack Reporting from a Standby database (11g)
• Jonathan Lewis: Lesser known AWR scripts
• Jonathan Lewis: Lesser known Statspack scripts
• Jonathan Lewis: Caution with “Timed Events”
• Jonathan Lewis: The Top Timed Event
• Jonathan Lewis: Comments on “Instance Efficiency Ratios”
• Jonathan Lewis: The Statspack levels
• Jonathan Lewis: Threats of Statspack level 6 (in 9i)
• Jonathan Lewis: Checking Statspack collection times in 10g
• Jonathan Lewis: CPU Used
• Jonathan Lewis: Finding the wrong problem
• Jonathan Lewis: Scoping and capture problems with statspack
• Jonathan Lewis: Saving Optimizer Stats
• Jonathan Lewis: Timed events example from tkprof
• Jonathan Lewis: Analysing Statspack  1 – basic strategies
• Jonathan Lewis: Analysing Statspack  2 – avoiding ratios
• Jonathan Lewis: Analysing Statspack  3 – be cautious about averages
• Jonathan Lewis: Analysing Statspack  4 – generating trends from reports
• Jonathan Lewis: Analysing Statspack  5 – resource hogs
• Jonathan Lewis: Analysing Statspack  6 – Some redo log examples
• Jonathan Lewis: Analysing Statspack  7 – System work or user waits
• Jonathan Lewis: Analysing Statspack  8 – Reading and cross-checking
• Jonathan Lewis: Analysing Statspack  9 – Complete case study
• Jonathan Lewis: Analysing Statspack 10 – Context is critical
• Jonathan Lewis: Analysing Statspack 11 – Chasing some clues
• Jonathan Lewis: Analysing Statspack 12 - A little bit of RAC

Footnote: Any advice about reading statspack reports is almost always relevant when reading AWR reports.

(from Oracle Scratchpad)

Undocumented secrets for super-sizing your PGA

Introduction to PGA management

Almost every Oracle professional agrees that the old-fashioned sort_area_size and hash_area_size parameters imposed a cumbersome one-size-fits-all approach to sorting and hash joins. Different tasks require different RAM areas, and the trick has been to allow “enough” PGA RAM for sorting and hash joins without having any high-resource task “hog” all of the PGA, to the exclusion of other users.

Oracle9i introduced the pga_aggregate_target parameters to fix this resource issue, and by-and-large, pga_aggregate_target works very well for most systems. You can check your overall PGA usage with the v$pga_target_advice advisory utility or a STATSPACK or AWR report. High values for multi-pass executions, high disk sorts, or low hash join invocation might indicate a low resource usage for PGA regions.

Let’s take a look at the issues surrounding the hidden limits of pga_aggregate_target.

Hidden parameters for Oracle PGA regions

With proper understanding (and knowing that these undocumented parameters are not supported by Oracle), you can adjust your PGA regions to allow for system-specific sorting and hash joins.

• _pga_max_size – this hidden parameter defaults to 200 megabytes, regardless of the setting for pga_aggregate_target.
  
• _smm_px_max_size – This parameter is used for Oracle parallel query, and defaults to 30% of the pga_aggregate_target setting, divided by degree of parallelism (as set by a PARALLEL hint, “alter table xxx parallel” command, or the parallel_automatic_tuning initialization parameter). For example, by default a DEGREE=4 parallel query would have a maximum sort area value of 15 megabytes per session with a 200 megabyte pga_aggregate_target setting. Remember, parallel full-table scans bypass the data buffers and store the incoming data rows in the PGA region and not inside the data buffers (as defined by the db_cache_size parameter).

The limits of sorting and hashing

There are important limitations of pga_aggregate_target:

• The total work area cannot exceed 200 megabytes of RAM because of the default setting for _pga_max_size.
  
• No RAM sort may use more than 5% of pga_aggregate_target or _pga_max_size, whichever is smaller. This means that no task may use more than 200 megabytes for sorting or hash joins. The algorithm further reduces this to (200/2) for sorts so the actual limit for pure sorts will be 100 megabytes.

These restrictions were made to ensure that no large sorts or hash joins hog the PGA RAM area, but there are some secrets to optimize the PGA. For example, the following set of parameters may be mutually-exclusive:

• sort_area_size=1048576 <– sort_area_size is ignored when pga_aggregate_target is set and when workarea_size_policy =auto, unless you are using a specialized feature such as the MTS. If dedicated server connections are used, the sort_area_size parameter is ignored.
  
• pga_aggregate_target = 500m <– The maximum default allowed value is 200 megabytes, this limits sorts to 25 megabytes (5% of 500m).
  
• mts_servers<>0 <– If Multi-threaded server is being used, the pga_aggregate_target setting would be ignored in all versions except Oracle10g.

(Note: there may be some cases where sort_area_size is used in Oracle utilities, but these have not been documented, even with pga_aggregate_target.)

We also see these additional undocumented parameters:

Parameter Name	Description
_smm_advice_enabled	if TRUE, enable v$pga_advice
_smm_advice_log_size	overwrites default size of the PGA advice workarea history log
_smm_auto_cost_enabled	if TRUE, use the AUTO size policy cost functions
_smm_auto_max_io_size	Maximum IO size (in KB) used by sort/hash-join in auto mode
_smm_auto_min_io_size\	Minimum IO size (in KB) used by sort/hash-join in auto mode
_smm_bound	overwrites memory manager automatically computed bound
_smm_control	provides controls on the memory manager
_smm_max_size	maximum work area size in auto mode (serial)
_smm_min_size	minimum work area size in auto mode
_smm_px_max_size	maximum work area size in auto mode (global)
_smm_trace	Turn on/off tracing for SQL memory manager

WARNING – These are unsupported parameters and they should not be used unless you have tested their behavior on your own database and you are willing to accept full responsibility for any issues.

Super-size me

For certain Oracle applications the Oracle professional will want to allow individual tasks to exceed the default limits imposed by Oracle. For example, PC-based, 64 bit Oracle servers (1 or 2 CPU’s with 8 gigabytes of RAM) will often have unused RAM available. For example, a fully-cached 5 gigabyte database on an 8 gigabyte dedicated Oracle server will have approximately 1 gigabyte available for the PGA (allowing 20% for the OS and other SGA regions):

• O/S – 1.6 gig
• SGA – 5 gig
• PGA Space – 1 gig
• Total – 8 gig

The system has a pga_aggregate_target setting of 1 gigabyte and the undocumented parameters are at their default settings.  While it is unusual for an online system to require super-sized regions for sorting (because the result sets for online screens are normally small), there can be a benefit to having large RAM regions available for the Oracle optimizer.

The Oracle cost-based optimizer will determine whether a hash join would be beneficial over a nested-loop join, so making more PGA available for hash joins will not have any detrimental effect since the optimizer will only invoke a super-sized hash join if it is better than a nested-loop join. In a system like the example above, the following settings would increase the default sizes for large sorts and hash joins while limiting those for parallel sorts.

• pga_aggregate_target = 4g
• _pga_max_size = 400m
• _smm_px_max_size = 333m

With these hidden parameters set we see significant size increase for serial sorts and a throttling effect for parallel queries and sorts. To see a reproducible, artificial test case demonstrating sort throttling, Mike Ault has prepared a 230 page artificial test case: Validation of Sort Sizes in a Linux Oracle10g Database. However, bear in mind that it only valid for a specific release of Oracle10g, on a specific hardware and OS environment, and not using any optional features such as the MTS.

• A RAM sort or hash join may now have up to the full 200 megabytes (5% of pga_aggregate_target) a 400% increase over a 1 gigabyte pga_aggregate_target setting. With the default settings, only a 200% (100 megabyte size) increase would be possible.
  
• Parallel queries are now limited to 333 megabytes of RAM (30% of pga_aggregate_target or _smm_px_max_size), such that a DEGREE=4 parallel query would have a maximum of 83 megabytes (333 meg/4) per slave which may actually be less due to internal sizing algorithms that set the memory increments used in setting sort areas. This throttling is to prevent one parallel query using all available memory since _smm_px_max_size would default to 1.2 gigabytes with the setting for pga_aggregate_target at 4 gigabytes.
  
• You must be careful in setting the pga_aggregate_target to greater than the available memory, calculate the maximum number of users who would be sorting/hashing and multiple that times the predicted size to get your actual limitations otherwise ORA-4030 errors or swapping may occur.

In conclusion, overriding the built-in safeguards of pga_aggregate_target can make more efficient use of RAM resources in cases where large RAM regions are available on the database server. When used with care (and the blessing of Oracle Technical Support) it can often make sense to over-ride these default values to make better use of expensive RAM resources.

There is also lots of evidence that changing these parameters will have a positive effect of large, batch-oriented Oracle jobs, but you must be very careful to fully understand the limitations of the PGA parameters:

Success stories for PGA size expansion

If you have a limited number of active sessions you may wish to override the PGA governor that only allows any single task to consume 5% of the total PGA.  Laurent Schneider notes in Oracle MOSC that overriding the PGA defaults made a large batch processes run more than 8x faster:

“I set appropriate values for pga_aggregate_target and _pga_max_size…

alter system set pga_aggregate_target=6G; 
alter system set "_pga_max_size"=2000000000;

…and I gave the query some hints “NOREWRITE FULL USE_HASH ORDERED”. As a result, it boosted my query performance from 12 hours to 1.5 hour.”

Ah, if only it were that easy, just change a setting and batch jobs run six times faster.  Laurent Schneider notes some perils and reliability issues relating to this parameter and says “this parameter often leads to an ORA-4030, even when plenty of memory available, for some obscure reasons“. 

PGA usage Note:  There are other tricks for overcoming the built-in governor for PGA usage.  Oracle has a 5% limit for any individual process, and by using parallel DML any single batch job can consume 30% of the PGA without touching any of the undocumented parameters.  Oracle author Laurent Schneider noted:

“I finally opted for a more maintainable solution.  No more hints, no more undocumented parameter, but parallel processing up to 16 threads on a 4 CPU server.   As discussed in MOSC thread 460157.996, a supported way to increase the maximum PGA memory per single SQL query is to increase the degree of parallelism.“

While Laurent abandoned the undocumented approach, the promise of eight times faster execution speeds are very tempting.  Once you get permission from Oracle Technical Support to set an undocumented parameter, they can work with  to resolve errors.  While they may not address bugs, they may be able to provide alternatives and workarounds.

References

• MOSC Note:223299.1: “If pga_aggregate_target is set in the init.ora, then SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_bitmap_AREA_SIZE are ignored.” . . .

  “If pga_aggregate_target is set in the init.ora, then SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_bitmap_AREA_SIZE are ignored. . .

  If pga_aggregate_target is set in init.ora, then WORKAREA_SIZE_POLICY defaults to AUTO.”
  

• MOSC Note: 223730.1: “1- When we set the pga_aggregate_target and WORKAREA_SIZE_POLICY to auto then the *_area_size parameter are automatically ignored and oracle will automatically use the computed value for these parameters.”
  
• MOSC Note: 30918.1: “Oracle does not recommend using the SORT_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting pga_aggregate_target instead. SORT_AREA_SIZE is retained for backward compatibility.”

(dba-oracle.com)

User-Program/CPU used Statistics

/*************************************************/
/******* For how long the statements are running *******/
/*************************************************/

select sid,username, decode(status,’ACTIVE’,round(last_call_et/60),0) time,
resource_consumer_group,status,server,lockwait,
osuser,process,program,module,action, machine from V$SESSION
where username is not null
order by 3 desc;

/*************************************************/
/******* CPU used by every session *******************/
/*************************************************/

SELECT sess.username, v.sid, substr(s.name,1,30) “Statistic”, v.value
FROM v$statname s , v$sesstat v , v$session sess
WHERE s.name = ‘CPU used by this session’
and v.statistic#=s.statistic#
and v.value > 0
and sess.sid = v.sid
ORDER BY 4 desc;

/*************************************************/
/******* Memory used ******************************/
/*************************************************/

SELECT NVL(a.username,’{Background Task}’) “Username”,
a.program “Program”,
Trunc(b.value/1024) “Memory (Kb)”
FROM v$session a,
v$sesstat b,
v$statname c
WHERE a.sid = b.sid
AND b.statistic# = c.statistic#
AND c.name = ‘session pga memory’
AND a.program IS NOT NULL
ORDER BY b.value DESC;

/*************************************************/
/******* Datafile I/O *******************************/
/*************************************************/

select name df,
phywrts writes,
phyrds reads
from v$datafile a,
v$filestat b
where a.file# = b.file#
order by 2 desc ,1 desc;

/*************************************************/
/******* DBA users in database **********************/
/*************************************************/

select ‘GRANT ‘ || lower(granted_role) || ‘ TO ‘ || lower(grantee) ||
decode(admin_option,’YES’,’ WITH ADMIN OPTION;’,';’)
from sys.dba_role_privs
where grantee != ‘SYS’
and granted_role = ‘DBA’;

/*************************************************/
/******* What rollback segment is used ****************/
/*************************************************/

select a.username, a.sid, a.taddr, b.XIDUSN as “RSEG_ID”, c.segment_name, d.sql_text
from
v$session a,
v$transaction b,
dba_rollback_segs c,
v$sql d
where
a.taddr = b.addr
and a.sql_address = d.address
and b.xidusn = c.segment_id;

/*************************************************/
/******* Locks ************************************/
/*************************************************/

select * from dba_waiters where holding_session
not in (select waiting_session from dba_waiters);

/*************************************************/
/******* Consumer groups **************************/
/*************************************************/

Select
V.NAME,
V.ACTIVE_SESSIONS, V.REQUESTS, V.CPU_WAIT_TIME, V.CPU_WAITS,
V.CONSUMED_CPU_TIME, V.CURRENT_UNDO_CONSUMPTION
From SYS.V_$RSRC_CONSUMER_GROUP V;

/*************************************************/
/******* Rollback contention *************************/
/*************************************************/

select ‘The average of waits/gets is ‘||
round((sum(waits) / sum(gets)) * 100,2)||’%’
From v$rollstat;

/*************************************************/
/******* Session info *******************************/
/*************************************************/

select n.name,s.value
from v$statname n,V$sesstat s
where n.statistic# = s.statistic#
and value > 0
and s.sid = (select a.sid from v$process p,v$session a
where p.addr =a.paddr
and a.audsid = userenv(‘sessionid’))
order by n.class,n.name;

/*************************************************/
/******* Log switch *********************************/
/*************************************************/

SELECT to_char(first_time, ‘mm/dd’) “Date”,
to_char(first_time, ‘Dy’) “Day”,
count(1) “Total”,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’00′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’00′,1,0)),’999′)) “00″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’01′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’01′,1,0)),’999′)) “01″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’02′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’02′,1,0)),’999′)) “02″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’03′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’03′,1,0)),’999′)) “03″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’04′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’04′,1,0)),’999′)) “04″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’05′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’05′,1,0)),’999′)) “05″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’06′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’06′,1,0)),’999′)) “06″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’07′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’07′,1,0)),’999′)) “07″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’08′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’08′,1,0)),’999′)) “08″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’09′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’09′,1,0)),’999′)) “09″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’10′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’10′,1,0)),’999′)) “10″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’11′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’11′,1,0)),’999′)) “11″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’12′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’12′,1,0)),’999′)) “12″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’13′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’13′,1,0)),’999′)) “13″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’14′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’14′,1,0)),’999′)) “14″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’15′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’15′,1,0)),’999′)) “15″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’16′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’16′,1,0)),’999′)) “16″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’17′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’17′,1,0)),’999′)) “17″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’18′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’18′,1,0)),’999′)) “18″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’19′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’19′,1,0)),’999′)) “19″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’20′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’20′,1,0)),’999′)) “20″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’21′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’21′,1,0)),’999′)) “21″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’22′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’22′,1,0)),’999′)) “22″,
decode(to_char(SUM(decode(to_char(first_time, ‘hh24′),’23′,1,0)),’999′),’ 0′,’ ‘,to_char(SUM(decode(to_char(first_time, ‘hh24′),’23′,1,0)),’999′)) “23″
FROM V$log_history
group by to_char(first_time, ‘mm/dd’), to_char(first_time, ‘Dy’)
order by 1 desc;

Tuning Oracle’s Buffer Cache

Introduction

Oracle maintains its own buffer cache inside the system global area (SGA) for each instance. A properly sized buffer cache can usually yield a cache hit ratio over 90%, meaning that nine requests out of ten are satisfied without going to disk.

If a buffer cache is too small, the cache hit ratio will be small and more physical disk I/O will result. If a buffer cache is too big, then parts of the buffer cache will be under-utilized and memory resources will be wasted.

Checking The Cache Hit Ratio

Oracle maintains statistics of buffer cache hits and misses. The following query will show you the overall buffer cache hit ratio for the entire instance since it was started:

     SELECT (P1.value + P2.value - P3.value) / (P1.value + P2.value)
     FROM   v$sysstat P1, v$sysstat P2, v$sysstat P3
     WHERE  P1.name = 'db block gets'
     AND    P2.name = 'consistent gets'
     AND    P3.name = 'physical reads'

You can also see the buffer cache hit ratio for one specific session since that session started:

     SELECT (P1.value + P2.value - P3.value) / (P1.value + P2.value)
     FROM   v$sesstat P1, v$statname N1, v$sesstat P2, v$statname N2,
            v$sesstat P3, v$statname N3
     WHERE  N1.name = 'db block gets'
     AND    P1.statistic# = N1.statistic#
     AND    P1.sid = <enter SID of session here>
     AND    N2.name = 'consistent gets'
     AND    P2.statistic# = N2.statistic#
     AND    P2.sid = P1.sid
     AND    N3.name = 'physical reads'
     AND    P3.statistic# = N3.statistic#
     AND    P3.sid = P1.sid

You can also measure the buffer cache hit ratio between time X and time Y by collecting statistics at times X and Y and computing the deltas.

Adjusting The Size Of The Buffer Cache

The db_block_buffers parameter in the parameter file determines the size of the buffer cache for the instance. The size of the buffer cache (in bytes) is equal to the value of the db_block_buffers parameter multiplied by the data block size.

You can change the size of the buffer cache by editing the db_block_buffers parameter in the parameter file and restarting the instance.

Determining If The Buffer Cache Should Be Enlarged

If you set the db_block_lru_extended_statistics parameter to a positive number in the parameter file for an instance and restart the instance, Oracle will populate a dynamic performance view called v$recent_bucket. This view will contain the same number of rows as the setting of the db_block_lru_extended_statistics parameter. Each row will indicate how many additional buffer cache hits there might have been if the buffer cache were that much bigger.

For example, if you set db_block_lru_extended_statistics to 1000 and restart the instance, you can see how the buffer cache hit ratio would have improved if the buffer cache were one buffer bigger, two buffers bigger, and so on up to 1000 buffers bigger than its current size. Following is a query you can use, along with a sample result:

     SELECT   250 * TRUNC (rownum / 250) + 1 || ' to ' || 
              250 * (TRUNC (rownum / 250) + 1) "Interval", 
              SUM (count) "Buffer Cache Hits"
     FROM     v$recent_bucket
     GROUP BY TRUNC (rownum / 250)

     Interval           Buffer Cache Hits
     --------------- --------------------
     1 to 250                       16083
     251 to 500                     11422
     501 to 750                       683
     751 to 1000                      177

This result set shows that enlarging the buffer cache by 250 buffers would have resulted in 16,083 more hits. If there were about 30,000 hits in the buffer cache at the time this query was performed, then it would appear that adding 500 buffers to the buffer cache might be worthwhile. Adding more than 500 buffers might lead to under-utilized buffers and therefore wasted memory.

There is overhead involved in collecting extended LRU statistics. Therefore you should set the db_block_lru_extended_ statistics parameter back to zero as soon as your analysis is complete.

In Oracle7, the v$recent_bucket view was named X$KCBRBH. Only the SYS user can query X$KCBRBH. Also note that in X$KCBRBH the columns are called indx and count, instead of rownum and count.

Determining If The Buffer Cache Is Bigger Than Necessary

If you set the db_block_lru_statistics parameter to true in the parameter file for an instance and restart the instance, Oracle will populate a dynamic performance view called v$current_bucket. This view will contain one row for each buffer in the buffer cache, and each row will indicate how many of the overall cache hits have been attributable to that particular buffer.

By querying v$current_bucket with a GROUP BY clause, you can get an idea of how well the buffer cache would perform if it were smaller. Following is a query you can use, along with a sample result:

     SELECT   1000 * TRUNC (rownum / 1000) + 1 || ' to ' || 
              1000 * (TRUNC (rownum / 1000) + 1) "Interval",
              SUM (count) "Buffer Cache Hits"
     FROM     v$current_bucket
     WHERE    rownum > 0 
     GROUP BY TRUNC (rownum / 1000)

     Interval     Buffer Cache Hits
     ------------ ----------------- 
     1 to 1000               668415   
     1001 to 2000            281760   
     2001 to 3000            166940   
     3001 to 4000             14770    
     4001 to 5000              7030     
     5001 to 6000               959

This result set shows that the first 3000 buffers are responsible for over 98% of the hits in the buffer cache. This suggests that the buffer cache would be almost as effective if it were half the size; memory is being wasted on an oversized buffer cache.

There is overhead involved in collecting LRU statistics. Therefore you should set the db_block_lru_statistics parameter back to false as soon as your analysis is complete.

In Oracle7, the v$current_bucket view was named X$KCBCBH. Only the SYS user can query X$KCBCBH. Also note that in X$KCBCBH the columns are called indx and count, instead of rownum and count.

Full Table Scans

When Oracle performs a full table scan of a large table, the blocks are read into the buffer cache but placed at the least recently used end of the LRU list. This causes the blocks to be aged out quickly, and prevents one large full table scan from wiping out the entire buffer cache.

Full table scans of large tables usually result in physical disk reads and a lower buffer cache hit ratio. You can get an idea of full table scan activity at the data file level by querying v$filestat and joining to SYS.dba_data_files. Following is a query you can use and sample results:

     SELECT   A.file_name, B.phyrds, B.phyblkrd
     FROM     SYS.dba_data_files A, v$filestat B
     WHERE    B.file# = A.file_id
     ORDER BY A.file_id

     FILE_NAME                            PHYRDS   PHYBLKRD
     -------------------------------- ---------- ----------
     /u01/oradata/PROD/system01.dbf        92832     130721
     /u02/oradata/PROD/temp01.dbf           1136       7825
     /u01/oradata/PROD/tools01.dbf          7994       8002
     /u01/oradata/PROD/users01.dbf           214        214
     /u03/oradata/PROD/rbs01.dbf           20518      20518
     /u04/oradata/PROD/data01.dbf         593336    9441037
     /u05/oradata/PROD/data02.dbf        4638037    4703454
     /u06/oradata/PROD/index01.dbf       1007638    1007638
     /u07/oradata/PROD/index02.dbf       1408270    1408270

PHYRDS shows the number of reads from the data file since the instance was started. PHYBLKRD shows the actual number of data blocks read. Usually blocks are requested one at a time. However, Oracle requests blocks in batches when performing full table scans. (The db_file_multiblock_read_count parameter controls this batch size.)

In the sample result set above, there appears to be quite a bit of full table scan activity in the data01.dbf data file, since 593,336 read requests have resulted in 9,441,037 actual blocks read.

Spotting I/O Intensive SQL Statements

The v$sqlarea dynamic performance view contains one row for each SQL statement currently in the shared SQL area of the SGA for the instance. v$sqlarea shows the first 1000 bytes of each SQL statement, along with various statistics. Following is a query you can use:

     SELECT   executions, buffer_gets, disk_reads, 
              first_load_time, sql_text
     FROM     v$sqlarea
     ORDER BY disk_reads

EXECUTIONS indicates the number of times the SQL statement has been executed since it entered the shared SQL area. BUFFER_GETS indicates the collective number of logical reads issued by all executions of the statement. DISK_READS shows the collective number of physical reads issued by all executions of the statement. (A logical read is a read that resulted in a cache hit or a physical disk read. A physical read is a read that resulted in a physical disk read.)

You can review the results of this query to find SQL statements that perform lots of reads, both logical and physical. Consider how many times a SQL statement has been executed when evaluating the number of reads.

Conclusion

This brief document gives you the basic information you need in order to optimize the buffer cache size for your Oracle database. Also, you can zero in on SQL statements that cause a lot of I/O, and data files that experience a lot of full table scans.

(Follow dbspecialists.com/)