Section 12.1. Repository Table Structure

12.1 Repository Table Structure

The toolkit repository contains two sets of tables. The first is a set of tables containing information we copy from the Oracle data dictionary on a regular basis. We don't copy the entire dictionary, by any means, but we do copy the most interesting and changeable data. The second set of tables is our own group of specialized tables containing information that allows us to track SQL and generate explain plans.

12.1.1 Tables from the Oracle Data Dictionary

Table 12-1 lists the repository tables that mirror the contents of certain Oracle data dictionary tables, and summarizes the types of database objects stored in those tables. The names of the tables are derived from the names of the corresponding data dictionary tables. For example, our PDBA_INDEXES table pulls data from the Oracle's DBA_INDEXES data dictionary view. In the later section, Section 12.4, we'll show how we use the data in these tables to analyze changes to database objects.

The repository itself is compatible with the data dictionary provided in Oracle Versions 8.0 and later.

Table 12-1. Main PDBA repository tables

Table	Contents
PDBA_INDEXES	Index information, statistics, storage information, and a number of other parameters.
PDBA_IND_COLUMNS	Index columns , statistics, storage information, and other related items.
PDBA_PARAMETERS	V$PARAMETER initialization parameters.
PDBA_PROFILES	DBA_PROFILES data.
PDBA_SNAP_DATES	Database dictionary image and the date it was taken. The primary key of this table is used as a foreign key in most of the other tables in the repository; in this way, it ties information into the proper databases.
PDBA_SYS_PRIVS	System privileges, as granted to users and roles.
PDBA_TAB_PRIVS	Object and stored procedure privileges.
PDBA_ROLE_PRIVS	Role grants from DBA_ROLE_PRIVS.
PDBA_ROLES	Role definitions from DBA_ROLES.
PDBA_SEQUENCES	DBA_SEQUENCES sequence definitions.
PDBA_TABLES	Tables, table statistics, table storage information, and related information.
PDBA_TAB_COLUMNS	Table columns, column statistics, and related information.
PDBA_TABLESPACES	DBA_TABLESPACES definitions.
PDBA_USERS	Usernames, passwords, default tablespaces, temporary tablespaces, creation dates, and other information from DBA_USERS.

12.1.2 Specialized Repository Tables

In addition to the tables described in the previous section that mirror the Oracle data dictionary tables, the repository contains another set of more specialized tables. The purpose of these tables is to track the SQL found in the V$SQLTEXT data dictionary table and generate execution plans from that SQL. In these tables we'll store Oracle SQL and EXPLAIN PLAN information so we can perform comparisons on it at a later date. We'll describe the scripts that perform these comparisons in the later section, Section 12.5.

Use of the data in these tables will help to answer a common complaint from users ” that their SQL, which worked perfectly last week, has slowed down significantly. This isn't necessarily a figment of our users' imaginations! It's often obvious to all, including the troubleshooting DBA, that something has changed in the database since last week. It just isn't always clear what has changed:

Was an index dropped?

Was a new index created?

Were statistics available on this table last week?

What did the execution plan look like last week?

All of these things can have a major impact on SQL execution and overall database performance.

Having read Stephen Hawking's Universe in a Nutshell (Bantam Press, 2001), we know that travelling backwards in time is impossible (unless you're Mr. Spock), so the last two questions in the list are usually quite difficult to answer. We've often thought, though, that if we could answer them, it would be very interesting. At last, such a thing is possible. All we need is the SXP (Sql eXplain Plan) repository tables, which track the objects listed in Table 12-2.

Table 12-2. SQL explain plan repository tables

Table	Contents
PDBA_SXP_DATES	Database dates for SQL statements.
PDBA_SXP_EXP	Execution plans for SQL statements.
PDBA_SXP_SQL	Actual SQL statements.