oracle自带建表命令

----------------------------------------------------------------------- Oracle10g Server Release 10.2 Production ------------------------------------------------------------------------- Copyright (C) 1993, 2005, Oracle Corporation. All rights reserved. Author: Connie Dialeris Green Contributors: Cecilia Gervasio, Graham Wood, Russell Green, Patrick Tearle, Harald Eri, Stefan Pommerenk, Vladimir Barriere Please refer to the Oracle10g server README file in the rdbms doc directory, for copyright, disclosure, restrictions, warrant, trademark, disclaimer, and licensing information. The README file is README_RDBMS.HTM. Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065. ------------------------------------------------------------------------- Statistics Package (STATSPACK) README (spdoc.txt) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ TABLE OF CONTENTS ----------------- 0. Introduction and Terminology 1. Enterprise Manager (EM), Automatic Workload Repository (AWR) and Statspack 2. Statspack Configuration 2.1. Database Space Requirements 2.2. Installing the Tool 2.3. Errors during Installation 3. Gathering data - taking a snapshot 3.1. Automating Statspack Statistics Gathering 3.2. Using dbms_job 4. Running the Performance reports 4.1. Running the instance report 4.2. Running the instance report when there are multiple instances 4.3. Configuring the Instance Report 4.4. Running the SQL report 4.5. Running the SQL report when there are multiple instances 4.6. Configuring the SQL report 4.7. Gathering optimizer statistics on the PERFSTAT schema 5. Configuring the amount of data captured 5.1. Snapshot Level 5.2. Snapshot SQL thresholds 5.3. Changing the default values for Snapshot Level and SQL Thresholds 5.4. Snapshot Levels - details 5.5. Specifying a Session Id 5.6. Input Parameters for the SNAP and MODIFY_STATSPACK_PARAMETERS procedures 6. Time Units used for Performance Statistics 7. Event Timings 8. Managing and Sharing performance data 8.1. Baselining performance data 8.1.1. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE procedure and function which accept Begin and End Snap Ids 8.1.2. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE procedure and function which accept Begin and End Dates 8.2. Purging/removing unnecessary data 8.2.1. Input Parameters for the PURGE procedure and function which accept Begin Snap Id and End Snap Id 8.2.2. Input Parameters for the PURGE procedure and function which accept Begin Date and End Date 8.2.3. Input Parameters for the PURGE procedure and function which accept a single Purge Before Date 8.2.4. Input Parameters for the PURGE procedure and function which accept the Number of Days of data to keep 8.2.5. Using sppurge.sql 8.3. Removing all data 8.4. Sharing data via export 9. New and Changed Features 9.1. Changes between 10.1 and 10.2 9.2. Changes between 9.2 and 10.1 9.3. Changes between 9.0 and 9.2 9.4. Changes between 8.1.7 and 9.0 9.5. Changes between 8.1.6 and 8.1.7 10. Compatibility and Upgrading from previous releases 10.1. Compatibility Matrix 10.1.1. Using Statspack shipped with 10.1 10.1.2. Using Statspack shipped with 10.0 10.1.3. Using Statspack shipped with 9.2 10.1.4. Using Statspack shipped with 9.0 10.1.5. Using Statspack shipped with 8.1.7 on 9i releases 10.2. Upgrading an existing Statspack schema to a newer release 10.2.1. Upgrading the Statspack schema from 10.1 to 10.2 10.2.2. Upgrading the Statspack schema from 9.2 to 10.1 10.2.3. Upgrading the Statspack schema from 9.0 to 9.2 10.2.4. Upgrading the Statspack schema from 8.1.7 to 9.0 10.2.5. Upgrading the Statspack schema from 8.1.6 to 8.1.7 10.2.6. Upgrading the Statspack schema from 8.1.6 to 9.2 10.2.7. Upgrading the Statspack schema from 8.1.6 to 9.0 10.2.8. Upgrading the Statspack schema from 8.1.7 to 9.2 11. Oracle Real Application Clusters specific considerations 11.1. Changing Instance Numbers 11.2. Cluster Specific Reports 11.3. Cluster Specific Data 12. Conflicts and differences compared to UTLBSTAT/UTLESTAT 12.1. Running BSTAT/ESTAT in conjunction to Statspack 12.2. Differences between Statspack and BSTAT/ESTAT 13. Removing the package 14. Supplied Scripts Overview 15. Limitations and Modifications 15.1. Limitations 15.2. Modifications 0. Introduction and Terminology -------------------------------- To effectively perform reactive tuning, it is vital to have an established baseline for later comparison when the system is running poorly. Without a baseline data point, it becomes very difficult to identify what a new problem is attributable to: Has the volume of transactions on the system increased? Has the transaction profile or application changed? Has the number of users increased? Statspack fundamentally differs from the well known UTLBSTAT/UTLESTAT tuning scripts by collecting more information, and also by storing the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using the report provided, which includes an 'instance health and load' summary page, high resource SQL statements, as well as the traditional wait events and initialization parameters. Statspack improves on the existing UTLBSTAT/UTLESTAT performance scripts in the following ways: - Statspack collects more data, including high resource SQL (and the optimizer execution plans for those statements) - Statspack pre-calculates many ratios useful when performance tuning, such as cache hit ratios, per transaction and per second statistics (many of these ratios must be calculated manually when using BSTAT/ESTAT) - Permanent tables owned by PERFSTAT store performance statistics; instead of creating/dropping tables each time, data is inserted into the pre-existing tables. This makes historical data comparisons easier - Statspack separates the data collection from the report generation. Data is collected when a 'snapshot' is taken; viewing the data collected is in the hands of the performance engineer when he/she runs the performance report - Data collection is easy to automate using either dbms_job or an OS utility NOTE: The term 'snapshot' is used to denote a set of statistics gathered at a single time, identified by a unique Id which includes the snapshot number (or snap_id). This term should not be confused with Oracle's Snapshot Replication technology. How does Statspack work? Statspack is a set of SQL, PL/SQL and SQL*Plus scripts which allow the collection, automation, storage and viewing of performance data. A user is automatically created by the installation script - this user, PERFSTAT, owns all objects needed by this package. This user is granted limited query-only privileges on the V$views required for performance tuning. Statspack users will become familiar with the concept of a 'snapshot'. 'snapshot' is the term used to identify a single collection of performance data. Each snapshot taken is identified by a 'snapshot id' which is a unique number generated at the time the snapshot is taken; each time a new collection is taken, a new snap_id is generated. The snap_id, along with the database identifier (dbid) and instance number (instance_number) comprise the unique key for a snapshot (using this unique co