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