1366133.1 SQLHC 11.4.4.1 Report: sqlhc_2_11.1.0.7.0_84kz9zfcrpy89_20131211192118.html

License : "y"
SQL_ID  : "84kz9zfcrpy89"
RDBMS   : "11.1.0.7.0"
Platform: "IBM/AIX RISC SYSTEM/6000"
OFE     : "11.1.0.7"
DYN_SAMP: "2"
EBS:      ""
SIEBEL  : ""
PSFT    : ""
Date    : "2013-12-11/19:21:18"

Observations

Observations below are the outcome of several heath-checks on the schema objects accessed by your SQL and its environment. Review them carefully and take action when appropriate. Then re-execute your SQL and generate this report again.

#	Type	Name	Observation	More
1	CBO PARAMETER	_PGA_MAX_SIZE	CBO initialization parameter "_pga_max_size" with a non-default value of "2097152 KB" as per V$SQL_OPTIMIZER_ENV.	Review the correctness of this non-default value "2097152 KB" for SQL_ID 84kz9zfcrpy89.
2	CBO PARAMETER	_SMM_PX_MAX_SIZE	CBO initialization parameter "_smm_px_max_size" with a non-default value of "5537792 KB" as per V$SQL_OPTIMIZER_ENV.	Review the correctness of this non-default value "5537792 KB" for SQL_ID 84kz9zfcrpy89.
3	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.
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	xcxt.T_SJ_SJJBXX	Sample size of 258529 rows may be too small for table with 5170580 rows.	Sample percent used was:5.00%. Consider gathering better quality table statistics with a sample size of 30%.
6	TABLE	xcxt.T_SJ_SJJBXX	Table CBO statistics are 43 days old: 2013-10-29/15:47:20.	Consider gathering fresh table statistics with a sample size of 30%. Old statistics could contain low/high values for which a predicate may be out of range, producing then a poor plan.
7	TABLE	xcxt.T_SYS_ORGANIZATION	Table CBO statistics are 88 days old: 2013-09-14/12:11:24.	Consider gathering fresh table statistics with a sample size of 100%. Old statistics could contain low/high values for which a predicate may be out of range, producing then a poor plan.
8	INDEX	xcxt.CIX_SYS_ORGANIZATION_TREECODE	Table/Index CBO statistics out of sync.	Table and index statistics were gathered 88 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.
9	INDEX	xcxt.CIX_SYS_ORGANIZATION_TREECODE	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.
10	INDEX	xcxt.INDEX_SJ_SJJBXX_BLDWID	Table/Index CBO statistics out of sync.	Table and index statistics were gathered 23 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.
11	INDEX	xcxt.INDEX_SJ_SJJBXX_BLDWID	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.
12	INDEX	xcxt.INDEX_SJ_SJJBXX_RQDW	Table/Index CBO statistics out of sync.	Table and index statistics were gathered 40 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.
13	INDEX	xcxt.INDEX_SJ_SJJBXX_RQDW	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.
14	INDEX	xcxt.UI_T_SYS_ORGANIZATION_TREEPATH	Table/Index CBO statistics out of sync.	Table and index statistics were gathered 64 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.
15	INDEX	xcxt.UI_T_SYS_ORGANIZATION_TREEPATH	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.
16	1-COL INDEX	INDEX_SJ_SJJBXX_BLDWID(BLDWID)	Number of distinct values (575) does not match number of distinct keys (778) by 35%.	This is an inconsistency on this indexed column. Gather fresh statistics or adjusting DISTCNT and DENSITY using SET_COLUMN_statistics APIs.

SQL Text

select /*+use_hash(org sj)*/count(*)
from xcxt.t_sj_sjjbxx sj
inner join xcxt.t_sys_organization org
on org.orgcode = sj.bldwid
where sj.lrrq >= to_date('2013-09-01', 'yyyy-mm-dd hh24:mi:ss')
and sj.lrrq < to_date('2013-09-30', 'yyyy-mm-dd hh24:mi:ss')
and org.treepath like '11000000000000.11010600000000' || '%'

Tables Summary

Values below have two purposes:
1. Provide a quick view of the state of Table level CBO statistics, as well as their indexes and columns.
2. Ease a compare between two systems that are believed to be similar.

#	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_SJ_SJJBXX	ZFBA	5170580	258529	29-OCT-13 15:47:20	9	1400448	22	0	232555
2	T_SYS_ORGANIZATION	ZFBA	3517	3517	14-SEP-13 12:11:24	5	3518	32	0	2016

Indexes Summary

Values below have two purposes:
1. Provide a quick view of the state of Index level CBO statistics, as well as their columns.
2. Ease a compare between two systems that are believed to be similar.

#	Table Name	Table Owner	Index Name	Index Owner	In MEM Plan	Num Rows	Index Sample Size	Last Analyzed	Index Columns	Avg Column Sample Size
1	T_SJ_SJJBXX	ZFBA	INDEX_SJ_SJJBXX_BLDWID	ZFBA		5283399	5283399	21-NOV-13 17:32:12	1	254106
2	T_SJ_SJJBXX	ZFBA	INDEX_SJ_SJJBXX_LRDWID	ZFBA		5111320	255566	29-OCT-13 15:47:48	1	258519
3	T_SJ_SJJBXX	ZFBA	INDEX_SJ_SJJBXX_LRRQ	ZFBA		5246740	262337	29-OCT-13 15:47:40	1	258428
4	T_SJ_SJJBXX	ZFBA	INDEX_SJ_SJJBXX_RQDW	ZFBA	YES	5526878	5526878	08-DEC-13 22:51:18	2	256267
5	T_SJ_SJJBXX	ZFBA	INDEX_SJ_SJJBXX_SJBH	ZFBA		5168900	258445	29-OCT-13 15:47:55	1	258529
6	T_SJ_SJJBXX	ZFBA	INDEX_SJ_SJJBXX_SJID	ZFBA		4728500	236425	29-OCT-13 15:47:37	1	243881
7	T_SJ_SJJBXX	ZFBA	INDEX_T_SJ_SJJBXX_OPTIME	ZFBA		5102100	255105	29-OCT-13 15:47:29	1	258529
8	T_SJ_SJJBXX	ZFBA	INDEX__SJ_SJJBXX_LRRZH	ZFBA		5233207	267422	29-OCT-13 15:47:34	1	256655
9	T_SJ_SJJBXX	ZFBA	PK_T_SJ_SJJBXX	ZFBA		5169100	258455	29-OCT-13 15:47:45	1	258529
10	T_SYS_ORGANIZATION	ZFBA	CIX_SYS_ORGANIZATION_TREECODE	ZFBA	YES	3522	3522	11-DEC-13 14:53:30	2	3517
11	T_SYS_ORGANIZATION	ZFBA	INX_T_SYS_ORG_ORGNAME	ZFBA		3517	3517	14-SEP-13 12:11:25	1	3517
12	T_SYS_ORGANIZATION	ZFBA	PK_T_SYS_ORGANIZATION	ZFBA		3517	3517	14-SEP-13 12:11:25	1	3517
13	T_SYS_ORGANIZATION	ZFBA	UI_T_SYS_ORGANIZATION	ZFBA		3517	3517	14-SEP-13 12:11:25	1	3517
14	T_SYS_ORGANIZATION	ZFBA	UI_T_SYS_ORGANIZATION_TREEPATH	ZFBA		3517	3517	18-NOV-13 08:53:05	1	3517

Current SQL Statistics (GV$SQL)

Performance metrics of child cursors of 84kz9zfcrpy89 while still in memory.

#	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	2	0	2716384415	1	1	2	0	1	1208	1112	0	1	0.461	0.210	0.222	0.000	0.000	0.075	0.000	0.000	ALL_ROWS	821	1967963347	SYS	sqlplus@fzbdb2 (TNS V1-V3)				2013-12-11/17:40:33	2013-12-11/18:59:09	2013-12-11/18:59:10

Historical SQL Statistics (DBA_HIST_SQLSTAT)

Performance metrics of execution plans of 84kz9zfcrpy89 captured by AWR.

#	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

Current Execution Plans (last execution)

Captured while still in memory. Metrics below are for the last 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.

Inst: 2   Child: 0    Plan hash value: 2716384415

                      ---------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation           | Name                          | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
                      ---------------------------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT    |                               |        |       |   821 (100)|          |       |       |          |
                      |   1 |  SORT AGGREGATE     |                               |      1 |    93 |            |          |       |       |          |
                      |*  2 |   FILTER            |                               |        |       |            |          |       |       |          |
                      |*  3 |    HASH JOIN        |                               |    275 | 25575 |   821   (1)| 00:00:10 |  1095K|  1095K| 1284K (0)|
                      |*  4 |     INDEX RANGE SCAN| CIX_SYS_ORGANIZATION_TREECODE |      1 |    70 |     2   (0)| 00:00:01 |       |       |          |
                      |*  5 |     INDEX RANGE SCAN| INDEX_SJ_SJJBXX_RQDW          |    161K|  3616K|   817   (1)| 00:00:10 |       |       |          |
                      ---------------------------------------------------------------------------------------------------------------------------------

                      Query Block Name / Object Alias (identified by operation id):
                      -------------------------------------------------------------

                         1 - SEL$58A6D7F6
                         4 - SEL$58A6D7F6 / ORG@SEL$1
                         5 - SEL$58A6D7F6 / SJ@SEL$1

                      Outline Data
                      -------------

                        /*+
                            BEGIN_OUTLINE_DATA
                            IGNORE_OPTIM_EMBEDDED_HINTS
                            OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
                            DB_VERSION('11.1.0.7')
                            ALL_ROWS
                            OUTLINE_LEAF(@"SEL$58A6D7F6")
                            MERGE(@"SEL$1")
                            OUTLINE(@"SEL$2")
                            OUTLINE(@"SEL$1")
                            INDEX(@"SEL$58A6D7F6" "ORG"@"SEL$1" ("T_SYS_ORGANIZATION"."TREEPATH" "T_SYS_ORGANIZATION"."ORGCODE"))
                            INDEX(@"SEL$58A6D7F6" "SJ"@"SEL$1" ("T_SJ_SJJBXX"."LRRQ" "T_SJ_SJJBXX"."BLDWID"))
                            LEADING(@"SEL$58A6D7F6" "ORG"@"SEL$1" "SJ"@"SEL$1")
                            USE_HASH(@"SEL$58A6D7F6" "SJ"@"SEL$1")
                            END_OUTLINE_DATA
                        */

                      Predicate Information (identified by operation id):
                      ---------------------------------------------------

                         2 - filter(TO_DATE('2013-09-01','yyyy-mm-dd hh24:mi:ss')=TO_DATE('2013-09-01','yyyy-mm-dd hh24:mi:ss') AND
                                    "SJ"."LRRQ"
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 2013-12-11/19:21:18