1366133.1 SQLHC 11.4.4.1 Report: sqlhc_cslcq_cqdb1_10.2.0.4.0_4npnv8ffvn3qn_20130618181734.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-18/18:17:34"

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. object_alias ) v * ERROR at line 23: ORA-00904: "OBJECT_ALIAS": invalid identifier

#	Type	Name	Observation	More

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	143824	35956	23-OCT-12 22:07:05	1	144776	3	0	15693
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	Avg Column Sample Size
1	DRAWID_SEQ	HELIOS	SYS_C007083	HELIOS		144776	144776	23-OCT-12 22:07:05	2	20759
2	SITE_DAILY_REPORT	HELIOS	IDX_SITE_DAILY_REPORT2	HELIOS		34730178	0	06-DEC-12 22:01:38	3	5568
3	SITE_DAILY_REPORT	HELIOS	PK_SITE_DAILY_REPORT	HELIOS	YES	34730178	0	06-DEC-12 22:01:38	4	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	4613	2557	0	1	2.031	1.527	0.614	0.000	0.000	0.000	0.000	0.000	ALL_ROWS	587	152994848	HELIOS	SQL*Plus				2013-06-18/18:14:00	2013-06-18/18:14:00	2013-06-18/18:14:00

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                |                      |  35067 |   924K|   587   (7)| 00:00:08 |       |       |  4766K|  1861K| 6279K (0)|
                      |   3 |    VIEW                    |                      |   7191 | 93483 |    89   (8)| 00:00:02 |       |       |       |       |          |
                      |   4 |     HASH UNIQUE            |                      |   7191 | 79101 |    89   (8)| 00:00:02 |       |       |  4786K|  1742K| 2169K (0)|
                      |*  5 |      FILTER                |                      |        |       |            |          |       |       |       |       |          |
                      |*  6 |       MAT_VIEW ACCESS FULL | DRAWID_SEQ           |   7191 | 79101 |    88   (7)| 00:00:02 |       |       |       |       |          |
                      |   7 |    PARTITION RANGE ITERATOR|                      |  35067 |   479K|   497   (6)| 00:00:06 |   KEY |   KEY |       |       |          |
                      |*  8 |     INDEX FAST FULL SCAN   | PK_SITE_DAILY_REPORT |  35067 |   479K|   497   (6)| 00:00:06 |   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-01'

                      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(:V5||' 23:59:59','  yyyy-mm-dd hh24:mi:ss') AND
                                    "A"."REPORT_DATE">=TO_DATE(:V4,'yyyy-mm-dd')))

                      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-18/18:17:34