License : "Y" SQL_ID : "5zn5rwavxhdcu" RDBMS : "10.2.0.4.0" Platform: "SOLARIS" OFE : "10.2.0.4" DYN_SAMP: "2" EBS: "" SIEBEL : "" PSFT : "" Date : "2014-01-17/15:14:57"
| # | 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 | _PGA_MAX_SIZE | CBO initialization parameter "_pga_max_size" with a non-default value of "409600 KB" as per V$SQL_OPTIMIZER_ENV. | Review the correctness of this non-default value "409600 KB" for SQL_ID 5zn5rwavxhdcu. |
| 3 | 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. |
| 4 | TABLE | REP.KD_APP_BILL_MONTH | Sample size of 151831 rows may be too small for table with 3036620 rows. | Sample percent used was:5.00%. Consider gathering better quality table statistics with a sample size of 30%. |
| 5 | TABLE | REP.KD_SUBSCRIBER_CONNECT_MONTH | Sample size of 77657 rows may be too small for table with 1553140 rows. | Sample percent used was:5.00%. Consider gathering better quality table statistics with a sample size of 30%. |
| 6 | INDEX | CS.IDX_PK_CS_APP | 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. |
| 7 | INDEX | REP.IDX_KD_APP_BILL_MONTH_APPID | Table/Index CBO statistics out of sync. | Table and index statistics were gathered 1 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. |
| 8 | INDEX | REP.IDX_KD_APP_BILL_MONTH_APPID | 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. |
| 9 | 1-COL INDEX | IDX_KD_APP_BILL_MONTH_APPID(APP_ID) | Number of distinct values (1582638) does not match number of distinct keys (2175315) by 37%. | This is an inconsistency on this indexed column. Gather fresh statistics or adjusting DISTCNT and DENSITY using SET_COLUMN_statistics APIs. |
select dtl.corp_org_id org_id,
sub1.org_name_c branch_name,
sum(case
when dtl.account_book_id = 0 then
dtl.total_charge
else
0
end) / 100 Ò»´ÎÐÔÍË·Ñ
from cs.cs_app app
inner join cs.cs_app appr on app.relat_app_id =
appr.app_id
inner join rep.kd_app_bill_month dtl on dtl.app_id= app.app_id
left join rep.kd_subscriber_connect_month sub1 on sub1.subscriber_id=dtl.subscriber_id
where app.app_action in ('CS_CANCEL', 'CS_REVERSE')
and dtl.busy_type =2
and appr.app_time < date'2013-12-01'
and app.app_time between date'2013-12-01' and date'2013-12-31'+1
group by dtl.corp_org_id,sub1.org_name_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 | CS_APP | CS | 102841600 | 5142080 | 08-JAN-14 14:09:47 | 4 | 29595374 | 15 | 0 | 4545293 |
| 2 | KD_APP_BILL_MONTH | REP | 3036620 | 151831 | 08-JAN-14 14:13:36 | 1 | 3042638 | 13 | 0 | 151598 |
| # | 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 | CS_APP | CS | IDX_CS_APP_APP_CODE | CS | 105827900 | 5291395 | 08-JAN-14 14:09:53 | 1 | 0 | 5142080 | ||
| 2 | CS_APP | CS | IDX_CS_APP_APP_TIME | CS | YES | 101703940 | 5085197 | 08-JAN-14 14:10:01 | 1 | 0 | 5142080 | |
| 3 | CS_APP | CS | IDX_CS_APP_OPERATOR_ID | CS | 103064520 | 5153226 | 08-JAN-14 14:10:08 | 1 | 0 | 5142080 | ||
| 4 | CS_APP | CS | IDX_PK_CS_APP | CS | YES | 102851676 | 102851676 | 08-JAN-14 17:01:41 | 1 | 0 | 5142080 | |
| 5 | KD_APP_BILL_MONTH | REP | IDX_KD_APP_BILL_MONTH_APPID | REP | YES | 3042638 | 3042638 | 09-JAN-14 15:31:07 | 1 | 0 | 151831 |
| # | 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 | 1974753989 | 1 | 1 | 1 | 0 | 1 | 2782643 | 61188 | 0 | 12 | 165.992 | 18.540 | 155.031 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | ALL_ROWS | 63717 | 2513728184 | SYSTEM | TOAD background query session | 2014-01-17/15:11:32 | 2014-01-17/15:11:32 | 2014-01-17/15:14:22 |
| # | 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: 1974753989
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 91 | 8463 | 63717 (1)| 00:12:45 | 885K| 885K| 818K (0)|
|* 2 | HASH JOIN OUTER | | 1091 | 99K| 63716 (1)| 00:12:45 | 1023K| 1023K| 1185K (0)|
|* 3 | TABLE ACCESS BY INDEX ROWID | KD_APP_BILL_MONTH | 2 | 56 | 4 (0)| 00:00:01 | | | |
| 4 | NESTED LOOPS | | 1091 | 86189 | 62093 (1)| 00:12:26 | | | |
| 5 | NESTED LOOPS | | 576 | 29376 | 60922 (1)| 00:12:12 | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| CS_APP | 576 | 20736 | 59769 (1)| 00:11:58 | | | |
|* 7 | INDEX RANGE SCAN | IDX_CS_APP_APP_TIME | 76566 | | 206 (1)| 00:00:03 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID| CS_APP | 1 | 15 | 2 (0)| 00:00:01 | | | |
|* 9 | INDEX UNIQUE SCAN | IDX_PK_CS_APP | 1 | | 1 (0)| 00:00:01 | | | |
|* 10 | INDEX RANGE SCAN | IDX_KD_APP_BILL_MONTH_APPID | 2 | | 2 (0)| 00:00:01 | | | |
| 11 | TABLE ACCESS FULL | KD_SUBSCRIBER_CONNECT_MONTH | 1553K| 20M| 1603 (3)| 00:00:20 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$B331CAF9
3 - SEL$B331CAF9 / DTL@SEL$2
6 - SEL$B331CAF9 / APP@SEL$1
7 - SEL$B331CAF9 / APP@SEL$1
8 - SEL$B331CAF9 / APPR@SEL$1
9 - SEL$B331CAF9 / APPR@SEL$1
10 - SEL$B331CAF9 / DTL@SEL$2
11 - SEL$B331CAF9 / SUB1@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$B331CAF9")
MERGE(@"SEL$7237DA6D")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$7237DA6D")
MERGE(@"SEL$3")
MERGE(@"SEL$58A6D7F6")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$58A6D7F6")
MERGE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$B331CAF9" "APP"@"SEL$1" ("CS_APP"."APP_TIME"))
INDEX_RS_ASC(@"SEL$B331CAF9" "APPR"@"SEL$1" ("CS_APP"."APP_ID"))
INDEX(@"SEL$B331CAF9" "DTL"@"SEL$2" ("KD_APP_BILL_MONTH"."APP_ID"))
FULL(@"SEL$B331CAF9" "SUB1"@"SEL$3")
LEADING(@"SEL$B331CAF9" "APP"@"SEL$1" "APPR"@"SEL$1" "DTL"@"SEL$2" "SUB1"@"SEL$3")
USE_NL(@"SEL$B331CAF9" "APPR"@"SEL$1")
USE_NL(@"SEL$B331CAF9" "DTL"@"SEL$2")
USE_HASH(@"SEL$B331CAF9" "SUB1"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SUB1"."SUBSCRIBER_ID"="DTL"."SUBSCRIBER_ID")
3 - filter("DTL"."BUSY_TYPE"=2)
6 - filter(("APP"."APP_ACTION"='CS_CANCEL' OR "APP"."APP_ACTION"='CS_REVERSE'))
7 - access("APP"."APP_TIME">=TO_DATE(' 2013-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "APP"."APP_TIME"<=TO_DATE('
2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
8 - filter("APPR"."APP_TIME"
Current Execution Plans (all executions)
Captured while still in memory. Metrics below are an aggregate for all the execution of each child cursor.
If STATISTICS_LEVEL was set to ALL at the time of the hard-parse then A-Rows column is populated.
Historical Execution Plans
Captured by AWR.
1366133.1 SQLHC 11.4.4.1 2014-01-17/15:14:57