Have Multiple Blocksizes?
Oracle Tips by Burleson Consulting
In the pre-Oracle9i environment, it was
necessary to choose a single blocksize for the entire database. The
choice usually was based on index retrieval efficiency, to get the
best performance (which also helped optimize full table scans); and in
most cases, this meant choosing the largest blocksize available.
However, in many OLTP or mixed-mode databases, it might be more
efficient to use smaller blocksizes for when we want to return only a
small amount of information from the database.
Now, in Oracle9i, we can place OLTP data in 2K
blocks, the indexes for that data in 8K or 16K blocks, and the full
table scan tables in 32K blocks if we wish. This allows us to
custom-tailor the blocksize based on how the data it contains will be
used the majority of the time. Another example is for bitmapped
indexes: we would want the blocksize in a bitmapped index to be as
small as possible, especially if the index undergoes INSERT, UPDATE,
and DELETE operations on a frequent basis. This allows you to tune
Other New Tuning Features
I covered the use of external tables, the
MERGE command, the multitable INSERT command, bitmap-join indexes and
skip-scan indexes in Chapter 6. Refer to the index to locate their
sections if you have not read about them already.
Using the Remote DBA_UTILITIES Package
The Wiley Web site includes a number of SQL,
PL/SQL and UNIX shell scripts. Chief among the SQL and PL/SQL scripts
is the core package, Remote DBA_UTILITIES. The Remote DBA_UTILITIES package has
evolved over the last few years from a loosely connected group of
functions and procedures to an integrated package of functions and
procedures that can greatly assist the Remote DBA in the day-to-day database
Installing Remote DBA_UTILITIES Package
The Remote DBA_UTILITIES package is dependent upon a
set of grants and a set of tables and views. I suggest that you
create a separate monitoring user with its own tablespace (about 10
megabytes in size) and its own index area of about 5 meg in size (not
critical, but nice) to hold the Remote DBA_UTILITIES package definition and
support tables. This set of tablespaces will be created if the
cre_Remote DBAutil_tbsp.sql script is executed from the SYS user. (Note: It
is assumed monitoring will be done using my scripts through a
Windows-compliant workstation running SQLPLUS. See section 13.9,
“Evaluating the Results of the Ault Status.sql Reports” later in this
chapter). The grants required by the Remote DBA_UTILITIES (and other scripts)
are contained in the Remote DBAUTIL_GRANTS.SQL script. The tables and views
are created via the CREA_Remote DBAUTIL_TAB.SQL script. The general procedure
for installing the package is:
Install the files from the Remote DBAUTIL.ZIP file
into an SQL_SCRIPTS directory.
Verify or install SQLPLUS and NET8 on the
workstation where the scripts reside.
Create the Remote DBAUTIL_DATA and Remote DBAUTIL_INDEX
tablespaces (10 and 5 meg, respectively) using the
cre_Remote DBAutil_tbsp.sql procedure.
From the SYS user on the HOST machine, verify
that the DBMS_SHARED_POOL package (dbmspool.sql and prvtpool.plb in
ORACLE_HOME/rdbms/admin), the DBMS_REPAIR (dbmsrpr.sql and prvtrpr.plb
in ORACLE)HOME/rdbms/admin), and the CATBLOCK.SQL script are
installed on your database. Create a public synonym on the
DBMS_SHARED_POOL and DBMS_REPAIR packages.
From the SYS or INTERNAL Oracle user, create
the monitoring user (usually called Remote DBAUTIL), give it the CONNECT
role, with the Remote DBAUTIL_DATA tablespace as a default tablespace, and
unlimited quota on Remote DBAUTIL_DATA and Remote DBAUTIL_INDEX tablespaces. Ensure
that a suitable temporary tablespace is also assigned. This is done
using the cre_Remote DBAutil_user.sql.
From the SYS or INTERNAL user, run the
Remote DBAutil_grants.sql script. (Note: If you’re using versions prior to
8i, email me at email@example.com for the proper scripts; I may
still have them).
NOTE: I suggest making a copy of the
SQLPLUS icon and changing its “start in” directory, via the
PROPERTIES-SHORTCUT menus, to the location of the SQL scripts.
Once the Remote DBAutil_grants.sql script has been
run against the user created in step 4, log in to the instance as that
user and run the crea_Remote DBAutil_tab.sql script to create the required
Run the Remote DBA_utilities9.sql script to create
the Remote DBA_UTILITIES package. (This should work for all versions from
7.3.4 on up).
NOTE: As supplied, the version of
Remote DBA_UTILITIES may differ and may not run on the latest version of
Oracle; if this is the case, contact me at firstname.lastname@example.org and
a proper version will be emailed to you. Once a successful compilation
of the Remote DBA_UTILITIES script is completed, you are ready to begin using
the package and related scripts to perform monitoring and tuning.
The directories used by the Remote DBA_UTILITIES
scripts require that the following structure be in place for your
Upper level SQL directory\rep_out\instance1_name
The upper-level SQL directory is the same as
the directory used in step 6.
Functions and Procedures in the
Remote DBA_UTILITIES Package
The best place to start when looking at any
package is the package header. A properly written package header will
provide documentation on each object contained in the package. The
header for the Remote DBA_UTILITIES package contains most of the information
given in this section should you lose this book. Let’s look at each
function and procedure in the package and see what it does for a Remote DBA
Function start_it. I suggest that every
package have a function similar to start_it. The function does
nothing, it consists of a begin, NULL, return, and end sequence. The
return variable just shows successful calling of the package. So what
the heck does this do for us? By calling any part of a package, the
entire package is loaded into the shared library. This allows us to
pin it into the shared pool. By having a null function in the package,
you can construct a simple script to load and pin all of your
in-house-designed packages into memory. In versions after Oracle8i,
the act of pinning automatically calls an object into the pool, but
what the heck, I’ll leave this in here and have those folks who don’t
read documentation scratching their heads over it.
Function return_version. The
return_version function looks at the v$version internal view and
returns a string that contains the Oracle database version. This
string can then be used by the calling procedure to decide which
features to use or monitor, since some of the internal tables change
from one version to another.
Procedure startup_date. The
startup_date procedure returns the date the instance was started. This
startup date is then added to all statistics reports where startup
date makes a difference. This procedure changes from Oracle 7.2 to 7.3
so this requires two versions of the Remote DBA_UTILITIES package (the actual
structure of the v$instance table changes, so a single version is not
Procedure change_role. The change_role
procedure allows a role to by dynamically assigned during operation.
It uses the DBMS_SQL package, which expects a role and role password
and then uses the DBMS_SQL and DBMS_SESSION packages to reset the
user’s role to the requested role. Designed to be used from within an
Procedure change_pwd. The change_pwd
procedure allows a user to change his or her password with a procedure
call. The procedure uses the DBMS_SQL package to execute a dynamic
ALTER USER command. The change_pwd procedure is designed to be used by
Procedure kill_session. The procedure
kill_session is passed the session ID and serial number and then uses
DBMS_SQL to issue an ALTER SYSTEM KILL SESSION command. The
kill_session procedure is designed for use from an application or
script to do bulk killing of user sessions prior to maintenance
operations. Take care when automating session killing using
kill_session to avoid killing sessions owned by SYS or NULL. The
ORA_KILL.SQL script shows an example use of this procedure.
Procedure just_statistics. The
just_statistics procedure calculates a set of database health
statistics. Various ratios, percentages, counts are performed to pull
vital database statistics into the Remote DBA_temp table; from there a report
can be created. The do_cals2.sql script executes this procedure and
generates a report. The just_statistics procedure uses a less complex
algorithm that utilizes cursors to simplify the statistics-gathering
process and make better use of reusable SQL areas in the shared pool.
The status.sql script calls the do_cals2.sql script to run this
procedure and generate a report. The procedure uses the Remote DBA_temp
Function get_avble_bytes. The function
get_avble_bytes accepts a tablespace name as an input and returns the
free bytes available for the specified tablespace in all of its
datafiles. The function uses the Remote DBA_free_space view.
Functions get_start and get_end. The
functions get_start and get_end generate the starting and ending byte
positions at which a specified columns column ID would either start or
end in a specified table. The functions are designed to be used in
generating control files for a table for use in SQLLOADER.
Function get_bytes. The function
get_bytes is used to return the number of bytes allocated to a
tablespace over all of its available datafiles. The function uses the
Remote DBA_free_space view to generate these values.
Procedure get_count. The procedure
get_count is used to get the row count for a specified table. It is
used in various table-checking routines to perform automated analysis
of the table.
Procedures update_column and update_tables.
The procedures update_column and update_tables are designed to be used
with a trigger to perform cascade updates to a set of tables. The
trigger must be of the form:
replace trigger cascade_update_<tabname>
after update of <column> on <table>
referencing new as upd old as prev
for each row
The table name is passed to the update_tables
procedure, as is the previous and updated values of the column the
trigger is for. The update_tables procedure then looks at the
Remote DBAutil_update_tables table to get the name of all tables and columns
that need to be updated. The tables are called and the update_column
procedure is executed to cascade the update to the dependent tables.
Procedure check_tables. The
check_tables procedure uses the statistics in the Remote DBA_TABLES view and
a row count generated by Remote DBA_UTILITIES.GET_COUNT to determine if a
table’s contents have changed by greater than plus or minus the
percent change entered; if so, the table is analyzed. If the table has
less than lim_rows rows, a complete analysis is done of all rows; if
more than lim_rows rows are present, a 30 percent sample is performed.
This procedure is designed for use in the DBMS_JOB package to be run
automatically on a periodic basis. Any table that is analyzed and any
errors encountered are logged in the Remote DBA_RUNNING_STATS table.
Procedures redo_pin and chk_pin. The
procedures redo_pin and chk_pin are used with the Remote DBAutil_kept_objects
table to verify that kept objects (using the DBMS_SHARED_POOL package)
are still valid; if not, the objects are unkept and the pool is
flushed, then the objects are repinned. Designed for use in a
development environment where objects may be made invalid on a
Procedure running_stats. The
running_stats procedure is a modified version of the just_statistics
package that is designed to be run on a periodic, automated basis
using the DBMS_JOB package. The running_stats procedure inserts
records into the Remote DBA_RUNNING_STATS table to allow trending of results.
Delta values are also calculated and stored. The procedure can be run
in interactive mode if a Boolean TRUE is passed as its sole argument
or as a background procedure, if the default value of FALSE is passed.
If TRUE is passed, Remote DBA_TEMP is deleted and then reloaded. If the
procedure is run with FALSE as its Boolean argument, the
Remote DBA_RUNNING_STATS table is updated.
Procedure flush_it. The flush_it
procedure is designed to be run on an automated, periodic basis using
the DBMS_JOB package. The flush_it procedure is provided a percent
full value that it uses to check the shared pool. If the shared pool
exceeds the specified percent full, the ALTER SYSTEM FLUSH SHARED_POOL
command is issued to flush nonpinned SQL areas from the pool. The
flush_it procedure is designed for use in systems where ad hoc SQL or
poorly designed applications result in high percentages of nonreusable
code; it should not be used on systems where a majority of code is
reused. The SQL_GARBAGE.SQL report, which uses the sql_garbage view
(created by CREA_TAB.SQL), should be used to determine your system’s
reusable code ratios before considering using the flush_it procedure.
If a system is virtually 100 percent ad hoc with no reusable code,
consider a reduced shared-pool size rather than automated flushing.
The flush_it procedure has been updated to include pinning of cursors
(based on a minimum number of reuses), packages, and procedures, as
well as sequences.
Procedure hitratio. The hitratio
procedure calculates cumulative hit ratio, period hitratio, number of
concurrent users, and usage (total I/Os) and sorts these values in the
HIT_RATIO table. The procedure is designed to be run hourly, and must
be modified if a greater or lesser periodicity is desired. The
procedure is designed to be run from the DBMS_JOB package.
Function gen_pword. The gen_pword
function returns a randomized six-character password. The gen_pword
function is designed for use in bulk loading of users or
application-driven user creation. There is some chance that the
function will not return a fully unique password for each execution,
but the chance is small enough to be ignored. The function uses a
14-place pi value, the v$timer view and the POWER function to derive a
Procedure auto_defrag. The auto_defrag
procedure is used to defragment honeycomb fragmentation in
tablespaces. If the value of PCTINCREASE is set to 0 for the default
storage in a tablespace, that tablespace is not automatically
coalesced by SMON. The auto_defrag procedure allows a Remote DBA to keep the
default value of PCTINCREASE set to 0 and still get automated
coalescence of adjacent areas of freespace. The auto_defrag procedure
uses the FREE_SPACE view. If used, I suggest that the auto_defrag
procedure be called from the Remote DBA_JOB package on an automated periodic
basis during off-hours.
Function hextointeger. The function
hextointeger takes in a hexadecimal value and converts it to an
integer. This function is useful when Oracle internally stores a value
as hex and you need it as a decimal (such as with thread numbers in
Function integertohex. The function
integertohex converts an integer value to a hexadecimal equivalent.
Procedure check_corrupt. The
check_corrupt procedure uses the DBMS_REPAIR package (new in Oracle8i)
to check a schema for data corruption.
Evaluating the Results of the Ault
The Wiley Web site includes a bundled set of
reports known collectively as the Status collection. Run on a daily
basis, the Status series of scripts give a view into the health of the
Oracle database. This section describes the major reports available in
the Status series and how to interpret them.
Evaluating Tuning Scripts Run from
times a novice or even an intermediate-level Remote DBA won’t know what to
monitor on an ongoing basis. In an attempt to make this monitoring of
databases more routine and standard, I have provided a set of scripts
that can be used as-is or be modified to suit an individual Remote DBA’s
needs. One of these scripts, status.sql, groups some of the monitoring
scripts into a set of daily reports.
Code Depot for Full Scripts
||This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.