1366133.1 SQLHC 11.4.4.1 Report: sqlhc_cslcq_cqdb1_10.2.0.4.0_4npnv8ffvn3qn_20130617140116.html

License : "n"
SQL_ID  : "4npnv8ffvn3qn"
RDBMS   : "10.2.0.4.0"
Platform: "LINUX"
OFE     : "10.2.0.4"
DYN_SAMP: "2"
EBS:      ""
SIEBEL  : ""
PSFT    : ""
Date    : "2013-06-17/14:01:16"

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	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	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.
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	HELIOS.DRAWID_SEQ	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.
5	TABLE	HELIOS.SITE_DAILY_REPORT	Sample size of 57062 rows may be too small for table with 35183760 rows.	Sample percent used was:0.16%. Consider gathering better quality table statistics with a sample size of 10%.
6	TABLE	HELIOS.SITE_DAILY_REPORT	Table CBO statistics are 237 days old: 2012-10-23/22:32:53.	Consider gathering fresh table statistics with a sample size of 10%. Old statistics could contain low/high values for which a predicate may be out of range, producing then a poor plan.
7	INDEX	HELIOS.IDX_SITE_DAILY_REPORT2	Table/Index CBO statistics out of sync.	Table and index statistics were gathered 43 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	HELIOS.PK_SITE_DAILY_REPORT	Table/Index CBO statistics out of sync.	Table and index statistics were gathered 43 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	TABLE PARTITION	HELIOS.SITE_DAILY_REPORT	15 out of 122 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.
10	INDEX PARTITION	HELIOS.IDX_SITE_DAILY_REPORT2	15 out of 122 partition(s) with number of rows equal to zero according to partition's CBO statistics.	If these index partitions are not empty, consider gathering table statistics using GRANULARITY=>GLOBAL AND PARTITION.
11	INDEX PARTITION	HELIOS.IDX_SITE_DAILY_REPORT2	Index contains partition(s) with index/partition CBO statistics out of sync for up to 43 day(s).	Index and partition statistics were gathered up to 43 day(s) appart, so they do not offer a consistent view to the CBO. Consider re-gathering table statistics using GRANULARITY=>GLOBAL AND PARTITION.
12	INDEX PARTITION	HELIOS.PK_SITE_DAILY_REPORT	15 out of 122 partition(s) with number of rows equal to zero according to partition's CBO statistics.	If these index partitions are not empty, consider gathering table statistics using GRANULARITY=>GLOBAL AND PARTITION.
13	INDEX PARTITION	HELIOS.PK_SITE_DAILY_REPORT	Index contains partition(s) with index/partition CBO statistics out of sync for up to 43 day(s).	Index and partition statistics were gathered up to 43 day(s) appart, so they do not offer a consistent view to the CBO. Consider re-gathering table statistics using GRANULARITY=>GLOBAL AND PARTITION.

SQL Text

select count(*) from  SITE_DAILY_REPORT A,
(SELECT DISTINCT DRAW_ID FROM DRAWID_SEQ
WHERE GAME_ID like DECODE(:v1,'All','%',:v2)
) D  where A.DRAW_ID=D.DRAW_ID and A.REPORT_DATE BETWEEN TO_DATE(:v4,'yyyy-mm-dd') AND TO_DATE(:v5 || ' 23:59:59','
yyyy-mm-dd hh24:mi:ss')

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	DRAWID_SEQ	HELIOS	142800	142800	14-JUN-13 09:42:02	1	142800	3	3	142800
2	SITE_DAILY_REPORT	HELIOS	35183760	57062	23-OCT-12 22:32:53	2	0	42	0	4792

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	Columns with Histogram	Avg Column Sample Size
1	DRAWID_SEQ	HELIOS	SYS_C007083	HELIOS		142800	142800	14-JUN-13 09:42:03	2	2	142800
2	SITE_DAILY_REPORT	HELIOS	IDX_SITE_DAILY_REPORT2	HELIOS		34730178	0	06-DEC-12 22:01:38	3	0	5568
3	SITE_DAILY_REPORT	HELIOS	PK_SITE_DAILY_REPORT	HELIOS	YES	34730178	0	06-DEC-12 22:01:38	4	0	5568

Current SQL Statistics (GV$SQL)

Performance metrics of child cursors of 4npnv8ffvn3qn 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	1	0	3242075036	1	1	1	0	1	2525	0	0	1	1.566	1.548	0.000	0.000	0.000	0.000	0.000	0.000	ALL_ROWS	610	152994848	HELIOS	SQL*Plus				2013-06-17/13:53:04	2013-06-17/13:53:04	2013-06-17/13:53:04

Historical SQL Statistics (DBA_HIST_SQLSTAT)

Performance metrics of execution plans of 4npnv8ffvn3qn 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: 1   Child: 0    Plan hash value: 3242075036

                      -----------------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                  | Name                 | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |  OMem |  1Mem | Used-Mem |
                      -----------------------------------------------------------------------------------------------------------------------------------------------
                      |   1 |  SORT AGGREGATE            |                      |      1 |    27 |            |          |       |       |       |       |          |
                      |*  2 |   HASH JOIN                |                      |    534K|    13M|   610  (10)| 00:00:08 |       |       |  4766K|  1861K| 6260K (0)|
                      |   3 |    VIEW                    |                      |   7140 | 92820 |    92   (8)| 00:00:02 |       |       |       |       |          |
                      |   4 |     HASH UNIQUE            |                      |   7140 | 78540 |    92   (8)| 00:00:02 |       |       |  4786K|  1742K| 2218K (0)|
                      |*  5 |      FILTER                |                      |        |       |            |          |       |       |       |       |          |
                      |*  6 |       MAT_VIEW ACCESS FULL | DRAWID_SEQ           |   7140 | 78540 |    91   (7)| 00:00:02 |       |       |       |       |          |
                      |   7 |    PARTITION RANGE ITERATOR|                      |    534K|  7309K|   513   (9)| 00:00:07 |   KEY |   KEY |       |       |          |
                      |*  8 |     INDEX FAST FULL SCAN   | PK_SITE_DAILY_REPORT |    534K|  7309K|   513   (9)| 00:00:07 |   KEY |   KEY |       |       |          |
                      -----------------------------------------------------------------------------------------------------------------------------------------------

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

                         1 - SEL$1
                         3 - SEL$2 / D@SEL$1
                         4 - SEL$2
                         6 - SEL$2 / DRAWID_SEQ@SEL$2
                         8 - SEL$1 / A@SEL$1

                      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$1")
                            OUTLINE(@"SEL$2")
                            OUTLINE(@"SEL$1")
                            NO_ACCESS(@"SEL$1" "D"@"SEL$1")
                            INDEX_FFS(@"SEL$1" "A"@"SEL$1" ("SITE_DAILY_REPORT"."DRAW_ID" "SITE_DAILY_REPORT"."SITE_ID" "SITE_DAILY_REPORT"."CLERK_ID"
                                    "SITE_DAILY_REPORT"."REPORT_DATE"))
                            LEADING(@"SEL$1" "D"@"SEL$1" "A"@"SEL$1")
                            USE_HASH(@"SEL$1" "A"@"SEL$1")
                            FULL(@"SEL$2" "DRAWID_SEQ"@"SEL$2")
                            END_OUTLINE_DATA
                        */

                      Peeked Binds (identified by position):
                      --------------------------------------

                         3 - (VARCHAR2(30), CSID=852): '2011-05-01'
                         4 - (VARCHAR2(30), CSID=852): '2011-05-31'

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

                         2 - access("A"."DRAW_ID"="D"."DRAW_ID")
                         5 - filter(TO_DATE(:V4,'yyyy-mm-dd')<=TO_DATE(:V5||' 23:59:59','  yyyy-mm-dd hh24:mi:ss'))
                         6 - filter(TO_CHAR("GAME_ID") LIKE DECODE(:V1,'All','%',:V2))
                         8 - filter(("A"."REPORT_DATE">=TO_DATE(:V4,'yyyy-mm-dd') AND "A"."REPORT_DATE"<=TO_DATE(:V5||' 23:59:59','  yyyy-mm-dd
                                    hh24:mi:ss')))

                      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

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-06-17/14:01:16