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:07:06"
| # | 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 "1340000 KB" as per V$SQL_OPTIMIZER_ENV. | Review the correctness of this non-default value "1340000 KB" for SQL_ID 5zn5rwavxhdcu. |
| 3 | DBMS_STATS | DBA_SCHEDULER_JOBS | 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. |
| 4 | 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. |
| 5 | TABLE | REP.KD_SUBSCRIBER_CONNECT_MONTH | Sample size of 388668 rows may be too small for table with 1554672 rows. | Sample percent used was:25.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 7 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. |
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 | 102844940 | 5142247 | 08-JAN-14 14:02:52 | 4 | 29539215 | 15 | 0 | 4545528 |
| 2 | KD_APP_BILL_MONTH | REP | 3042638 | 3042638 | 01-JAN-14 22:03:07 | 1 | 3042638 | 13 | 0 | 1101917 |
| # | 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 | 101145300 | 5057265 | 08-JAN-14 14:02:58 | 1 | 0 | 5142247 | ||
| 2 | CS_APP | CS | IDX_CS_APP_APP_TIME | CS | YES | 106341880 | 5317094 | 08-JAN-14 14:03:06 | 1 | 0 | 5142247 | |
| 3 | CS_APP | CS | IDX_CS_APP_OPERATOR_ID | CS | 98615900 | 4930795 | 08-JAN-14 14:03:11 | 1 | 0 | 5142247 | ||
| 4 | CS_APP | CS | IDX_PK_CS_APP | CS | YES | 102851706 | 102851706 | 08-JAN-14 17:03:09 | 1 | 0 | 5142247 | |
| 5 | KD_APP_BILL_MONTH | REP | IDX_KD_APP_BILL_MONTH_APPID | REP | YES | 3042638 | 3042638 | 09-JAN-14 15:31:23 | 1 | 0 | 3042638 |
| # | 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 | 101977 | 61380 | 0 | 12 | 46.019 | 10.430 | 42.085 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | ALL_ROWS | 58408 | 1134700906 | SYSTEM | TOAD background query session | 2014-01-17/15:06:15 | 2014-01-17/15:06:15 | 2014-01-17/15:07:02 |
| # | 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 | | 80 | 7440 | 58408 (1)| 00:11:41 | 885K| 885K| 822K (0)|
|* 2 | HASH JOIN OUTER | | 801 | 74493 | 58407 (1)| 00:11:41 | 1023K| 1023K| 1185K (0)|
|* 3 | TABLE ACCESS BY INDEX ROWID | KD_APP_BILL_MONTH | 1 | 28 | 3 (0)| 00:00:01 | | | |
| 4 | NESTED LOOPS | | 801 | 63279 | 56799 (1)| 00:11:22 | | | |
| 5 | NESTED LOOPS | | 580 | 29580 | 55626 (1)| 00:11:08 | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| CS_APP | 580 | 20880 | 54464 (1)| 00:10:54 | | | |
|* 7 | INDEX RANGE SCAN | IDX_CS_APP_APP_TIME | 76569 | | 216 (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 | 1 | | 2 (0)| 00:00:01 | | | |
| 11 | TABLE ACCESS FULL | KD_SUBSCRIBER_CONNECT_MONTH | 1554K| 20M| 1588 (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:07:06