1366133.1 SQLHC 11.4.5.2 Report: sqlhc_20121226_180358_f995z9antmhxn_1_health_check.html

License   : T
Input     : f995z9antmhxn
SIGNATURE : 1178211779310957485
SIGNATUREF: 1178211779310957485
RDBMS     : 11.2.0.3.0
Platform  : 64-BIT WINDOWS
Database  : orcl
DBID      : 1311055076
Host      : csierraus
Instance  : 1
CPU Count : 4
Block Size: 8192
OFE       : 11.2.0.3
DYN_SAMP  : 2
EBS       : ""
SIEBEL    : ""
PSFT      : ""
Date      : 2012-12-26/18:03:59

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 More
1 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.
2 DBMS_STATS SYSTEM STATISTICS Workload CBO System Statistics are not gathered. CBO is using default values. Consider gathering workload system statistics using DBMS_STATS.GATHER_SYSTEM_STATS.
See also 465787.1.
3 MAT_VIEW REWRITE_ENABLED There are 2 materialized views with rewrite enabled. A large number of materialized views could affect parsing time since CBO would have to evaluate each during a hard-parse.
4 OPTIMZER MODE FIRST_ROWS OPTIMIZER_MODE was set to FIRST_ROWS in 3 Plan(s). The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.
Using heuristics sometimes leads the query optimizer to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic.
FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead.
5 PLAN OPTIMIZER_ENV AWR references 10 distinct CBO Enviornments for this one SQL. Distinct CBO Environments may produce different Plans.
6 PLAN PLAN_HASH_VALUE Plan 2816325939 has operations with Cost 0 and Card 1. Possible incorrect Selectivity. Review Execution Plans.
Look for Plan operations in 2816325939 where Cost is 0 and Estimated Cardinality is 1.
Suspect predicates out of range or incorrect statistics.
7 PLAN PLAN_HASH_VALUE Plan 2903038705 has operations with Cost 0 and Card 1. Possible incorrect Selectivity. Review Execution Plans.
Look for Plan operations in 2903038705 where Cost is 0 and Estimated Cardinality is 1.
Suspect predicates out of range or incorrect statistics.
8 PLAN PLAN_HASH_VALUE Plan 3322900607 has operations with Cost 0 and Card 1. Possible incorrect Selectivity. Review Execution Plans.
Look for Plan operations in 3322900607 where Cost is 0 and Estimated Cardinality is 1.
Suspect predicates out of range or incorrect statistics.
9 PLAN PLAN_HASH_VALUE Plan 3427378629 has operations with Cost 0 and Card 1. Possible incorrect Selectivity. Review Execution Plans.
Look for Plan operations in 3427378629 where Cost is 0 and Estimated Cardinality is 1.
Suspect predicates out of range or incorrect statistics.
10 PLAN PLAN_HASH_VALUE Plan 1503269649 has operations with Cost 0 and Card 1. Possible incorrect Selectivity. Review Execution Plans.
Look for Plan operations in 1503269649 where Cost is 0 and Estimated Cardinality is 1.
Suspect predicates out of range or incorrect statistics.
11 PLAN PLAN_HASH_VALUE Plan 1186590923 has operations with Cost 0 and Card 1. Possible incorrect Selectivity. Review Execution Plans.
Look for Plan operations in 1186590923 where Cost is 0 and Estimated Cardinality is 1.
Suspect predicates out of range or incorrect statistics.
12 PLAN PLAN_HASH_VALUE Plan 657302870 has operations with Cost 0 and Card 1. Possible incorrect Selectivity. Review Execution Plans.
Look for Plan operations in 657302870 where Cost is 0 and Estimated Cardinality is 1.
Suspect predicates out of range or incorrect statistics.
13 PLAN PLAN_HASH_VALUE Plan 142578110 has operations with Cost 0 and Card 1. Possible incorrect Selectivity. Review Execution Plans.
Look for Plan operations in 142578110 where Cost is 0 and Estimated Cardinality is 1.
Suspect predicates out of range or incorrect statistics.
14 PLAN PLAN_HASH_VALUE Plan 24296316 has operations with Cost 0 and Card 1. Possible incorrect Selectivity. Review Execution Plans.
Look for Plan operations in 24296316 where Cost is 0 and Estimated Cardinality is 1.
Suspect predicates out of range or incorrect statistics.
15 PLAN PLAN_HASH_VALUE Plan 3267771367 has operations with Cost 0 and Card 1. Possible incorrect Selectivity. Review Execution Plans.
Look for Plan operations in 3267771367 where Cost is 0 and Estimated Cardinality is 1.
Suspect predicates out of range or incorrect statistics.
16 VERSION COUNT VERSION COUNT This SQL shows evidence of high version count of 41. Review Execution Plans for details.
17 TABLE QTUNE.CUSTOMER Table has 1 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.
18 TABLE QTUNE.PART Table has 1 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.
19 TABLE QTUNE.SALES_ORDER Table has 1 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.
20 TABLE QTUNE.SALES_ORDER Table contains 1 column(s) where the number of distinct values does not match the number of buckets. Review column statistics for this table and look for "Num Distinct" and "Num Buckets". If there are values missing from the frecuency histogram you may have Bug 10174050.
If you are referencing in your predicates one of the missing values the CBO can over estimate table cardinality, and this may produce a sub-optimal plan.
You can either gather statistics with 100% or as a workaround: ALTER system/session "_fix_control"='5483301:OFF';
# Type Name Observation More

SQL Text

SELECT /*+ gather_plan_statistics monitor bind_aware */
v.customer_name,
v.orders_total,
v.credit_limit,
(orders_total - credit_limit) over_limit
FROM customer_v v
WHERE orders_total > credit_limit
AND customer_type = :b1
ORDER BY
over_limit DESC

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. Ease a compare between 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 CUSTOMER QTUNE 100000 100000 21-DEC-12 10:05:20 4 100000 5 2 62367
2 ORDER_LINE QTUNE 3450201 3450201 21-DEC-12 10:05:26 3 508768 6 0 2990174
3 PART QTUNE 200000 200000 21-DEC-12 10:05:23 2 200000 6 0 200000
4 SALES_ORDER QTUNE 300000 300000 21-DEC-12 10:05:23 3 300000 6 1 250894
# 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. Ease a compare between 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 CUSTOMER QTUNE CUSTOMER_F1 QTUNE 100000 100000 21-DEC-12 10:05:22 1 0 100000
2 CUSTOMER QTUNE CUSTOMER_N1 QTUNE 100000 100000 21-DEC-12 10:05:22 1 0 100000
3 CUSTOMER QTUNE CUSTOMER_N2 QTUNE YES 100000 100000 21-DEC-12 10:05:22 2 2 5918
4 CUSTOMER QTUNE CUSTOMER_PK QTUNE YES YES 100000 100000 21-DEC-12 10:05:22 1 0 100000
5 ORDER_LINE QTUNE ORDER_LINE_N1 QTUNE YES 3400013 499714 21-DEC-12 10:05:28 1 0 3450201
6 ORDER_LINE QTUNE ORDER_LINE_N2 QTUNE 3555812 523416 21-DEC-12 10:05:29 1 0 3450201
7 ORDER_LINE QTUNE ORDER_LINE_PK QTUNE 3366619 503175 21-DEC-12 10:05:27 1 0 3450201
8 PART QTUNE PART_N1 QTUNE 200000 200000 21-DEC-12 10:05:23 1 0 200000
9 PART QTUNE PART_PK QTUNE YES 200000 200000 21-DEC-12 10:05:23 1 0 200000
10 SALES_ORDER QTUNE SALES_ORDER_N1 QTUNE 300000 300000 21-DEC-12 10:05:24 1 0 300000
11 SALES_ORDER QTUNE SALES_ORDER_N2 QTUNE YES 300000 300000 21-DEC-12 10:05:25 2 0 300000
12 SALES_ORDER QTUNE SALES_ORDER_PK QTUNE 300000 300000 21-DEC-12 10:05:24 1 0 300000
# 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 11.4.5.2 2012-12-26/18:03:59