Listing 7.9 chain.sql shows all chained rows in the database tables.

SET ECHO OFF;
SET HEADING OFF;
SET FEEDBACK OFF;
SET VERIFY OFF;

DROP TABLE chained_rows;
@/opt/oracle/product/7.1.6/rdbms/admin/utlchain.sql

--define owner = &tableowner
SPOOL /opt/oracle/admin/adhoc/chainrun.sql;

SELECT 'analyze table ' || owner || '.' ||table_name ||
        ' list chained rows;'
FROM dba_tables
WHERE OWNER NOT IN ('SYS','SYSTEM');

SPOOL OFF;

--!more chainrun.sql
@chainrun.sql

SELECT 'There are ' || count(*) || ' chained rows in this database.'
FROM chained_rows;

SELECT DISTINCT owner_name, table_name, count(*)
FROM chained_rows
GROUP BY owner_name, table_name;

PROMPT
PROMPT You may now query the chained_rows table.
PROMPT This table contains one row for each row that has chained.
PROMPT
PROMPT suggested query:  select table_name, head_rowid, timestamp
PROMPT                   from chained_rows
PROMPT
PROMPT Refer to Oracle Administrators guide page 22-8 for directions
PROMPT regarding cleaning-up chained rows.

@chain

ANALYZE TABLE ORACLE.PUMPDATA LIST CHAINED ROWS;
ANALYZE TABLE ORACLE.SALESORG LIST CHAINED ROWS;
ANALYZE TABLE ORACLE.EMP LIST CHAINED ROWS;
ANALYZE TABLE ORACLE.LOB LIST CHAINED ROWS;
ANALYZE TABLE ORACLE.PRODUCT LIST CHAINED ROWS;
ANALYZE TABLE ORACLE.PAC1 LIST CHAINED ROWS;
ANALYZE TABLE ORACLE.PAC12 LIST CHAINED ROWS;
ANALYZE TABLE ORACLE.PAC23 LIST CHAINED ROWS;
ANALYZE TABLE ORACLE.MGC LIST CHAINED ROWS;
ANALYZE TABLE ORACLE.FILM_CODE LIST CHAINED ROWS;
ANALYZE TABLE ORACLE.CUST_CAT LIST CHAINED ROWS;
ANALYZE TABLE ORACLE.SALES_SUM LIST CHAINED ROWS;
ANALYZE TABLE ORACLE.DEPT LIST CHAINED ROWS;
ANALYZE TABLE ORACLE.BONUS LIST CHAINED ROWS;
ANALYZE TABLE ORACLE.SALGRADE LIST CHAINED ROWS;

ANALYZE TABLE ORACLE.DUMMY LIST CHAINED ROWS;
SQL>
SQL> SPOOL OFF;
SQL>
SQL> @chainrun.sql
SQL> SELECT 'analyze table ' || owner || '.' ||table_name ||
                ' list chained rows;'
SQL>   2  FROM dba_tables
SQL>   3  WHERE OWNER NOT IN ('SYS','SYSTEM');
SQL>

SQL> ANALYZE TABLE ORACLE.LOB LIST CHAINED ROWS;
SQL> ANALYZE TABLE ORACLE.PRODUCT LIST CHAINED ROWS;
SQL> ANALYZE TABLE ORACLE.PAC1 LIST CHAINED ROWS;
SQL> ANALYZE TABLE ORACLE.PAC12 LIST CHAINED ROWS;
SQL> ANALYZE TABLE ORACLE.PAC23 LIST CHAINED ROWS;
SQL> ANALYZE TABLE ORACLE.MGC LIST CHAINED ROWS;
SQL> ANALYZE TABLE ORACLE.FILM_CODE LIST CHAINED ROWS;
SQL> ANALYZE TABLE ORACLE.CUST_CAT LIST CHAINED ROWS;
SQL> ANALYZE TABLE ORACLE.SALES_SUM LIST CHAINED ROWS;
SQL> ANALYZE TABLE ORACLE.DEPT LIST CHAINED ROWS;
SQL> ANALYZE TABLE ORACLE.BONUS LIST CHAINED ROWS;
SQL> ANALYZE TABLE ORACLE.SALGRADE LIST CHAINED ROWS;
SQL> ANALYZE TABLE ORACLE.DUMMY LIST CHAINED ROWS;

Listing 7.10 shows the listing from this script.

Listing 7.10 The results from the chain.sql script.

There are 16784 chained rows in this database.

SQL>
SQL> select distinct owner_name, table_name, count(*)
  2    from chained_rows
  3  group by owner_name, table_name;

ORACLE                          SALESNET                            16784
ORACLE                          SALES                                 432
ORACLE                          CUST                               126744

Designing Expert Systems For Performance Measurement

When a centralized database is split into multiple distributed systems, the overall maintenance requirements for each database increases significantly. While the overall costs for the system hardware will decline as companies abandon their mainframes, human resources will increase as redundant personnel are added to perform system and database administration tasks at each node.

Many distributed database shops are responding to this challenge by creating systems that automate the common performance tracking for each remote database, alerting the DBA staff when predefined thresholds are exceeded. This type of automation can be extended with statistical trend analysis tools such as the SAS product to give forecasts of database maintenance and performance trends.

While these systems can become very sophisticated, this author recommends an evolutionary approach to the design of expert systems for performance and tuning. The work in 1981 by Robert Bonczeck into the theoretical structure of expert systems applies very well to performance and tuning applications. Bonczeck identifies a generalized framework for solving problems that consists of three components: states, operators, and goals. This approach assumes that the initial state is identified (namely a performance degradation), and a series of operators is applied to this state until the goal state is achieved (i.e., acceptable performance). This “state-space” approach to problem solving is especially useful for systems that collect and analyze performance and tuning information.

The idea is to create a knowledge system to store relevant statistical information, periodically feeding this knowledge system into a problem processing system as illustrated in Figure 7.8. The problem processing system contains the decision rules that are used to analyze trends in usage and alert the DBA if a parameter has been exceeded.

Figure 7.8  The architecture of expert systems.

This technique also relies on the knowledge that database performance and tuning do not require human intuition. While the decision rules applied to the problem are very complex, performance analysis is nothing more than the application of well-structured rules to problem data. Therefore, an automated system can be devised to replicate the DBA’s expertise in performance analysis.

The following steps can be used to create an expert system for performance and tuning:

1.  Identify the packaged utilities to be used, which may include:

•  SQL trace facility

•  Tablespace reports

•  Log analysis reports

•  Operating system specific reports

•  Performance monitor reports

2.  Schedule the reports to run on a periodic basis and direct the output to a file.

3.  Write a summary program to interpret the reports, and write summary statistics to a master file.

4.  Create a problem processing system to read the knowledge system, generating trend reports and DBA alerts.