License : "N" SQL_ID : "8mcfgzdas2amj" RDBMS : "10.2.0.4.0" Platform: "LINUX" OFE : "10.2.0.4" DYN_SAMP: "2" EBS: "" SIEBEL : "" PSFT : "" Date : "2012-05-14/10:02:13"
| # | 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 "2097140 KB" as per V$SQL_OPTIMIZER_ENV. | Review the correctness of this non-default value "2097140 KB" for SQL_ID 8mcfgzdas2amj. |
| 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 | HSEAS.T_GL_ACCOUNTBALANCE | Table contains column(s) with outdated CBO statistics for up to 2 day(s). | CBO table and column statistics are inconsistent. Consider gathering statistics for this table. Old statistics could contain low/high values for which a predicate may be out of range, producing then a poor plan. |
| 6 | TABLE | HSEAS.T_GL_ACCOUNTBALANCE | Table contains 1 long CHAR column(s) with Histogram. Number of distinct values (NDV) could be incorrect. | Possible Bug 9885553. When building histogram for a varchar column that is long, we only use its first 32 characters. Two distinct values that share the same first 32 characters are deemed the same in the histogram. Therefore the NDV derived from the histogram is inaccurate. If NDV is wrong then drop the Histogram. |
| 7 | TABLE | HSEAS.T_GL_ACCOUNTBALANCE | Table contains 1 column(s) with no popular values on a "HEIGHT BALANCED" histogram. | A Height-balanced histogram with no popular values is not helpful nor desired. Consider dropping this histogram by collecting new CBO statistics while using METHOD_OPT with SIZE 1. |
| 8 | TABLE | HSEAS.T_GL_VOUCHER | Table contains 3 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'; |
| 9 | TABLE | HSEAS.T_GL_VOUCHER | Table contains 4 column(s) where the number of buckets is 1 for a "FREQUENCY" histogram. | Review column statistics for this table and look for "Num Buckets" and "Histogram". Possible Bugs 1386119, 4406309, 4495422, 4567767, 5483301 or 6082745. 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'; |
| 10 | TABLE | HSEAS.T_GL_VOUCHERENTRY | Table contains column(s) with outdated CBO statistics for up to 72 day(s). | CBO table and column statistics are inconsistent. Consider gathering statistics for this table. Old statistics could contain low/high values for which a predicate may be out of range, producing then a poor plan. |
| 11 | TABLE | HSEAS.T_GL_VOUCHERENTRY | Table contains 1 long CHAR column(s) with Histogram. Number of distinct values (NDV) could be incorrect. | Possible Bug 9885553. When building histogram for a varchar column that is long, we only use its first 32 characters. Two distinct values that share the same first 32 characters are deemed the same in the histogram. Therefore the NDV derived from the histogram is inaccurate. If NDV is wrong then drop the Histogram. |
| 12 | TABLE | HSEAS.T_GL_VOUCHERENTRY | Table contains 3 column(s) with no popular values on a "HEIGHT BALANCED" histogram. | A Height-balanced histogram with no popular values is not helpful nor desired. Consider dropping this histogram by collecting new CBO statistics while using METHOD_OPT with SIZE 1. |
| 13 | 1-COL INDEX | IX_GL_VE_DESC(FDESCRIPTION) | Number of distinct values (930215) does not match number of distinct keys (1088785) by 17%. | Possible Bug 4495422 or 9885553. This is an inconsistency on this indexed column. Gather fresh statistics with no histograms or adjusting DISTCNT and DENSITY using SET_COLUMN_statistics APIs. |
SELECT DISTINCT v.fcompanyid, v.fperiodid FROM (SELECT vch.fcompanyid, vch.fperiodid, ve.faccountid, ve.fcurrencyid, sum(ve.foriginalamount * ve.FentryDC) FDEBITFOR, sum(ve.foriginalamount * (1 - ve.FentryDC)) FCREDITFOR FROM t_gl_voucherentry VE, t_gl_voucher VCH WHERE (vch.fid = ve.fbillid AND vch.FBizStatus = 5) GROUP BY vch.fcompanyid, vch.fperiodid, ve.faccountid, ve.fcurrencyid) V, (SELECT forgunitid, fperiodID, faccountid, fcurrencyid, fdebitfor, fcreditfor, fendbalancefor FROM t_gl_accountbalance WHERE fbaltype = :1) A WHERE (((((1 = 1 AND a.forgunitid = v.fcompanyid) AND a.fcurrencyid = v.fcurrencyid) AND a.fperiodid = v.fperiodid) AND a.faccountid = v.faccountid) AND ((a.fdebitfor <> v.fdebitfor) OR (a.fcreditfor <> v.fcreditfor)))
| # | 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 | T_GL_ACCOUNTBALANCE | HSEAS | 19373219 | 19373219 | 13-5月 -12 12:49:50 | 4 | 19373219 | 35 | 35 | 2803553 |
| 2 | T_GL_VOUCHER | HSEAS | 1007848 | 1007848 | 10-5月 -12 22:10:18 | 5 | 345736 | 45 | 39 | 69767 |
| 3 | T_GL_VOUCHERENTRY | HSEAS | 4869947 | 4869947 | 13-5月 -12 13:37:34 | 4 | 4869944 | 24 | 14 | 1276977 |
| # | 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 | T_GL_ACCOUNTBALANCE | HSEAS | IX_GL_ACCTBAL_1 | HSEAS | 19373219 | 19373219 | 13-5月 -12 13:26:05 | 5 | 5 | 19373219 | ||
| 2 | T_GL_ACCOUNTBALANCE | HSEAS | IX_GL_ACCTBAL_2 | HSEAS | 19373219 | 19373219 | 13-5月 -12 13:26:54 | 1 | 1 | 19373219 | ||
| 3 | T_GL_ACCOUNTBALANCE | HSEAS | IX_T_GL_AB_FBALTYPE | HSEAS | 19373219 | 19373219 | 13-5月 -12 13:28:04 | 1 | 1 | 19373219 | ||
| 4 | T_GL_ACCOUNTBALANCE | HSEAS | PK_GL_ACCTBAL | HSEAS | 19373219 | 19373219 | 13-5月 -12 13:27:30 | 5 | 5 | 19373219 | ||
| 5 | T_GL_VOUCHER | HSEAS | IX_GL_VOUCHER1 | HSEAS | 1020895 | 86499 | 10-5月 -12 22:10:25 | 3 | 3 | 5794 | ||
| 6 | T_GL_VOUCHER | HSEAS | IX_GL_VOUCHERCOMPR | HSEAS | 1023298 | 814981 | 10-5月 -12 22:10:56 | 2 | 2 | 5794 | ||
| 7 | T_GL_VOUCHER | HSEAS | IX_VCH_SOURCE | HSEAS | 9408 | 9408 | 10-5月 -12 22:11:41 | 1 | 0 | 9408 | ||
| 8 | T_GL_VOUCHER | HSEAS | IX_VC_FORCSLRPT1 | HSEAS | 977101 | 640322 | 10-5月 -12 22:11:40 | 3 | 3 | 5794 | ||
| 9 | T_GL_VOUCHER | HSEAS | PK_GL_VCH | HSEAS | 1024581 | 177471 | 10-5月 -12 22:11:41 | 1 | 0 | 5794 | ||
| 10 | T_GL_VOUCHERENTRY | HSEAS | IX_GL_VCHENTRYFQ | HSEAS | 4869947 | 4869947 | 13-5月 -12 13:41:54 | 3 | 3 | 4869947 | ||
| 11 | T_GL_VOUCHERENTRY | HSEAS | IX_GL_VCHENTRY_2 | HSEAS | 4869947 | 4869947 | 13-5月 -12 13:38:10 | 3 | 3 | 4869945 | ||
| 12 | T_GL_VOUCHERENTRY | HSEAS | IX_GL_VE_DESC | HSEAS | 4869936 | 4869936 | 13-5月 -12 13:41:46 | 1 | 1 | 4869936 | ||
| 13 | T_GL_VOUCHERENTRY | HSEAS | PK_GL_VCHENTRY | HSEAS | 4869947 | 4869947 | 13-5月 -12 13:38:17 | 1 | 1 | 4869947 |
| # | 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 | 2599172720 | 206 | 206 | 1 | 0 | 206 | 173519529 | 0 | 0 | 0 | 6336.431 | 6335.719 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | ALL_ROWS | 732887 | 3026966530 | HSEAS | JDBC Thin Client | 2012-05-13/17:05:58 | 2012-05-13/17:05:58 | 2012-05-14/10:00: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: 2599172720
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 1 | HASH UNIQUE | | 13662 | 4015K| 758M| 732K (1)| 02:26:35 | 1236K| 1236K| |
|* 2 | HASH JOIN | | 2413K| 692M| 782M| 677K (1)| 02:15:29 | 130M| 7885K| 160M (0)|
| 3 | VIEW | | 4828K| 727M| | 427K (1)| 01:25:33 | | | |
| 4 | HASH GROUP BY | | 4828K| 1501M| 3280M| 427K (1)| 01:25:33 | 155M| 7752K| 148M (0)|
|* 5 | HASH JOIN | | 4828K| 1501M| 190M| 90692 (1)| 00:18:09 | 113M| 8285K| 143M (0)|
|* 6 | TABLE ACCESS FULL| T_GL_VOUCHER | 1007K| 178M| | 13736 (1)| 00:02:45 | | | |
| 7 | TABLE ACCESS FULL| T_GL_VOUCHERENTRY | 4869K| 650M| | 32449 (1)| 00:06:30 | | | |
|* 8 | TABLE ACCESS FULL | T_GL_ACCOUNTBALANCE | 9682K| 1320M| | 139K (1)| 00:27:57 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$8976F1A6
3 - SEL$2 / V@SEL$1
4 - SEL$2
6 - SEL$2 / VCH@SEL$2
7 - SEL$2 / VE@SEL$2
8 - SEL$8976F1A6 / T_GL_ACCOUNTBALANCE@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$8976F1A6")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$3")
NO_ACCESS(@"SEL$8976F1A6" "V"@"SEL$1")
FULL(@"SEL$8976F1A6" "T_GL_ACCOUNTBALANCE"@"SEL$3")
LEADING(@"SEL$8976F1A6" "V"@"SEL$1" "T_GL_ACCOUNTBALANCE"@"SEL$3")
USE_HASH(@"SEL$8976F1A6" "T_GL_ACCOUNTBALANCE"@"SEL$3")
FULL(@"SEL$2" "VCH"@"SEL$2")
FULL(@"SEL$2" "VE"@"SEL$2")
LEADING(@"SEL$2" "VCH"@"SEL$2" "VE"@"SEL$2")
USE_HASH(@"SEL$2" "VE"@"SEL$2")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 5
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FORGUNITID"="V"."FCOMPANYID" AND "FCURRENCYID"="V"."FCURRENCYID" AND "FPERIODID"="V"."FPERIODID" AND
"FACCOUNTID"="V"."FACCOUNTID")
filter(("FDEBITFOR"<>"V"."FDEBITFOR" OR "FCREDITFOR"<>"V"."FCREDITFOR"))
5 - access("VCH"."FID"="VE"."FBILLID")
6 - filter("VCH"."FBIZSTATUS"=5)
8 - filter("FBALTYPE"=:1)
Note
-----
- 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: 2599172720
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | OMem | 1Mem | O/1/M |
----------------------------------------------------------------------------------------------------------------------------------
| 1 | HASH UNIQUE | | 13662 | 4015K| 758M| 732K (1)| 02:26:35 | 1236K| 1236K| 206/0/0|
|* 2 | HASH JOIN | | 2413K| 692M| 782M| 677K (1)| 02:15:29 | 130M| 7885K| 206/0/0|
| 3 | VIEW | | 4828K| 727M| | 427K (1)| 01:25:33 | | | |
| 4 | HASH GROUP BY | | 4828K| 1501M| 3280M| 427K (1)| 01:25:33 | 155M| 7752K| 206/0/0|
|* 5 | HASH JOIN | | 4828K| 1501M| 190M| 90692 (1)| 00:18:09 | 113M| 8285K| 206/0/0|
|* 6 | TABLE ACCESS FULL| T_GL_VOUCHER | 1007K| 178M| | 13736 (1)| 00:02:45 | | | |
| 7 | TABLE ACCESS FULL| T_GL_VOUCHERENTRY | 4869K| 650M| | 32449 (1)| 00:06:30 | | | |
|* 8 | TABLE ACCESS FULL | T_GL_ACCOUNTBALANCE | 9682K| 1320M| | 139K (1)| 00:27:57 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$8976F1A6
3 - SEL$2 / V@SEL$1
4 - SEL$2
6 - SEL$2 / VCH@SEL$2
7 - SEL$2 / VE@SEL$2
8 - SEL$8976F1A6 / T_GL_ACCOUNTBALANCE@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$8976F1A6")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$3")
NO_ACCESS(@"SEL$8976F1A6" "V"@"SEL$1")
FULL(@"SEL$8976F1A6" "T_GL_ACCOUNTBALANCE"@"SEL$3")
LEADING(@"SEL$8976F1A6" "V"@"SEL$1" "T_GL_ACCOUNTBALANCE"@"SEL$3")
USE_HASH(@"SEL$8976F1A6" "T_GL_ACCOUNTBALANCE"@"SEL$3")
FULL(@"SEL$2" "VCH"@"SEL$2")
FULL(@"SEL$2" "VE"@"SEL$2")
LEADING(@"SEL$2" "VCH"@"SEL$2" "VE"@"SEL$2")
USE_HASH(@"SEL$2" "VE"@"SEL$2")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 5
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FORGUNITID"="V"."FCOMPANYID" AND "FCURRENCYID"="V"."FCURRENCYID" AND "FPERIODID"="V"."FPERIODID" AND
"FACCOUNTID"="V"."FACCOUNTID")
filter(("FDEBITFOR"<>"V"."FDEBITFOR" OR "FCREDITFOR"<>"V"."FCREDITFOR"))
5 - access("VCH"."FID"="VE"."FBILLID")
6 - filter("VCH"."FBIZSTATUS"=5)
8 - filter("FBALTYPE"=:1)
Note
-----
- 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