Oracle脚本script

Script:诊断SYSAUX表空间使用情况

2012/06/01 by admin 1 Comment

Script:以下脚本可以用于诊断SYSAUX表空间使用情况

./opatch lsinventory -detail

@?/rdbms/admin/awrinfo

select dbms_stats.get_stats_history_retention from dual;

select dbms_stats.get_stats_history_availability from dual;

select min(SAVTIME), max(SAVTIME) from WRI$_OPTSTAT_TAB_HISTORY;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_ind_history;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_histhead_history;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_histgrm_history;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_aux_history;

select count(*) from sys.wri$_optstat_tab_history;

select count(*) from sys.wri$_optstat_ind_history;

select count(*) from sys.wri$_optstat_histhead_history;

select count(*) from sys.wri$_optstat_histgrm_history;

select count(*) from sys.wri$_optstat_aux_history;

select count(*) from sys.wri$_optstat_opr;

示例输出：

~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~

Report generated at
08:57:12 on Jun 01, 2012 ( Friday ) in Timezone -04:00

select count(*) from sys.wri$_optstat_aux_history;

select count(*) from sys.wri$_optstat_opr;

Warning: Non Default AWR Setting!
--------------------------------------------------------------------------------
Snapshot interval is 60 minutes and Retention is 8 days

       DB_ID DB_NAME   HOST_PLATFORM                             INST STARTUP_TIME      LAST_ASH_SID PAR
------------ --------- ---------------------------------------- ----- ----------------- ------------ ---
* 195600696  PROD      maclean1.oracle.com - Linux x86 64-bit       1 07:42:19 (06/01)         65130 YES
  195600696  PROD      maclean2.oracle.com - Linux x86 64-bit       2 07:41:20 (06/01)         65226 YES

########################################################
(I) AWR Snapshots Information
########################################################

*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size                        193.6 MB ( 1% of 32,768.0 MB MAX with AUTOEXTEND ON )
|
| Schema  SYS          occupies            101.1 MB (  52.2% )
| Schema  SYSMAN       occupies             74.5 MB (  38.5% )
| Schema  SYSTEM       occupies             13.7 MB (   7.1% )
| Schema  WMSYS        occupies              3.5 MB (   1.8% )
| Schema  DBSNMP       occupies              0.8 MB (   0.4% )
|
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name        Schema Name               Space Usage
| -------------------- -------------------- ----------------
| EM                   SYSMAN                        74.5 MB
| SM/AWR               SYS                           56.2 MB
| LOGMNR               SYSTEM                        12.3 MB
| SM/ADVISOR           SYS                            8.9 MB
| SM/OPTSTAT           SYS                            7.7 MB
| SM/OTHER             SYS                            6.9 MB
| WM                   WMSYS                          3.5 MB
| SQL_MANAGEMENT_BASE  SYS                            1.7 MB
| PL/SCOPE             SYS                            1.6 MB
| AO                   SYS                            1.5 MB
| XSOQHIST             SYS                            1.5 MB
| LOGSTDBY             SYSTEM                         1.4 MB
| STREAMS              SYS                            1.0 MB
| EM_MONITORING_USER   DBSNMP                         0.8 MB
| JOB_SCHEDULER        SYS                            0.5 MB
| SMON_SCN_TIME        SYS                            0.5 MB
| AUTO_TASK            SYS                            0.3 MB
| AUDIT_TABLES         SYS                            0.0 MB
| EXPRESSION_FILTER    EXFSYS                         0.0 MB
| ORDIM                ORDSYS                         0.0 MB
| ORDIM/ORDDATA        ORDDATA                        0.0 MB
| ORDIM/ORDPLUGINS     ORDPLUGINS                     0.0 MB
| ORDIM/SI_INFORMTN_SC SI_INFORMTN_SCHEMA             0.0 MB
| SDO                  MDSYS                          0.0 MB
| STATSPACK            PERFSTAT                       0.0 MB
| TEXT                 CTXSYS                         0.0 MB
| TSM                  TSMSYS                         0.0 MB
| ULTRASEARCH          WKSYS                          0.0 MB
| ULTRASEARCH_DEMO_USE WK_TEST                        0.0 MB
| XDB                  XDB                            0.0 MB
| XSAMD                OLAPSYS                        0.0 MB
|
| Others (Unaccounted space)                         12.9 MB
|

******************************************
(1c) SYSAUX usage - Unregistered Schemas
******************************************

| This section displays schemas that are not registered
| in V$SYSAUX_OCCUPANTS
|
|
| Total space                                0.0 MB
|

*************************************************************
(1d) SYSAUX usage - Unaccounted space in registered schemas
*************************************************************
|
| This section displays unaccounted space in the registered
| schemas of V$SYSAUX_OCCUPANTS.
|
| Unaccounted space in SYS/SYSTEM           12.9 MB
|
| Total space                               12.9 MB
|
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 60 mins snapshot INTERVAL:
|    AWR size/day                          674.3 MB (28,768 K/snap * 24 snaps/day)
|    AWR size/wk                         4,719.8 MB (size_per_day * 7) per instance
|    AWR size/wk                         9,439.5 MB (size_per_day * 7) per database
|
| Estimates based on 1 snaps in past 24 hours:
|    AWR size/day                          533.8 MB (28,768 K/snap and 1 snaps in past 1.3 hours)
|    AWR size/wk                         3,736.5 MB (size_per_day * 7) per instance
|    AWR size/wk                         7,472.9 MB (size_per_day * 7) per database
|

**********************************
(3a) Space usage by AWR components (per database)
**********************************

COMPONENT        MB  % AWR  KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%
--------- --------- ------ ------------ ---------- ----------- ----------------
FIXED          35.8   63.7       18,336      340.2     2,381.5    56% : 44%
EVENTS          5.8   10.3        2,976       55.2       386.5    43% : 57%
SQL             4.0    7.1        2,048       38.0       266.0    72% : 28%
SPACE           3.9    7.0        2,016       37.4       261.8    65% : 35%
SQLPLAN         2.3    4.1        1,184       22.0       153.8    86% : 14%
RAC             1.3    2.2          640       11.9        83.1    65% : 35%
SQLTEXT         0.6    1.0          288        5.3        37.4    89% : 11%
ASH             0.6    1.0          288        5.3        37.4    67% : 33%
SQLBIND         0.3    0.6          160        3.0        20.8    60% : 40%

**********************************
(3b) Space usage within AWR Components (> 500K)
**********************************

COMPONENT        MB SEGMENT_NAME - % SPACE_USED                                           SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
FIXED           3.0 WRH$_SYSMETRIC_HISTORY_INDEX                                  -  33%  INDEX
FIXED           3.0 WRH$_SYSMETRIC_HISTORY                                        -   6%  TABLE
FIXED           2.0 WRH$_LATCH.WRH$_LATCH_195600696_0                             -  56%  TABLE PARTITION
FIXED           2.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_195600696_0                       -  53%  INDEX PARTITION
FIXED           0.9 WRH$_LATCH_PK.WRH$_LATCH_195600696_0                          -  78%  INDEX PARTITION
FIXED           0.8 WRH$_SYSSTAT.WRH$_SYSSTA_195600696_0                          -  85%  TABLE PARTITION
FIXED           0.7 WRH$_PARAMETER_PK.WRH$_PARAME_195600696_0                     -  80%  INDEX PARTITION
FIXED           0.7 WRH$_PARAMETER.WRH$_PARAME_195600696_0                        -  85%  TABLE PARTITION
FIXED           0.6 WRH$_SYSMETRIC_SUMMARY                                        -   8%  TABLE
FIXED           0.6 WRH$_WAITCLASSMETRIC_HIST_IND                                 -  29%  INDEX
FIXED           0.6 WRH$_WAITCLASSMETRIC_HISTORY                                  -  13%  TABLE
EVENTS          2.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__195600696_0               -  59%  INDEX PARTITION
EVENTS          0.9 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__195600696_0                  -  85%  TABLE PARTITION
EVENTS          0.5 WRH$_SYSTEM_EVENT.WRH$_SYSTEM_195600696_0                     -  63%  TABLE PARTITION
SQL             2.0 WRH$_SQLSTAT.WRH$_SQLSTA_195600696_0                          -  47%  TABLE PARTITION
SPACE           0.6 WRH$_SEG_STAT.WRH$_SEG_ST_195600696_0                         -  80%  TABLE PARTITION
SQLPLAN         2.0 WRH$_SQL_PLAN                                                 -  65%  TABLE
SQLTEXT         0.5 WRH$_SQLTEXT                                                  -  74%  TABLE

**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************

COMPONENT        MB SEGMENT_NAME                                                          SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR         4.0 SYSMAN.MGMT_MESSAGES                                                  TABLE
NON_AWR         4.0 SYSTEM.SYS_LOB0000001147C00009$$                                      LOBSEGMENT
NON_AWR         2.0 SYSMAN.MGMT_METRICS                                                   TABLE
NON_AWR         2.0 SYSMAN.MGMT_MESSAGES_PK                                               INDEX
NON_AWR         2.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST                                     INDEX
NON_AWR         1.0 SYS.SYS$SERVICE_METRICS_TAB                                           TABLE
NON_AWR         1.0 SYS.SYS_LOB0000006339C00038$$                                         LOBSEGMENT
NON_AWR         0.9 SYSMAN.MGMT_METRICS_PK                                                INDEX
NON_AWR         0.9 SYS.SYS_LOB0000005097C00005$$                                         LOBSEGMENT
NON_AWR         0.9 SYSMAN.MGMT_METRICS_IDX_03                                            INDEX
NON_AWR         0.9 SYSMAN.MGMT_METRICS_IDX_01                                            INDEX
NON_AWR         0.8 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY                                     TABLE
NON_AWR         0.8 SYSMAN.SYS_LOB0000015321C00004$$                                      LOBSEGMENT
NON_AWR         0.6 SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS                                     TABLE
NON_AWR         0.6 SYSMAN.MGMT_METRICS_RAW_PK                                            INDEX
NON_AWR         0.6 SYSMAN.MGMT_POLICIES                                                  TABLE
NON_AWR         0.6 SYS.I_WRI$_OPTSTAT_HH_ST                                              INDEX
NON_AWR         0.5 SYSMAN.MGMT_JOB_STEP_PARAMS                                           TABLE
NON_AWR         0.5 SYSMAN.MGMT_POLICY_ASSOC_CFG                                          TABLE
NON_AWR         0.5 SYSMAN.PK_MGMT_JOB_STEP_PARAMS                                        INDEX
NON_AWR         0.5 SYS.SYS_LOB0000006331C00004$$                                         LOBSEGMENT

**********************************
(5a) AWR snapshots - last 50
**********************************

Total snapshots in DB 195600696 Instance 2 = 1
Total snapshots in DB 195600696 Instance 1 = 1

      DBID    SNAP_ID  INST FLUSH_ELAPSED        ENDTM             STARTUP_TIME      STATUS ERRCNT
---------- ---------- ----- -------------------- ----------------- ----------------- ------ ------
 195600696         20     1 +00000 00:00:13.4    07:52:14 (06/01)  07:42:19 (06/01)       0      0
 195600696         20     2 +00000 00:00:11.8    07:52:15 (06/01)  07:41:20 (06/01)       0      0

**********************************
(5b) AWR snapshots with errors or invalid
**********************************

no rows selected

**********************************
(5c) AWR snapshots -- OLDEST Non-Baselined snapshots
**********************************

      DBID  INST    SNAP_ID ENDTM             STATUS ERROR_COUNT
---------- ----- ---------- ----------------- ------ -----------
 195600696     1         20 07:52:14 (06/01)       0           0

**********************************
(6) AWR Control Settings - interval, retention
**********************************

       DBID  LSNAPID LSPLITID LSNAPTIME      LPURGETIME      FLAG INTERVAL          RETENTION         VRSN
----------- -------- -------- -------------- -------------- ----- ----------------- ----------------- ----
  195600696       20        0 06/01 07:52:28 06/01 08:11:13     2 +00000 01:00:00.0 +00008 00:00:00.0    5

**********************************
(7a) AWR Contents - row counts for each snapshots
**********************************

   SNAP_ID  INST        ASH        SQL      SQBND      FILES      SEGST     SYSEVT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
        20     1          3         99        257          5         55        155
        20     2         12        105        300          5         58        156

**********************************
(7b) AWR Contents - average row counts per snapshot
**********************************

SNAP_COUNT  INST        ASH    SQLSTAT    SQLBIND      FILES    SEGSTAT   SYSEVENT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
         1     2         12        105        300          5         58        156
         1     1          3         99        257          5         55        155

**********************************
(7c) AWR total item counts - names, text, plans
**********************************

   SQLTEXT    SQLPLAN   SQLBMETA     SEGOBJ   DATAFILE   TEMPFILE
---------- ---------- ---------- ---------- ---------- ----------
       407       4506       1267        297          5          1

########################################################
(II) Advisor Framework Info
########################################################

**********************************
(1) Advisor Tasks - Last 50
**********************************

OWNER/ADVISOR  TASK_ID/NAME                     CREATED          EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK       07:52:03 (05/20)                       AUTO  INITIAL
SYS/ADDM       24/ADDM:195600696_3              11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       12/ADDM:195600696_1_3            11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       23/ADDM:195600696_2_3            11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       13/ADDM:195600696_1_4            12:00:23 (05/20)          1          1 AUTO  COMPLETED
SYS/ADDM       25/ADDM:195600696_2_4            12:00:24 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       26/ADDM:195600696_4              12:00:24 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       14/ADDM:195600696_1_5            13:00:29 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       28/ADDM:195600696_5              13:00:29 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       27/ADDM:195600696_2_5            13:00:29 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       29/ADDM:195600696_2_6            14:00:17 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       15/ADDM:195600696_1_6            14:00:17 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       16/ADDM:195600696_6              14:00:17 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       17/ADDM:195600696_1_7            15:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       30/ADDM:195600696_2_7            15:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       18/ADDM:195600696_7              15:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       20/ADDM:195600696_8              16:00:34 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       19/ADDM:195600696_1_8            16:00:34 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       31/ADDM:195600696_2_8            16:00:34 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       32/ADDM:195600696_2_9            17:00:40 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       42/ADDM:195600696_9              17:00:40 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       41/ADDM:195600696_1_9            17:00:40 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       33/ADDM:195600696_2_10           18:00:51 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       44/ADDM:195600696_10             18:01:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       43/ADDM:195600696_1_10           18:01:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       45/ADDM:195600696_1_11           19:00:01 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       34/ADDM:195600696_2_11           19:00:01 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       35/ADDM:195600696_11             19:00:01 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       36/ADDM:195600696_2_12           20:00:02 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       46/ADDM:195600696_1_12           20:00:02 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       47/ADDM:195600696_12             20:00:02 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       37/ADDM:195600696_2_13           21:00:09 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       48/ADDM:195600696_1_13           21:00:09 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       49/ADDM:195600696_13             21:00:09 (05/20)          9          9 AUTO  COMPLETED
SYS/ADDM       50/ADDM:195600696_1_14           22:00:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       38/ADDM:195600696_2_14           22:00:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       51/ADDM:195600696_14             22:00:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       39/ADDM:195600696_2_15           23:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       52/ADDM:195600696_1_15           23:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       53/ADDM:195600696_15             23:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       54/ADDM:195600696_1_16           00:00:25 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       40/ADDM:195600696_2_16           00:00:25 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       55/ADDM:195600696_16             00:00:25 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       57/ADDM:195600696_17             01:00:28 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       56/ADDM:195600696_1_17           01:00:28 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       61/ADDM:195600696_2_17           01:00:28 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       62/ADDM:195600696_2_18           02:00:32 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       59/ADDM:195600696_18             02:00:32 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       58/ADDM:195600696_1_18           02:00:32 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       71/ADDM:195600696_19             03:00:41 (05/21)          1          1 AUTO  COMPLETED
SYS/ADDM       60/ADDM:195600696_1_19           03:00:41 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       63/ADDM:195600696_2_19           03:00:41 (05/21)          0          0 AUTO  COMPLETED

**********************************
(2) Advisor Task - Oldest 5
**********************************

OWNER/ADVISOR  TASK_ID/NAME                     CREATED          EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
SYS/ADDM       11/ADDM:195600696_1_2            10:00:13 (05/20)          1          1 AUTO  COMPLETED
SYS/ADDM       21/ADDM:195600696_2_2            10:00:13 (05/20)          1          1 AUTO  COMPLETED
SYS/ADDM       22/ADDM:195600696_2              10:00:14 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       23/ADDM:195600696_2_3            11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       12/ADDM:195600696_1_3            11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       24/ADDM:195600696_3              11:00:19 (05/20)          0          0 AUTO  COMPLETED

**********************************
(3) Advisor Tasks With Errors - Last 50
**********************************

OWNER/ADVISOR  TASK_ID/NAME                     CREATED          EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
TASK_DESC
--------------------------------------------------------------------------------------------------------------
ERROR_MSG
--------------------------------------------------------------------------------------------------------------
SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK       07:52:03 (05/20)                       AUTO  INITIAL
Description: Automatic SQL Tuning Task
Error Msg  :

########################################################
(III) ASH Usage Info
########################################################

**********************************
(1a) ASH histogram (past 3 days)
**********************************

**********************************
(1b) ASH histogram (past 1 day)
**********************************

**********************************
(2a) ASH details (past 3 days)
**********************************

**********************************
(2b) ASH details (past 1 day)
**********************************

**********************************
(2c) ASH sessions (Fg Vs Bg) (past 1 day across all instances in RAC)
**********************************

Foreground %
Background %
MMNL %

End of Report
Report written to awrinfo.txt
SQL>
GET_STATS_HISTORY_RETENTION
---------------------------
                         31

SQL> SQL>
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
20-MAY-12 07.52.20.633129000 AM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.52.27.091525 AM -04:00
20-MAY-12 06.01.15.033444 PM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.52.28.548542 AM -04:00
20-MAY-12 06.01.15.071814 PM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.52.27.103629 AM -04:00
20-MAY-12 06.01.15.048213 PM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.53.50.386756 AM -04:00
20-MAY-12 07.57.17.388624 AM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.57.47.200604 AM -04:00
20-MAY-12 07.57.47.205012 AM -04:00

SQL> SQL>
  COUNT(*)
----------
      2191

SQL> SQL>
  COUNT(*)
----------
      2631

SQL> SQL>
  COUNT(*)
----------
     21962

SQL> SQL>
  COUNT(*)
----------
      5206

SQL> SQL>
  COUNT(*)
----------
        18

SQL> SQL>
  COUNT(*)
----------
       126

若发现statistics统计信息占用了SYSAUX上的大量空间，则可以考虑使用dbms_stats.purge_stats过程实施清理

Modify retention period: DBMS_STATS.ALTER_STATS_HISTORY_RETENTION
Purge old statistics: DBMS_STATS.PURGE_STATS This procedure purges old versions of statistics saved in the dictionary. To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.

1. Stats Retention is set to 31 days. By Default it is 7 days. I suggest you can consider reducing the retention days to 10.

2. The number of rows exits in the SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY is 42 Million Rows , where the data exists from “09-JUN-11 01.52.06.895132 PM -05:00 ” to “22-AUG-11 02.53.34.754747 PM -05:00”

3. For other tables ie: SYS. WRI$_OPTSTAT_TAB_HISTORY, SYS.WRI$_OPTSTAT_HISTGRM_HISTORY, the data exists for a month, this is just because the retention set to 31.

ACTION PLAN:
=========

A) Purge the Snapshot . Retain data for 10 days and then purge all the other data. (The number of days data to be kept depends on your Business needs)

SQL> SPOOL CHECK1.OUT

1) Try to force the execution of the purge operations :

SQL> alter session set “_swrf_test_action” = 72;

2) Purging snapshots :

SQL> exec dbms_stats.purge_stats(sysdate-&days);

using &days = n, n-1, n-2, …, n-x

3) Then again execute the below set of SQL’s and upload the spool output file

SQL> SELECT MIN(SAVTIME),MAX(SAVTIME) FROM WRI$_OPTSTAT_TAB_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_TAB_HISTORY;

SQL> SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

SQL> SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

SQL> SPOOL OFF

B) Change the No. of Retention days from 31 to 10. For performing the same, use the below command : (The new retention time is specified in minutes.)

SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>14400);

NOTE : The parameter value is in minutes so 10 daysx 24 hours x 60 minutes = 14400 minutes

C) Check the value of STATISTICS_LEVEL

If the above value is Set to ALL, then consider changing to TYPICAL.

The reason for requesting to change it to TYPICAL , is because statistics_level=ALL will gather lot of additional information in AWR repository which would consume more space.

Most of the cases, if the statistics_level is set to TYPICAL then the growth would be stopped.

Once the above actions performed, please keep us posted on the status of the same

Filed Under: Oracle, Oracle脚本script Tagged With: Oracle脚本script, statistics, sysaux

Script:查找表或索引增长的历史信息

2012/03/22 by admin 7 Comments

有同学在Oracle ALL STARS群中提问如何通过AWR来查找一段时间内，数据库段对象(堆表、索引)等的空间增长信息。

在Oracle 10g开始awr自动负载仓库引入了dba_hist_seg_stat视图，该视图记录了快照时间内segment-level段级的历史统计信息：DBA_HIST_SEG_STAT displays historical information about segment-level statistics. This view captures the top segments based on a set of criteria and captures information from V$SEGSTAT. The total value is the value of the statistics since instance startup. The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.

我们可以通过以下SQL脚本来列出相关段对象在快照时间内的使用空间的历史变化信息：

column owner format a16
column object_name format a36
column start_day format a11
column block_increase format 9999999999

select   obj.owner, obj.object_name,
         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD') start_day,
         sum(a.db_block_changes_delta) block_increase
from     dba_hist_seg_stat a,
         dba_hist_snapshot sn,
         dba_objects obj
where    sn.snap_id = a.snap_id
and      obj.object_id = a.obj#
and      obj.owner not in ('SYS','SYSTEM')
and      end_interval_time between to_timestamp('01-JAN-2000','DD-MON-RRRR')
         and to_timestamp('02-FEB-2013','DD-MON-RRRR')
group by obj.owner, obj.object_name,
         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD')
order by obj.owner, obj.object_name
/

使用示例：

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn maclean/maclean
Connected.
SQL>
SQL>
SQL>
SQL> create table check_size tablespace users as select  * from dba_objects where rownum=0;

Table created.

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> insert into check_size select * from dba_objects;

75536 rows created.

SQL> insert into check_size select * from check_size;

75536 rows created.

SQL> /

151072 rows created.

SQL> commit;

Commit complete.

SQL> insert into check_size select * from check_size;

302144 rows created.

SQL> insert into check_size select * from check_size;

604288 rows created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> @seg_hist

OWNER            OBJECT_NAME                          START_DAY   BLOCK_INCREASE
---------------- ------------------------------------ ----------- --------------
DBSNMP           BSLN_STATISTICS                      2012-MAR-18            224
DBSNMP           BSLN_STATISTICS_PK1                  2012-MAR-18            192
MACLEAN          CHECK_SIZE                           2012-MAR-22          96176
SH               CUSTOMERS                            2012-MAR-17              0

SQL> select data_object_id from dba_objects where object_name='CHECK_SIZE';

DATA_OBJECT_ID
--------------
         78062

SQL> select seg.snap_id,
  2         seg.ts#,
  3         seg.space_used_total,
       seg.space_allocated_total,
       seg.SPACE_ALLOCATED_DELTA
  from dba_hist_seg_stat seg
 where seg.DATAOBJ#=78062
   /  4    5    6    7    8  

   SNAP_ID        TS# SPACE_USED_TOTAL SPACE_ALLOCATED_TOTAL SPACE_ALLOCATED_DELTA
---------- ---------- ---------------- --------------------- ---------------------
       354          4         30909079              36700160              36700160
       355          4        123645655             149946368             113246208

SPACE_USED_DELTA  	Delta value for space used
SPACE_ALLOCATED_DELTA   Delta value for space allocated

Filed Under: Oracle, Oracle脚本script Tagged With: AWR, Oracle脚本script, SEGMENT SIZE HISTORY, space

Script:找出ASM中的Spfile参数文件

2012/01/30 by admin 3 Comments

以下脚本可以用于找出ASM存储中的Spfile参数文件，因为使用asmcmd去查找很不方便，而spfile丢失又是很头大的事情，所以有一个脚本代劳可以省不少功夫呢！

--- listspfiles.sql
--- Purpose: Sample script to list spfiles kept in ASM instance
--- Usage: This should be run against an ASM instance,
--- not a database instance.
---
--- cut here --%<----%<----%<----%<----%<----%<--

--list all spfiles

set lines 120
col full_path for a110
SELECT full_path, dir, sys
FROM
(SELECT
CONCAT('+'||gname,SYS_CONNECT_BY_PATH(aname,'/')) full_path,
dir, sys FROM
(SELECT g.name gname,
a.parent_index pindex, a.name aname,
a.reference_index rindex, a.ALIAS_DIRECTORY dir,
a.SYSTEM_CREATED sys
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
ORDER BY dir desc, full_path asc)
WHERE UPPER(full_path) LIKE '%SPFILE%'
/

Sample output:

FULL_PATH                                                                                                      D S
-------------------------------------------------------------------------------------------------------------- - -
+DATA/Aspfile.ora                                                                                              N N
+DATA/VPROD/PARAMETERFILE/spfile.273.766620265                                                                 N Y
+DATA/VPROD/PARAMETERFILE/spfile.365.773976489                                                                 N Y
+DATA/VPROD/spfileVPROD.ora                                                                                    N N

Filed Under: Oracle, Oracle脚本script Tagged With: Oracle脚本script, spfile

Script:收集Exadata诊断信息

2012/01/15 by admin Leave a Comment

以下汇总的command 可以用于收集Exadata数据库一体机的诊断信息：

实例相关参数v$parameter
Exadata相关的统计信息 v$sysstat
与Exadata相关的一些动态性能视图v$cell,v$cell_state,v$cell_thread_history,v$cell_request_totals
  - /etc/oracle/cell/network-config/cellinit.ora
  - /etc/oracle/cell/network-config/cellip.ora
  - cellcli -e list alerthistory detail
  - cellcli -e list cell detail
  - cellcli -e list celldisk detail
  - cellcli -e list griddisk detail
  - cellcli -e list iormplan detail
  - cellcli -e list metricdefinition detail
  - cellcli -e list metriccurrent detail
  - cellcli -e list metrichistory detail
  - cellcli -e list physicaldisk detail
  - /opt/oracle/cell/cellsrv/deploy/config/cellinit.ora
  - /opt/oracle/cell/log/diag/asm/cell/*/trace/alert.log
  - /opt/oracle/cell/log/diag/asm/cell/*/trace/ms-odl.trc
  - ls -alt /opt/oracle/cell/log/diag/asm/cell/*/trace/

Filed Under: Exadata, Oracle Tagged With: Oracle脚本script

Script:收集自动SGA内存管理ASMM诊断信息

2012/01/13 by admin Leave a Comment

以下脚本可以用于收集自动SGA(sga_target>0)内存管理ASMM下的实例诊断信息：

 
spool auto_sga_diag.log 

set line 190 pagesize 1400

 SELECT a.SGA_MEM + b.PGA_MEM "TOTAL_MEMORY"
    FROM (SELECT SUM(current_size) / 1024 / 1024 "SGA_MEM"
            FROM v$sga_dynamic_components,
                 (SELECT SUM(pga_alloc_mem) / 1024 / 1024 "PGA_MEM"
                    FROM v$process) a
           WHERE component IN ('shared pool',
                               'large pool',
                               'java pool',
                               'streams pool',
                               'DEFAULT buffer cache')) a,
         (SELECT SUM(pga_alloc_mem) / 1024 / 1024 "PGA_MEM" FROM v$process) b;

select component,
       current_size / 1024 / 1024 "CURRENT_SIZE",
       min_size / 1024 / 1024 "MIN_SIZE",
       user_specified_size / 1024 / 1024 "USER_SPECIFIED_SIZE",
       last_oper_type "TYPE"
  from v$sga_dynamic_components;

select component, granule_size / 1024 / 1024 "GRANULE_SIZE(Mb)"
  from v$sga_dynamic_components;

col component for a25
col status format a10 head "Status"
col initial_size for 999,999,999,999 head "Initial"
col parameter for a25 heading "Parameter"
col final_size for 999,999,999,999 head "Final"
col changed head "Changed At"
col low format 999,999,999,999 head "Lowest"
col high format 999,999,999,999 head "Highest"
col lowMB format 999,999 head "MBytes"
col highMB format 999,999 head "MBytes"

SELECT component,
       parameter,
       initial_size,
       final_size,
       status,
       to_char(end_time, 'mm/dd/yyyy hh24:mi:ss') changed
  FROM v$sga_resize_ops
 ORDER BY component;

SELECT component,
       min(final_size) low,
       (min(final_size / 1024 / 1024)) lowMB,
       max(final_size) high,
       (max(final_size / 1024 / 1024)) highMB
  FROM v$sga_resize_ops
 GROUP BY component
 ORDER BY component;

SELECT name, bytes FROM v$sgainfo;

select * from v$sgastat order by bytes asc;

select name, trunc(bytes / 1024 / 1024, 2) "size(MB)"
  from v$sgastat
 where pool is null
union
select pool, trunc(sum(bytes) / 1024 / 1024, 2) "size(MB)"
  from v$sgastat
 where pool is not null
 group by pool;

 select * from V$SGA_CURRENT_RESIZE_OPS;

 select * from v$sga_target_advice;

show parameter size
show parameter statistics        

spool off;

Filed Under: Oracle, Oracle脚本script Tagged With: ASMM, Oracle脚本script, sga_target

Script:Collect vip resource Diagnostic Information

2012/01/10 by admin Leave a Comment

以下脚本可以用于收集 Oracle RAC中vip 资源或其他CRS resource的诊断信息：

action plan:

./runcluvfy.sh stage -post crsinst -n all  -verbose

./runcluvfy.sh stage -pre crsinst -n all  -verbose

or


cluvfy stage -post crsinst -n all -verbose

cluvfy stage -pre  crsinst -n all -verbose

1. Please upload the following logs of all two nodes:
$CRS_HOME/log/<nodename>/*.log
$CRS_HOME/log/<nodename>/crsd/*.log
$CRS_HOME/log/<nodename>/cssd/*.log
$CRS_HOME/log/<nodename>/racg/*.log
$CRS_HOME/log/<nodename>/client/*.log
$CRS_HOME/log/<nodename>/evmd/*.log

/etc/oracle/oprocd/*.log.* or /var/opt/oracle/oprocd/*.log.* (If have)

$crs_stat –t
$crsctl check crs
$crsctl check boot

2. 

Please consult your sysadmin and make sure that the gateway is pingable all the time 

1- test the gw on every node
consult your sysadmin to create a crontab unix shell script to ping the
gateway of your public interface every 2 seconds for example and the result is to be
spooled in /tmp/test_gw_<nodename>.log

ping your gateway  and upload the ping log 

2- increase the tracing level of the vip resource  
  as root user
  # cd $ORA_CRS_HOME/bin
  # crsctl debug log res <resname:level>
  # crsctl debug log res <vip resourfce name>:5

3- restart the clusterware

3- execute this test on both nodes at the same time
   $ script /tmp/testvip_<nodename>.log
   $ cd $ORA_CRS_HOME/bin
   $ hostname
   $ date
   $ cat /etc/hosts
   $ ifconfig -a
   $ oifcfg getif  
   $ netstat -rn

   $ oifcfg iflist
   $ srvctl config nodeapps -n <nodename> -a -g -s -l               (repeate it for all nodes)

   $ crs_stat –t
   $ exit

4- reset the tracing level of the vip resource  
  as root user
  # cd $ORA_CRS_HOME/bin
  # crsctl debug log res <resname:level>
  # crsctl debug log res <vip resourfce name>:1

Up on the next occurence, please upload the following information from all nodes

  a-  /tmp/test_gw_<nodename>.log
  b- /tmp/testvip_<nodename>.log
  c- the crsd log
  d. The resource racg
     $ORA_CRS_HOME/log/<nodename>/racg/vip*

  e. the racgvip script from
     $ORA_CRS_HOME/bin/racgvip
  f- RDA from all the nodes
    Note 314422.1 Remote Diagnostic Agent (RDA) 4.0 – Overview

   g- the o/s message file
      IBM: /bin/errpt -a > messages.out
      Linux: /var/log/messages
      Solaris: /var/adm/messages

3. CRS Diagnostics

note 330358.1 -- CRS Diagnostic Collection Guide, please use (all .gz files especially crsData_$HOST.tar.gz
need to be uploaded)

diagcollection.pl --collect 

Please make sure to include *ALL* requested files (missing any will delay or prevendting from
identifying root cause) from *ALL* nodes in a single zip and upload.

Note 330358.1 - CRS 10gR2/ 11gR1/ 11gR2 Diagnostic Collection Guide       
Note 298895.1 - Modifying the default gateway address used by the Oracle 10g VIP
Note 399213.1 - VIP Going Offline Intermittantly - Slow Response from Default Gateway
Note 401783.1 - Changes in Oracle Clusterware after applying 10.2.0.3 Patchset

Filed Under: Oracle, Oracle RAC, Oracle脚本script Tagged With: crs resource, Oracle脚本script, vip

11g新特性:hangdiag.sql实例hang诊断脚本

2012/01/09 by admin Leave a Comment

11g中引入了hangdiag.sql 这个脚本来帮助Oracle Support 用来收集诊断实例hang住所需要的diagnostic信息，这个脚本直接存放在11g 以后的$ORACLE_HOME/rdbms/admin目录下，无需用户另外下载。

该脚本用于收集单实例的hang诊断信息还是很不错的，如果是RAC环境的话推荐使用racdiag.sql这个脚本。

值得一提的是 hangdiag.sql会做 11g中独有的(oradebug dump hangdiag_header 1)并将v$wait_chain的源信息x$ksdhng_chains文本形式输出，之后会做ashdump和systemstate(level 267 可能耗时较长)的dump转储操作，这样获得的trace文件信息就很全面了。

hangdiag.sql:

[oracle@vrh2 ~]$ cat $ORACLE_HOME/rdbms/admin/hangdiag.sql
Rem
Rem $Header: hangdiag.sql 08-jun-2007.02:06:43 amysoren Exp $
Rem
Rem hangdiag.sql
Rem
Rem Copyright (c) 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      hangdiag.sql - Hang Diagnosis script
Rem
Rem    DESCRIPTION
Rem      It is generally useful (but not necessary) to run this script from a
Rem      prelim mode connection.
Rem
Rem      This script dumps data required to diagnose hangs:
Rem      1. ASH dump
Rem      2. Systemstate dump with short callstacks
Rem      3. Hang analysis results (x$ksdhng_chains)
Rem
Rem    NOTES
Rem      It is required to set PID using oradebug setmypid/setospid/setorapid
Rem      before invoking this script. 
Rem
Rem      "oradebug tracefile_name" gives the file name including the path of
Rem      the trace file containing the dumps.
Rem
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    amysoren    06/08/07 - direct_access command syntax change
Rem    avaliani    05/17/07 - bug 5499564: add x$ksdhng_chains
Rem    amysoren    01/10/07 - add ashdump, systemstate dump
Rem    amysoren    01/10/07 - Created
Rem

-- begin hang diag dump
oradebug dump hangdiag_header 1

-- dump hang analysis chains
oradebug direct_access enable trace
oradebug direct_access disable reply
oradebug direct_access set content_type = 'text/plain'
oradebug direct_access select * from x$ksdhng_chains

-- dump ash data
oradebug dump ashdump 5

-- dump systemstate with short callstacks
oradebug dump systemstate 267

x$ksdhng_chains内部视图的字段如下

SQL> desc x$ksdhng_chains;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 CHAIN_ID                                           NUMBER
 CHAIN_IS_CYCLE                                     NUMBER
 CHAIN_SIGNATURE                                    VARCHAR2(801)
 CHAIN_SIGNATURE_HASH                               NUMBER
 INSTANCE                                           NUMBER
 OSID                                               VARCHAR2(25)
 PID                                                NUMBER
 SID                                                NUMBER
 SESS_SERIAL#                                       NUMBER
 BLOCKER_IS_VALID                                   NUMBER
 BLOCKER_INSTANCE                                   NUMBER
 BLOCKER_OSID                                       VARCHAR2(25)
 BLOCKER_PID                                        NUMBER
 BLOCKER_SID                                        NUMBER
 BLOCKER_SESS_SERIAL#                               NUMBER
 BLOCKER_CHAIN_ID                                   NUMBER
 IN_WAIT                                            NUMBER
 TIME_SINCE_LAST_WAIT_SECS                          NUMBER
 WAIT_ID                                            NUMBER
 WAIT_EVENT                                         NUMBER
 WAIT_EVENT_TEXT                                    VARCHAR2(64)
 P1                                                 NUMBER
 P1_TEXT                                            VARCHAR2(64)
 P2                                                 NUMBER
 P2_TEXT                                            VARCHAR2(64)
 P3                                                 NUMBER
 P3_TEXT                                            VARCHAR2(64)
 IN_WAIT_SECS                                       NUMBER
 TIME_REMAINING_SECS                                NUMBER
 NUM_WAITERS                                        NUMBER
 ROW_WAIT_OBJ#                                      NUMBER
 ROW_WAIT_FILE#                                     NUMBER
 ROW_WAIT_BLOCK#                                    NUMBER
 ROW_WAIT_ROW#                                      NUMBER

使用范例如下：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn & www.askmac.cn  

SQL> oradebug setmypid;
Statement processed.
SQL>
SQL>
SQL> @?/rdbms/admin/hangdiag    
Statement processed.
Statement processed.
Statement processed.
Statement processed.
Statement processed.
Statement processed.
Statement processed.
SQL> oradebug tracefile_name;
/s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_ora_22612.trc

Sample output 示例输出如下：

Processing Oradebug command 'setmypid'

Oradebug command 'setmypid' console output: <none>

Processing Oradebug command 'dump hangdiag_header 1'
Hang diagnosis dumps

Oradebug command 'dump hangdiag_header 1' console output: <none>
Result of fixed table query: select * from x$ksdhng_chains
ADDR    = 7FA5333FDC08
INDX    = 0
INST_ID = 2
CHAIN_ID        = 1
CHAIN_IS_CYCLE  = 0
CHAIN_SIGNATURE = 'SQL*Net message from client'<='enq: TM - contention'
CHAIN_SIGNATURE_HASH    = 373050554
INSTANCE        = 2
OSID    = 26593
PID     = 47
SID     = 188
SESS_SERIAL#    = 1879
BLOCKER_IS_VALID        = 1
BLOCKER_INSTANCE        = 2
BLOCKER_OSID    = 25967
BLOCKER_PID     = 43
BLOCKER_SID     = 176
BLOCKER_SESS_SERIAL#    = 9631
BLOCKER_CHAIN_ID        = 0
IN_WAIT = 1
TIME_SINCE_LAST_WAIT_SECS       = 0
WAIT_ID = 8
WAIT_EVENT      = 234
WAIT_EVENT_TEXT = enq: TM - contention
P1      = 1414332422
P1_TEXT = name|mode
P2      = 79775
P2_TEXT = object #
P3      = 0
P3_TEXT = table/partition
IN_WAIT_SECS    = 692
TIME_REMAINING_SECS     = -1
NUM_WAITERS     = 0
ROW_WAIT_OBJ#   = 79775
ROW_WAIT_FILE#  = 0
ROW_WAIT_BLOCK# = 0
ROW_WAIT_ROW#   = 0

ADDR    = 7FA5333FDC08
INDX    = 1
INST_ID = 2
CHAIN_ID        = 1
CHAIN_IS_CYCLE  = 0
CHAIN_SIGNATURE = 'SQL*Net message from client'<='enq: TM - contention'
CHAIN_SIGNATURE_HASH    = 373050554
INSTANCE        = 2
OSID    = 25967
PID     = 43
SID     = 176
SESS_SERIAL#    = 9631
BLOCKER_IS_VALID        = 0
BLOCKER_INSTANCE        = 0
BLOCKER_OSID    =
BLOCKER_PID     = 0
BLOCKER_SID     = 0
BLOCKER_SESS_SERIAL#    = 0
BLOCKER_CHAIN_ID        = 0
IN_WAIT = 1
TIME_SINCE_LAST_WAIT_SECS       = 0
WAIT_ID = 148
WAIT_EVENT      = 352
WAIT_EVENT_TEXT = SQL*Net message from client
P1      = 1650815232
P1_TEXT = driver id
P2      = 1
P2_TEXT = #bytes
P3      = 0
P3_TEXT =
IN_WAIT_SECS    = 568
TIME_REMAINING_SECS     = -1
NUM_WAITERS     = 1
ROW_WAIT_OBJ#   = -1
ROW_WAIT_FILE#  = 0
ROW_WAIT_BLOCK# = 0
ROW_WAIT_ROW#   = 0

2 rows selected

Processing Oradebug command 'dump ashdump 5'
ASH dump
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER BEGIN>>>
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER BEGIN>>>
****************
SCRIPT TO IMPORT
****************
------------------------------------------
Step 1: Create destination table <ashdump>
------------------------------------------
CREATE TABLE ashdump AS
SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY WHERE rownum < 0
----------------------------------------------------------------
Step 2: Create the SQL*Loader control file <ashldr.ctl> as below
----------------------------------------------------------------
load data
infile * "str '\n####\n'"
append
into table ashdump
fields terminated by ',' optionally enclosed by '"'
(
SNAP_ID  CONSTANT 0           ,
DBID                          ,
INSTANCE_NUMBER               ,
SAMPLE_ID                     ,
SAMPLE_TIME TIMESTAMP ENCLOSED BY '"' AND '"' "TO_TIMESTAMP(:SAMPLE_TIME   ,'MM-DD-YYYY HH24:MI:SSXFF')"   ,
SESSION_ID                    ,
SESSION_SERIAL#               ,
SESSION_TYPE                  ,
USER_ID                       ,
SQL_ID                        ,
SQL_CHILD_NUMBER              ,
SQL_OPCODE                    ,
FORCE_MATCHING_SIGNATURE      ,
TOP_LEVEL_SQL_ID              ,
TOP_LEVEL_SQL_OPCODE          ,
SQL_PLAN_HASH_VALUE           ,
SQL_PLAN_LINE_ID              ,
SQL_PLAN_OPERATION#           ,
SQL_PLAN_OPTIONS#             ,
SQL_EXEC_ID                   ,
SQL_EXEC_START DATE 'MM/DD/YYYY HH24:MI:SS' ENCLOSED BY '"' AND '"' ":SQL_EXEC_START"   ,
PLSQL_ENTRY_OBJECT_ID         ,
PLSQL_ENTRY_SUBPROGRAM_ID     ,
PLSQL_OBJECT_ID               ,
PLSQL_SUBPROGRAM_ID           ,
QC_INSTANCE_ID                ,
QC_SESSION_ID                 ,
QC_SESSION_SERIAL#            ,
EVENT_ID                      ,
SEQ#                          ,
P1                            ,
P2                            ,
P3                            ,
WAIT_TIME                     ,
TIME_WAITED                   ,
BLOCKING_SESSION              ,
BLOCKING_SESSION_SERIAL#      ,
BLOCKING_INST_ID              ,
CURRENT_OBJ#                  ,
CURRENT_FILE#                 ,
CURRENT_BLOCK#                ,
CURRENT_ROW#                  ,
TOP_LEVEL_CALL#               ,
CONSUMER_GROUP_ID             ,
XID                           ,
REMOTE_INSTANCE#              ,
TIME_MODEL                    ,
SERVICE_HASH                  ,
PROGRAM                       ,
MODULE                        ,
ACTION                        ,
CLIENT_ID                     ,
MACHINE                       ,
PORT                          ,
ECID
)
---------------------------------------------------
Step 3: Load the ash rows dumped in this trace file
---------------------------------------------------
sqlldr userid/password control=ashldr.ctl data=<this_trace_filename> errors=1000000
---------------------------------------------------
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER END>>>
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP BEGIN>>>
####
881465081,2,2082369,"01-10-2012 00:49:06.871520000",188,1879,1,0,"562cub3hk2tjy",
0,26,0,"",0,0,0,0,0,33554433,"01/10/2012 00:37:27",0,0,0,0,0,0,0,668627480,9,1414332422,
79775,0,0,0,176,9631,2,79775,0,0,0,94,12553,,0,1024,3427055676,"sqlplus@vrh2.oracle.com (TNS V1-V3)",
"sqlplus@vrh2.oracle.com (TNS V1-V3)","","","vrh2.oracle.com",0,""
.......................................

Processing Oradebug command 'dump systemstate 267'
===================================================
SYSTEM STATE (level=11, with short stacks)
------------
System global information:
.............................
call stack performance statistics:
total                  : 0.030000 sec              
setup                  : 0.000000 sec              
stack unwind           : 0.000000 sec              
symbol translation     : 0.030000 sec
printing the call stack: 0.000000 sec              
printing frame data    : 0.000000 sec              
printing argument data : 0.000000 sec              
----- End of Call Stack Trace -----

好了，就这么简单，把trace文件打个zip包传到 My Oracle Support上去吧，当然你也可以找我！我叫 Maclean.Liu!

Filed Under: Oracle, Oracle 11g新特性, Oracle脚本script Tagged With: hang, Oracle脚本script

利用rowid分块实现非分区表的并行update与delete

2012/01/08 by admin 6 Comments

大表中海量历史数据的更新与删除一直是令DBA非常头痛的事情，在表已经分区的前提下我们还可以利用并行或者truncate parition等手段来为UPDATE或者DELETE提速，但是如果对象是普通的非分区对表(non-partitioned heap table)的话，似乎就没有太好的加速方法了， nologging或parallel 对非分区表都没有效果。

之前我也有介绍过一个利用rowid将非分区表分割成指定数量个区间块的方法，见<Script:partition table into rowid extent chunks>；利用该脚本可以获取到这些分割后的区间块的起始rowid和结尾rowid，之后利用between start_rowid and end_rowid的条件构造多条DML语句，因为这些DML语句所更新的数据都是在互不相关的区间内的，所以可以在多个终端内并行地运行这些DML语句，而不会造成锁的争用或者Oracle并行执行协调(Parallel Execution coordinator ) 所带来的一些开销。

为了加深理解，我们来实践一下<Script:partition table into rowid extent chunks>中提到的方法：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn & www.askmac.cn

SQL> select count(*) from order_history;

  COUNT(*)
----------
   1137523

order_history 是一张非分区表， 存有100w条记录

SQL> select count(*) from order_history where order_id<1999999;

  COUNT(*)
----------
    499999

假设我们要删除order_history上order_id<1999999的所有记录，占到总行数的50%。由于order_history是非分区表，所以这里无法用Oracle的并行执行Parallel Execution来加速操作。因此我们利用上述脚本来构造多条DML语句：

SQL> @rowid_chunk

Enter value for rowid_ranges: 24             ==>这里输入要构造的rowid分区个数

Enter value for segment_name: ORDER_HISTORY  ==> 输入表名

Enter value for owner: SYS                   ==> owner名

where rowid between 'AAANJFAAEAAEXlBAAA' and 'AAANJFAAEAAEYjdCcP';
where rowid between 'AAANJFAAEAAEYjeAAA' and 'AAANJFAAEAAEZDdCcP';
where rowid between 'AAANJFAAEAAEZDeAAA' and 'AAANJFAAEAAEZhdCcP';
where rowid between 'AAANJFAAEAAEZheAAA' and 'AAANJFAAEAAEaBdCcP';
where rowid between 'AAANJFAAEAAEaBeAAA' and 'AAANJFAAEAAEahdCcP';
where rowid between 'AAANJFAAEAAEaheAAA' and 'AAANJFAAEAAEa3dCcP';
where rowid between 'AAANJFAAEAAEa3eAAA' and 'AAANJFAAEAAEbfdCcP';
where rowid between 'AAANJFAAEAAEbfeAAA' and 'AAANJFAAEAAEbzdCcP';
where rowid between 'AAANJFAAEAAEbzeAAA' and 'AAANJFAAEAAEcbdCcP';
where rowid between 'AAANJFAAEAAEcbeAAA' and 'AAANJFAAEAAEcvdCcP';
where rowid between 'AAANJFAAEAAEcveAAA' and 'AAANJFAAEAAEdXdCcP';
where rowid between 'AAANJFAAEAAEdXeAAA' and 'AAANJFAAEAAEdrdCcP';
where rowid between 'AAANJFAAEAAEdreAAA' and 'AAANJFAAEAAEeTdCcP';
where rowid between 'AAANJFAAEAAEeTeAAA' and 'AAANJFAAEAAEe5dCcP';
where rowid between 'AAANJFAAEAAEe5eAAA' and 'AAANJFAAEAAEfNdCcP';
where rowid between 'AAANJFAAEAAEfNeAAA' and 'AAANJFAAEAAEf1dCcP';
where rowid between 'AAANJFAAEAAEf1eAAA' and 'AAANJFAAEAAEgJdCcP';
where rowid between 'AAANJFAAEAAEgJeAAA' and 'AAANJFAAEAAEgxdCcP';
where rowid between 'AAANJFAAEAAEgxeAAA' and 'AAANJFAAEAAEhZdCcP';
where rowid between 'AAANJFAAEAAEhZeAAA' and 'AAANJFAAEAAEhtdCcP';
where rowid between 'AAANJFAAEAAEhteAAA' and 'AAANJFAAEAAEiVdCcP';
where rowid between 'AAANJFAAEAAEiVeAAA' and 'AAANJFAAEAAEi1dCcP';
where rowid between 'AAANJFAAEAAEi1eAAA' and 'AAANJFAAEAAEjVdCcP';
where rowid between 'AAANJFAAEAAEjVeAAA' and 'AAANJFAAEAAEjldCcP';

以上我们利用脚本构造出来24个由rowid标示的分区块(chunks)，相当于我们人为地将一张非分区表划分成24个区域，每个区域都互不重叠，利用rowid做分界线。尝试利用上面获取到的条件condition来构造DML:

UPDATE OR DELETE
where rowid between ‘start_rowid’ and ‘end_rowid’ AND 其他条件

例如要删除非分区表上所有order_id<1999999;的记录，如不优化则是一条语句：

DELETE FROM ORDER_HISTORY where order_id<1999999;
COMMIT;

实际在很大的表上这样删除数据是不理想也不可行的，几点理由：

1. 单条SQL语句串行执行，速度缓慢
2. 运行时间过长可能引发ORA-1555等著名错误
3. 如果失败rollback回滚可能是一场灾难

若利用我这里介绍的方法，则可以构造出多条DML语句并行删除，每一条均只删除一小部分：

DELETE FROM ORDER_HISTORY where rowid between ‘AAANJFAAEAAEXlBAAA’ and ‘AAANJFAAEAAEYjdCcP’ and order_id<1999999;
COMMIT;
DELETE FROM ORDER_HISTORY where rowid between ‘AAANJFAAEAAEYjeAAA’ and ‘AAANJFAAEAAEZDdCcP’ and order_id<1999999;
COMMIT;
DELETE FROM ORDER_HISTORY where rowid between ‘AAANJFAAEAAEZDeAAA’ and ‘AAANJFAAEAAEZhdCcP’ and order_id<1999999;
COMMIT;
………………………………..

视乎你想要的并行度，将以上构成DML语句再分割几块，打开多个终端同时执行。

这样做的几个优点：

1. 用户手动控制的并行执行，省去了Oracle Parallel并行控制的开销，使用得当的话比加parallel hint或者表上加并行度效率更高。
2. 将数据分割成小块(chunks)来处理，避免了ORA-1555错误
3. 用户可以根据主机负载和IO 动态地加减并行度

SQL> select count(*) from order_history where rowid between 'AAANJFAAEAAEXlBAAA' and 'AAANJFAAEAAEYjdCcP';

  COUNT(*)
----------
     84342

SQL> select count(*) from order_history where rowid between 'AAANJFAAEAAEYjeAAA' and 'AAANJFAAEAAEZDdCcP';

  COUNT(*)
----------
     49959

SQL> select count(*)
  2    from order_history
  3   where rowid between 'AAANJFAAEAAEXlBAAA' and 'AAANJFAAEAAEYjdCcP'
  4     and order_id < 1999999;

  COUNT(*)
----------
     84342

SQL> select count(*)
  2    from order_history
  3   where rowid between 'AAANJFAAEAAEYjeAAA' and 'AAANJFAAEAAEZDdCcP'
  4     and order_id < 1999999;

  COUNT(*)
----------
     49959

SQL> delete order_history
  2   where rowid between 'AAANJFAAEAAEXlBAAA' and 'AAANJFAAEAAEYjdCcP'
  3     and order_id < 1999999;

84342 rows deleted.

SQL> delete order_history
  2   where rowid between 'AAANJFAAEAAEYjeAAA' and 'AAANJFAAEAAEZDdCcP'
  3     and order_id < 1999999;

49959 rows deleted.

SQL>commit;

Commit complete

但是以上方法仍存在几点不足：

1. 《Script:partition table into rowid extent chunks》脚本目前不支持分区表
2. 因为《Script:partition table into rowid extent chunks》的脚本是根据表段的大小均匀地分割成指定数目的区域，试想当一些要更新或者删除的历史数据集中分布在segment的某些位置时(例如所要删除的数据均存放在一张表的前200个Extents中)，因为脚本是根据大小均匀分割区域的，所以某些区域是根本没有我们所要处理的数据的，由这些区域构造出来的DML语句都是无意义的。

基于以上这些考虑，我重写了获取rowid分块的SQL脚本，如下：

REM   put it in GUI TOOLS! otherwise caused ORA-00933
REM   control commit yourself, avoid ORA-1555

select 'and rowid between ''' || ora_rowid || ''' and ''' ||
       lead(ora_rowid, 1) over(order by rn asc) || '''' || ';'
  from (

       with cnt as (select count(*) from order_history)             -- 注意替换这里！！
         select rn, ora_rowid
           from (select rownum rn, ora_rowid
                   from (select rowid ora_rowid
                           from order_history                       -- 注意替换这里！！
                          order by rowid))
          where rn in (select (rownum - 1) *
                              trunc((select * from cnt) / &row_range) + 1
                         from dba_tables
                        where rownum < &row_range                  --输入分区的数目
                       union
                       select * from cnt))

and rowid between AAANJFAAEAAEZELAAB and AAANJFAAEAAEaRaABm
and rowid between AAANJFAAEAAEaRaABm and AAANJFAAEAAEbi+ABu
and rowid between AAANJFAAEAAEbi+ABu and AAANJFAAEAAEc0iAB2
and rowid between AAANJFAAEAAEc0iAB2 and AAANJFAAEAAEeGHAAG
and rowid between AAANJFAAEAAEeGHAAG and AAANJFAAEAAEfVrAAO
and rowid between AAANJFAAEAAEfVrAAO and AAANJFAAEAAEgnPAAW
and rowid between AAANJFAAEAAEgnPAAW and AAANJFAAEAAEjQIAB2
and rowid between AAANJFAAEAAEjQIAB2 and

以上脚本同样可以实现rowid分区的目的，但是因为其rowid是直接取自SELECT语句查询，所以不存在不支持分区表等复杂对象的情况。也因为rowid是来源于SELECT，所以我们可以指定针对那些存在符合条件数据的范围分区。

例如我们希望仅针对存有满足order_id<1999999条件数据的范围rowid分块，那么将replace here的地方替换成你的条件：

select 'and rowid between ''' || ora_rowid || ''' and ''' ||
       lead(ora_rowid, 1) over(order by rn asc) || '''' || ';'
  from (

       with cnt as (select count(*)
                      from order_history
                     where order_id < 1999999) -- replace here
         select rn, ora_rowid
           from (select rownum rn, ora_rowid
                   from (select rowid ora_rowid
                           from order_history
                          where order_id < 1999999 -- replace here
                          order by rowid))
          where rn in (select (rownum - 1) *
                              trunc((select * from cnt) / &row_range) + 1
                         from dba_tables
                        where rownum < &row_range
                       union
                       select * from cnt))

and rowid between 'AAANJFAAEAAEZELAAB' and 'AAANJFAAEAAEZf7ABd';
and rowid between 'AAANJFAAEAAEZf7ABd' and 'AAANJFAAEAAEZ9uABB';
and rowid between 'AAANJFAAEAAEZ9uABB' and 'AAANJFAAEAAEaXhAB2';
and rowid between 'AAANJFAAEAAEaXhAB2' and 'AAANJFAAEAAEa3dABW';
and rowid between 'AAANJFAAEAAEa3dABW' and 'AAANJFAAEAAEbTVAA2';
and rowid between 'AAANJFAAEAAEbTVAA2' and 'AAANJFAAEAAEbzRAAW';
and rowid between 'AAANJFAAEAAEbzRAAW' and 'AAANJFAAEAAEcyIAB2';
and rowid between 'AAANJFAAEAAEcyIAB2' and '';

SQL> select order_id from order_history where rowid='AAANJFAAEAAEZELAAB';

  ORDER_ID
----------
    538672

SQL>  select order_id from order_history where rowid='AAANJFAAEAAEcyIAB2';

  ORDER_ID
----------
   1994752

DELETE ORDER_HISTORY WHERE ORDER_ID<1999999 and rowid between 'AAANJFAAEAAEZELAAB' and 'AAANJFAAEAAEZf7ABd';
DELETE ORDER_HISTORY WHERE ORDER_ID<1999999 and rowid between 'AAANJFAAEAAEZf7ABd' and 'AAANJFAAEAAEZ9uABB';
DELETE ORDER_HISTORY WHERE ORDER_ID<1999999 and rowid between 'AAANJFAAEAAEZ9uABB' and 'AAANJFAAEAAEaXhAB2';
DELETE ORDER_HISTORY WHERE ORDER_ID<1999999 and rowid between 'AAANJFAAEAAEaXhAB2' and 'AAANJFAAEAAEa3dABW';
DELETE ORDER_HISTORY WHERE ORDER_ID<1999999 and rowid between 'AAANJFAAEAAEa3dABW' and 'AAANJFAAEAAEbTVAA2';
DELETE ORDER_HISTORY WHERE ORDER_ID<1999999 and rowid between 'AAANJFAAEAAEbTVAA2' and 'AAANJFAAEAAEbzRAAW';
DELETE ORDER_HISTORY WHERE ORDER_ID<1999999 and rowid between 'AAANJFAAEAAEbzRAAW' and 'AAANJFAAEAAEcyIAB2';

45713 rows deleted.

SQL>
45712 rows deleted.

SQL>
45712 rows deleted.

SQL>
45712 rows deleted.

SQL>
45712 rows deleted.

SQL>
45712 rows deleted.

SQL> 

91425 rows deleted.

SQL> SQL>
SQL>
SQL>
SQL> commit;

Commit complete.

SQL>
SQL>
SQL> select count(*) from ORDER_HISTORY WHERE ORDER_ID<1999999;

  COUNT(*)
----------
         0

几点注意事项：

1. 请将该脚本放到Pl/SQL Developer或Toad之类的工具中运行，在sqlplus中运行可能出现ORA-00933
2. 不要忘记替换红色标记的replace here的条件
3. 自行控制commit 避免出现ORA-1555错误

该脚本目前存在一个不足，在获取rowid分块时要求大表上有适当的索引，否则可能会因为全表扫描并排序而十分缓慢，若有恰当的索引则会使用INDEX FAST FULL SCAN。这里的恰当索引是指至少有一个非空列的普通b*tree索引，最好的情况是有主键索引或者bitmap位图索引。

Oracle在版本11.2中引入了DBMS_PARALLEL_EXECUTE 的新特性来帮助更新超大表，文档<11.2 New Feature : Using DBMS_PARALLEL_EXECUTE to Update Large Tables in Parallel [ID 1066555.1]>对该特性做了介绍。

该DBMS_PARALLEL_EXECUTE新特性的一大亮点就是可以对表上的行数据进行分组为更小的块(chunks)，且并行更新这些小的块：

dbmspexe.sql – DBMS Parallel EXEcute package
This package contains APIs to chunk a table into smaller units and execute those chunks in parallel.

The DBMS_PARALLEL_EXECUTE package enables you to incrementally update the data in a large table in parallel, in two high-level steps:

Group sets of rows in the table into smaller chunks.
Apply the desired UPDATE statement to the chunks in parallel, committing each time you have finished processing a chunk.

This technique is recommended whenever you are updating a lot of data. Its advantages are:

You lock only one set of rows at a time, for a relatively short time, instead of locking the entire table.
You do not lose work that has been done if something fails before the entire operation finishes.
You reduce rollback space consumption.
You improve performance.

DBMS_PARALLEL_EXECUTE可以以3种方式将数据分块：

Different Ways to Spilt Workload

CREATE_CHUNKS_BY_NUMBER_COL : Chunks the table associated with the given task by the specified column.
CREATE_CHUNKS_BY_ROWID : Chunks the table associated with the given task by ROWID
CREATE_CHUNKS_BY_SQL : Chunks the table associated with the given task by means of a user-provided SELECT statement

其中就包含了CREATE_CHUNKS_BY_ROWID这种按照ROWID分块的方式。

：）　这是一个来的有点迟的新特性(版本11.2才加入)，迟到我们已经自行找到了多种原理类似的解决方案。　从另一个角度看，　用户的需求永远是那么正确，是大量的用户在驱动着Oracle这个巨人！

Filed Under: Oracle, Oracle脚本script Tagged With: large update, Oracle脚本script

Script:verify Oracle Object timestamp discrepancy

2012/01/03 by admin Leave a Comment

首先我们要知道这几个结构，那就是

create table obj$                                            /* object table */
( obj#          number not null,                            /* object number */
  dataobj#      number,                          /* data layer object number */
  owner#        number not null,                        /* owner user number */
  name          varchar2("M_IDEN") not null,                  /* object name */
  namespace     number not null,         /* namespace of object (see KQD.H): */
 /* 1 = TABLE/PROCEDURE/TYPE, 2 = BODY, 3 = TRIGGER, 4 = INDEX, 5 = CLUSTER, */
                                                  /* 8 = LOB, 9 = DIRECTORY, */
  /* 10 = QUEUE, 11 = REPLICATION OBJECT GROUP, 12 = REPLICATION PROPAGATOR, */
                                     /* 13 = JAVA SOURCE, 14 = JAVA RESOURCE */
                                                 /* 58 = (Data Mining) MODEL */
  subname       varchar2("M_IDEN"),               /* subordinate to the name */
  type#         number not null,                 /* object type (see KQD.H): */
  /* 1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE, */
             /* 7 = PROCEDURE, 8 = FUNCTION, 9 = PACKAGE, 10 = NON-EXISTENT, */
              /* 11 = PACKAGE BODY, 12 = TRIGGER, 13 = TYPE, 14 = TYPE BODY, */
      /* 19 = TABLE PARTITION, 20 = INDEX PARTITION, 21 = LOB, 22 = LIBRARY, */
                                             /* 23 = DIRECTORY , 24 = QUEUE, */
    /* 25 = IOT, 26 = REPLICATION OBJECT GROUP, 27 = REPLICATION PROPAGATOR, */
    /* 28 = JAVA SOURCE, 29 = JAVA CLASS, 30 = JAVA RESOURCE, 31 = JAVA JAR, */
                 /* 32 = INDEXTYPE, 33 = OPERATOR , 34 = TABLE SUBPARTITION, */
                                                  /* 35 = INDEX SUBPARTITION */
                                                 /* 82 = (Data Mining) MODEL */
                                /* 92 = OLAP CUBE DIMENSION,  93 = OLAP CUBE */
                   /* 94 = OLAP MEASURE FOLDER, 95 = OLAP CUBE BUILD PROCESS */
  ctime         date not null,                       /* object creation time */
  mtime         date not null,                      /* DDL modification time */
 stime date not null, /* specification timestamp (version) */
  status        number not null,            /* status of object (see KQD.H): */
                                     /* 1 = VALID/AUTHORIZED WITHOUT ERRORS, */
                          /* 2 = VALID/AUTHORIZED WITH AUTHORIZATION ERRORS, */
                            /* 3 = VALID/AUTHORIZED WITH COMPILATION ERRORS, */
                         /* 4 = VALID/UNAUTHORIZED, 5 = INVALID/UNAUTHORIZED */

 create table dependency$                                 /* dependency table */
( d_obj#        number not null,                  /* dependent object number */
  d_timestamp   date not null,   /* dependent object specification timestamp */
  order#        number not null,                             /* order number */
  p_obj#        number not null,                     /* parent object number */
 p_timestamp date not null, /* parent object specification timestamp */
  d_owner#      number,                           /*  dependent owner number */
  property      number not null,                   /* 0x01 = HARD dependency */
                                                   /* 0x02 = REF  dependency */
                                          /* 0x04 = FINER GRAINED dependency */
  d_attrs       raw("M_CSIZ"), /* Finer grain attr. numbers if finer grained */
  d_reason      raw("M_CSIZ"))  /* Reason mask of attrs causing invalidation */

obj$ 和dependency$ 是2个基础的数据字典表:

obj$.stime的解释是specification timestamp (version)，实际上这个字段代表了该对象的版本，指这个数据字典版本被创建的日期。
dependency$.p_timestamp代表依赖关系中父对象的数据字典版本被创建的日期。

例如STANDARD这个对象的STIME 字典版本的创建日期可能非常早：

SQL> select name,stime,ctime,mtime from sys.obj$ where name='STANDARD';

NAME                           STIME     CTIME     MTIME
------------------------------ --------- --------- ---------
STANDARD 18-APR-06 17-SEP-11 17-SEP-11
STANDARD                       17-SEP-11 17-SEP-11 17-SEP-11

注意若你的数据库是通过restore seed database(DBCA时选择非custom database)可能许多对象的stime=ctime。

有时候为了确保Oracle组件的正常，我们需要检验父对象和依赖关系的数据字典版本是一致的，若不一致那么会导致组件对象失效(component object invalid)。

以下脚本可以列出数据库中所有父对象和依赖关系的数据字典版本是不一致的对象：

set pagesize 10000
column d_name format a20
column p_name format a20
select do.obj# d_obj,
       do.name d_name,
       do.type# d_type,
       po.obj# p_obj,
       po.name p_name,
       to_char(p_timestamp, 'DD-MON-YYYY HH24:MI:SS') "P_Timestamp",
       to_char(po.stime, 'DD-MON-YYYY HH24:MI:SS') "STIME",
       decode(sign(po.stime - p_timestamp), 0, 'SAME', '*DIFFER*') X
  from sys.obj$ do, sys.dependency$ d, sys.obj$ po
 where P_OBJ# = po.obj#(+)
   and D_OBJ# = do.obj#
   and do.status = 1 /*dependent is valid*/
   and po.status = 1 /*parent is valid*/
   and po.stime != p_timestamp /*parent timestamp not match*/
 order by 2, 1;

Filed Under: Oracle, Oracle脚本script Tagged With: dependency, Oracle脚本script

Script:SQL调优健康检查脚本

2011/12/31 by admin 4 Comments

以下脚本可以用于收集SQL调优的相关信息，包括统计信息、优化器参数等。

When executed for one SQL_ID, this script generates an HTML report with the results of a set of health-checks around the one SQL statement provided. Health-checks are performed over:

CBO Statistics for schema objects accessed by the one SQL statement being analyzed
CBO Parameters
CBO System Statistics
CBO Data Dictionary Statistics
CBO Fixed-objects Statistics

下载sqlhc脚本

示例输出HTML:

Filed Under: Oracle, Oracle SQL性能调优, Oracle脚本script Tagged With: Oracle脚本script, SQL tuning