License : T Input : 00pspjfdy328d SIGNATURE : 16432764016050952009 SIGNATUREF : 13626564656978028 RDBMS : 11.2.0.3.0 Platform : IBM/AIX RISC SYSTEM/6000 Database : bdpdb DBID : 3936693823 Host : bdpdbserver Instance : 1 CPU_Count : 24 Num CPUs : 24 Num Cores : 6 Num Sockets: Block Size : 16384 OFE : 11.2.0.3 DYN_SAMP : 2 EBS : "" SIEBEL : "" PSFT : "" Date : 2015-03-04/11:48:44 User : SYS
| # | Type | Name | Observation | Details |
|---|---|---|---|---|
| 1 | CBO PARAMETER | DEFERRED_SEGMENT_CREATION | CBO initialization parameter "deferred_segment_creation" with a non-default value of "false" as per V$SYS_OPTIMIZER_ENV. | Review the correctness of this non-default value "false". Unset this parameter unless there is a strong reason for keeping its current value. Default value is "true" as per V$SYS_OPTIMIZER_ENV. |
| 2 | CBO PARAMETER | OPTIMIZER_DYNAMIC_SAMPLING | Dynamic Sampling is set to small value of 2 as per V$SYSTEM_PARAMETER2. | Be aware that using such a small value may produce statistics of poor quality. If you rely on this functionality consider using a value no smaller than 4. |
| 3 | CBO PARAMETER | _OPTIMIZER_DISTINCT_AGG_TRANSFORM | CBO initialization parameter "_optimizer_distinct_agg_transform" with a non-default value of "false" as per V$SYS_OPTIMIZER_ENV. | Review the correctness of this non-default value "false". Unset this parameter unless there is a strong reason for keeping its current value. Default value is "true" as per V$SYS_OPTIMIZER_ENV. |
| 4 | CBO PARAMETER | _OPTIMIZER_USE_FEEDBACK | CBO initialization parameter "_optimizer_use_feedback" with a non-default value of "false" as per V$SYS_OPTIMIZER_ENV. | Review the correctness of this non-default value "false". Unset this parameter unless there is a strong reason for keeping its current value. Default value is "true" as per V$SYS_OPTIMIZER_ENV. |
| 5 | CBO PARAMETER | _PGA_MAX_SIZE | CBO initialization parameter "_pga_max_size" with a non-default value of "2097152 KB" as per V$SYS_OPTIMIZER_ENV. | Review the correctness of this non-default value "2097152 KB". Unset this parameter unless there is a strong reason for keeping its current value. Default value is "204800 KB" as per V$SYS_OPTIMIZER_ENV. |
| 6 | DBMS_STATS | DBA_AUTOTASK_CLIENT | Automatic gathering of CBO statistics is enabled. | Be aware that small sample sizes could produce poor quality histograms, which combined with bind sensitive predicates could render suboptimal plans. See 465787.1. |
| 7 | DBMS_STATS | DBA_AUTOTASK_CLIENT | Automatic gathering of CBO statistics is enabled but some job did not complete in the last 8 days. |
The job is enabled in the system but there are some jobs in the last 8 days that did not complete. |
| 8 | DBMS_STATS | SYSTEM STATISTICS | Multi-block read time of 4.401ms seems too small compared to single-block read time of 4.122ms. | Consider gathering workload system statistics using DBMS_STATS.GATHER_SYSTEM_STATS or adjusting SREADTIM and MREADTIM using DBMS_STATS.SET_SYSTEM_STATS. See also 465787.1. |
| 9 | PLAN | OPTIMIZER_ENV | AWR references 1 distinct CBO Enviornments for this one SQL. | Distinct CBO Environments may produce different Plans. |
| 10 | TABLE | ODS.CUP_ACCOUNT | Table lacks CBO Statistics. | Consider gathering table statistics using DBMS_STATS.GATHER_TABLE_STATS. See 465787.1. |
| 11 | TABLE | ODS.CUP_ACCOUNT | Table has 2 CBO statistics extension(s). | Review table statistics extensions. Extensions can be used for expressions or column groups. If your SQL contain matching predicates these extensions can influence the CBO. |
| 12 | 1-COL INDEX | IDX_CUP_ACCOUNT_EDT(BDP_ETL_DATE) | Lack of CBO statistics in column of this single-column index. | To avoid CBO guessed statistics on this indexed column, gather table statistics and include this column in METHOD_OPT used. |
| # | Type | Name | Observation | Details |
DELETE FROM DDS.CUP_ACCOUNT T1 WHERE EXISTS( SELECT 1 FROM ODS.CUP_ACCOUNT T2 WHERE T2.BDP_ETL_DATE = '20150201' AND T1.ACCT_NMBR = SUBSTR(T2.CARD_NMBR,1,13) AND T1.CURR_CODE = TRIM(T2.CURR_CODE) )
| # | Table Name | Owner | Num Rows | Table Sample Size |
Last Analyzed | Indexes | Avg Index Sample Size |
Table Columns |
Columns with Histogram |
Avg Column Sample Size |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | CUP_ACCOUNT | DDS | 14027490 | 1402749 | 04-MAR-15 03:04:07 | 2 | 698574 | 167 | 3 | 1128289 |
| 2 | CUP_ACCOUNT | ODS | 2 | 3962528 | 165 | 0 | ||||
| # | Table Name | Owner | Num Rows | Table Sample Size |
Last Analyzed | Indexes | Avg Index Sample Size |
Table Columns |
Columns with Histogram |
Avg Column Sample Size |
| # | Table Name | Table Owner |
Index Name | Index Owner |
In MEM Plan |
In AWR Plan |
Num Rows | Index Sample Size |
Last Analyzed | Index Columns |
Columns with Histogram |
Avg Column Sample Size |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | CUP_ACCOUNT | DDS | IDX_CUP_ACCOUNT_ANBR_CC | DDS | 13966690 | 1396669 | 04-MAR-15 03:04:44 | 2 | 1 | 1402749 | ||
| 2 | CUP_ACCOUNT | DDS | IDX_CUP_ACCOUNT_MDT | DDS | 478 | 478 | 04-MAR-15 03:04:40 | 1 | 1 | 1402749 | ||
| 3 | CUP_ACCOUNT | ODS | IDX_CUP_ACCOUNT_EDT | ODS | 614 | 614 | 04-MAR-15 06:12:55 | 1 | 0 | |||
| 4 | CUP_ACCOUNT | ODS | IDX_CUP_ACCOUNT_EDT_CNMBR_CCD | ODS | YES | 7924442 | 7924442 | 04-MAR-15 06:13:42 | 3 | 0 | ||
| # | Table Name | Table Owner |
Index Name | Index Owner |
In MEM Plan |
In AWR Plan |
Num Rows | Index Sample Size |
Last Analyzed | Index Columns |
Columns with Histogram |
Avg Column Sample Size |