Notes.Add( 'Checkpoint Change# - Last SCN checkpointed' );
Notes.Add( 'Archive Change# - Last SCN archived' );
end;
1 : begin
Notes.Add( 'PRODUCT VERSION NOTES:' );
Notes.Add( '' );
Notes.Add( 'Version number of core library components in the Oracle Server' );
end;
2 : begin
Notes.Add( 'DATABASE OPTION NOTES:' );
Notes.Add( '' );
Notes.Add( 'Option - Name of the option' );
Notes.Add( 'Value - Value of the option' );
end;
3 : begin
Notes.Add( 'LICENSE INFORMATION NOTES:' );
Notes.Add( '' );
Notes.Add( 'Sessions Max - Maximum number of concurrent user sessions allowed for the instance.' );
Notes.Add( 'Sessions Warn - Warning limit for concurrent user sessions for the instance.' );
Notes.Add( 'Sessions Current - Current number of concurrent user sessions.' );
Notes.Add( 'Sessions Highwater - Highest number of concurrent user sessions since the instance started.' );
Notes.Add( 'Users Max - Maximum number of named users allowed for the database.' );
end;
4 : begin
Notes.Add('INIT.ORA PARAMETER NOTES:');
Notes.Add('');
Notes.Add('Parameter - Init.ora parameter name');
Notes.Add('Value - Current value');
Notes.Add('Is Default - Whether the parameter value is the default. FALSE indicates the value has been altered in the init.ora.');
Notes.Add('Session Modifiable - TRUE=the parameter can be changed with ALTER SESSION / FALSE=the parameter cannot be changed with ALTER SESSION');
Notes.Add('System Modifiable - IMMEDIATE=the parameter can be changed with ALTER SYSTEM / DEFERRED=the parameter cannot be changed until the next session / FALSE=the parameter cannot be changed with ALTER SYSTEM');
Notes.Add('Is Modified - Indicates how the parameter was modified. If an ALTER SESSION was performed, the value will be MODIFIED. If an ALTER SYSTEM (which will cause all the currently logged in sessions values ');
Notes.Add(' to be modified) was performed the value will be SYS_MODIFIED. ');
Notes.Add('Parameter - Name of the undocumented parameter');
Notes.Add('Value - Current value for the parameter');
Notes.Add('Desc - Brief description of parameter');
Notes.Add('');
Notes.Add('_ALLOW_RESETLOGS_CORRUPTION - This saves you when you have corrupted redo logs');
Notes.Add('_CORRUPTED_ROLLBACK_SEGMENTS - This saves you when you have corrupted RB segments');
Notes.Add('_SPIN_COUNT - This shows how often the processor will take a new request (reduce CPU timeouts)');
Notes.Add('_LOG_ENTRY_PREBUILD_THRESHOLD - Redo entries larger than this will be prebuilt');
Notes.Add('_LATCH_SPIN_COUNT - This shows how often a latch request will be taken (reduce latch timeouts)');
Notes.Add('_DB_BLOCK_WRITE_BATCH - Number of blocks to group in each DB Writer I/O');
Notes.Add('_CPU_COUNT - Number of CPUs that you have (causes bugs with Parallel Query option). Check with Oracle support for restrictions on some versions and O/S');
Notes.Add('_INIT_SQL_FILE - Where the sql.bsq file is located');
Notes.Add('_TRACE_FILES_PUBLIC - This allows users to see the TRACE output without major privileges');
Notes.Add('_FAST_FULL_SCAN_ENABLED - This allow indexed fast full scans if only indexes are needed');
Notes.Add('_CORRUPT_BLOCKS_ON_STUCK_RECOVERY - This sometimes gets a corrupted database up.');
Notes.Add('_ALWAYS_STAR_TRANSFORMATION - This is a fast method for Data Warehouse if you have a good designer.');
Notes.Add('_SMALL_TABLE_THRESHOLD - Small tables are pinned into memory if smaller than this');
Notes.Add('');
Notes.Add('Do NOT change any of these values unless instructed by ORACLE support');
end;
6 : begin
Notes.Add('ALL INIT.ORA PARAMETER NOTES:');
Notes.Add('');
Notes.Add('Parameter - Name of the undocumented parameter');
Notes.Add('Desc - Brief description of parameter');
Notes.Add('Value - Current value for the parameter');
Notes.Add('Default? - Is this the default value. FALSE indicates the value has been changed in the init.ora.');
Notes.Add('');
Notes.Add('Some of these default values parameters are derived from others. They have KSPPSTDF = FALSE because the parameters on which they are based are not defaulting. Note also that there is some non-trivial operating system dependency');
Notes.Add(' (read inconsistency) here, particularly with the cpu_count derived parameters.');
Notes.Add('Always double check your init.ora to determine if the value has actually been changed.');
Notes.Add('');
end;
7 : begin
Notes.Add('SGA NOTES:');
Notes.Add('');
Notes.Add('Name - SGA component group');
Notes.Add('Value - Memory size in bytes');
Notes.Add('');
Notes.Add('This reports shows the SGA memory structures of your system.');
Notes.Add('Database Buffers - The value of the init.ora parameters DB_BLOCK_BUFFERS multiplied by DB_BLOCK_SIZE');
Notes.Add('Redo Buffers - The value of the init.ora parameter LOG_BUFFER');
end;
8 : begin
Notes.Add('DATAFILE NOTES:');
Notes.Add('');
Notes.Add('File Name - Name of the datafile');
Notes.Add('Tablespace - Name of the tablespace');
Notes.Add('Datafile Size - Size of datafile (bytes)');
Notes.Add('Bytes Used - Amount of datafile used');
Notes.Add('Percent Used - Percent of datafile used');
Notes.Add('Bytes Free - Amount of datafile free');
end;
9 : begin
Notes.Add('DATABASE FILE NOTES:');
Notes.Add('');
Notes.Add('Filename - Name of the file(s)');
Notes.Add('Location - Location of the file(s)');
end;
10 : begin
Notes.Add('TABLESPACE INFORMATION NOTES:');
Notes.Add('');
Notes.Add('Tablespace Name - Name of the tablespace');
Notes.Add('Min Extents - Default minimum number of extents');
Notes.Add('Max Extents - Default maximum number of extents');
Notes.Add('Percent Increase - Default percent increase for extent size');
Notes.Add('Min Ext Size * - Minimum extent size for the tablespace');
Notes.Add('Status - Tablespace status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped)');
Notes.Add('Contents - Type of tablespace. This column will have ''TEMPORARY'' (v7.3+) for dedicated temporary tablespaces, and ''PERMANENT'' for tablespaces that can store both temporary sort segments and permanent objects.');
Notes.Add('Extent Mgmt * - Extent management: DICTIONARY or LOCAL');
Notes.Add('Allocation Type * - Type of extent allocation in effect for this tablespace');
Notes.Add('Plugged In * - YES, the tablespace is plugged in; NO, it is not plugged in. If a tablespace is transported to an instance, the tablespace has its PLUGGED_IN value set to YES.');
Notes.Add('');
Notes.Add('By setting PCTINCREASE > 0, the SMON background process will automatically coalesce neighboring free extents in the tablespace.');
Notes.Add('As of Oracle7.3, you can create a dedicated temporary tablespace. In this kind of tablespace, a single temporary segment is created; it supports multiple users concurrently. The temporary segment does not get ');
Notes.Add('dropped when the users no longer needs it; instead, it remains in the temporary tablespace awaiting another user request for temporary segment space. Keeping the temporary segment in place avoids the costs');
Notes.Add(' associated with the creation and dropping of temporary segments.');
end;
11 : begin
Notes.Add('TABLESPACE QUOTA NOTES:');
Notes.Add('');
Notes.Add('Tablespace Name - Tablespace name');
Notes.Add('Username - User with resource rights on the tablespace');
Notes.Add('Bytes - Number of bytes charged to the user');
Notes.Add('Max Bytes - User''s quota in bytes, or -1 if no limit.');
Notes.Add('Blocks - Number of Oracle blocks charged to the user');
Notes.Add('Max Blocks - User''s quota in Oracle blocks, or -1 if no limit');
end;
12 : begin
Notes.Add('TABLESPACE COALESCED EXTENT NOTES:');
Notes.Add('');
Notes.Add('Tablespace Name - Name of tablespace');
Notes.Add('Total Extents - Total number of free extents in tablespace');
Notes.Add('Extents Coalesced - Total number of coalesced free extents in tablespace');
Notes.Add('% Extents Coalesced - Percentage of coalesced free extents in tablespace');
Notes.Add('Total Bytes - Total number of free bytes in tablespace');
Notes.Add('Bytes Coalesced - Total number of coalesced free bytes in tablespace');
Notes.Add('Total Blocks - Total number of free oracle blocks in tablespace');
Notes.Add('Blocks Coalesced - Total number of coalesced free Oracle blocks in tablespace');
Notes.Add('% Blocks Coalesced - Percentage of coalesced free Oracle blocks in tablespace');
end;
13 : begin
Notes.Add('TABLESPACE USAGE NOTES:');
Notes.Add('');
Notes.Add('Tablespace Name - Name of the tablespace');
Notes.Add('Bytes Allocated - Total space allocated in bytes');
Notes.Add('Bytes Used - Used space in bytes');
Notes.Add('Percent Used - Percentage of tablespace that is being used');
Notes.Add('Bytes Free - Free space in bytes');
Notes.Add('Percent Free - Percentage of tablespace that is free');
end;
14 : begin
Notes.Add('USER DEFAULT TABLESPACE NOTES:');
Notes.Add('');
Notes.Add('Username - Name of the user');
Notes.Add('Created - User creation date');
Notes.Add('Profile - Name of resource profile assigned to the user');
Notes.Add('Default Tablespace - Default tablespace for data objects');
Notes.Add('Temporary Tablespace - Default tablespace for temporary objects');
end;
15 : begin
Notes.Add('OBJECTS IN SYSTEM TABLESPACE NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the object');
Notes.Add('Object Name - Name of object');
Notes.Add('Object Type - Type of object');
Notes.Add('Tablespace - Tablespace name');
Notes.Add('Size - Size (bytes) of object');
Notes.Add('');
Notes.Add('Any user (other than SYS, SYSTEM) should have their objects moved out of the SYSTEM tablespace');
end;
16 : begin
Notes.Add('FREE AND LARGEST EXTENT NOTES:');
Notes.Add('');
Notes.Add('Tablespace - Name of the tablespace');
Notes.Add('Total Free Space - Total amount (bytes) of freespace in the tablespace');
Notes.Add('Largest Free Extent - Largest free extent (bytes) in the tablespace');
end;
17 : begin
Notes.Add('AUTOEXTEND NOTES:');
Notes.Add('');
Notes.Add('File Name - Datafile name');
Notes.Add('Tablespace Name - Name of the tablespace');
Notes.Add('Bytes - Size of the datafile');
Notes.Add('Status - Status of the datafile');
Notes.Add('MaxBytes - Maximum file size in bytes');
Notes.Add('Increment By - Size (in blocks) of the next autoextend');
end;
18 : begin
Notes.Add('ROLLBACK SEGMENT INFORMATION NOTES:');
Notes.Add('');
Notes.Add('Segment Name - Name of the rollback segment.');
Notes.Add('Owner - Owner of the rollback segment.');
Notes.Add('Tablespace - Name of the tablespace containing the rollback segment.');
Notes.Add('Segment ID - ID number of the rollback segment.');
Notes.Add('File ID - ID number of the block containing the segment header.');
Notes.Add('Block ID - Starting block number of the extent.');
Notes.Add('Initial Extent - Initial extent size in bytes.');
Notes.Add('Next Extent - Secondary extent size in bytes.');
Notes.Add('Min Extents - Minimum number of extents.');
Notes.Add('Max Extents - Maximum number of extents.');
Notes.Add('PCT Increase - Percent increase for extent size.');
Notes.Add('Status - ONLINE if the segment is online, or PENDING OFFLINE if the segment is going offline but some active (distributed) transactions are using the rollback segment. When the transaction(s) complete, the segment goes OFFLINE.');
Notes.Add('Instance - Instance this rollback segment belongs to (Parallel Server), or NULL for a single-instance system .');
Notes.Add('');
Notes.Add('Rollback segments store system undo data allowing non-committed transactions to be rolled back, they can be likened to before image logs or journals in other database systems. Rollback segments store the before images of changed data.');
Notes.Add('In addition to the SYSTEM rollback segment created when the database is built for use strictly by the SYSTEM tablespace, there must be at least one additional rollback segment created. Usually the number of private rollback');
Notes.Add('segments is determined by determining how many concurrent users will access the database and deciding how many users should be assigned to each rollback segment (by specifying the MINEXTENTS value). For example if you have ');
Notes.Add('100 concurrent users and you want (on the average) 20 users per rollback segment then the MINEXTENTS would be set to 20 for each of 5 rollback segments. For private rollback segments the calculated ratio of the initialization');
Notes.Add(' parameters TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT rounded up to the nearest integer should be used to determine the number of rollback segments created.');
Notes.Add('You cannot take the SYSTEM rollback segment offline.');
Notes.Add('If the owner is SYS, they''re private. If the owner is PUBLIC they''re public.');
Notes.Add('You cannot alter a rollback segment from public to private or private to public. It must be dropped and re-created for this type of change.');
end;
19 : begin
Notes.Add('ROLLBACK SEGMENT CONTENTION NOTES:');
Notes.Add('');
Notes.Add('Segment Name - Name of the rollback segment.');
Notes.Add('Seg# - Rollback segment number.');
Notes.Add('Gets - Number of header gets.');
Notes.Add('Waits - Number of header waits.');
Notes.Add('Hit Ratio - Ratio of gets to waits. This should be >= 99%.');
Notes.Add('Active Transactions - Number of active transactions.');
Notes.Add('Writes - Number of bytes written to rollback segment.');
Notes.Add('');
Notes.Add('Hit Ratio should be >= 99% - if not, consider adding additional rollback segments.');
Notes.Add('Check the system undo header, system undo block, undo header, undo block statistics under "Wait Statistics" for additional information on rollback contention.');
Notes.Add('Note: This report only shows information for ROLLBACK segments that are ONLINE.');
end;
20 : begin
Notes.Add('ROLLBACK GROWTH NOTES:');
Notes.Add('');
Notes.Add('Segment Name - Name of rollback segment.');
Notes.Add('Seg# - Rollback segment number.');
Notes.Add('Size - Size in bytes of the rollback segment.');
Notes.Add('OptSize - Optimal size of rollback segment.');
Notes.Add('HWM - High Water Mark of rollback segment size.');
Notes.Add('Extends - Number of times rollback segment was extended to have a new extent.');
Notes.Add('Wraps - Number of times rollback segment wraps from one extent to another.');
Notes.Add('Shrinks - Number of times rollback segment shrank, eliminating one or more additional extents each time.');
Notes.Add('Average Shrink - Total size of freed extents divided by number of shrinks.');
Notes.Add('Average Active - Current average size of active extents, where "active" extents have uncommitted transaction data.');
Notes.Add('Status - ONLINE if the segment is online, or PENDING OFFLINE if the segment is going offline but some active (distributed)');
Notes.Add(' transactions are using the rollback segment. When the transaction(s) complete, the segment goes OFFLINE.');
Notes.Add('');
Notes.Add('The first time the rollback segment is extended beyond its optimal setting, when the transaction releases it, it stays at');
Notes.Add('the extended size. If further down the line, the rollback segment is extended yet again, beyond its already extended size -');
Notes.Add('when the transaction releases the rollback segment, only then will oracle shrink it back to its optimal size, as it is read from v$rollstat.optsize.');
Notes.Add('Note: This report only shows information for ROLLBACK segments that are ONLINE');
end;
21 : begin
Notes.Add('ROLLBACK USER NOTES:');
Notes.Add('');
Notes.Add('Segment Name - Name of the rollback segment.');
Notes.Add('Xacts - Number of active transactions.');
Notes.Add('SID - Session identifier.');
Notes.Add('Serial# - Session serial number. Used to uniquely identify a sessions'' objects.');
Notes.Add('Username - Oracle username.');
Notes.Add('OS User - Operating system client user name.');
Notes.Add('SQL Text - Text of the SQL statement requiring the rollback segment, or the PL/SQL anonymous code.');
Notes.Add('Statistic Name - Name of the statistic');
Notes.Add('Value - Current value');
Notes.Add('');
Notes.Add('The name of the consistent changes statistic is misleading.');
Notes.Add('It does not indicate the number of updates (or changes), but rather, the number of times a');
Notes.Add('consistent get had to retrieve and "old" version of a block because of updates that occurred');
Notes.Add('after the cursor had been opened. As of Oracle7.3, a more accurate statistic was added. Named data');
Notes.Add('blocks consistent reads - undo records applied; the new statistic gives the actual number of data');
Notes.Add('records applied.');
Notes.Add('The consistent gets statistic reflects the number of accesses made to the block buffer to retrieve');
Notes.Add('data in a consistent mode. Most accesses to the buffer are done with the consistent get mechanism,');
Notes.Add('which uses the SCN (System Change Number) to make sure the data being read has not changed since');
Notes.Add('the query was started.');
Notes.Add('The data blocks consistent reads - undo records applied statistic reflects the number of updates');
Notes.Add('(or changes) applied.');
end;
// Users
23 : begin
Notes.Add('USER OBJECT NOTES:');
Notes.Add('');
Notes.Add('Username - Owner of the object(s)');
Notes.Add('Tabs - Table(s)');
Notes.Add('Inds - Index(es)');
Notes.Add('Syns - Synonym(s)');
Notes.Add('Views - Views(s)');
Notes.Add('Seqs - Sequence(s)');
Notes.Add('Procs - Procedure(s)');
Notes.Add('Funcs - Function(s)');
Notes.Add('Pkgs - Packages(s)');
Notes.Add('Trigs - Trigger(s)');
Notes.Add('Deps - Dependencies');
Notes.Add('Total - Totals for each username');
end;
24 : begin
Notes.Add('USER SPACE ALLOCATED NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the object(s)');
Notes.Add('Type - Type of object');
Notes.Add('Size - Size in bytes (for all objects)');
end;
25 : begin
Notes.Add('INVALID OBJECT NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the object');
Notes.Add('Object Type - Type of object');
Notes.Add('Object Name - Name of the object');
Notes.Add('Status - Status of the object');
end;
26 : begin
Notes.Add('OBJECT MODIFICATION NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the object');
Notes.Add('Object Name - Name of the object');
Notes.Add('Object Type - Type of the object');
Notes.Add('Last Modified - Last modification date/time');
Notes.Add('Created - Object creation date/time');
Notes.Add('Status - Status of the object');
end;
27 : begin
Notes.Add('USER PRIVILEGES NOTES:');
Notes.Add('');
Notes.Add('Grantee - Grantee name, user or role receiving the grant');
Notes.Add('Granted Role - Granted role name');
Notes.Add('Admin - Grant was with the ADMIN option');
Notes.Add('Default - Role is designated as a DEFAULT ROLE for the user');
Notes.Add('Privilege - System privilege');
end;
28 : begin
Notes.Add('OBJECTS USERS ARE ACCESSING NOTES:');
Notes.Add('');
Notes.Add('Username - Oracle username');
Notes.Add('OS User - Operating system client user name');
Notes.Add('SID - Session identifier');
Notes.Add('Owner - Owner of the object');
Notes.Add('Object Name - Name of the object');
Notes.Add('Object Type - Type of object being accessed');
end;
29 : begin
Notes.Add('USER SESSION NOTES:');
Notes.Add('');
Notes.Add('Username - Oracle username');
Notes.Add('OS User - Operating system client username');
Notes.Add('Count - Number of sessions for this user');
end;
30 : begin
Notes.Add('USER HIT RATIO NOTES:');
Notes.Add('');
Notes.Add('Username - Name of the user');
Notes.Add('Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.');
Notes.Add('DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get');
Notes.Add('mechanism).');
Notes.Add('Physical Reads - The cumulative number of blocks read from disk.');
Notes.Add('');
Notes.Add('Logical reads are the sum of consistent gets and db block gets.');
Notes.Add('The db block gets statistic value is incremented when a block is read for update and when segment');
Notes.Add('header blocks are accessed.');
Notes.Add('Hit ratio should be > 90%');
end;
31 : begin
Notes.Add('DATATYPES NOTES:');
Notes.Add('');
Notes.Add('Datatype - Name of the datatype');
Notes.Add('Owner - Name of the owner');
Notes.Add('Total - Total count for this datatype');
end;
// tables
32 : begin
Notes.Add('TABLE/INDEX LOCATION NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the object');
Notes.Add('Tablespace Name - Location of the object');
Notes.Add('Tables Type - Count for tables');
Notes.Add('Indexes - Count for indexes');
end;
33 : begin
Notes.Add('TABLES WITH QUESTIONABLE INDEX(ES) NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the table');
Notes.Add('Table Name - Name of the table');
Notes.Add('Column - Name of the column in question');
Notes.Add('');
Notes.Add('The above query shows all tables that have more than one index with the same leading column.');
Notes.Add('These indexes can cause queries to use an inappropriate indexes; in other words, Oracle will use the');
Notes.Add('index that was created most recently if two indexes are of equal ranking. This can cause different');
Notes.Add('indexes to be used from one environment to the next (e.g., from DEV to QA to PROD).');
Notes.Add('The information does not automatically indicate that an index is incorrect; however, you may need');
Notes.Add('to justify the existence of each of the indexes above');
end;
34 : begin
Notes.Add('TABLES WITH MORE THAN 5 INDEXES NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the table');
Notes.Add('Table Name - Name of the table');
Notes.Add('Index Count - Number of indexes');
Notes.Add('');
Notes.Add('Over indexing can have an impact on Inserts, Updates, and Deletes');
Notes.Add('Large batch inserts can be slowed by indexes, you may wish to drop indexes before large');
Notes.Add('inserts are done. Usually, if the insert is around 15 - 20% of the current size.');
Notes.Add('Indexes do not give back space from deleted rows');
Notes.Add('Primary keys are unique and cannot be null');
Notes.Add('Unique keys may have no duplicates, but may be null');
Notes.Add('Index foreign keys to prevent locking problems');
end;
35 : begin
Notes.Add('TABLES WITHOUT INDEXES NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the table');
Notes.Add('Table Name - Name of the table');
end;
36 : begin
Notes.Add('NO PRIMARY KEY NOTES:');
Notes.Add('');
Notes.Add('Table Owner - Owner of the table');
Notes.Add('Table Name - Name of the table');
end;
37 : begin
Notes.Add('TABLES EXPERIENCING CHAINING NOTES:');
Notes.Add('');
Notes.Add('Owner Name - Owner of the table');
Notes.Add('Table Name - Name of the table');
Notes.Add('Chained Rows - Number of chained rows');
Notes.Add('Total Rows - Number of total rows');
Notes.Add('Percent Chained - Percentage of chained rows');
Notes.Add('');
Notes.Add('Chaining can occur when there is not enough room in the data blocks to store changes. A chained');
Notes.Add('record is one that exists in multiple blocks instead of a single block. Accessing multiple blocks');
Notes.Add('for the same record can be costly in terms of performance.');
Notes.Add('To avoid chaining, set PCTFREE (amount of space reserved in a block for updates) correctly.');
Notes.Add('This parameter is set when the table is created. The default value is set to 10 (10 percent free');
Notes.Add('for updates) but this needs to be much higher in a table where there is a large frequency of update');
Notes.Add('activity');
end;
38 : begin
Notes.Add('DISABLED CONSTRAINT NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the table');
Notes.Add('Table Name - Name of the table');
Notes.Add('Constraint Name - Name of the constraint');
Notes.Add('Constraint Type - Type of constraint');
Notes.Add('Status - Current status of the constraint');
end;
39 : begin
Notes.Add('FOREIGN KEY CONSTRAINTS NOTES:');
Notes.Add('');
Notes.Add('Table Owner - Owner of the table');
Notes.Add('Table Name - Name of the table');
Notes.Add('Constraint Name - Name of the constraint');
Notes.Add('Column Name - Name of the column');
Notes.Add('Referenced Table - Name of the referenced table');
Notes.Add('Reference Column - Name of the referenced column');
Notes.Add('Position - Position of the column');
end;
40 : begin
Notes.Add('FK CONSTRAINTS WITHOUT INDEX ON CHILD TABLE NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the table');
Notes.Add('Constraint Name - Name of the constraint');
Notes.Add('Column Name - Name of the column');
Notes.Add('Position - Position of the index');
Notes.Add('Problem - Nature of the problem');
Notes.Add('');
Notes.Add('It is highly recommended that an index be created if the Foreign Key column is used in joining,');
Notes.Add('or often used in a WHERE clause. Otherwise a table level lock will be placed on the parent table.');
end;
41 : begin
Notes.Add('INCONSISTENT COLUMN DATATYPE NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the table');
Notes.Add('Column - Name of the column');
Notes.Add('Table Name - Name of the table');
Notes.Add('Datatype - Datatype of the column');
end;
42 : begin
Notes.Add('OBJECTS THAT CANNOT EXTEND NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the object');
Notes.Add('Object Name - Name of the object');
Notes.Add('Object Type - Type of object');
Notes.Add('Tablespace - Name of the tablespace');
Notes.Add('Next Extent - Size of next extent (bytes)');
end;
43 : begin
Notes.Add('SEGMENTS WITH MORE THAN 50% OF MAXEXTENTS NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the object');
Notes.Add('Tablespace Name - Name of the tablespace');
Notes.Add('Segment Name - Name of the segment');
Notes.Add('Segment Type - Type of segment');
Notes.Add('Size - Size of the object (bytes)');
Notes.Add('Extents - Current number of extents');
Notes.Add('Max Extents - Maximum extents for the segment');
Notes.Add('Percentage - Percentage of extents in use');
Notes.Add('');
Notes.Add('As of v7.3.4, you can set MAXEXTENTS=UNLIMITED to avoid ORA-01631: max # extents (%s) reached in');
Notes.Add('table $s.%s.');
Notes.Add('To calculate the MAXEXTENTS value on versions < 7.3.4 use the following equation: DBBLOCKSIZE / 16 - 7');
Notes.Add('Here are the MAXEXTENTS for common blocksizes: 1K=57, 2K=121, 4K=249, 8K=505, and 16K=1017');
end;
44 : begin
Notes.Add('SEGMENT MAXEXTENT NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the segment');
Notes.Add('Segment Type - Type of segment');
Notes.Add('Max Extents - MaxExtents value');
Notes.Add('Count - Number of records with this MaxExtents value');
Notes.Add('');
Notes.Add('Your maxextents are dependent on your blocksize and version of Oracle you''re running:');
Notes.Add('1K DB_BLOCKSIZE = 57 extents');
Notes.Add('2K DB_BLOCKSIZE = 121 extents');
Notes.Add('4K DB_BLOCKSIZE = 249 extents');
Notes.Add('8K DB_BLOCKSIZE = 505 extents');
Notes.Add('16K DB_BLOCKSIZE = 1017 extents');
Notes.Add('');
Notes.Add('You can use (DBBLOCKSIZE / 16 -7) to determine the correct MAXEXTENTS for your blocksize');
Notes.Add('As of Oracle v7.3 you can specify MAXEXTENTS UNLIMITED (actually 2,147,483,645 extents)');
end;
45 : begin
Notes.Add('ANALYZED TABLE NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the table');
Notes.Add('Analyzed - Number of analyzed tables');
Notes.Add('Not Analyzed - Number of tables that have not be analyzed');
Notes.Add('Total - Total number of tables owned by user');
Notes.Add('');
Notes.Add('The ANALYZE statement allows you to validate and compute statistics for an index, table, or cluster.');
Notes.Add('These statistics are used by the cost-based optimizer when it calculates the most efficient plan for');
Notes.Add('retrieval. In addition to its role in statement optimization, ANALYZE also helps in validating object');
Notes.Add('structures and in managing space in your system. You can choose the following operations: COMPUTER,');
Notes.Add('ESTIMATE, and DELETE. Early version of Oracle7 produced unpredicatable results when the ESTIMATE');
Notes.Add('operation was used. It is best to compute your statistics.');
Notes.Add('A COMPUTE will cause a table-level lock to be placed on the table during the operation');
end;
46 : begin
Notes.Add('RECENTLY ANALYZED TABLE NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the table');
Notes.Add('Table Name - Name of the table');
Notes.Add('Last Analyzed - Last analyzed date/time');
end;
47 : begin
Notes.Add('CACHED TABLE NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the table');
Notes.Add('Table Name - Name of the table');
Notes.Add('Cache - Cached?');
Notes.Add('');
Notes.Add('Oracle 7.1+ provides a mechanism for caching table in the buffer cache. Caching tables will speed up data access and');
Notes.Add('improve performance by finding the data in memory and avoiding disk reads');
end;
// Partitions
48 : begin
Notes.Add('PARTITIONED TABLE NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the partitioned table');
Notes.Add('Table Name - Name of the partitioned table');
Notes.Add('Partitioning Type - Partitioning algorithm');
Notes.Add('Partition Count - Number of partitions in this table');
Notes.Add('Partitioning Key Count - Number of columns in the partitioning key');
Notes.Add('Tablespace - Default TABLESPACE, used for ADD partition');
Notes.Add('Pctfree - Default PCTFREE, used for ADD partition');
Notes.Add('Pctused - Default PCTUSED, used for ADD partition');
Notes.Add('Initrans - Default INITRANS, used for ADD partition');
Notes.Add('Maxtrans - Default MAXTRANS, used for ADD partition');
Notes.Add('Initial Extent - Default INITIAL, used for ADD partition, ''DEFAULT'' if attribute was not specified');
Notes.Add('Next Extent - Default NEXT, used for ADD partition, ''DEFAULT'' if attribute was not specified');
Notes.Add('Min Extents - Default MINEXTENTS, used for ADD partition, ''DEFAULT'' if attribute was not specified');
Notes.Add('Max Extents - Default MAXEXTENTS, used for ADD partition, ''DEFAULT'' if attribute was not specified');
Notes.Add('Pct Increase - Default PCTINCREASE, used for ADD partition, ''DEFAULT'' if attribute was not specified');
Notes.Add('Freelists - Default FREELISTS, used for ADD partition');
Notes.Add('Freelist Groups - Default FREELIST GROUPS, used for ADD partition');
Notes.Add('Logging - Default LOGGING attribute, used for ADD partition');
Notes.Add('Buffer Pool - Default buffer pool for the given object, used for ADD partition');
end;
49 : begin
Notes.Add('PARTITIONED INDEX NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the partitioned table');
Notes.Add('Index Name - Name of the partitioned index');
Notes.Add('Partitioning Type - Partitioning algorithm');
Notes.Add('Partition Count - Number of partitions in this table');
Notes.Add('Partitioning Key Count - Number of columns in the partitioning key');
Notes.Add('Locality - Whether this partitioned index is LOCAL or GLOBAL');
Notes.Add('Alignment - Whether this partitioned index is PREFIXED or NON-PREFIXED');
Notes.Add('Tablespace - Default TABLESPACE, used for ADD partition');
Notes.Add('Pctfree - Default PCTFREE, used for ADD partition');
Notes.Add('Initrans - Default INITRANS, used for ADD partition');
Notes.Add('Maxtrans - Default MAXTRANS, used for ADD partition');
Notes.Add('Initial Extent - Default INITIAL, used for ADD partition, ''DEFAULT'' if attribute was not specified');
Notes.Add('Next Extent - Default NEXT, used for ADD partition, `DEFAULT'' if attribute was not specified');
Notes.Add('Min Extents - Default MINEXTENTS, used for ADD partition, ''DEFAULT'' if attribute was not specified');
Notes.Add('Max Extents - Default MAXEXTENTS, used for ADD partition, ''DEFAULT'' if attribute was not specified');
Notes.Add('Pct Increase - Default PCTINCREASE, used for ADD partition, ''DEFAULT'' if attribute was not specified');
Notes.Add('Freelists - Default FREELISTS, used for ADD partition');
Notes.Add('Freelist Groups - Default FREELIST GROUPS, used for ADD partition');
Notes.Add('Logging - Default LOGGING attribute, used for ADD partition');
Notes.Add('Buffer Pool - Default buffer pool for the given object, used for ADD partition');
end;
50 : begin
Notes.Add('PARTITION KEY COLUMN NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the partitioned table or index');
Notes.Add('Name - Name of the partitioned table or index');
Notes.Add('Object Type - Object type');
Notes.Add('Column Name - Name of the column');
Notes.Add('Column Position - Position of the column within the partitioning key');
end;
51 : begin
Notes.Add('TABLE LEVEL PARTITIONING NOTES:');
Notes.Add('');
Notes.Add('Table Owner - Owner of the partitioned table');
Notes.Add('Table Name - Name of the partitioned table');
Notes.Add('Partition Name - Partition name');
Notes.Add('High Value Length - Length of the partition bound value expression');
Notes.Add('Partition Position - Position of the partition within the table');
Notes.Add('Tablespace - Name of the tablespace containing the partition');
Notes.Add('Pctfree - Minimum percentage of free space in a block');
Notes.Add('Pctused - Minimum percentage of used space in a block');
Notes.Add('Initrans - Initial number of transactions');
Notes.Add('Maxtrans - Maximum number of transactions');
Notes.Add('Initial Extent - Size of the initial extent in bytes');
Notes.Add('Next Extent - Size of secondary extents in bytes');
Notes.Add('Min Extents - Minimum number of extents allowed in the segments');
Notes.Add('Max Extents - Maximum number of extents allowed in the segments');
Notes.Add('Pct Increase - Percentage increase in extent size');
Notes.Add('Freelists - Number of process freelists allocated in this segment');
Notes.Add('Freelist Groups - Number of freelist groups allocated in this segment');
Notes.Add('Logging - Logging attribute of partition');
Notes.Add('Num Rows * - Number of rows in the partition');
Notes.Add('Blocks * - Number of used blocks in the partition');
Notes.Add('Empty Blocks * - Number of empty (never used) blocks in the partition');
Notes.Add('Avg Space * - Average available free space in the partition');
Notes.Add('Chain Count * - Number of chained rows in the partition');
Notes.Add('Avg Row Length * - Average row length, including row overhead');
Notes.Add('Sample Size * - Sample size used in analyzing this partition');
Notes.Add('Last Analyzed * - Date of the most recent time this parition was analyzed');
Notes.Add('Buffer Pool - The buffer pool for the partition');
Notes.Add('* - Populated when table has been analyzed');
end;
52 : begin
Notes.Add('INDEX LEVEL PARTITIONING NOTES:');
Notes.Add('');
Notes.Add('Index Owner - Owner of the partitioned table');
Notes.Add('Index Name - Name of the partitioned table');
Notes.Add('Partition Name - Partition name');
Notes.Add('High Value Length - Length of the partition bound value expression');
Notes.Add('Partition Position - Position of the partition within the table');
Notes.Add('Status - Indicates whether the partition is usable or not');
Notes.Add('Tablespace Name - Name of the tablespace containing the partition');
Notes.Add('Pctfree - Minimum percentage of free space in a block');
Notes.Add('Initrans - Initial number of transactions');
Notes.Add('Maxtrans - Maximum number of transactions');
Notes.Add('Initial Extent - Size of the initial extent in bytes');
Notes.Add('Next Extent - Size of secondary extents in bytes');
Notes.Add('Min Extents - Minimum number of extents allowed in the segments');
Notes.Add('Max Extents - Maximum number of extents allowed in the segments');
Notes.Add('Pct Increase - Percentage increase in extent size');
Notes.Add('Freelists - Number of process freelists allocated in this segment');
Notes.Add('Freelist Groups - Number of freelist groups allocated in this segment');
Notes.Add('Logging - Logging attribute of partition');
Notes.Add('Blevel * - B-Tree level');
Notes.Add('Leaf Blocks * - Number of leaf blocks in the index partition');
Notes.Add('Distinct Keys * - Number of distinct keys in the index partition');
Notes.Add('Avg Leaf Blocks Per Key * - Average number of leaf blocks per key');
Notes.Add('Avg Data Blocks Per Key * - Average number of data blocks per key');
Notes.Add('Clustering Factor * - Measurement of the amount of (dis)order of the table this index partition is for');
Notes.Add('Num Rows * - Number of rows in the partition');
Notes.Add('Sample Size * - Sample size used in analyzing this partition');
Notes.Add('Last Analyzed * - Date of the most recent time this parition was analyzed');
Notes.Add('Buffer Pool - The buffer pool for the partition');
Notes.Add('* - Populated when table has been analyzed');
end;
//objects
53 : begin
Notes.Add('DATABASE CLUSTER NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the table/cluster');
Notes.Add('Tablespace - Name of the tablespace containing the cluster');
Notes.Add('Cluster Name - Name of the cluster');
Notes.Add('Table Name - Clustered table name');
Notes.Add('Table Column - Key column in the table');
Notes.Add('Cluster Column - Key column in the cluster');
Notes.Add('');
Notes.Add('A cluster can be used when several tables store a row that is of the same data type and size in the');
Notes.Add('same location. This reduces storage requirements and in some cases can speed access to data. The major');
Notes.Add('drawback is that in operations involving updates, inserts and deletes there can be performance');
Notes.Add('degradation. The DBA should look at the expected mix of transaction types on the tables to be');
Notes.Add('clustered and only cluster those that are frequently joined and don''t have numerous updates, inserts');
Notes.Add('and deletes.');
Notes.Add('Clusters store shared data values in the same physical blocks (the cluster key values). For tables that');
Notes.Add(' are frequently joined this can speed access, for tables frequently accessed separately joining is not');
Notes.Add(' the answer. An exception is when a single table is clustered. A single table cluster forces the key');
Notes.Add(' values for that table into a single set of blocks thus access can be sped up for accesses of that');
Notes.Add(' table. Usually this single table clustering also uses a HASH structure to further improve access');
Notes.Add(' times.');
Notes.Add('Oracle also has the ability to specify a HASH cluster. A HASH cluster uses a HASH form of storage');
Notes.Add('and no index. Hash structures should only be used for static tables. Hashing is the process where a');
Notes.Add('value, either of a unique or non-unique row, is used to generate a hash value. This hash value is used');
Notes.Add('to place the row into the hashed table. To retrieve the row, the value is simply recalculated. Hashes');
Notes.Add('can only by used for equality operations.');
end;
54 : begin
Notes.Add('DATABASE JOB NOTES:');
Notes.Add('');
Notes.Add('Log User - USER who was logged in when the job was submitted.');
Notes.Add('Schema - Default schema used to parse the job. For example, if the SCHEMA_USER is SCOTT and you submit the procedure HIRE_EMP as a job, Oracle looks for SCOTT.HIRE_EMP.');
Notes.Add('Job# - Identifier of job. Neither import/export nor repeated executions change it.');
Notes.Add('Interval - A date function, evaluated at the start of execution, becomes next NEXT_DATE.');
Notes.Add('Next Execution - Date/time that this job will next be executed.');
Notes.Add('Broken - If Y, no attempt is made to run this job. See DBMS_JOBQ.BROKEN (JOB).');
Notes.Add('What - Body of the anonymous PL/SQL block that this job executes.');
Notes.Add('');
Notes.Add('The DBMS_JOB package is actually an API into an Oracle subsystem known as the job queue. The Oracle');
Notes.Add('job queue allows for the scheduling and execution of PL/SQL routines (jobs) at predefined times and/or');
Notes.Add(' repeated job execution at regular intervals. The DBMS_JOB package provides programs for submitting and');
Notes.Add(' executing jobs, changing job execution parameters, and removing or temporarily suspending job');
Notes.Add(' execution. This package is the only interface with the Oracle job queue.');
Notes.Add('DBMS_JOB is used to schedule many different types of tasks that can be performed in PL/SQL and that');
Notes.Add('require regular execution. The job queue is used extensively by Oracle replication facilities, and was');
Notes.Add('originally developed for the purpose of refreshing Oracle snapshots. DBMS_JOB is often used by DBAs to');
Notes.Add(' schedule regular maintenance activities on databases, typically during periods of low usage by end');
Notes.Add(' users. It can similarly be used by applications to schedule large batch operations during off hours.');
Notes.Add(' The job queue can also be used to start up service programs that listen on database pipes and respond');
Notes.Add(' to service requests by user sessions.');
end;
55 : begin
Notes.Add('DATABASE LINK NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the database link');
Notes.Add('DBLink - Name of the database link');
Notes.Add('Username - Name of user to log in as');
Notes.Add('Host - SQL*Net string for connect');
Notes.Add('Created - Creation time of the database link');
Notes.Add('');
Notes.Add('Database links allow a user to treat tables in remote databases as if they were located in their current instance.');
Notes.Add('Database links cannot be altered, they can only be created and dropped');
end;
56 : begin
Notes.Add('DATABASE PROCEDURE/PACKAGE NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the object');
Notes.Add('Name - Name of the object');
Notes.Add('Type - Type of object');
Notes.Add('');
Notes.Add('PL/SQL procedures are stored, named objects that can return zero, one, or many values to the calling');
Notes.Add('process. However, only one row of values can be returned from a single procedure call. Procedure');
Notes.Add('variables used for sending variables into the program and for receiving values from the program are');
Notes.Add('specified at the start of the program and designated as IN (input variables only that can''t have their');
Notes.Add('values altered by the program, so they''re treated as constants), OUT (output variables that are set by');
Notes.Add('the program during execution), and IN OUT (variables that are used for both input and output of values.)');
Notes.Add('When creating procedures, the next line after the end of the procedure definition must be a forward');
Notes.Add('slash (/) to tell Oracle to compile the procedure. The last statement of a PL/SQL procedure must end');
Notes.Add('with a semicolon.');
Notes.Add('Neither input nor output is required from a procedure. A procedure also doesn''t require a declarative');
Notes.Add('section. In fact, the only required part of a procedure is the PROCEDURE line and the executable block.');
Notes.Add('Procedures are created using the CREATE PROCEDURE command');
end;
57 : begin
Notes.Add('DATABASE SEQUENCE NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the sequence');
Notes.Add('Name - Name of the sequence');
Notes.Add('MinValue - Minimum value of the sequence');
Notes.Add('MaxValue - Maximum value of the sequence');
Notes.Add('Increment - Value by which sequence is incremented');
Notes.Add('Cycle - Does sequence wrap around on reaching limit?');
Notes.Add('Order - Are sequence numbers generated in order?');
Notes.Add('Cache Size - Number of sequence numbers to cache');
Notes.Add('Last Number - Last sequence number written to disk');
Notes.Add('');
Notes.Add('Sequences are special database objects that provide');
Notes.Add('numbers in sequence for input to a table. They are useful for providing generated unique key');
Notes.Add('values and input of number type columns such as purchase order, employee number, sample number,');
Notes.Add('sales order number, where the input must be unique and in numerical sequence');
end;
58 : begin
Notes.Add('DATABASE SNAPSHOT NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the snapshot');
Notes.Add('Name - The view used by users and applications for viewing the snapshot');
Notes.Add('Table Name - Table the snapshot is stored in, has an extra column for the master rowid');
Notes.Add('Master View - View of the master table, owned by the snapshot owner, used for refreshes');
Notes.Add('Master Owner - Owner of the master table');
Notes.Add('Master - Name of the master table of which this snapshot is a copy');
Notes.Add('Master Link - Database link name to the master site');
Notes.Add('Can Use Log - If NO, this snapshot is complex and will never use a log');
Notes.Add('Updatable - If NO, the snapshot is read only');
Notes.Add('Last Refresh - SYSDATE from the master site at the time of the last refresh');
Notes.Add('Error - The number of failed automatic refreshes since last successful refresh');
Notes.Add('Type - The type of refresh (complete, fast, force) for all automatic refreshes');
Notes.Add('Next Refresh - The date function used to compute next refresh dates');
Notes.Add('Refresh Group - GROUP All snapshots in a given refresh group get refreshed in the same transaction');
Notes.Add('');
Notes.Add('Oracle snapshots are an excellent method to replicate data from a small table in one database to');
Notes.Add('another. Typically you will create a snapshot log on the table to be replicated in the source database');
Notes.Add('and create a snapshot from the target database. If you desire, you can replicate from one user in an');
Notes.Add('instance to another user in the same instance.');
Notes.Add('Each release of Oracle7 made significant inroads into improving replication. Oracle8 has made still');
Notes.Add('further significant improvements. Our advice is that if you plan to replicate large amounts of data');
Notes.Add('across databases, if practical, you should be using Oracle8 Advanced Replication. Do NOT use snapshots');
Notes.Add('for replicating large volumes of data with Oracle7! Oracle8 has introduced dramatic performance');
Notes.Add('improvements in both snapshots and advanced replication. However, there are still many occasions where');
Notes.Add('an entire table requires to be refreshed if snapshots are utilized.');
Notes.Add('Much of the performance improvement in Oracle8 is due to the snapshot logs and advanced replication');
Notes.Add('being maintained by code in the kernel, as opposed to Oracle7''s triggers. The kernel code is less');
Notes.Add('error prone and more efficient. Another advance in Oracle8 has been the ability to perform parallel');
Notes.Add('propagation. Many nodes on the network can be replicated to simultaneously. Another bonus is that');
Notes.Add('Oracle8 supports LONG columns through it''s implementation of the LOB data type. One major advantage of');
Notes.Add('the Oracle8 snapshots is that a correlated subquery can be attached to a snapshot without a complete');
Notes.Add('refresh of the replicated table.');
Notes.Add('The main problem with snapshots is their inefficiency when they deal with large amounts of data, and');
Notes.Add('the large amount of administration.');
Notes.Add('A simple snapshot is one in which each row is based on a single row in a single remote table. A simple');
Notes.Add('snapshot''s defining query has no GROUP BY or ORDER BY clauses, or subqueries, joins or set operations.');
Notes.Add('If the defining query of a snapshot contains any of these clauses or operations, it is referred to as');
Notes.Add('a complex snapshot. An Oracle8 simple snapshot can contain a WHERE clause. An Oracle7 simple snapshot');
Notes.Add('cannot.');
Notes.Add('The most important characteristic of a simple snapshot is that a fast refresh can be used. A fast');
Notes.Add('refresh allows just rows that have been inserted, updated or modified since the last refresh to be');
Notes.Add('applied to the replicated table.');
Notes.Add('A complex snapshot requires a full refresh of the entire replicated table each time the snapshot is');
Notes.Add('applied. Re-building the entire replicated table is unacceptable if high availability is required on');
Notes.Add('the replicated database and the table is large');
end;
59 : begin
Notes.Add('DATABASE SYNONYM NOTES:');
Notes.Add('');
Notes.Add('Owner - Owner of the synonym');
Notes.Add('Name - Name of the synonym');
Notes.Add('Table Owner - Owner of the table');
Notes.Add('Table Name - Name of the table');
Notes.Add('DB Link - Name of the database link');
Notes.Add('');
Notes.Add('Synonyms are a database''s shorthand. Synonyms allow long complex combinations of schema, object name');
Notes.Add('and connection strings to be reduced to a simple alias. Synonyms remove the requirement to prefix a');
Notes.Add('table, view or sequence with a schema name, thus performing a simple form of data hiding by allowing');
Notes.Add('tables from one or more schemas to appear to be located in the users own schema.');
end;
60 : begin
Notes.Add('DATABASE TRIGGER NOTES:');
Notes.Add('');
Notes.Add('Table Owner - Owner of the table');
Notes.Add('Table Name - Name of the table');
Notes.Add('Trigger Name - Name of the trigger');
Notes.Add('Trigger Name - When the trigger fires (BEFORE EACH ROW, AFTER EACH ROW, BEFORE STATEMENT, AFTER STATEMENT)');
Notes.Add('Triggering Event - Statement that fires the trigger (INSERT, UPDATE, DELETE)');
Notes.Add('Status - Whether the trigger is enabled (ENABLED or DISABLED)');
Notes.Add('');
Notes.Add('Before Oracle 7.3 triggers where compiled at runtime. After 7.3, triggers are stored in the database as');
Notes.Add(' Pcode. This provides significant performance benefits over earlier versions since the overhead of');
Notes.Add(' re-parsing the trigger for each firing is eliminated. This allows larger and more complex triggers to');
Notes.Add(' be created without fear of performance degradation caused by re-parsing large sections of code. In');
Notes.Add(' Oracle8.0 and 8.1 triggers can be pinned into memory using the DBMS_SHARED_POOL.KEEP(''trigger_name'',');
Notes.Add(' ''T'') procedure call.');
Notes.Add('The Oracle8i DDL and database event triggers are also created using the CREATE TRIGGER command. DDL');
Notes.Add('events which can cause a trigger to fire are CREATE, ALTER or DROP on clusters, tables, views,');
Notes.Add('Pay close attention to highlighted items');
end;
66 : begin
Notes.Add('SYSTEM EVENT (ALL) NOTES:');
Notes.Add('');
Notes.Add('Event Name - Name of the event');
Notes.Add('Total Waits - Total number of waits for the event');
Notes.Add('Total Timeouts - Total number of timeouts for the event');
Notes.Add('Time Waited - The total amount of time waited for this event, in hundredths of a second');
Notes.Add('Average Wait - The average amount of time waited for this event, in hundredths of a second');
end;
67 : begin
Notes.Add('SGA STAT NOTES:');
Notes.Add('');
Notes.Add('Statistic Name - Name of the statistic');
Notes.Add('Bytes - Size');
Notes.Add('');
Notes.Add('The "free memory" value is an indicator of the free contiguous memory in the shared pool. If this');
Notes.Add('value is constantly low or is reduced in a short period of time after database startup, it is a good');
Notes.Add('indicator that your shared pool should be increased. In the case where free memory is low, a stored');
Notes.Add('package or procedure may result in an error, since there may not be enough contiguous memory to load');
Notes.Add('the object into the shared pool.');
end;
68 : begin
Notes.Add('WAIT STATISTIC NOTES:');
Notes.Add('');
Notes.Add('Class - Class of block subject to contention');
Notes.Add('Count - Number of waits by this OPERATION for this CLASS of block');
Notes.Add('Time -Sum of all wait times for all the waits by this OPERATION for this CLASS of block');
Notes.Add('');
Notes.Add('Data Blocks - Usually occurs when there are too many modified blocks in the buffer cache; reduce');
Notes.Add('contention by adding DBWR processes.');
Notes.Add('Free List - May occur if multiple data loading programs run simultaneously.');
Notes.Add('Segment Header - May occur when may full table scans execute simultaneously with data loading');
Notes.Add('processes; aggravated by the parallel options. Reschedule data loading jobs to reduce contention;');
Notes.Add('Sort Block - Rarely seen except when the Parallel Query option is used; reduce contention by reducing');
Notes.Add('the degree of parallelism or decreasing the SORT_AREA_SIZE init.ora parameter setting.');
Notes.Add('Undo Block - Very rarely occurs; may be caused by multiple users updating records in the same data');
Notes.Add('block at a very fast rate; contention can usually be resolved by increasing the PCTFREE of the tables');
Notes.Add('being modified.');
Notes.Add('Undo Header - May occur if there are not enough rollback segments to support the number of concurrent');
Notes.Add('transactions');
end;
69 : begin
Notes.Add('SORT NOTES:');
Notes.Add('');
Notes.Add('Sort Parameter - Name of the sort parameter');
Notes.Add('Value - Number of sorts');
Notes.Add('');
Notes.Add('sorts (memory) - The number of sorts small enough to be performed entirely in sort areas without');
Notes.Add('using temporary segments.');
Notes.Add('sorts (disk) - The number of sorts that were large enough to require the use of temporary segments');
Notes.Add('for sorting.');
Notes.Add('sorts (rows) - Number of sorted rows');
Notes.Add('');
Notes.Add('The memory area available for sorting is set via the SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE');
Notes.Add('init.ora parameters');
end;
70 : begin
Notes.Add('DATAFILE I/O NOTES:');
Notes.Add('');
Notes.Add('File Name - Datafile name');
Notes.Add('Physical Reads - Number of physical reads');
Notes.Add('Reads % - Percentage of physical reads');
Notes.Add('Physical Writes - Number of physical writes');
Notes.Add('Writes % - Percentage of physical writes');
Notes.Add('Total Block I/O''s - Number of I/O blocks');
Notes.Add('');
Notes.Add('When an Oracle instance is initially configured, the data and index configuration is determined based');
Notes.Add('on analysis of best guess statistics. Once an application becomes production, it is very important to');
Notes.Add('monitor the true disk activity. If the output reveals that the balance is not distributed properly,');
Notes.Add('then it is the responsibility of the DBA to reconfigure the system to ensure proper balancing. Any');
Notes.Add('time there is a significant imbalance; there will be overall system performance degradation.');
Notes.Add('Use this report to identify any "hot spots" and I/O contention');
end;
// Full Table Scans
71 : begin
Notes.Add('SYSTEM STATISTICS (TABLE) NOTES:');
Notes.Add('');
Notes.Add('Statistic Name - Name of the statistic');
Notes.Add('Bytes - Size');
Notes.Add('');
Notes.Add('This query provides information on the full table scan activity. If your application is OLTP only,');
Notes.Add('having long full table scans can be an indicator of having missing or incorrect indexes or untuned SQL.');
Notes.Add('Table fetch by rowid reflect the cumulative number of rows fetched from tables using a TABLE ACCESS');
Notes.Add('BY ROWID operation.');
Notes.Add('Table fetch continued row reflect the cumulative number of continued rows fetched. This value is');
Notes.Add('incremented when accessing a row that is longer than a block in length and when accessing "migrated"');
Notes.Add('rows. Migrated rows are rows that were relocated from their original location to a new location');
Notes.Add('because of an update that increased their size to the point where they could no longer be accommodated');
Notes.Add('inside their original block. Access to migrated rows will cause this statistic''s value to increment');
Notes.Add('only if the access is performed by ROWID. Full table scans of tables that contain migrated rows do not');
Notes.Add(' cause this counter to increment.');
Notes.Add('Table scan blocks gotten reflect the cumulative number of blocks read for full table scans.');
Notes.Add('Table scans rows gotten reflect the cumulative number of rows read for full table scans.');
Notes.Add('Table scans (cache partitions) is used with the Parallel Query Option. The number of RowID ranges');
Notes.Add('corresponds to the number of simultaneous query server processes that scan the table.');
Notes.Add('Table scans (long scans) indicate a full scan of a table that has > 5 database blocks.');
Notes.Add('Table scans (rowid ranges) is used with the Parallel Query Option. The number of RowID ranges');
Notes.Add('corresponds to the number of simultaneous query server processes that scan the table.');
Notes.Add('Table scans (short scans) indicate a full scan of a table that has <= 5 database blocks');
end;
72 : begin
Notes.Add('PROCESS TABLE SCAN NOTES:');
Notes.Add('');
Notes.Add('Username - Name of the user');
Notes.Add('SID - Session ID of user');
Notes.Add('Long Scans - Full scan of a table that has > 5 database blocks.');
Notes.Add('Short Scans - Full scan of a table that has <= 5 database blocks.');
Notes.Add('Row Retrieved - Cumulative number of rows read for full table scans.');
end;
73 : begin
Notes.Add('PROCESS TABLE SCAN (AVERAGE) NOTES:');
Notes.Add('');
Notes.Add('Username - Name of the user');
Notes.Add('SID - Session ID of user');
Notes.Add('Short Scans - Number of short scans (<= 5 blocks)');
Notes.Add('Long Scans - Number of long scans (> 5 blocks)');
Notes.Add('Rows Retrieved - Number of rows retrieved');
Notes.Add('Long Scans Length - Average long scan length (i.e. full table scan of > 5 blocks)');
end;
// Data Dict
74 : begin
Notes.Add('DATA DICTIONARY CACHE NOTES:');
Notes.Add('');
Notes.Add('Parameter - Name of the parameter that determines the number of entries in the data dictionary cache.');
Notes.Add('Gets - Total number of requests for information on the data object.');
Notes.Add('Getmisses - Number of data requests resulting in cache misses.');
Notes.Add('% Cache Misses - Miss Ratio');
Notes.Add('Count - Total number of entries in the cache');
Notes.Add('Usage - Number of cache entries that contain valid data.');
Notes.Add('');
Notes.Add('This report shows the DC cache statistics (a part of the shared pool).');
Notes.Add('Whenever the database parses a SQL statement, it scans the text for syntax and semantic correctness.');
Notes.Add(' The semantic check requires cross-referencing of the information in the SQL statement and the data');
Notes.Add(' dictionary, including the table names, columns-to-date relationships, column names, data types, and');
Notes.Add(' security access privileges. To resolve the relationships, Oracle uses the data dictionary cache in');
Notes.Add(' the SGA. When the data sought in not in the cache, Oracle executes SQL statements to retrieve the');
Notes.Add(' data dictionary information from the SYSTEM tablespace. These statements for data dictionary');
Notes.Add(' information represent one type of recursive SQL statement. To increase the size available to the');
Notes.Add(' dictionary cache, increase the size of the shared pool area (via the SHARED_POOL_SIZE init.ora');
Notes.Add(' parameter.)');
Notes.Add('Missing a get on the data dictionary or shared pool area of the SGA is more costly than missing a get');
Notes.Add(' on a data buffer or waiting for a redo buffer.');
Notes.Add('If these parameters look familiar - you are probably recalling them from Oracle v6 (when you tuned');
Notes.Add('these in the init.ora file)');
end;
75 : begin
Notes.Add('LATCH GET AND MISS NOTES:');
Notes.Add('');
Notes.Add('Latch Name - Name of the latch');
Notes.Add('Gets - Number of times obtained wait');
Notes.Add('Misses - Number of time obtained with wait but failed first try');
Notes.Add('Gets / Misses % - Ratio of misses to gets');
Notes.Add('Immediate Gets - Number of times obtained with no wait');
Notes.Add('Immediate Misses - Number of times failed to get with no wait');
end;
// Cursor & SQL
76 : begin
Notes.Add('SQL WITH MOST DISK READ NOTES:');
Notes.Add('');
Notes.Add('Username - Name of the user');
Notes.Add('Disk Reads - Total number of disk reads for this statement');
Notes.Add('Executions - Total number of times this statement has been executed');
Notes.Add('Reads/Execs - Number of reads per execution');
Notes.Add('SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code');
Notes.Add('');
Notes.Add('The goal of this statement is to highlight the SQL statements in your system that can potentially be');
Notes.Add('optimized. The disk_reads signify the volume of disk reads that are being performed on the system.');
Notes.Add('This combined with the executions (reads/executions) returns the SQL statements that have the most');
Notes.Add('disk hits per statement execution. Once identified, the top statements should be reviewed and');
Notes.Add('optimized to improve overall performance. Typically, the statement is not using an index or the');
Notes.Add('execution path is forcing the statement not to use the proper indexes. This script should be executed');
Notes.Add('regularly to determine if new statements are being introduced to your system that have not been');
Notes.Add('properly optimized.');
Notes.Add('Remember 80% of most systems that are improved in terms of performance is directly attributable to');
Notes.Add('poorly written SQL statements.');
end;
77 : begin
Notes.Add('SQL WITH MOST BUFFER SCAN NOTES:');
Notes.Add('');
Notes.Add('Username - Name of the user');
Notes.Add('Buffer Gets - Total number of buffer gets for this statement');
Notes.Add('Executions - Total number of times this statment has been executed');
Notes.Add('Gets/Execs - Number of buffer gets per execution');
Notes.Add('SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code');
Notes.Add('');
Notes.Add('The buffer_gets column provides information on SQL statements that may not possess the large disk hits,');
Notes.Add(' but possess a large number of memory hits (higher than normally desired). In this case, the SQL');
Notes.Add(' statement may be using an index, but using the wrong index. These types of SQL statements can involve');
Notes.Add(' a join operation that is forcing the path to utilize a different index than desired or using');
Notes.Add(' multiple indexes and forcing index merging.');
Notes.Add('Excessive BUFFER_GETS suggests that the query is causing heavy memory (logical) reads');
Notes.Add('Check for overindexes tables');
end;
78 : begin
Notes.Add('SQL WITH MOST LOAD NOTES:');
Notes.Add('');
Notes.Add('Loads - Number of times the cursor has been loaded after the body of the cursor has been aged out of');
Notes.Add('the cache while the text of the SQL statement remained in it, or after the cursor is invalidated');
Notes.Add('First Load Time - Time at which the cursor was first loaded into the SGA');
Notes.Add('Sorts - Number of sorts performed by the SQL statement');
Notes.Add('SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code');
end;
79 : begin
Notes.Add('OPEN CURSORS BY USER NOTES:');
Notes.Add('');
Notes.Add('Username - Name of the user');
Notes.Add('SID - Session ID of the user');
Notes.Add('SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code');
Notes.Add('');
Notes.Add('This report identified the current activity in your system and highlights if and when cursors are');
Notes.Add('not being closed properly. If a user is identified from this statement with a large number of open');
Notes.Add('cursors, it may signify that an application module or modules are not closing cursors when complete.');
Notes.Add('As a general rule, cursors should be closed when they are no longer needed to eliminate system');
Notes.Add('overhead (this is often times a common PL/SQL coding area overlooked).');
end;
80 : begin
Notes.Add('RUNNING CURSORS BY USER NOTES:');
Notes.Add('');
Notes.Add('Username - Name of the user');
Notes.Add('SID - Session ID of the user');
Notes.Add('SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code');
end;
81 : begin
Notes.Add('OPEN CURSORS WITH LOW HIT RATIO NOTES:');
Notes.Add('');
Notes.Add('Username - Name of the user');
Notes.Add('SID - Session ID of the user');
Notes.Add('SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code');
end;
82 : begin
Notes.Add('RUNNING CURSORS WITH LOW HIT RATIO NOTES:');
Notes.Add('');
Notes.Add('Username - Name of the user');
Notes.Add('SID - Session ID of the user');
Notes.Add('SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code');
end;
83 : begin
Notes.Add('OBJECTS BEING USED BY USERS WITH LOW HIT RATIO NOTES:');
Notes.Add('');
Notes.Add('Username - Name of the user');
Notes.Add('SID - Session ID of the user');
Notes.Add('Object Owner - Owner of the object');
Notes.Add('Object - Name of the object');
end;
84 : begin
Notes.Add('LOCK INFORMATION NOTES:');
Notes.Add('');
Notes.Add('OS User - Name of operating system user');
Notes.Add('OS PID - Operating system process id');
Notes.Add('Oracle User - Name of Oracle user');
Notes.Add('Oracle ID - Oracle session id');
Notes.Add('Lock Type - Type of lock');
Notes.Add('Lock Held - Current lock held');
Notes.Add('Lock Requested - Type of lock requested');
Notes.Add('Status - Status of object (Blocking, Not Blocking, Global)');
Notes.Add('Object Owner - Owner of the object');
Notes.Add('Object Name - Name of the object');
end;
85 : begin
Notes.Add('SQL ASSOCIATED WITH LOCK NOTES:');
Notes.Add('');
Notes.Add('Oracle User - Name of the oracle user');
Notes.Add('SID - Oracle session id');
Notes.Add('Serial# - Serial# of the process');
Notes.Add('Type - Resource type (RW - Row wait enqueue lock, TM - DML enqueue lock, TX - Transaction enqueue lock, UL - User supplied lock)');
Notes.Add('Held - Type of lock held');
Notes.Add('Requested - Type of lock requested');
Notes.Add('ID1 - Resource identifier #1');
Notes.Add('ID2 - Resource identifier #2');
Notes.Add('SQL - SQL statement');
end;
86 : begin
Notes.Add('SESSION TIME NOTES:');
Notes.Add('');
Notes.Add('SID - Session ID of the session');
Notes.Add('Serial# - Serial# of the session');
Notes.Add('Username - Oracle username');
Notes.Add('OS User - Name of the operating system user');
Notes.Add('Server - Server Type: DEDICATED, SHARED, PSEUDO, NONE, etc.');
Notes.Add('Status - Status of the session: ACTIVE (currently executing SQL), INACTIVE, KILLED (marked to be killed), CACHED (temporarily caches for use by Oracle*XA)');
Notes.Add('Type - Session type');
Notes.Add('Program - Operating system program name');
Notes.Add('Logon Time - Time session was started');
Notes.Add('Last Call - Last time session was active');
end;
87 : begin
Notes.Add('SESSION I/O BY USER NOTES:');
Notes.Add('');
Notes.Add('Username - Name of the Oracle process user');
Notes.Add('OS User - Name of the operating system user');
Notes.Add('PID - Process ID of the session');
Notes.Add('SID - Session ID of the session');
Notes.Add('Serial# - Serial# of the session');
Notes.Add('Physical Reads - Physical reads for the session');
Notes.Add('Block Gets - Block gets for the session');
Notes.Add('Consistent Gets - Consistent gets for the session');
Notes.Add('Block Changes - Block changes for the session');
Notes.Add('Consistent Changes - Consistent changes for the session');
end;
88 : begin
Notes.Add('CPU USAGE BY SESSION NOTES:');
Notes.Add('');
Notes.Add('Username - Name of the user');
Notes.Add('SID - Session id');
Notes.Add('CPU Usage - CPU centiseconds used by this session (divide by 100 to get real CPU seconds)');
end;
89 : begin
Notes.Add('RESOURCE USAGE BY USER NOTES:');
Notes.Add('');
Notes.Add('SID - Session ID');
Notes.Add('Username - Name of the user');
Notes.Add('Statistic - Name of the statistic');
Notes.Add('Value - Current value');
end;
90 : begin
Notes.Add('SESSION STAT NOTES:');
Notes.Add('');
Notes.Add('Username - Name of the user');
Notes.Add('SID - Session ID');
Notes.Add('Statistic - Name of the statistic');
Notes.Add('Usage - Usage according to Oracle');
end;
91 : begin
Notes.Add('CURSOR USAGE BY SESSION NOTES:');
Notes.Add('');
Notes.Add('Username - Name of the user');
Notes.Add('SID - Session ID of the user');
Notes.Add('Recursive Calls - Total number of recursive calls');
Notes.Add('Opened Cursors - Total number of opened cursors');
Notes.Add('Current Cursors - Number of cursor currently in use');
