License : "Y" SQL_ID : "b7rngj2cx4v07" RDBMS : "10.2.0.5.0" Platform: "SOLARIS" OFE : "10.2.0.5" DYN_SAMP: "2" EBS: "" SIEBEL : "" PSFT : "" Date : "2012-05-23/10:38:22"
| # | Type | Name | Observation | More |
|---|---|---|---|---|
| 1 | CBO PARAMETER | DB_FILE_MULTIBLOCK_READ_COUNT | MBRC Parameter is set to "16" overriding its default value. | The default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms. Because the parameter is expressed in blocks, it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size. |
| 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_INDEX_COST_ADJ | CBO initialization parameter "optimizer_index_cost_adj" with a non-default value of "10" as per V$SQL_OPTIMIZER_ENV. | Review the correctness of this non-default value "10" for SQL_ID b7rngj2cx4v07. |
| 4 | CBO PARAMETER | OPTIMIZER_MODE | CBO initialization parameter "optimizer_mode" with a non-default value of "choose" as per V$SQL_OPTIMIZER_ENV. | Review the correctness of this non-default value "choose" for SQL_ID b7rngj2cx4v07. |
| 5 | CBO PARAMETER | PARALLEL_EXECUTION_ENABLED | CBO initialization parameter "parallel_execution_enabled" with a non-default value of "false" as per V$SQL_OPTIMIZER_ENV. | Review the correctness of this non-default value "false" for SQL_ID b7rngj2cx4v07. |
| 6 | CBO PARAMETER | QUERY_REWRITE_ENABLED | CBO initialization parameter "query_rewrite_enabled" with a non-default value of "false" as per V$SQL_OPTIMIZER_ENV. | Review the correctness of this non-default value "false" for SQL_ID b7rngj2cx4v07. |
| 7 | CBO PARAMETER | _PGA_MAX_SIZE | CBO initialization parameter "_pga_max_size" with a non-default value of "819200 KB" as per V$SQL_OPTIMIZER_ENV. | Review the correctness of this non-default value "819200 KB" for SQL_ID b7rngj2cx4v07. |
| 8 | 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. |
| 9 | TABLE | NJSJFX.SDTYPE | Table lacks CBO Statistics. | Consider gathering table statistics using DBMS_STATS.GATHER_TABLE_STATS. See 465787.1. |
| 10 | TABLE | NJSJFX.TABLE_CR | Table has more empty blocks (1770932) than actual blocks (113292) according to CBO statistics. | Review Table statistics and consider re-organizing this Table. |
| 11 | TABLE | NJSJFX.TABLE_CR | Table has stale statistics. | Consider gathering table statistics using DBMS_STATS.GATHER_TABLE_STATS. See 465787.1. |
| 12 | INDEX | NJSJFX.SDTYPE_TIME | CBO statistics were either gathered using deprecated ANALYZE command or derived by aggregation from lower level objects. | When ANALYZE is used on a non-partitioned index, the global_stats column of the index statistics receives a value of 'NO'. Same is true when statistics were derived by aggregation from lower level objects. Consider gathering statistics using DBMS_STATS instead. See 465787.1. |
| 13 | INDEX | NJSJFX.TABLE_PK | Table/Index CBO statistics out of sync. | Table and index statistics were gathered 141 day(s) appart, so they do not offer a consistent view to the CBO. Consider gathering table and index statistics using DBMS_STATS.GATHER_TABLE_STATS using CASCADE=>TRUE. See also 465787.1. |
| 14 | INDEX | NJSJFX.TABLE_PK | CBO statistics were either gathered using deprecated ANALYZE command or derived by aggregation from lower level objects. | When ANALYZE is used on a non-partitioned index, the global_stats column of the index statistics receives a value of 'NO'. Same is true when statistics were derived by aggregation from lower level objects. Consider gathering statistics using DBMS_STATS instead. See 465787.1. |
| 15 | 1-COL INDEX | SDTYPE_TIME(ETIME) | 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. |
| 16 | 1-COL INDEX | TABLE_PK(ID) | Single-column index with number of distinct values greater than number of rows by 176%. | There cannot be a larger number of distinct values (17044121) in a column than actual rows (6164840) in the index. This is an inconsistency on this indexed column. Consider gathering table statistics using a large sample size. |
| 17 | TABLE PARTITION | NJSJFX.TABLE_CR | 81 out of 174 partition(s) lack(s) CBO statistics. | Consider gathering statistics using DBMS_STATS.GATHER_TABLE_STATISTICS. See 465787.1. |
| 18 | TABLE PARTITION | NJSJFX.TABLE_CR | 2 out of 174 partition(s) with number of rows equal to zero according to partition's CBO statistics. | If these table partitions are not empty, consider gathering table statistics using GRANULARITY=>GLOBAL AND PARTITION. |
| 19 | TABLE PARTITION | NJSJFX.TABLE_CR | 7 column(s) lack(s) partition level CBO statistics. | Consider gathering statistics using DBMS_STATS.GATHER_TABLE_STATISTICS. See 465787.1. |
SELECT NVL(A.TIMELENGTH,0),NVL(A.COUNT,0),NVL(A.CREATE_TIME,B.ENDTIME) C,NVL(A.RTGCOUNT,0) FROM(SELECT SUM(SUM_TIMELENGTH) TIMELENGTH,TO_CHAR(CREATE_TIME,'YYYY-MM-DD HH24:MI:SS') CREATE_TIME,SUM(NUM_OF_FRE) COUNT ,SUM(NUM_OF_RTG) RTGCOUNT FROM TABLE_CR WHERE ADDRESS IN('建邺 ','玄武 ','栖霞 ','白下 ','江宁 ','','下关 ','秦淮 ','鼓楼 ','浦口 ','六合 ','其它 ') AND CREATE_TIME>=TO_DATE('2012-05-22','YYYY-MM-DD') AND CREATE_TIME<=TO_DATE('2012-05-23','YYYY-MM-DD') AND TO_CHAR(CREATE_TIME,'HH24:MI:SS') BETWEEN '00:00:00'AND '23:59:59' AND CHANNEL_NAME='江苏卫视' GROUP BY CREATE_TIME)A,(SELECT TO_CHAR(ENDTIME,'YYYY-MM-DD HH24:MI:SS') ENDTIME FROM (SELECT TO_DATE('2012-05-22 '||ETIME,'YYYY-MM-DD HH24:MI:SS') ENDTIME FROM SDTYPE WHERE ETIME BETWEEN '00:00:00' AND '23:59:59'
))B WHERE A.CREATE_TIME(+)=B.ENDTIME ORDER BY C
| # | 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 | SDTYPE | NJSJFX | 1 | 288 | 3 | 0 | ||||
| 2 | TABLE_CR | NJSJFX | 17044121 | 0 | 31-12月-11 14:49:08 | 1 | 6164840 | 7 | 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 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SDTYPE | NJSJFX | SDTYPE_TIME | NJSJFX | YES | 288 | 288 | 21-5月 -12 13:42:30 | 1 | 0 | ||
| 2 | TABLE_CR | NJSJFX | TABLE_PK | NJSJFX | 6164840 | 6164840 | 21-5月 -12 11:42:11 | 1 | 0 |
| # | Inst ID |
Child Num |
Plan HV | Execs | Fetch | Loads | Inval | Parse Calls |
Buffer Gets |
Disk Reads |
Direct Writes |
Rows Proc |
Elapsed Time (secs) |
CPU Time (secs) |
IO Time (secs) |
Conc Time (secs) |
Appl Time (secs) |
Clus Time (secs) |
PLSQL Time (secs) |
Java Time (secs) |
Optimizer Mode |
Cost | Opt Env HV | Parsing Schema Name |
Module | Action | Outline | Profile | First Load | Last Load | Last Active |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 0 | 860405492 | 9 | 261 | 1 | 0 | 9 | 597666 | 346 | 0 | 2592 | 30.523 | 30.540 | 0.711 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | CHOOSE | 15771 | 1641369505 | NJSJFX | 2012-05-23/10:29:08 | 2012-05-23/10:29:08 | 2012-05-23/10:29:45 |
| # | Snap ID |
Snaphot | Inst ID |
Plan HV | Vers Cnt |
Execs | Fetch | Loads | Inval | Parse Calls |
Buffer Gets |
Disk Reads |
Direct Writes |
Rows Proc |
Elapsed Time (secs) |
CPU Time (secs) |
IO Time (secs) |
Conc Time (secs) |
Appl Time (secs) |
Clus Time (secs) |
PLSQL Time (secs) |
Java Time (secs) |
Optimizer Mode |
Cost | Opt Env HV | Parsing Schema Name |
Module | Action | Profile |
|---|
Inst: 1 Child: 0 Plan hash value: 860405492
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15771 (100)| | | | | | |
| 1 | SORT ORDER BY | | 288 | 17280 | 15771 (1)| 00:03:10 | | | 31744 | 31744 |28672 (0)|
|* 2 | HASH JOIN OUTER | | 288 | 17280 | 15770 (1)| 00:03:10 | | | 933K| 933K| 1201K (0)|
|* 3 | INDEX RANGE SCAN | SDTYPE_TIME | 288 | 2880 | 1 (0)| 00:00:01 | | | | | |
| 4 | VIEW | | 1 | 50 | 15768 (1)| 00:03:10 | | | | | |
| 5 | HASH GROUP BY | | 1 | 77 | 15768 (1)| 00:03:10 | | | 789K| 789K| 1231K (0)|
| 6 | PARTITION RANGE SINGLE| | 1 | 77 | 15767 (1)| 00:03:10 | 174 | 174 | | | |
|* 7 | TABLE ACCESS FULL | TABLE_CR | 1 | 77 | 15767 (1)| 00:03:10 | 174 | 174 | | | |
--------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2F74276C
3 - SEL$2F74276C / SDTYPE@SEL$4
4 - SEL$2 / A@SEL$1
5 - SEL$2
7 - SEL$2 / TABLE_CR@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
OPT_PARAM('query_rewrite_enabled' 'false')
OPT_PARAM('optimizer_index_cost_adj' 10)
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$2F74276C")
MERGE(@"SEL$07BDC5B4")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$07BDC5B4")
MERGE(@"SEL$4")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$4")
INDEX(@"SEL$2F74276C" "SDTYPE"@"SEL$4" ("SDTYPE"."ETIME"))
NO_ACCESS(@"SEL$2F74276C" "A"@"SEL$1")
LEADING(@"SEL$2F74276C" "SDTYPE"@"SEL$4" "A"@"SEL$1")
USE_HASH(@"SEL$2F74276C" "A"@"SEL$1")
FULL(@"SEL$2" "TABLE_CR"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."CREATE_TIME"=TO_CHAR(TO_DATE('2012-05-22 '||"ETIME",'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'))
3 - access("ETIME">='00:00:00' AND "ETIME"<='23:59:59')
7 - filter((INTERNAL_FUNCTION("ADDRESS") AND "CHANNEL_NAME"='江苏卫视' AND "CREATE_TIME">=TO_DATE(' 2012-05-22 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "CREATE_TIME"<=TO_DATE(' 2012-05-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
TO_CHAR(INTERNAL_FUNCTION("CREATE_TIME"),'HH24:MI:SS')>='00:00:00' AND
TO_CHAR(INTERNAL_FUNCTION("CREATE_TIME"),'HH24:MI:SS')<='23:59:59'))
Note
-----
- dynamic sampling used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Inst: 1 Child: 0 Plan hash value: 860405492
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | OMem | 1Mem | O/1/M |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15771 (100)| | | | | | |
| 1 | SORT ORDER BY | | 288 | 17280 | 15771 (1)| 00:03:10 | | | 31744 | 31744 | 9/0/0|
|* 2 | HASH JOIN OUTER | | 288 | 17280 | 15770 (1)| 00:03:10 | | | 933K| 933K| 9/0/0|
|* 3 | INDEX RANGE SCAN | SDTYPE_TIME | 288 | 2880 | 1 (0)| 00:00:01 | | | | | |
| 4 | VIEW | | 1 | 50 | 15768 (1)| 00:03:10 | | | | | |
| 5 | HASH GROUP BY | | 1 | 77 | 15768 (1)| 00:03:10 | | | 789K| 789K| 9/0/0|
| 6 | PARTITION RANGE SINGLE| | 1 | 77 | 15767 (1)| 00:03:10 | 174 | 174 | | | |
|* 7 | TABLE ACCESS FULL | TABLE_CR | 1 | 77 | 15767 (1)| 00:03:10 | 174 | 174 | | | |
--------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2F74276C
3 - SEL$2F74276C / SDTYPE@SEL$4
4 - SEL$2 / A@SEL$1
5 - SEL$2
7 - SEL$2 / TABLE_CR@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
OPT_PARAM('query_rewrite_enabled' 'false')
OPT_PARAM('optimizer_index_cost_adj' 10)
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$2F74276C")
MERGE(@"SEL$07BDC5B4")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$07BDC5B4")
MERGE(@"SEL$4")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$4")
INDEX(@"SEL$2F74276C" "SDTYPE"@"SEL$4" ("SDTYPE"."ETIME"))
NO_ACCESS(@"SEL$2F74276C" "A"@"SEL$1")
LEADING(@"SEL$2F74276C" "SDTYPE"@"SEL$4" "A"@"SEL$1")
USE_HASH(@"SEL$2F74276C" "A"@"SEL$1")
FULL(@"SEL$2" "TABLE_CR"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."CREATE_TIME"=TO_CHAR(TO_DATE('2012-05-22 '||"ETIME",'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'))
3 - access("ETIME">='00:00:00' AND "ETIME"<='23:59:59')
7 - filter((INTERNAL_FUNCTION("ADDRESS") AND "CHANNEL_NAME"='江苏卫视' AND "CREATE_TIME">=TO_DATE(' 2012-05-22 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "CREATE_TIME"<=TO_DATE(' 2012-05-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
TO_CHAR(INTERNAL_FUNCTION("CREATE_TIME"),'HH24:MI:SS')>='00:00:00' AND
TO_CHAR(INTERNAL_FUNCTION("CREATE_TIME"),'HH24:MI:SS')<='23:59:59'))
Note
-----
- dynamic sampling used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level