1366133.1 SQLHC 12.1.06 Report: sqlhc_20150304_114844_00pspjfdy328d_1_health_check.html

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

Observations

Observations below are the outcome of several heath-checks on the schema objects accessed by your SQL and its environment. Review them carefully and take action when appropriate. Then re-execute your SQL and generate this report again.
# 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

SQL Text

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) )

Tables Summary

Values below have two purposes:
1. Provide a quick view of the state of Table level CBO statistics, as well as their indexes and columns.
2. More easily allow the comparison of two systems that are believed to be similar.
# 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

Indexes Summary

Values below have two purposes:
1. Provide a quick view of the state of Index level CBO statistics, as well as their columns.
2. More easily allow the comparison of two systems that are believed to be similar.
This section includes data captured by AWR. If this is a stand-by read-only database then the AWR information below is from the Primary database.
# 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

1366133.1 SQLHC 12.1.06 2015-03-04/11:48:44 tool_date: 2014/01/30 executed by: SYS