statistics

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

Know more about RAC statistics and wait event

2011/12/13 by admin Leave a Comment

以下列出了RAC中的主要统计信息和等待事件：

1. Statistics:

1.1 V$SYSSTAT, V$SESSTAT (join to V$STATNAME)

gc cr blocks served
gc cr block build time
gc cr block flush time
gc cr block send time
gc current blocks served
gc current block pin time
gc current block flush time
gc current block send time
gc cr blocks received
gc cr block receive time
gc current blocks received
gc current block receive time
gc blocks lost
gc claim blocks lost
gc blocks corrupt
global enqueue gets sync
global enqueue gets async
global enqueue get time
global enqueue releases
gcs messages sent
ges messages sent
global enqueue CPU used by this session
gc CPU used by this session
IPC CPU used by this session
global undo segment hints helped (10.2)
global undo segment hints were stale (10.2)

1.2 V$SYSMETRIC

GC CR Block Received Per Second
GC CR Block Received Per Txn
GC Current Block Received Per Second
GC Current Block Received Per Txn
Global Cache Average CR Get Time
Global Cache Average Current Get Time
Global Cache Blocks Corrupted
Global Cache Blocks Lost

1.3 V$SEGMENT_STATISTICS, V$SEGSTAT

gc buffer busy
gc cr blocks received
gc current blocks received

1.4 DBA_HIST_SEG_STAT

GC_CR_BLOCKS_SERVED_TOTAL
GC_CR_BLOCKS_SERVED_DELTA
GC_CU_BLOCKS_SERVED_TOTAL
GC_CU_BLOCKS_SERVED_DELTA

1.5 V$GES_STATISTICS

acks for commit broadcast(actual)
acks for commit broadcast(logical)
broadcast msgs on commit(actual)
broadcast msgs on commit(logical)
broadcast msgs on commit(wasted)
dynamically allocated gcs resources
dynamically allocated gcs shadows
false posts waiting for scn acks
flow control messages received
flow control messages sent
gcs assume cvt
gcs assume no cvt
gcs ast xid
gcs blocked converts
gcs blocked cr converts
gcs compatible basts
gcs compatible cr basts (global)
gcs compatible cr basts (local)
gcs cr basts to PIs
gcs cr serve without current lock
gcs dbwr flush pi msgs
gcs dbwr write request msgs
gcs error msgs
gcs forward cr to pinged instance
gcs immediate (compatible) converts
gcs immediate (null) converts
gcs immediate cr (compatible) converts
gcs immediate cr (null) converts
gcs indirect ast
gcs lms flush pi msgs
gcs lms write request msgs
gcs msgs process time(ms)
gcs msgs received
gcs out-of-order msgs
gcs pings refused
gcs queued converts
gcs recovery claim msgs
gcs refuse xid
gcs regular cr
gcs retry convert request
gcs side channel msgs actual
gcs side channel msgs logical
gcs undo cr
gcs write notification msgs
gcs writes refused
ges msgs process time(ms)
ges msgs received
global posts dropped
global posts queue time
global posts queued
global posts requested
global posts sent
implicit batch messages received
implicit batch messages sent
lmd msg send time(ms)
lms(s) msg send time(ms)
messages flow controlled
messages queue sent actual
messages queue sent logical
messages received actual
messages received logical
messages sent directly
messages sent indirectly
messages sent not implicit batched
messages sent pbatched
msgs causing lmd to send msgs
msgs causing lms(s) to send msgs
msgs received queue time (ms)
msgs received queued
msgs sent queue time (ms)
msgs sent queue time on ksxp (ms)
msgs sent queued
msgs sent queued on ksxp
process batch messages received
process batch messages sent

2. Wait Events:

在10g中RAC等待事件可以分为3类，下面列出了主要的等待事件包括一些undocumented wait event.

2.1. Real time only :

Those wait events are only defined while the process is waiting; after the wait is over, they are reclassified according to the outcome of the global cache operation. They should appear only on the following views: V$SESSION_WAIT, V$ACTIVE_SESSION_HISTORY, V$EVENT_HISTOGRAM.

gc cr request
gc current request

2.2. Historical only:

These are events represent the outcome of a GC request (fixup events). They can appear in the following views: V$SESSION_EVENT, V$SYSTEM_EVENT, DBA_HIST_SYSTEM_EVENT, V$EVENTMETRIC.

gc cr block 2-way
gc cr block 3-way
gc cr block busy
gc cr block congested
gc cr block lost
gc cr block unknown
gc cr grant 2-way
gc cr grant busy
gc cr grant congested
gc cr grant unknown
gc current block 2-way
gc current block 3-way
gc current block busy
gc current block congested
gc current block lost
gc current block unknown
gc current grant 2-way
gc current grant busy
gc current grant congested
gc current grant unknown

2.3. Other events:

The remaining wait events may appear in any of the views listed before, namely: V$SESSION_WAIT, V$ACTIVE_SESSION_HISTORY, V$EVENT_HISTOGRAM, V$SESSION_EVENT, V$SYSTEM_EVENT, DBA_HIST_SYSTEM_EVENT, V$EVENTMETRIC.

LMON global data update
cr request retry
gc assume
gc block recovery request
gc buffer busy
gc claim
gc cr cancel
gc cr disk read
gc cr disk request
gc cr failure
gc cr multi block request
gc current cancel
gc current multi block request
gc current retry
gc current split
gc domain validation
gc freelist
gc prepare
gc quiesce wait
gc recovery free
gc recovery quiesce
gc remaster
gcs ddet enter server mode
gcs domain validation
gcs drm freeze begin
gcs drm freeze in enter server mode
gcs enter server mode
gcs log flush sync
gcs remastering wait for read latch
gcs remastering wait for write latch
gcs remote message
gcs resource directory to be unfrozen
gcs to be enabled
ges LMD suspend for testing event
ges LMD to inherit communication channels
ges LMD to shutdown
ges LMON for send queues
ges LMON to get to FTDONE
ges LMON to join CGS group
ges cached resource cleanup
ges cancel
ges cgs registration
ges enter server mode
ges generic event
ges global resource directory to be frozen
ges inquiry response
ges lmd and pmon to attach
ges lmd/lmses to freeze in rcfg – mrcvr
ges lmd/lmses to unfreeze in rcfg – mrcvr
ges master to get established for SCN op
ges performance test completion
ges pmon to exit
ges process with outstanding i/o
ges reconfiguration to start
ges remote message
ges resource cleanout during enqueue open
ges resource cleanout during enqueue open-cvt
ges resource directory to be unfrozen
ges retry query node
ges reusing os pid
ges user error
ges wait for lmon to be ready
ges1 LMON to wake up LMD – mrcvr
ges2 LMON to wake up LMD – mrcvr
ges2 LMON to wake up lms – mrcvr 2
ges2 LMON to wake up lms – mrcvr 3
ges2 proc latch in rm latch get 1
ges2 proc latch in rm latch get 2
global cache busy
global enqueue expand wait
latch: KCL gc element parent latch
latch: gcs resource hash
latch: ges resource hash list

Filed Under: Oracle, Oracle RAC Tagged With: RAC, statistics, wait event

dbms_stats.lock_table_stats对于没有统计信息的表分区同样有效

2011/11/08 by admin 1 Comment

常见的分区表DDL如 split partition、add partition都会生成没有统计信息的表分区table partition，长期以来我对dbms_stats.lock_table_stats有一个错误的认识，即对于没有统计信息的分区，LOCK_STATS并不生效。实际测试发现锁表统计信息对没有统计信息的分区同样有效，结果就是自动收集统计信息的作业不会收集这些实际没有统计信息的分区：


CREATE TABLE MacleanV
 nologging
      partition by range(object_id)
      (partition p1 values less than (99999) tablespace users,
      partition p2 values less than (maxvalue) tablespace users)
	as select * from dba_objects where rownum=0;  
	  
	  
SQL> insert into macleanV select * from dba_objects;

51029 rows created.

SQL> commit;

Commit complete.

	  

SQL>  exec dbms_stats.gather_table_stats('SYS','MACLEANV');

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from dba_tables where table_name='MACLEANV';

  NUM_ROWS     BLOCKS
---------- ----------
     51029        748



SQL> select partition_name,num_rows,blocks from dba_tab_partitions where table_name='MACLEANV';

PARTITION_NAME                   NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
P1                                  51029        748
P2                                      0          0




   
SQL> exec dbms_stats.lock_table_stats('SYS','MACLEANV');

PL/SQL procedure successfully completed.



SQL> exec dbms_stats.gather_table_stats('SYS','MACLEANV');
BEGIN dbms_stats.gather_table_stats('SYS','MACLEANV'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 15027
ORA-06512: at "SYS.DBMS_STATS", line 15049
ORA-06512: at line 1


	  
SQL> alter table macleanv split partition p1 at (20001) into (partition p3, partition p4);

Table altered.

SQL> select partition_name,num_rows,blocks from dba_tab_partitions where table_name='MACLEANV';

PARTITION_NAME                   NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
P4
P2                                      0          0
P3





SQL> exec DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC;

PL/SQL procedure successfully completed.

SQL> select partition_name,num_rows,blocks from dba_tab_partitions where table_name='MACLEANV';

PARTITION_NAME                   NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
P2                                      0          0
P3
P4

SQL> exec  DBMS_STATS.GATHER_DATABASE_STATS;

PL/SQL procedure successfully completed.

SQL> select partition_name,num_rows,blocks from dba_tab_partitions where table_name='MACLEANV';

PARTITION_NAME                   NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
P2                                      0          0
P3
P4

使用DBMS_STATS.LOCK_TABLE_STATS锁定相关统计信息
语句为：
EXECUTE DBMS_STATS.LOCK_TABLE_STATS (‘owner name’, ‘table name’);

查询锁定状态
SELECT stattype_locked FROM dba_tab_statistics

当锁定表的统计信息后，这个表相关的对象的统计信息也被锁定，比如列信息、直方图、索引的统计信息。

在锁定前，请在适当时刻对表的统计信息进行收集，并确认当前的统计信息是合适的。
在锁定后，请用刷新shared pool或grant/revoke等方法将表相关的SQL失效。

更多DBMS_STATS.LOCK_TABLE_STATS信息可以参考：

Preserving Statistics using DBMS_STATS.LOCK_TABLE_STATS [ID 283890.1]
ORA-7445 [kdapprv] Running dbms_stats.gather_table_stats or dbms_stats.lock_table_stats [ID 333377.1]
FAQ: Automatic Statistics Collection Jobs (10g and 11g) [ID 1233203.1]

Filed Under: Oracle, Oracle SQL性能调优 Tagged With: dbms_stats.lock_table_stats, statistics

Find INTCOL#=1001 in col_usage$?

2011/07/05 by admin Leave a Comment

在<了解你所不知道的SMON功能(四):维护col_usage$字典基表>中我介绍了SMON后台进程维护字典基表COL_USAGE$一些细节，有网友阅读了这篇文档后发现其数据库的COL_USAGE$中存在INTCOL#=1001的记录。

INTCOL#列表示internal column number对应于COL$基表的INTCOL#，注意Internal Column Number与COL#(column number as created)是不同的。$ORACLE_HOME/rdbms/admin/sql.bsq对于INTCOL#给出了解释:

* If a table T(c1, addr, c2) contains an ADT column addr which is stored
   * exploded, the table will be internally stored as
   *              T(c1, addr, C0003$, C0004$, C0005$, c2)
   * Of these, only c1, addr and c2 are user visible columns. Thus, the
   * user visible column numbers for (c1, addr, C0003$, C0004$, C0005$, c2)
   * will be 1,2,0,0,0,3. And the corresponding internal column numbers will
   * be 1,2,3,4,5,6.
   *
   * Some dictionary tables like icol$, ccol$ need to contain intcol# so
   * that we can have indexes and constraints on ADT attributes. Also, these
   * tables also need to contain col# to maintain backward compatibility.
   * Most of these tables will need to be accessed by col#, intcol# so
   * indexes are created on them based on (obj#, col#) and (obj#, intcol#).
   * Indexes based on col# have to be non-unique if ADT attributes might
   * appear in the table. Indexes based on intcol# can be unique.

这里的ADT指的是抽象数据类型(Abstract DataType is a user defined data type)，例如:

CREATE OR REPLACE TYPE persons_address AS OBJECT (
  streetNumber NUMBER,
  streetName   VARCHAR2(30),
  citySuburb   VARCHAR2(30),
  state        VARCHAR2(4),
  postCode     NUMBER
);

熟读Oracle官方文档的朋友一定会记得，Oracle中单表的column总数存在一个上限:1000，即单表不能拥有超过1000个列。

但令人疑惑的是INTCOL#居然是1001，显然1001是某种magic number，而不是指第1001列。

搞清楚这个问题后，再进一步探索就不难发现问题的关键了:

SQL> select * from v$version;

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

SQL> select * from global_name;

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

SQL> drop table maclean;

Table dropped.

SQL> create table maclean(oppo_find_me int);

Table created.

SQL> select object_id from dba_objects where object_name='MACLEAN';

 OBJECT_ID
----------
   1343832

SQL> select intcol# from col_usage$ where obj#=1343832;

no rows selected

SQL> insert into maclean values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid from maclean;    

ROWID
------------------
AAFIFYAABAAByPKAAA

SQL> delete maclean where rowid='AAFIFYAABAAByPKAAA';

1 row deleted.

SQL> commit;

Commit complete.

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> select intcol#,equality_preds from col_usage$ where obj#=1343832;

   INTCOL# EQUALITY_PREDS
---------- --------------
      1001              1

通过这一点可以了解那些字典基表是以rowid为条件来查询或更新的

SQL> select owner || '.' || object_name
  2    from dba_objects
  3   where object_id in (select obj# from col_usage$ where intcol# = 1001);

OWNER||'.'||OBJECT_NAME
--------------------------------------------------------------------------------
SYS.COL$
SYS.CDEF$
SYS.VIEW$
SYS.SUMPARTLOG$
SYS.SUM$
SYS.SUMKEY$
SYS.SUMAGG$
SYS.SUMPRED$
SYS.SUMQB$
SYS.PS$
SYS.AW_OBJ$

OWNER||'.'||OBJECT_NAME
--------------------------------------------------------------------------------
SYS.AW_PROP$
SYS.WRI$_ADV_OBJECTS
WMSYS.WM$WORKSPACES_TABLE
SYS.MACLEAN
CTXSYS.DR$INDEX
XDB.XDB$H_INDEX
XDB.XDB$RESOURCE
EXFSYS.RLM$SCHACTLIST
SYS.AW$EXPRESS
MACLEAN.SAMPLE

总结:
Oracle最早在9i中引入了col_usage$字典基表，其目的在于监控column在SQL语句作为predicate的情况，col_usage$的出现完善了CBO中柱状图自动收集的机制。该字典基表上的INTCOL#列代表Internal Column Number以标识ADT列。INTCOL#等于1001代表ROWID伪列，也就是相关对象曾使用ROWID充当predicate。

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: cbo, col_usage$, statistics

How to understand goldengate report file statistics

2010/12/20 by admin Leave a Comment

- Are total process records = inserts + updates + deletes +discards + ignores?
Generally total process records = inserts + updates + deletes +discards + ignores

- Are update collisions is part of updates ?
Yes

- Are delete collisions is part of deletes?
Yes

- Are total successfully process records = inserts + updates + deletes?
Yes

- Can HANDLECOLLISIONS handle ORA-00001 error?
No, HANDLECOLLISIONS can not handle ORA-00001 error. This error is an unique constraint violation error.
HANDLECOLLISIONS control whether or not replicate tries to resolve duplicate-record
and missing-record errors when applying SQL on the target.
These errors can occur during an initial load, when data from source tables is
being loaded to target tables while Oracle Golden Gate is replicating
transactional changes that are being made to those tables.
You can read more about HANDLECOLLISIONS and NOHANDLECOLLISIONS in Oracle® GoldenGate Windows and UNIX Reference Guide.

- How to find the value of bind variable when error is occured by update statement?
In case you want to capture conflict records, you can capture the conflict records to discard file.
Also the conflict record can be written to trace table.

- How to capture the conflict records to discard file?
A discard file is generated when the error condition is encountered by the extract or replicat,
and there is a database error generated from a DDL operation.
Discard file is generally used to do the troubleshooting.

The location of the discard file, is determined by the DISCARDFILE parameter in the Extract or Replicat parameter file.

DISCARDFILE [, APPEND | PURGE | MEGABYTES ]

Where:

<file name> is the discard file name.
APPEND adds new content to existing content if the file already exists.
PURGE purges the file vefore writing new content.
MEGABYTES <N> sets the maximum size of the file, in megabytes. The default is 1MB.

Filed Under: GoldenGate, Oracle Tagged With: GG, report, statistics

No way to disable datapump estimate？

2010/11/02 by admin 1 Comment

昨天晚上去客户现场配合一个导入导出作业；这是一套Solaris 10上的10.2.0.1系统，导出采用expdp数据泵工具，需要导出的数据源是一张大小在120G左右的单表，该表存放了最近一年的数据，实际导出时只需要最近三个月的数据，所以使用了QUERY参数,并指定了并行度为2。
该导出作业之前未经测试过，语句也是临时写的，实际执行导出工作时发现在评估阶段(estimate phase)耗费了大约十多分钟的时间，estimate默认使用blocks模式，即通过计算对象段占用的数据库块来预估dumpfile的大小；此外还有statistics模式通过对象的统计信息来推算导出文件的大小:

The block method of estimate is the least accurate. We investigate how many blocks were allocated for the storage segments for the tables specified. If the table had been recently analyzed, the estimate=statistics would be the most accurate. So remember, it is important to generate statistics before calculating the estimate using estimate=statistics method. Additionally, ESTIMATE=BLOCKS method could be inaccurate when:

a) The table was created with a much bigger initial extent size than was needed for the actual table data

b) Many rows have been deleted from the table, or a very small percentage of each block is used.

# When statistics have been explicitly set via the DBMS_STATS package in order to force certain optimizer plans, instead of being directly collected from the data, ESTIMATE=STATISTICS may not report correct results.

但实际上这2中方式在使用QUERY的情况下都无法正确预估导出文件的大小:

[maclean@rh2 udump]$ expdp maclean/maclean directory=dump  tables=estimate_me query=estimate_me:'"where t1<2000"'  estimate=statistics

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 21:28:26

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** directory=dump tables=estimate_me query=estimate_me:"where t1<2000" estimate=statistics
Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "MACLEAN"."ESTIMATE_ME"                     104.7 MB
Total estimation using STATISTICS method: 104.7 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."ESTIMATE_ME"                     32.42 KB    1999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" successfully completed at 21:28:29

/* statistics模式使用统计来判断导出文件大小，比较容易控制 */

begin
 dbms_stats.set_table_stats(ownname => 'MACLEAN',tabname => 'ESTIMATE_ME',numrows => 999999999999999,numblks => 99999999999999999999);
  end;
/

PL/SQL procedure successfully completed.

Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "MACLEAN"."ESTIMATE_ME"                     10244 GB
Total estimation using STATISTICS method: 10244 GB

/* 再次导出时数据泵采用了"作假的"统计信息*/

[maclean@rh2 udump]$ expdp maclean/maclean directory=dump  tables=estimate_me query=estimate_me:'"where t1<2000"'  estimate=blocks

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 21:31:58

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** directory=dump tables=estimate_me query=estimate_me:"where t1<2000" estimate=blocks
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "MACLEAN"."ESTIMATE_ME"                       192 MB
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."ESTIMATE_ME"                     32.42 KB    1999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" successfully completed at 21:32:01

/* 可以看到以上BLOCKS method预估的dumpfile大小为192MB，而STATISTICS方式预估为104.7MB；
    然而实际的使用QUERY参数导出的1999行数据只占用32k的空间！
    estimate在这里摆了一个大乌龙
*/

查了一下metalink发现10.2.0.1上存在expdp在estimate评估阶段耗时很久的bug：

EXPDP Slow and Estimate Takes Long Time [ID 822283.1]
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3
This problem can occur on any platform.
Symptoms
Data pump Export is very slow and long time is spent in Estimating the data size.
EXPDP is stuck for long at :

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** parfile=dpexp.par
Estimate in progress using BLOCKS method...

AWR report taken when EXPDP is running slow show the below query took long time to complete .

SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$),
XMLFORMAT.createFormat2('TABLE_DATA_T', '7')), 0 ,KU$.BASE_OBJ.NAME
,KU$.BASE_OBJ.OWNER_NAME ,'TABLE' ,to_char(KU$.BYTES_ALLOC) ,
to_char(KU$.ET_PARALLEL) ,KU$.FGAC ,KU$.NONSCOPED_REF ,KU$.XMLSCHEMACOLS
,KU$.NAME , KU$.NAME ,'TABLE_DATA' ,KU$.PART_NAME ,KU$.SCHEMA_OBJ.OWNER_NAME
,KU$.TS_NAME , KU$.TRIGFLAG
,decode(KU$.SCHEMA_OBJ.TYPE_NUM,2,decode(bitand(KU$.PROPERTY,8192),8192,
'NESTED TABLE','T'),19,'PARTITION',20,'PARTITION','SUBPARTITION')
,to_char(KU$.UNLOAD_METHOD)
FROM SYS.KU$_TABLE_DATA_VIEW KU$
WHERE NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND NOT (BITAND
(KU$.BASE_OBJ.FLAGS,16)=16) AND KU$.BASE_OBJ.NAME IN
('PA_DRAFT_INVOICES_ALL') AND KU$.BASE_OBJ.OWNER_NAME IN ('PA') AND NOT
EXISTS (SELECT 1 FROM SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='TABLE' AND
A.NAME=KU$.BASE_OBJ.NAME AND A.SCHEMA=KU$.BASE_OBJ.OWNER_NAME) AND NOT EXISTS
(SELECT 1 FROM SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='SCHEMA' AND
A.NAME=KU$.BASE_OBJ.OWNER_NAME);

Cause
The issue here is with using RBO.
Data pump is not choosing Cost Based Optimizer.
This is because of the 'unpublished' bug 5929373 -DATA PUMP TAKES LONG TIME TO EXPORT DATA

This bug is closed as duplicate of another bug << 5464834 >>

Solution

   1. Upgrade to 10.2.0.4 . The bug is fixed in 10.2.0.4
      Or
   2. Apply the << patch 5464834 >> if available for your platform .

        * Login to metalink
        * Go to 'Patches and Updates'
        * Click on 'Simple Search'
        * Enter the patch number 5464834
        * Select your platform from the dropdown list .
        * Patch,if available , will be displayed .
        * Download and apply the patch .

该Bug可以通过升级版本解决，但远水解不了近火，最初想disable掉estimate功能；但查阅若干资料后发现目前似乎没有能够关掉estimate功能的办法，这个就是悲哀所在了！耐心等estimate阶段完成后，实际数据导出阶段倒也只花了十来分钟，就这一点看来datapump很有中国式的办事风格。

另外当指定的导出文件数小于指定的parallelism，且导出数据量较大时可能出现ORA-39095错误，会导致导出作业意外终止:

39095, 00000, "Dump file space has been exhausted: Unable to allocate %s bytes"
// *Cause: The Export job ran out of dump file space before the job was
// completed.
// *Action: Reattach to the job and add additional dump files to the job
// restarting the job.

EXPDP generating ORA-39095 : " dump file space has been exhausted"
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2 - Release: 10.2 to 11.2
Information in this document applies to any platform.
Symptoms
Checked for relevance on 10-22-2010

Errors while performing Datapump Export:

- when exporting a large number of tables then it completely stops
- the jobs may remain in the datapump export tables
- No trace generated at the time of the failure.
ERROR:
ORA - 39095 : " dump file space has been exhausted. Unable to allocate 4096 bytes".jobsystem.sys_export_full_02 stops due to fatal error.

Cause
Customer chose only limited number of files in "dumpfile" parameter with limited "filesize" , i.e.:

full=Y
directory=dpump_dir
dumpfile=full_1.dmp,full_2.dmp,full_3.dmp,full_4.dmp
filesize=3000000000

This means 4 dump files with filesize 3000000000 byte (2.79 G).

the dumpfile parameter is the issue here.

.
Solution
1- You can use the dynamic format (i.e dumpfile=full_%U.dmp) :

The 'wildcard' specification for the dump file can expand up to 99 files. If 99 files have been generated before the export has completed, it will again return the ORA-39095 error.

2- If this is yet not enough and more files are needed, a workaround would be to speficy a bigger 'filesize' parameter.

3- If this is inconvenient, another option is to use this syntax:

dumpfile=fullexp%U.dmp, fullexp2_%U.dmp, fullexp3_%U.dmp

which can expand up to 3*99 files.

If encountering problems containing the dump in a single directory using this solution, you may prefer this syntax:

dumpfile=dmpdir1:fullexp1_%U.dmp, dmpdir2:fullexp2_%U.dmp, dmpdir3:fullexp3_U.dmp

(assuming the 3 directory objects listed above had been already created first).

解决方法很简单就是去掉并行度，或者指定动态的导出文件名。
记以录之，回去补觉了！

The block method of estimate is the least accurate. We investigate how many blocks were allocated for the storage segments for the tables specified. If the table had been recently analyzed, the estimate=statistics would be the most accurate. So remember, it is important to generate statistics before calculating the estimate using estimate=statistics method. Additionally, ESTIMATE=BLOCKS method could be inaccurate when:a) The table was created with a much bigger initial extent size than was needed for the actual table data
b) Many rows have been deleted from the table, or a very small percentage of each block is used.
When statistics have been explicitly set via the DBMS_STATS package in order to force certain optimizer plans, instead of being directly collected from the data, ESTIMATE=STATISTICS may not report correct results.

Filed Under: Oracle Tagged With: estimate, expdp, ORA-39095, PARALLELISM, statistics

11g新动态性能视图V$SQL_MONITOR,V$SQL_PLAN_MONITOR

2010/09/16 by admin Leave a Comment

11g中引入了新的动态性能视图V$SQL_MONITOR，该视图用以显示Oracle监视的SQL语句信息。SQL监视会对那些并行执行或者消耗5秒以上cpu时间或I/O时间的SQL语句自动启动，同时在V$SQL_MONITOR视图中产生一条记录。当SQL语句正在执行，V$SQL_MONITOR视图中的统计信息将被实时刷新，频率为每秒1次。SQL语句执行完成后，监视信息将不会被立即删除，Oracle会保证相关记录保存一分钟(由参数_sqlmon_recycle_time所控制，默认为60s)，最终这些记录都会被删除并被重用。这一新的SQL性能监视特性仅在CONTROL_MANAGEMENT_PACK_ACCESS为DIAGNOSTIC+TUNING和STATISTICS_LEVEL为ALL|TYPICAL时被启用。
[Read more…]

Filed Under: Oracle, Oracle 11g新特性 Tagged With: 11g new feature, autotune, dynamic performance view, hidden parameter, optimizer, sql monitor, SQL tuning, statistics

Gather DBMS_STATS Default parameter

2008/07/03 by admin 2 Comments

What are the default parameter values ?

   select dbms_stats.get_param('cascade') from dual;
   select dbms_stats.get_param('degree') from dual;
   select dbms_stats.get_param('estimate_percent') from dual;
   select dbms_stats.get_param('method_opt') from dual;
   select dbms_stats.get_param('no_invalidate') from dual;
   select dbms_stats.get_param('granularity') from dual;


DEFAULT PARAMETER

DBMS_STATS.AUTO_CASCADE
NULL
DBMS_STATS.AUTO_SAMPLE_SIZE
FOR ALL COLUMNS SIZE AUTO
DBMS_STATS.AUTO_INVALIDATE
AUTO

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