Why 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 data accessibility.

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 operations.

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 mikerault@earthlink.net 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 support tables.

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 mikerault@earthlink.net 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 system:

Upper level SQL directory\rep_out\instance1_name

                                 \instance2_name
                    …
                                \instancex_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 (or developer):

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 possible).

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 application.

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 an application.

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 table.

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:

create or replace trigger cascade_update_<tabname>
  after update of <column> on <table>     
  referencing new as upd old as prev
    for each row
      begin
Remote DBA_utilities.update_tables('<table>',:prev.<column>,:upd.<column>);
      end;

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 periodic basis.

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 password.

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 NT).

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 Status.sql Reports

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 status.sql

Many 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.

 
See 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.