215187.1 SQLT XPLAIN 11.2.9.8 Report: sqlt_s5655_ORA11R1_DANXP_main.html

General

Bind Variables

  • Peeked Binds
  • For Latest Execution

SQL Execution

  • Segment Statistics
  • Session Statistics
  • Session Events
    

Execution Plans

  • Explain Plan
  • Join Order
  • SQL Monitor
  • SQL Plan Monitor
  • SQL Statistics
  • Child Plan
  • Plan Statistics
  • Workareas
  • AWR Hist SQL Statistics
  • AWR Hist SQL Plan

Plan Control

  • Hints found in 10053
  • Stored Outlines
  • SQL Profiles
  • SQL Tuning Advisor
    

Schema Objects and CBO Stats

22-SEP-09 20:48:57

Environment

Method Used:XPLAIN
Script Used:sqltxplain.sql
Host Name:DANXP
CPUs:2
RAC:NO
Platform:32-bit Windows
Product Version:Oracle Database 11g Enterprise Edition (Production)
RDBMS Version:11.1.0.6.0(11.1.X)
RDBMS Release:11.1
Language:us:american_america.we8mswin1252
Database Name:ORA11R1(2470457352)
Instance Name:ora11r1(1)
Generated 10053:Y
User:USER0 (89)
CBO Stats Exp Rows - Schema Objects:64
Fixed Objects in Plan:0
Trace Directory:c:\oracle\diag\rdbms\ora11r1\ora11r1\trace (VALID)
Stage Directory:c:\oracle\diag\rdbms\ora11r1\ora11r1\trace (VALID)
Temporary Tablespace:TEMP (TEMPORARY)
OPTIMIZER_FEATURES_ENABLE:11.1.0.6
DB_BLOCK_SIZE:8192
CPUSPEEDNW:1845.90945194599
IOSEEKTIM:10
IOTFRSPEED:4096
MBRC:128
SREADTIM:12
MREADTIM:266
CPU Cost Scale Factor: 4.5145e-08
CPU Cost Inverse Scale Factor:22150914
Back to Top

SQL Identification

Input Filename:c:\temp\part0_sql1.sql
Statement ID:5655
Stat ID:s5655_ORA11R1_DANXP
Inst ID:1
Hash Value:2504866315
Address:2B1680BC
SQL ID:6r5j73kanufhb
SQL Signature (for Stored Outlines):E924AD25B1A0C45902078AC973AA7E03
SQL Signature (for SQL Profiles):17315877379555196788
Child Number:0
Plan Hash Value:2196188739
SQLT Plan HV:3963354750
Plan Source:EXPLAIN PLAN FOR
SQL Source:FILE
Plan Cost:149
Plan IO Cost:145
Plan CPU Cost:84621783
Estim Cardinality:61
Estim Bytes:1281
Estim Performance (secs):1.788
Other XML: [+]
Back to Top

SQL Statement

EXPLAIN PLAN SET statement_id = '5655' INTO sqltxplain.sqlt$_plan_table FOR
select t.team_id, count(*)
from  managers mg, master ma, teams t
where t.win_pct > .5
and t.team_id = mg.team_id
and mg.lahmanid = ma.lahmanid
and ma.debut_season_yyyy = 1965
group by t.team_id
Back to Top

Observations

[-]
Partial list of issues that may require some further attention.
#Object
Type
Object NameObservation
1SYSTEM STATSCPUSPEEDWORKLOAD SYSTEM STATISTICS are missing. Consider gathering workload system stats
2TABLEUSER0.MANAGERSCBO Stats seem gathered using deprecated ANALYZE command. Consider gathering stats with DBMS_STATS
3TABLEUSER0.MASTERCBO Stats seem gathered using deprecated ANALYZE command. Consider gathering stats with DBMS_STATS
4TABLEUSER0.TEAMSCBO Stats seem gathered using deprecated ANALYZE command. Consider gathering stats with DBMS_STATS
5INDEXUSER0.TEAMS_PKCBO Stats seem gathered using deprecated ANALYZE command Consider gathering stats with DBMS_STATS
6TABLE COLUMNUSER0.MANAGERS.DATE_YYYYCandidate for NNull constraint. Consider creating it
7TABLE COLUMNUSER0.MANAGERS.TEAM_IDCandidate for NNull constraint. Consider creating it
8TABLE COLUMNUSER0.MANAGERS.LEAGUE_IDCandidate for NNull constraint. Consider creating it
9TABLE COLUMNUSER0.TEAMS.ATTENDENCE_QTYCandidate for NNull constraint. Consider creating it
Back to Top

Constraints (DBA_CONSTRAINTS)

[-]
#OwnerConstraint
Name
Const
Type
Table NameSearch ConditionRef
Owner
Ref
Const
Name
Delete
Rule
StatusDeferrableDeferredValidatedGeneratedBadRelyLast ChangeIndex
Owner
Index
Name
InvalidView
Related
1USER0SYS_C0010565CMANAGERS "RECNUM" IS NOT NULL ENABLEDNOT DEFERRABLEIMMEDIATEVALIDATEDGENERATED NAME24-AUG-09
2USER0SYS_C0010566CMANAGERS "LAHMANID" IS NOT NULL ENABLEDNOT DEFERRABLEIMMEDIATEVALIDATEDGENERATED NAME24-AUG-09
3USER0SYS_C0010567CTEAMS "RECNUM" IS NOT NULL ENABLEDNOT DEFERRABLEIMMEDIATEVALIDATEDGENERATED NAME24-AUG-09
4USER0SYS_C0010568CTEAMS "DATE_YYYY" IS NOT NULL ENABLEDNOT DEFERRABLEIMMEDIATEVALIDATEDGENERATED NAME24-AUG-09
5USER0SYS_C0010569CTEAMS "LEAGUE_ID" IS NOT NULL ENABLEDNOT DEFERRABLEIMMEDIATEVALIDATEDGENERATED NAME24-AUG-09
6USER0SYS_C0010570CTEAMS "TEAM_ID" IS NOT NULL ENABLEDNOT DEFERRABLEIMMEDIATEVALIDATEDGENERATED NAME24-AUG-09
7USER0SYS_C0010571CMASTER "LAHMANID" IS NOT NULL ENABLEDNOT DEFERRABLEIMMEDIATEVALIDATEDGENERATED NAME24-AUG-09
Back to Top

Tablespaces (DBA_TABLESPACES)

[-]
#TablespaceBlock
Size
Initial
Extent
Next
Extent
Min
Extents
Max
Extents
Pct
Increase
Min
ExtLen
StatusContentsLoggingForce
Logging
Extent
Management
Allocation
Type
Plugged
In
Segment
Space
Management
Def Tab
Compression
RetentionBig
File
1EXAMPLE8192655361214748364565536ONLINEPERMANENTNOLOGGINGNOLOCALSYSTEMYESAUTODISABLEDNOT APPLYNO
2SYSAUX8192655361214748364565536ONLINEPERMANENTLOGGINGNOLOCALSYSTEMNOAUTODISABLEDNOT APPLYNO
3SYSTEM8192655361214748364565536ONLINEPERMANENTLOGGINGNOLOCALSYSTEMNOMANUALDISABLEDNOT APPLYNO
4TEMP819210485761048576101048576ONLINETEMPORARYNOLOGGINGNOLOCALUNIFORMNOMANUALDISABLEDNOT APPLYNO
5UNDOTBS18192655361214748364565536ONLINEUNDOLOGGINGNOLOCALSYSTEMNOMANUALDISABLEDNOGUARANTEENO
6USERS8192655361214748364565536ONLINEPERMANENTLOGGINGNOLOCALSYSTEMNOAUTODISABLEDNOT APPLYNO
Back to Top

DBMS_STATS Setup

DBMS_STATS Parameters

[-]
Auto Stats Target:AUTO
Publish:TRUE
Incremental:FALSE
Stale Percent:10
Estimate Percent:DBMS_STATS.AUTO_SAMPLE_SIZE(DEFAULT 0)
Degree:NULL(DEFAULT NULL:1)
Cascade:DBMS_STATS.AUTO_CASCADE(DEFAULT NULL)
No Invalidate:DBMS_STATS.AUTO_INVALIDATE(DEFAULT NULL)
Method Opt:FOR ALL COLUMNS SIZE AUTO
Granularity:AUTO
Back to Top

Auto Task "auto optimizer stats collection" (DBA_AUTOTASK_CLIENT)

[-]
Client Name:auto optimizer stats collection
Status:ENABLED
Consumer Group:ORA$AUTOTASK_STATS_GROUP
Client Tag:OS
Priority Override:INVALID
Attributes:ON BY DEFAULT, VOLATILE, SAFE TO KILL
Window Group:ORA$AT_WGRP_OS
Resource Percentage:25
Use Resource Estimates:FALSE
Mean Job Duration:+000000000 00:01:16.185714285
Mean Job CPU:+000000000 00:00:34.360142857
Mean Job Attempts:0
Mean incoming Tasks 7 days:2.5
Mean incoming Tasks 30 days:2
Total CPU last 7 days:+000000000 00:12:08.070000000
Total CPU last 30 days:+000000000 00:59:36.300000000
Max duration last 7 days:+000 00:01:11
Max duration last 30 days:+000 00:02:18
Window duration last 7 days:+000000020 05:03:00.329000000
Window duration last 30 days:+000000028 17:27:45.673000000
Back to Top

Statistics Operations History (WRI$_OPTSTAT_OPR)

[-]
#OperationStart TimeEnd Time
1gather_database_stats(auto)20-SEP-09 08.17.57.703000 PM -05:0020-SEP-09 08.18.49.593000 PM -05:00
2gather_database_stats(auto)20-SEP-09 10.02.21.187000 AM -05:0020-SEP-09 10.03.25.625000 AM -05:00
3gather_database_stats(auto)19-SEP-09 08.15.10.390000 AM -05:0019-SEP-09 08.15.57.015000 AM -05:00
4gather_database_stats(auto)14-SEP-09 10.00.06.562000 PM -05:0014-SEP-09 10.00.58.750000 PM -05:00
5gather_database_stats(auto)08-SEP-09 12.55.13.296000 AM -05:0008-SEP-09 12.56.03.171000 AM -05:00
6gather_database_stats(auto)06-SEP-09 11.19.22.468000 AM -05:0006-SEP-09 11.19.49.765000 AM -05:00
7gather_database_stats(auto)06-SEP-09 07.19.19.843000 AM -05:0006-SEP-09 07.19.56.062000 AM -05:00
8gather_database_stats(auto)05-SEP-09 11.33.13.078000 AM -05:0005-SEP-09 11.34.10.140000 AM -05:00
9gather_database_stats(auto)05-SEP-09 06.40.57.750000 AM -05:0005-SEP-09 06.41.30.937000 AM -05:00
10gather_database_stats(auto)04-SEP-09 10.00.06.640000 PM -05:0004-SEP-09 10.01.04.172000 PM -05:00
11gather_database_stats(auto)30-AUG-09 12.53.46.906000 PM -05:0030-AUG-09 12.54.51.406000 PM -05:00
12gather_database_stats(auto)28-AUG-09 10.00.05.734000 PM -05:0028-AUG-09 10.02.21.156000 PM -05:00
13gather_database_stats(auto)26-AUG-09 10.00.01.062000 PM -05:0026-AUG-09 10.00.36.656000 PM -05:00
14gather_database_stats(auto)26-AUG-09 01.20.09.562000 AM -05:0026-AUG-09 01.21.05.968000 AM -05:00
15gather_database_stats(auto)24-AUG-09 10.00.05.484000 PM -05:0024-AUG-09 10.00.54.406000 PM -05:00
16gather_database_stats(auto)24-AUG-09 12.07.38.515000 AM -05:0024-AUG-09 12.08.04.109000 AM -05:00
17gather_database_stats(auto)23-AUG-09 04.42.08.468000 PM -05:0023-AUG-09 04.42.33.843000 PM -05:00
18gather_database_stats(auto)23-AUG-09 12.01.54.671000 PM -05:0023-AUG-09 12.02.29.843000 PM -05:00
19gather_database_stats(auto)22-AUG-09 06.20.25.421000 AM -05:0022-AUG-09 06.20.56.062000 AM -05:00
Back to Top

System Statistics

Main System Statistics (AUX_STATS$)

[-]
Parameter
Name
ValueDescription
CPUSPEEDNW1845.90945194599Average CPU speed, in millions of instructions/second, as per no-workload
(statistics collected using 'NOWORKLOAD' option)
IOSEEKTIM10I/O seek time + latency time + operating system overhead time,
in milliseconds (default is 10)
IOTFRSPEED4096I/O transfer speed, in bytes/millisecond
(default is 4096)
CPUSPEEDAverage CPU speed, in millions of instructions/second, as per workload
(statistics collected using 'INTERVAL' or 'START' and 'STOP' options)
MBRCAverage multiblock read count for full scans, in blocks
SREADTIMAverage time for a single-block read request (random read),
in milliseconds
MREADTIMAverage time for a multi-block read request (for full scans),
in milliseconds
MAXTHRMaximum throughput the I/O subsystem can deliver, in bytes/second
SLAVETHRAverage parallel slave I/O throughput, in bytes/second
Use DBMS_STATS.GATHER_SYSTEM_STATS to refresh these CBO Stats
Back to Top

Info System Statistics (AUX_STATS$)

[-]
Parameter
Name
Value
DSTART10-15-2007 11:32
DSTOP10-15-2007 11:32
FLAGS
STATUSCOMPLETED
Use DBMS_STATS.GATHER_SYSTEM_STATS to refresh these CBO Stats
Back to Top

Optimizer Environment (10053)

[-]
#Is
Default
ParameterCurrent
Value
1Yoptimizer_mode_hintedfalse
2Yoptimizer_features_hinted0.0.0
3Yparallel_execution_enabledtrue
4Yparallel_query_forced_dop0
5Yparallel_dml_forced_dop0
6Yparallel_ddl_forced_degree0
7Yparallel_ddl_forced_instances0
8Y_query_rewrite_fudge90
9Yoptimizer_features_enable11.1.0.6
10Y_optimizer_search_limit5
11Ycpu_count2
12Yactive_instance_count1
13Yparallel_threads_per_cpu2
14Yhash_area_size131072
15Ybitmap_merge_area_size1048576
16Ysort_area_size65536
17Ysort_area_retained_size0
18Y_sort_elimination_cost_ratio0
19Y_optimizer_block_size8192
20Y_sort_multiblock_read_count2
21Y_hash_multiblock_io_count0
22Y_db_file_optimizer_read_count8
23Y_optimizer_max_permutations2000
24Ypga_aggregate_target262144 KB
25Y_pga_max_size204800 KB
26Y_query_rewrite_maxdisjunct257
27Y_smm_auto_min_io_size56 KB
28Y_smm_auto_max_io_size248 KB
29Y_smm_min_size262 KB
30Y_smm_max_size52428 KB
#Is
Default
ParameterCurrent
Value
31Y_smm_px_max_size131072 KB
32Y_cpu_to_io0
33Y_optimizer_undo_cost_change11.1.0.6
34Yparallel_query_modeenabled
35Yparallel_dml_modedisabled
36Yparallel_ddl_modeenabled
37Yoptimizer_modeall_rows
38Ysqlstat_enabledfalse
39Y_optimizer_percent_parallel101
40Y_always_anti_joinchoose
41Y_always_semi_joinchoose
42Y_optimizer_mode_forcetrue
43Y_partition_view_enabledtrue
44Y_always_star_transformationfalse
45Y_query_rewrite_or_errorfalse
46Y_hash_join_enabledtrue
47Ycursor_sharingexact
48Y_b_tree_bitmap_planstrue
49Ystar_transformation_enabledfalse
50Y_optimizer_cost_modelchoose
51Y_new_sort_cost_estimatetrue
52Y_complex_view_mergingtrue
53Y_unnest_subquerytrue
54Y_eliminate_common_subexprtrue
55Y_pred_move_aroundtrue
56Y_convert_set_to_joinfalse
57Y_push_join_predicatetrue
58Y_push_join_union_viewtrue
59Y_fast_full_scan_enabledtrue
60Y_optim_enhance_nnull_detectiontrue
#Is
Default
ParameterCurrent
Value
61Y_parallel_broadcast_enabledtrue
62Y_px_broadcast_fudge_factor100
63Y_ordered_nested_looptrue
64Y_no_or_expansionfalse
65Yoptimizer_index_cost_adj100
66Yoptimizer_index_caching0
67Y_system_index_caching0
68Y_disable_datalayer_samplingfalse
69Yquery_rewrite_enabledtrue
70Yquery_rewrite_integrityenforced
71Y_query_cost_rewritetrue
72Y_query_rewrite_2true
73Y_query_rewrite_1true
74Y_query_rewrite_expressiontrue
75Y_query_rewrite_jgmigratetrue
76Y_query_rewrite_fpctrue
77Y_query_rewrite_drjtrue
78Y_full_pwise_join_enabledtrue
79Y_partial_pwise_join_enabledtrue
80Y_left_nested_loops_randomtrue
81Y_improved_row_length_enabledtrue
82Y_index_join_enabledtrue
83Y_enable_type_dep_selectivitytrue
84Y_improved_outerjoin_cardtrue
85Y_optimizer_adjust_for_nullstrue
86Y_optimizer_degree0
87Y_use_column_stats_for_functiontrue
88Y_subquery_pruning_enabledtrue
89Y_subquery_pruning_mv_enabledfalse
90Y_or_expand_nvl_predicatetrue
#Is
Default
ParameterCurrent
Value
91Y_like_with_bind_as_equalityfalse
92Y_table_scan_cost_plus_onetrue
93Y_cost_equality_semi_jointrue
94Y_default_non_equality_sel_checktrue
95Y_new_initial_join_orderstrue
96Y_oneside_colstat_for_equijoinstrue
97Y_optim_peek_user_bindstrue
98Y_minimal_stats_aggregationtrue
99Y_force_temptables_for_gsetsfalse
100Yworkarea_size_policyauto
101Y_smm_auto_cost_enabledtrue
102Y_gs_anti_semi_join_allowedtrue
103Y_optim_new_default_join_seltrue
104Yoptimizer_dynamic_sampling2
105Y_pre_rewrite_push_predtrue
106Y_optimizer_new_join_card_computationtrue
107Y_union_rewrite_for_gsyes_gset_mvs
108Y_generalized_pruning_enabledtrue
109Y_optim_adjust_for_part_skewstrue
110Y_force_datefold_truncfalse
111Ystatistics_leveltypical
112Y_optimizer_system_stats_usagetrue
113Yskip_unusable_indexestrue
114Y_remove_aggr_subquerytrue
115Y_optimizer_push_down_distinct0
116Y_dml_monitoring_enabledtrue
117Y_optimizer_undo_changesfalse
118Y_predicate_elimination_enabledtrue
119Y_nested_loop_fudge100
120Y_project_view_columnstrue
#Is
Default
ParameterCurrent
Value
121Y_local_communication_costing_enabledtrue
122Y_local_communication_ratio50
123Y_query_rewrite_vop_cleanuptrue
124Y_slave_mapping_enabledtrue
125Y_optimizer_cost_based_transformationlinear
126Y_optimizer_mjc_enabledtrue
127Y_right_outer_hash_enabletrue
128Y_spr_push_pred_refsprtrue
129Y_optimizer_cache_statsfalse
130Y_optimizer_cbqt_factor50
131Y_optimizer_squ_bottomuptrue
132Y_fic_area_size131072
133Y_optimizer_skip_scan_enabledtrue
134Y_optimizer_cost_filter_predfalse
135Y_optimizer_sortmerge_join_enabledtrue
136Y_optimizer_join_sel_sanity_checktrue
137Y_mmv_query_rewrite_enabledtrue
138Y_bt_mmv_query_rewrite_enabledtrue
139Y_add_stale_mv_to_dependency_listtrue
140Y_distinct_view_unnestingfalse
141Y_optimizer_dim_subq_join_seltrue
142Y_optimizer_disable_strans_sanity_checks0
143Y_optimizer_compute_index_statstrue
144Y_push_join_union_view2true
145Y_optimizer_ignore_hintsfalse
146Y_optimizer_random_plan0
147Y_query_rewrite_setopgrw_enabletrue
148Y_optimizer_correct_sq_selectivitytrue
149Y_disable_function_based_indexfalse
150Y_optimizer_join_order_control3
#Is
Default
ParameterCurrent
Value
151Y_optimizer_cartesian_enabledtrue
152Y_optimizer_starplan_enabledtrue
153Y_extended_pruning_enabledtrue
154Y_optimizer_push_pred_cost_basedtrue
155Y_optimizer_null_aware_antijointrue
156Y_optimizer_extend_jppd_view_typestrue
157Y_sql_model_unfold_forloopsrun_time
158Y_enable_dml_lock_escalationfalse
159Y_bloom_filter_enabledtrue
160Y_update_bji_ipdml_enabled0
161Y_optimizer_extended_cursor_sharingudo
162Y_dm_max_shared_pool_pct1
163Y_optimizer_cost_hjsmj_multimatchtrue
164Y_optimizer_transitivity_retaintrue
165Y_px_pwg_enabledtrue
166Yoptimizer_secure_view_mergingtrue
167Y_optimizer_join_elimination_enabledtrue
168Yflashback_table_rpinon_fbt
169Y_optimizer_cbqt_no_size_restrictiontrue
170Y_optimizer_enhanced_filter_pushtrue
171Y_optimizer_filter_pred_pulluptrue
172Y_rowsrc_trace_level0
173Y_simple_view_mergingtrue
174Y_optimizer_rownum_pred_based_fkrtrue
175Y_optimizer_better_inlist_costingall
176Y_optimizer_self_induced_cache_costfalse
177Y_optimizer_min_cache_blocks10
178Y_optimizer_or_expansiondepth
179Y_optimizer_order_by_elimination_enabledtrue
180Y_optimizer_outer_to_anti_enabledtrue
#Is
Default
ParameterCurrent
Value
181Y_selfjoin_mv_duplicatestrue
182Y_dimension_skip_nulltrue
183Y_force_rewrite_enablefalse
184Y_optimizer_star_tran_in_with_clausetrue
185Y_optimizer_complex_pred_selectivitytrue
186Y_optimizer_connect_by_cost_basedtrue
187Y_gby_hash_aggregation_enabledtrue
188Y_globalindex_pnum_filter_enabledtrue
189Y_px_minus_intersecttrue
190Y_fix_control_key0
191Y_force_slave_mapping_intra_part_loadsfalse
192Y_force_tmp_segment_loadsfalse
193Y_query_mmvrewrite_maxpreds10
194Y_query_mmvrewrite_maxintervals5
195Y_query_mmvrewrite_maxinlists5
196Y_query_mmvrewrite_maxdmaps10
197Y_query_mmvrewrite_maxcmaps20
198Y_query_mmvrewrite_maxregperm512
199Y_query_mmvrewrite_maxmergedcmaps50
200Y_query_mmvrewrite_maxqryinlistvals500
201Y_disable_parallel_conventional_loadfalse
202Y_trace_virtual_columnsfalse
203Y_replace_virtual_columnstrue
204Y_virtual_column_overload_allowedtrue
205Y_kdt_bufferingtrue
206Y_first_k_rows_dynamic_prorationtrue
207Y_optimizer_sortmerge_join_inequalitytrue
208Y_aw_row_source_enabledtrue
209Y_optimizer_aw_stats_enabledtrue
210Y_bloom_pruning_enabledtrue
#Is
Default
ParameterCurrent
Value
211Yresult_cache_modeMANUAL
212Y_px_ual_serial_inputtrue
213Y_optimizer_skip_scan_guessfalse
214Y_enable_row_shippingtrue
215Y_row_shipping_threshold80
216Y_row_shipping_explainfalse
217Ytransaction_isolation_levelread_commited
218Y_optimizer_distinct_eliminationtrue
219Y_optimizer_multi_level_push_predtrue
220Y_optimizer_group_by_placementtrue
221Y_optimizer_rownum_bind_default10
222Y_enable_query_rewrite_on_remote_objstrue
223Y_optimizer_extended_cursor_sharing_relsimple
224Y_optimizer_adaptive_cursor_sharingtrue
225Y_direct_path_insert_features0
226Y_optimizer_improve_selectivitytrue
227Yoptimizer_use_pending_statisticsfalse
228Y_optimizer_enable_density_improvementstrue
229Y_optimizer_aw_join_push_enabledtrue
230Y_optimizer_connect_by_combine_swtrue
231Y_enable_pmo_ctas0
232Y_optimizer_native_full_outer_joinforce
233Y_bloom_predicate_enabledfalse
234Y_optimizer_enable_extended_statstrue
235Y_is_lock_table_for_ddl_wait_lock0
236Y_pivot_implementation_methodchoose
237Yoptimizer_capture_sql_plan_baselinesfalse
238Yoptimizer_use_sql_plan_baselinestrue
239Y_optimizer_star_trans_min_cost0
240Y_optimizer_star_trans_min_ratio0
#Is
Default
ParameterCurrent
Value
241Y_with_subqueryOPTIMIZER
242Y_optimizer_fkr_index_cost_bias10
243Y_optimizer_use_subheaptrue
244Y_parallel_policymanual
245Yparallel_degree0
246Y_parallel_time_threshold10
247Y_parallel_time_unit10
248Y_optimizer_or_expansion_subheaptrue
249Y_optimizer_free_transformation_heaptrue
250Y_optimizer_reuse_cost_annotationstrue
251Y_result_cache_auto_size_threshold100
252Y_result_cache_auto_time_threshold1000
253Y_optimizer_nested_rollup_for_gset100
254Y_nlj_batching_enabled1
255Yparallel_query_default_dop0
256Yis_recur_flags0
257Yoptimizer_use_invisible_indexesfalse
258Yflashback_data_archive_internal_cursor0
259Y_optimizer_extended_stats_usage_control240
Back to Top

Bug Fix Control Environment (10053)

[-]
#FixCurrent
Value
Is
Default
Optimizer1
Features
Enable
Description
138347701 Y8.0.0Lift restriction on unnest subquery with a view
23746511enabledY8.0.0do not combine predicates from LNNVL
34519016enabledY9.2.0Pick view card from view qb instead of parent qb
43118776enabledY9.2.0Check for obj# for named view estimated card
54488689enabledY10.2.0.2ignore IS NOT NULL predicate as an index filter
62194204disabledYdisable push predicate driven by functional index into partition
72660592enabledY8.1.7do not trigger bitmap plans if no protential domain index driver
82320291enabledY9.2.0push into table with RLS
92324795enabledY8.1.7add(remove) cluster index for push view
104308414enabledY10.1.0.3outer query must have more than one table unless lateral view
113499674disabledYenable tiny index improvements: consider small indexes as cachhe
124569940enabledY10.1.0.5Use index heuristic for join pred being pushed
134631959enabledY10.2.0.2Refine criteria for additional phase in JPPD
144519340enabledY10.2.0.2Generate distinct view in SU if candidate for JPPD
154550003enabledY10.1.0do not consider no sel predicates in join selectivity sanity
161403283enabledY8.1.6CBO do not count 0 rows partitions
174554846enabledY10.2.0.2tbl$or$idx$part$num() predicate causing peformance problems
184602374enabledY10.2.0.2optimize top-level predicate chain
194584065enabledY10.2.0.2do not disable cartesian products if ORDERED hint is used
204545833enabledY10.2.0.2no selectivity for transitive inequality predicates
214611850enabledY10.2.0.2do not clobber predicate during first-k row estimate phase
224663698enabledY10.2.0.2for cached NL table set tab_cost_io to zero
234663804enabledY10.2.0.2use smallest table as first table in join card. initial ordering
244666174enabledY11.1.0.6consider subqueries for pruning only from optimizer-refined set
254567767enabledY11.1.0.6do not re-evaluate density on rowcache reload
26455676215 Y10.2.0.2min # of join permutations for starting table, new initial order
274728348enabledY10.2.0.2consider mjc if equi-joined pred is dropped in kkoipt
284708389enabledY10.2.0.2Use inner table and index DOP for costing NL join
294175830enabledY10.2.0.1generate transitive predicates across anti join predicates
304752814enabledY11.1.0.6treat ansi char constant as varchar2 if operator is not ANSI
#FixCurrent
Value
Is
Default
Optimizer1
Features
Enable
Description
314583239enabledY10.2.0.1enable common expr elimination, pred move around 4 insert stmt
324386734enabledY11.1.0.6f()=f() => f() is not null, selectivity for f() is not null
334887636enabledY11.1.0.6remove restriction from first K row optimization
344483240enabledY9.2.0Favor unique index in case of cost tie
354872602disabledYDisable unnesting of SQ under certain conditions
364711525enabledY9.2.0Enable code optimization for bitmap access path
374545802enabledY10.2.0.2Adjust inner card for post filters for outer joins
384605810enabledY10.1.0apafio - do not set DONE if the matching failed
394704779enabledY11.1.0.6Fix histogram type determination macro
404900129enabledY10.2.0.1Only use relevant check constraints for transitive pred. generat
414924149enabledY10.2.0.2Allow secure view merging if outside of view is zero-argument
424663702enabledY10.2.0.3Use base NDV instead of scaled NDV when computing index selectiv
434878299enabledY10.2.0.3FKR: also prorate index ABK (avg blocks per key)
444658342enabledY9.2.0do not push predicates into deeply nested predicate trees
454881533enabledY11.1.0.6Limit # of slave groups to DOP
464676955enabledY11.1.0.6kkojcio: Get view card using kkotbS not kkotbC
474273361enabledY10.2.0.4convert range scan to unique access if possible
484967068enabledY11.1.0.6outer join table elimination
494969880disabledYupgrade to row-read access instead of row-write
505005866enabledY9.2.0remove null first element from multicolumn inlist if possible
515015557enabledY9.2.0add new predicate to the VC-EXPR mapping
524705343enabledY11.1.0.6Relax view merging security checks for SYS PL/SQL functions
534904838enabledY9.2.0allow index skip scan with no index keys
544716096enabledY11.1.0.6Relax view merging security checks for domain indexes
554483286disabledYDiscount FFS cost using optimizer_index_cost_adj.
564722900enabledY10.2.0.1kkoidc: add SI caching for index branch blocks
574615392enabledY10.2.0.3ANSI syntax prevents table elimination
585096560enabledY10.2.0.2use sql text from qbcqutxt for reparse
595029464enabledY9.2.0inline view in a stored view
604134994enabledY10.2.0.2multiple signatures for selectivity func
#FixCurrent
Value
Is
Default
Optimizer1
Features
Enable
Description
614904890enabledY11.1.0.6do not copy kcc of columns in non-view side of a JPPD
625104624enabledY10.2.0.2multiple signatures for selectivity func
635014836enabledY11.1.0.6Enable CBQT for INSERT statements
644768040enabledY11.1.0.6Enable CBQT for CTAS statements
654600710enabledY10.2.0.3Cost inlists as index filters as well as keys
665129233enabledY10.2.0.3peek at any foldable exprssion during pruning
674595987enabledY10.2.0.3skip internal ref columns for index only check for update/delete
684908162enabledY10.2.0.3Enable star plan for 2 column part pruning index
695139520enabledY11.1.0.6kkoDMcos: For PWJ on list dimension, use part/subpart bits
705084239enabledY10.2.0.3try for subqueries before forcing in star trans with FACT hint
715143477disabledYdisable the fix for the bug # 5011764
722663857enabledY8.0.0Use extended index caching discount
734717546enabledY9.2.0avoid semantically redundant predicates to be used
745240264enabledY10.2.0.3Share single copy of coldef within an expression
755099909enabledY10.2.0.3Consider subquery pruning using view containing single table pre
765240607enabledY10.2.0.3avoid NL with fts on right side in fkr mode
775195882enabledY10.2.0.3Enhance view merging security checks for PL/SQL functions
785220356enabledY10.2.0.4Allow predicate push for predicates that partition prune
795263572enabledY10.1.0.5correct selectivity of col LIKE func(:b1)
805385629enabledY11.1.0.6Remove restrictions on unsupported view structures for JPPD
815302124enabledY10.2.0.4Allow CBQT for queries with window functions
825391942enabledY10.1.0Favor rowid= over any other access path regardless of statistics
835384335enabledY11.1.0.6Add heuristic for group-by placement
845482831enabledY10.2.0.4discount for partitioned range scan in fkr mode
854158812enabledY11.1.0.6SQ to view transform - remove dual from qbcfro
865387148enabledY11.1.0.6Propagate interleaved CVM flag from CBQT analysis
875383891enabledY10.1.0.6Limit join permutations considered to 2000 for star plans
885466973enabledY10.1.0Do colocate join if columns in fro->frojand are on the same node
895396162enabledY10.2.0.4Lift restriction on semi-join elimination
905394888enabledY10.2.0.4don't over-write prorated cardinalities on switch to Amode plan
#FixCurrent
Value
Is
Default
Optimizer1
Features
Enable
Description
915395291enabledY10.2.0.4prefer index access when joining first non 1-row table in K mode
925236908enabledY9.2.0Allow _optimizer_index_caching to affect IOT primary keys
935509293enabledY11.1.0.6Relax semi/antijoin restrictions with remote tables
945449488enabledY10.2.0.4In bitmap costing use real sel for transitive negated predicates
955567933enabledY11.1.0.6no view with having for group-by placement
965570494enabledY11.1.0.6ignore KKOIXMSU flag and match index filter preds with index key
975288623enabledY10.1.0.6also try for sort eliminating index with maximum columns
985505995enabledY11.1.0.6Consider pushing into each of two views sharing join predicate
995505157enabledY11.1.0.6relax type matching in pre-rewrite transitive predicate generati
1005112460enabledY11.1.0.6Make table elimination independent of CVM
1015554865enabledY11.1.0.6Allow inner join table elimination on unique col
1025112260enabledY11.1.0.6semi join table elimination
1035112352enabledY11.1.0.6anti join table elimination
1045547058enabledY10.2.0.4Promote column min/max values in OJPPD for UNION [ALL] views
1055618040enabledY10.2.0.4amend costing for index use to avoid sort with old FIRST_ROWS
1065585313enabledY11.1.0.6enable index prefetching for NLJ with unique scan as inner acces
1075547895enabledY10.2.0.4controls generating transitive predicates in presence of UDFs
1085634346enabledY10.2.0.4Relax equality operator restrictions for multicolumn inlists
1095620485enabledY10.2.0.4do not push into UV if target table is outer joinded
1105483301enabledY10.1.0.6Use min repeat count in freq histogram to compute the density
1115657044enabledY11.1.0.6consider new alternate index to avoid ORDER BY sort
1125694984enabledY10.2.0.4scale index num distinct keys with Watkins func in first_rows(k)
1135868490enabledY8.0.0disallow CVM for top QB views for DELETE
1145650477enabledY10.2.0.4set correlation flag if outer col is set func
1155611962enabledY10.2.0.4use index keys as filters when considering index min/max
1164279274enabledY11.1.0.6Cache user-defined operator stats across transformations
1175741121enabledY10.2.0.4avoid zero selectivity for negated predicate
1185714944enabledY11.1.0.6set IO cost for index skip scan to at least 1.0
1195391505enabledY10.2.0.3add subheap for physical optimization
1205762598enabledY10.2.0.4improve selectivity for character strings of numeric data
#FixCurrent
Value
Is
Default
Optimizer1
Features
Enable
Description
1215578791enabledY10.2.0.4do not discount branch io cost if inner table io cost is already
1225259048enabledY10.2.0.4undo unused inlist
1235882954enabledY10.1.0.6Avoid unnecessary column stats load for partitioned tables
1242492766enabledY9.2.0use OR'ed predicates in index filter
1255707608enabledY10.1.0.4do not load statistics for non access partitions
1265891471enabledY10.2.0.4no transitive generation across logical join check constraints
1275884780enabledY10.2.0.4consider list of values dimensions during star transformation
1285680702enabledY10.2.0.4adjust anti/semijoin index selectivity only if join preds as key
1295371452enabledY10.2.0.2put false predicate ahead in the predicate chain
1305838613enabledY10.2.0.4Sort tables for initial join order during star transformation
1315949981enabledY10.2.0.4amend selectivity for match of a single-row frequency bucket
1325624216enabledY10.2.0.4disable union all transformation if qbc does not have whr
1335741044enabledY10.2.0.2consider predicate with subquery when determine 1-row tables
1345976822enabledY10.2.0.4check dynamic sampling index selecticity for nested loops join
1356006457enabledY11.1.0.6merge single-table anti-joined view
1365872956enabledY10.2.0.4push constant filter predicates on PRIORs to connect by pump
1375923644enabledY11.1.0.6Enable outer to inner join conversion for inlist
1385943234enabledY8.0.0Change drop column, set unused to take X DML lock
1395844495enabledY11.1.0.6Eliminate group-by from [NOT] EXISTS subqueries
1404168080enabledY11.1.0.6Eliminate unneeded bitmap conversion
1416020579enabledY11.1.0.6Allow semi-join elimination with multi column operand
1425842686disabledYcompute avg col length for LONG RAW column
1435996801enabledY11.1.0.6use subpartition-level statistics
1445593639enabledY11.1.0.6Push USERENV filter predicate into view
1456133948enabledY11.1.0.6allocate ckydef on the compilation heap
1466239909enabledY11.1.0.6Fix out-of-bound value in self induced caching
(1) Version on (and after) which the fix is enabled by default
Back to Top

Initialization Parameters (GV$PARAMETER2)

Session parameters with modified values (GV$PARAMETER2)

[-]
#Inst
ID
ParameterCurrent
Value
Is
Modified
11max_dump_file_sizeUNLIMITEDMODIFIED
21tracefile_identifierIGNORE_ME_SQLTMODIFIED
Back to Top

Session parameters with non-default values (GV$PARAMETER2)

[-]
#Inst
ID
ParameterCurrent
Value
11audit_file_destC:\ORACLE\ADMIN\ORA11R1\ADUMP
21audit_trailDB
31compatible11.1.0.0.0
41control_filesC:\ORACLE\ORADATA\ORA11R1\CONTROL01.CTL
51control_filesC:\ORACLE\ORADATA\ORA11R1\CONTROL02.CTL
61control_filesC:\ORACLE\ORADATA\ORA11R1\CONTROL03.CTL
71db_block_size8192
81db_domain
91db_nameora11r1
101db_recovery_file_destC:\oracle\flash_recovery_area
111db_recovery_file_dest_size2147483648 (2G)
121diagnostic_destC:\ORACLE
131dispatchers(PROTOCOL=TCP) (SERVICE=ora11r1XDB)
141memory_target805306368 (768M)
151open_cursors300
161processes150
171remote_login_passwordfileEXCLUSIVE
181undo_tablespaceUNDOTBS1
191user_dump_destc:\oracle\diag\rdbms\ora11r1\ora11r1\trace
Back to Top

Session parameters (GV$PARAMETER2)

[-]
#Inst
ID
ParameterCurrent
Value
TypeIs
Default
Is
System
Modifiable
Is
Instance
Modifiable
Is
Session
Modifiable
Is
Modified
Is
Adjusted
Is
Deprecated
11active_instance_count3TRUEFALSEFALSEFALSEFALSEFALSEFALSE
21aq_tm_processes03TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
31archive_lag_target03TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
41asm_diskgroups2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
51asm_diskstring2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
61asm_power_limit13TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
71asm_preferred_read_failure_groups2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
81audit_file_destC:\ORACLE\ADMIN\ORA11R1\ADUMP2FALSEDEFERREDTRUEFALSEFALSEFALSEFALSE
91audit_sys_operationsFALSE1TRUEFALSEFALSEFALSEFALSEFALSEFALSE
101audit_trailDB2FALSEFALSEFALSEFALSEFALSEFALSEFALSE
111background_core_dumppartial2TRUEFALSEFALSEFALSEFALSEFALSEFALSE
121background_dump_destc:\oracle\diag\rdbms\ora11r1\ora11r1\trace2TRUEIMMEDIATETRUEFALSEFALSEFALSETRUE
131backup_tape_io_slavesFALSE1TRUEDEFERREDTRUEFALSEFALSEFALSEFALSE
141bitmap_merge_area_size10485763TRUEFALSEFALSEFALSEFALSEFALSEFALSE
151blank_trimmingFALSE1TRUEFALSEFALSEFALSEFALSEFALSEFALSE
161buffer_pool_keep2TRUEFALSEFALSEFALSEFALSEFALSETRUE
171buffer_pool_recycle2TRUEFALSEFALSEFALSEFALSEFALSETRUE
181circuits3TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
191client_result_cache_lag30006TRUEFALSEFALSEFALSEFALSEFALSEFALSE
201client_result_cache_size06TRUEFALSEFALSEFALSEFALSEFALSEFALSE
211cluster_databaseFALSE1TRUEFALSEFALSEFALSEFALSEFALSEFALSE
221cluster_database_instances13TRUEFALSEFALSEFALSEFALSEFALSEFALSE
231cluster_interconnects2TRUEFALSEFALSEFALSEFALSEFALSEFALSE
241commit_logging2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
251commit_point_strength13TRUEFALSEFALSEFALSEFALSEFALSEFALSE
261commit_wait2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
271commit_write2TRUEIMMEDIATETRUETRUEFALSEFALSETRUE
281compatible11.1.0.0.02FALSEFALSEFALSEFALSEFALSEFALSEFALSE
291control_file_record_keep_time73TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
301control_filesC:\ORACLE\ORADATA\ORA11R1\CONTROL01.CTL2FALSEFALSEFALSEFALSEFALSEFALSEFALSE
#Inst
ID
ParameterCurrent
Value
TypeIs
Default
Is
System
Modifiable
Is
Instance
Modifiable
Is
Session
Modifiable
Is
Modified
Is
Adjusted
Is
Deprecated
311control_filesC:\ORACLE\ORADATA\ORA11R1\CONTROL02.CTL2FALSEFALSEFALSEFALSEFALSEFALSEFALSE
321control_filesC:\ORACLE\ORADATA\ORA11R1\CONTROL03.CTL2FALSEFALSEFALSEFALSEFALSEFALSEFALSE
331control_management_pack_accessDIAGNOSTIC+TUNING2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
341core_dump_destc:\oracle\diag\rdbms\ora11r1\ora11r1\cdump2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
351cpu_count23TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
361create_bitmap_area_size83886083TRUEFALSEFALSEFALSEFALSEFALSEFALSE
371create_stored_outlines2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
381cursor_sharingEXACT2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
391cursor_space_for_timeFALSE1TRUEFALSEFALSEFALSEFALSEFALSEFALSE
401db_16k_cache_size06TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
411db_2k_cache_size06TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
421db_32k_cache_size06TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
431db_4k_cache_size06TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
441db_8k_cache_size06TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
451db_block_buffers03TRUEFALSEFALSEFALSEFALSEFALSEFALSE
461db_block_checkingFALSE2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
471db_block_checksumTYPICAL2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
481db_block_size81923FALSEFALSEFALSEFALSEFALSEFALSEFALSE
491db_cache_adviceON2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
501db_cache_size06TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
511db_create_file_dest2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
521db_create_online_log_dest_12TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
531db_create_online_log_dest_22TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
541db_create_online_log_dest_32TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
551db_create_online_log_dest_42TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
561db_create_online_log_dest_52TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
571db_domain2FALSEFALSEFALSEFALSEFALSEFALSEFALSE
581db_file_multiblock_read_count1283TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
591db_file_name_convert2TRUEFALSEFALSETRUEFALSEFALSEFALSE
601db_files2003TRUEFALSEFALSEFALSEFALSEFALSEFALSE
#Inst
ID
ParameterCurrent
Value
TypeIs
Default
Is
System
Modifiable
Is
Instance
Modifiable
Is
Session
Modifiable
Is
Modified
Is
Adjusted
Is
Deprecated
611db_flashback_retention_target14403TRUEIMMEDIATEFALSEFALSEFALSEFALSEFALSE
621db_keep_cache_size06TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
631db_lost_write_protectNONE2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
641db_nameora11r12FALSEFALSEFALSEFALSEFALSEFALSEFALSE
651db_recovery_file_destC:\oracle\flash_recovery_area2FALSEIMMEDIATEFALSEFALSEFALSEFALSEFALSE
661db_recovery_file_dest_size2147483648 (2G)6FALSEIMMEDIATEFALSEFALSEFALSEFALSEFALSE
671db_recycle_cache_size06TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
681db_securefilePERMITTED2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
691db_ultra_safeOFF2TRUEFALSEFALSEFALSEFALSEFALSEFALSE
701db_unique_nameora11r12TRUEFALSEFALSEFALSEFALSEFALSEFALSE
711db_writer_processes13TRUEFALSEFALSEFALSEFALSEFALSEFALSE
721dbwr_io_slaves03TRUEFALSEFALSEFALSEFALSEFALSEFALSE
731ddl_lock_timeout03TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
741dg_broker_config_file1C:\ORACLE\PRODUCT\11.1.0\DB_1\DATABASE\DR1ORA11R1.DAT2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
751dg_broker_config_file2C:\ORACLE\PRODUCT\11.1.0\DB_1\DATABASE\DR2ORA11R1.DAT2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
761dg_broker_startFALSE1TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
771diagnostic_destC:\ORACLE2FALSEIMMEDIATETRUEFALSEFALSEFALSEFALSE
781disk_asynch_ioTRUE1TRUEFALSEFALSEFALSEFALSEFALSEFALSE
791dispatchers(PROTOCOL=TCP) (SERVICE=ora11r1XDB)2FALSEIMMEDIATETRUEFALSEFALSEFALSEFALSE
801distributed_lock_timeout603TRUEFALSEFALSEFALSEFALSEFALSEFALSE
811dml_locks7483TRUEFALSEFALSEFALSEFALSEFALSEFALSE
821drs_startFALSE1TRUEIMMEDIATETRUEFALSEFALSEFALSETRUE
831enable_ddl_loggingFALSE1TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
841event2TRUEFALSEFALSEFALSEFALSEFALSEFALSE
851fal_client2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
861fal_server2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
871fast_start_io_target03TRUEIMMEDIATETRUEFALSEFALSEFALSETRUE
881fast_start_mttr_target03TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
891fast_start_parallel_rollbackLOW2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
901file_mappingFALSE1TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
#Inst
ID
ParameterCurrent
Value
TypeIs
Default
Is
System
Modifiable
Is
Instance
Modifiable
Is
Session
Modifiable
Is
Modified
Is
Adjusted
Is
Deprecated
911fileio_network_adapters2TRUEFALSEFALSEFALSEFALSEFALSEFALSE
921filesystemio_options2TRUEFALSEFALSEFALSEFALSEFALSEFALSE
931fixed_date2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
941gc_files_to_locks2TRUEFALSEFALSEFALSEFALSEFALSEFALSE
951gcs_server_processes03TRUEFALSEFALSEFALSEFALSEFALSEFALSE
961global_context_pool_size2TRUEFALSEFALSEFALSEFALSEFALSETRUE
971global_namesFALSE1TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
981global_txn_processes13TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
991hash_area_size1310723TRUEFALSEFALSETRUEFALSEFALSEFALSE
1001hi_shared_memory_address03TRUEFALSEFALSEFALSEFALSEFALSEFALSE
1011hs_autoregisterTRUE1TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1021ifile4TRUEFALSEFALSEFALSEFALSEFALSEFALSE
1031instance_groups2TRUEFALSEFALSEFALSEFALSEFALSETRUE
1041instance_nameora11r12TRUEFALSEFALSEFALSEFALSEFALSEFALSE
1051instance_number03TRUEFALSEFALSEFALSEFALSEFALSEFALSE
1061instance_typeRDBMS2TRUEFALSEFALSEFALSEFALSEFALSEFALSE
1071java_jit_enabledTRUE1TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1081java_max_sessionspace_size03TRUEFALSEFALSEFALSEFALSEFALSEFALSE
1091java_pool_size06TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1101java_soft_sessionspace_limit03TRUEFALSEFALSEFALSEFALSEFALSEFALSE
1111job_queue_processes10003TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1121large_pool_size06TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1131ldap_directory_accessNONE2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1141ldap_directory_sysauthno2TRUEFALSEFALSEFALSEFALSEFALSEFALSE
1151license_max_sessions03TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1161license_max_users03TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1171license_sessions_warning03TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1181local_listener2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1191lock_name_space2TRUEFALSEFALSEFALSEFALSEFALSETRUE
1201lock_sgaFALSE1TRUEFALSEFALSEFALSEFALSEFALSEFALSE
#Inst
ID
ParameterCurrent
Value
TypeIs
Default
Is
System
Modifiable
Is
Instance
Modifiable
Is
Session
Modifiable
Is
Modified
Is
Adjusted
Is
Deprecated
1211log_archive_config2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1221log_archive_dest2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1231log_archive_dest_12TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1241log_archive_dest_102TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1251log_archive_dest_22TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1261log_archive_dest_32TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1271log_archive_dest_42TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1281log_archive_dest_52TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1291log_archive_dest_62TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1301log_archive_dest_72TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1311log_archive_dest_82TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1321log_archive_dest_92TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1331log_archive_dest_state_1enable2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1341log_archive_dest_state_10enable2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1351log_archive_dest_state_2enable2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1361log_archive_dest_state_3enable2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1371log_archive_dest_state_4enable2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1381log_archive_dest_state_5enable2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1391log_archive_dest_state_6enable2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1401log_archive_dest_state_7enable2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1411log_archive_dest_state_8enable2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1421log_archive_dest_state_9enable2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1431log_archive_duplex_dest2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1441log_archive_formatARC%S_%R.%T2TRUEFALSEFALSEFALSEFALSEFALSEFALSE
1451log_archive_local_firstTRUE1TRUEIMMEDIATETRUEFALSEFALSEFALSETRUE
1461log_archive_max_processes43TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1471log_archive_min_succeed_dest13TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1481log_archive_startFALSE1TRUEFALSEFALSEFALSEFALSEFALSETRUE
1491log_archive_trace03TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1501log_buffer56535043TRUEFALSEFALSEFALSEFALSEFALSEFALSE
#Inst
ID
ParameterCurrent
Value
TypeIs
Default
Is
System
Modifiable
Is
Instance
Modifiable
Is
Session
Modifiable
Is
Modified
Is
Adjusted
Is
Deprecated
1511log_checkpoint_interval03TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1521log_checkpoint_timeout18003TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1531log_checkpoints_to_alertFALSE1TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1541log_file_name_convert2TRUEFALSEFALSEFALSEFALSEFALSEFALSE
1551max_commit_propagation_delay03TRUEFALSEFALSEFALSEFALSEFALSETRUE
1561max_dispatchers3TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1571max_dump_file_sizeUNLIMITED2TRUEIMMEDIATETRUETRUEMODIFIEDFALSEFALSE
1581max_enabled_roles1503TRUEFALSEFALSEFALSEFALSEFALSETRUE
1591max_shared_servers3TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1601memory_max_target805306368 (768M)6TRUEFALSEFALSEFALSEFALSEFALSEFALSE
1611memory_target805306368 (768M)6FALSEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1621nls_calendar2TRUEFALSEFALSETRUEFALSEFALSEFALSE
1631nls_compBINARY2TRUEFALSEFALSETRUEFALSEFALSEFALSE
1641nls_currency2TRUEFALSEFALSETRUEFALSEFALSEFALSE
1651nls_date_format2TRUEFALSEFALSETRUEFALSEFALSEFALSE
1661nls_date_language2TRUEFALSEFALSETRUEFALSEFALSEFALSE
1671nls_dual_currency2TRUEFALSEFALSETRUEFALSEFALSEFALSE
1681nls_iso_currency2TRUEFALSEFALSETRUEFALSEFALSEFALSE
1691nls_languageAMERICAN2TRUEFALSEFALSETRUEFALSEFALSEFALSE
1701nls_length_semanticsBYTE2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1711nls_nchar_conv_excpFALSE2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1721nls_numeric_characters2TRUEFALSEFALSETRUEFALSEFALSEFALSE
1731nls_sort2TRUEFALSEFALSETRUEFALSEFALSEFALSE
1741nls_territoryAMERICA2TRUEFALSEFALSETRUEFALSEFALSEFALSE
1751nls_time_format2TRUEFALSEFALSETRUEFALSEFALSEFALSE
1761nls_time_tz_format2TRUEFALSEFALSETRUEFALSEFALSEFALSE
1771nls_timestamp_format2TRUEFALSEFALSETRUEFALSEFALSEFALSE
1781nls_timestamp_tz_format2TRUEFALSEFALSETRUEFALSEFALSEFALSE
1791o7_dictionary_accessibilityFALSE1TRUEFALSEFALSEFALSEFALSEFALSEFALSE
1801object_cache_max_size_percent103TRUEDEFERREDTRUETRUEFALSEFALSEFALSE
#Inst
ID
ParameterCurrent
Value
TypeIs
Default
Is
System
Modifiable
Is
Instance
Modifiable
Is
Session
Modifiable
Is
Modified
Is
Adjusted
Is
Deprecated
1811object_cache_optimal_size1024003TRUEDEFERREDTRUETRUEFALSEFALSEFALSE
1821olap_page_pool_size06TRUEDEFERREDTRUETRUEFALSEFALSEFALSE
1831open_cursors3003FALSEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1841open_links43TRUEFALSEFALSEFALSEFALSEFALSEFALSE
1851open_links_per_instance43TRUEFALSEFALSEFALSEFALSEFALSEFALSE
1861optimizer_capture_sql_plan_baselinesFALSE1TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1871optimizer_dynamic_sampling23TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1881optimizer_features_enable11.1.0.62TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1891optimizer_index_caching03TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1901optimizer_index_cost_adj1003TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1911optimizer_modeALL_ROWS2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1921optimizer_secure_view_mergingTRUE1TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1931optimizer_use_invisible_indexesFALSE1TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1941optimizer_use_pending_statisticsFALSE1TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1951optimizer_use_sql_plan_baselinesTRUE1TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
1961os_authent_prefixOPS$2TRUEFALSEFALSEFALSEFALSEFALSEFALSE
1971os_rolesFALSE1TRUEFALSEFALSEFALSEFALSEFALSEFALSE
1981parallel_adaptive_multi_userTRUE1TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
1991parallel_automatic_tuningFALSE1TRUEFALSEFALSEFALSEFALSEFALSETRUE
2001parallel_execution_message_size21483TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2011parallel_instance_group2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2021parallel_io_cap_enabledFALSE1TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2031parallel_max_servers203TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2041parallel_min_percent03TRUEFALSEFALSETRUEFALSEFALSEFALSE
2051parallel_min_servers03TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2061parallel_serverFALSE1TRUEFALSEFALSEFALSEFALSEFALSETRUE
2071parallel_server_instances13TRUEFALSEFALSEFALSEFALSEFALSETRUE
2081parallel_threads_per_cpu23TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2091pga_aggregate_target06TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2101plscope_settingsIDENTIFIERS:NONE2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
#Inst
ID
ParameterCurrent
Value
TypeIs
Default
Is
System
Modifiable
Is
Instance
Modifiable
Is
Session
Modifiable
Is
Modified
Is
Adjusted
Is
Deprecated
2111plsql_ccflags2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2121plsql_code_typeINTERPRETED2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2131plsql_debugFALSE1TRUEIMMEDIATETRUETRUEFALSEFALSETRUE
2141plsql_native_library_dir2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2151plsql_native_library_subdir_count03TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2161plsql_optimize_level23TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2171plsql_v2_compatibilityFALSE1TRUEIMMEDIATETRUETRUEFALSEFALSETRUE
2181plsql_warningsDISABLE:ALL2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2191pre_page_sgaFALSE1TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2201processes1503FALSEFALSEFALSEFALSEFALSEFALSEFALSE
2211query_rewrite_enabledTRUE2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2221query_rewrite_integrityenforced2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2231rdbms_server_dn2TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2241read_only_open_delayedFALSE1TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2251recovery_parallelism03TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2261recyclebinon2TRUEDEFERREDTRUETRUEFALSEFALSEFALSE
2271redo_transport_user2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2281remote_dependencies_modeTIMESTAMP2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2291remote_listener2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2301remote_login_passwordfileEXCLUSIVE2FALSEFALSEFALSEFALSEFALSEFALSEFALSE
2311remote_os_authentFALSE1TRUEFALSEFALSEFALSEFALSEFALSETRUE
2321remote_os_rolesFALSE1TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2331replication_dependency_trackingTRUE1TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2341resource_limitFALSE1TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2351resource_manager_cpu_allocation23TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2361resource_manager_plan2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2371result_cache_max_result53TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2381result_cache_max_size2031616 (1984K)6TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2391result_cache_modeMANUAL2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2401result_cache_remote_expiration03TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
#Inst
ID
ParameterCurrent
Value
TypeIs
Default
Is
System
Modifiable
Is
Instance
Modifiable
Is
Session
Modifiable
Is
Modified
Is
Adjusted
Is
Deprecated
2411resumable_timeout03TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2421rollback_segments2TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2431sec_case_sensitive_logonTRUE1TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2441sec_max_failed_login_attempts103TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2451sec_protocol_error_further_actionCONTINUE2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2461sec_protocol_error_trace_actionTRACE2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2471sec_return_server_release_bannerFALSE1TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2481serial_reusedisable2TRUEFALSEFALSEFALSEFALSEFALSETRUE
2491service_namesora11r12TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2501session_cached_cursors503TRUEFALSEFALSETRUEFALSEFALSEFALSE
2511session_max_open_files103TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2521sessions1703TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2531sga_max_size536870912 (512M)6TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2541sga_target06TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2551shadow_core_dumpnone2TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2561shared_memory_address03TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2571shared_pool_reserved_size102760446TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2581shared_pool_size06TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2591shared_server_sessions3TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2601shared_servers13TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2611skip_unusable_indexesTRUE1TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2621smtp_out_server2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2631sort_area_retained_size03TRUEDEFERREDTRUETRUEFALSEFALSEFALSE
2641sort_area_size655363TRUEDEFERREDTRUETRUEFALSEFALSEFALSE
2651spfileC:\ORACLE\PRODUCT\11.1.0\DB_1\DATABASE\SPFILEORA11R1.ORA2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2661sql92_securityFALSE1TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2671sql_traceFALSE1TRUEIMMEDIATETRUETRUEFALSEFALSETRUE
2681sql_versionNATIVE2TRUEFALSEFALSETRUEFALSEFALSETRUE
2691sqltune_categoryDEFAULT2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2701standby_archive_dest%ORACLE_HOME%\RDBMS2TRUEIMMEDIATETRUEFALSEFALSEFALSETRUE
#Inst
ID
ParameterCurrent
Value
TypeIs
Default
Is
System
Modifiable
Is
Instance
Modifiable
Is
Session
Modifiable
Is
Modified
Is
Adjusted
Is
Deprecated
2711standby_file_managementMANUAL2TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2721star_transformation_enabledFALSE2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2731statistics_levelTYPICAL2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2741streams_pool_size06TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2751tape_asynch_ioTRUE1TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2761thread03TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2771timed_os_statistics03TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2781timed_statisticsTRUE1TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2791trace_enabledTRUE1TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2801tracefile_identifierIGNORE_ME_SQLT2TRUEFALSEFALSETRUEMODIFIEDFALSEFALSE
2811transactions1873TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2821transactions_per_rollback_segment53TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2831undo_managementAUTO2TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2841undo_retention9003TRUEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2851undo_tablespaceUNDOTBS12FALSEIMMEDIATETRUEFALSEFALSEFALSEFALSE
2861use_indirect_data_buffersFALSE1TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2871user_dump_destc:\oracle\diag\rdbms\ora11r1\ora11r1\trace2FALSEIMMEDIATETRUEFALSEFALSEFALSETRUE
2881utl_file_dir2TRUEFALSEFALSEFALSEFALSEFALSEFALSE
2891workarea_size_policyAUTO2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
2901xml_db_eventsenable2TRUEIMMEDIATETRUETRUEFALSEFALSEFALSE
Back to Top

Tool Configuration Parameters

[+]

Explain Plan 2196188739 as per EXPLAIN PLAN FOR

IDExec
Order
Plan OperationObject
ID
CostEstim
Card
Search
Cols
Indexed
Cols
AP:
FP:
CBO
Stats
Metadata
0: 11 SELECT STATEMENT 14961
1: 10  SORT GROUP BY NOSORT 14961
2: 9 . MERGE JOIN 1491019
3: 6 .. SORT JOIN 11363
4: 5 ... VIEW VW_GBC_17 11363
5: 4 .... HASH GROUP BY 11363
6: 3 ....+ HASH JOIN 1122225 [+]
7: 1 ....+. TABLE ACCESS FULL MASTER 74055104472 [+] Stats Metadata
8: 2 ....+. TABLE ACCESS FULL MANAGERS 7405272895 Stats Metadata
9: 8 .. SORT JOIN 361019 [+]
10: 7 ... TABLE ACCESS FULL TEAMS 74053351019 [+] Stats Metadata
Other XML: [+]
Back to Top

[-]
IDObject
Owner
Object
Name
Object
Type
Object
ID
Parent
ID
PosObj
Inst
Search
Cols
Estim
Card
Estim
Bytes
Operation
Cost
(IO + CPU)
Plan
Table
Cost
IO
Cost
CPU
Cycles
TimeTemp
Space
Query
Block
AliasRemarks
0: 149611281148.820239149145846217832
1: 01611281148.820239149145846217832SEL$6989E656
2: 11101921399148.820239149145846217832
3: 21631008112.721843113110602913092
4: SYSVW_GBC_17VIEW3162631008112.721843113110602913092SEL$207703A3VW_GBC_17@SEL$6989E656
5: 41631701112.721843113110602913092SEL$207703A3
6: 51222560075111.67152112110370256972
7: USER0MASTERTABLE740556124725664104.118765104103247816612SEL$207703A3MA@SEL$1
8: USER0MANAGERSTABLE740526212895434257.03649778082791SEL$207703A3MG@SEL$1
9: 221019509536.0983963635243304741
10: USER0TEAMSTABLE740539131019509535.077684353517207791SEL$6989E656T@SEL$1
Back to Top

[-]
Access Predicates, Filter Predicates and Rows Source Projection per Operation ID
IDColumn NameColumn Value
1: Projection(#keys=1) "T"."TEAM_ID"[VARCHAR2,3], SUM("ITEM_2")[22]
2: Projection(#keys=0) "T"."TEAM_ID"[VARCHAR2,3], "ITEM_2"[NUMBER,22]
3: Projection(#keys=1) "ITEM_1"[VARCHAR2,3], "ITEM_2"[NUMBER,22]
4: Projection"ITEM_1"[VARCHAR2,3], "ITEM_2"[NUMBER,22]
5: Projection(#keys=1) "MG"."TEAM_ID"[VARCHAR2,3], COUNT(*)[22]
6: Access Predicates"MG"."LAHMANID"="MA"."LAHMANID"
6: Projection(#keys=1) "MG"."TEAM_ID"[VARCHAR2,3]
7: Filter Predicates"MA"."DEBUT_SEASON_YYYY"=1965
7: Projection"MA"."LAHMANID"[VARCHAR2,12]
8: Projection"MG"."LAHMANID"[VARCHAR2,12], "MG"."TEAM_ID"[VARCHAR2,3]
9: Access Predicates"T"."TEAM_ID"="ITEM_1"
9: Filter Predicates"T"."TEAM_ID"="ITEM_1"
9: Projection(#keys=1) "T"."TEAM_ID"[VARCHAR2,3]
10: Filter Predicates"T"."WIN_PCT">.5
10: Projection"T"."TEAM_ID"[VARCHAR2,3]
Back to Top

Join Order

[-]
Summary.
#Table NameAccess TypeIndex Name
1MATABLE ONLY
2MGTABLE ONLY
3TTABLE ONLY
Back to Top

[-]
Detail.
#Query
Block
Name
Oper
ID
Table NameObject AliasAccess TypeTable AccessTable
Access
Cost
Table
Orig
Card
Table
Estim
Card
Table
Starts
Table
Actual
Rows
Index NameIndex AccessIndex
Access
Cost
Index
Estim
Card
Index
Starts
Index
Actual
Rows
1SEL$207703A37USER0.MASTERMA@SEL$1TABLE ONLYFULL10461400472
2SEL$207703A38USER0.MANAGERSMG@SEL$1TABLE ONLYFULL728952895
3SEL$6989E65610USER0.TEAMST@SEL$1TABLE ONLYFULL3523271019
Back to Top

Object Dependency (DBA_OBJECT_DEPENDENCY)

[-]
Recursive list of Objects referenced by the Explain Plan.
#Inst
ID
TypeObject
Owner.Name
Hash
Value
AddressObject
ID
Script
Version
MetadataRef Object
Owner.Name
Ref
Hash Value
Ref
Address
Ref
Source
11TABLEUSER0.MANAGERS78187046627D4463874052 Metadata 25048663152B1680BCV$OBJECT_DEPENDENCY
21TABLEUSER0.MASTER294497917927EE10F074055 Metadata 25048663152B1680BCV$OBJECT_DEPENDENCY
31TABLEUSER0.TEAMS138528455827EE0EA474053 Metadata 25048663152B1680BCV$OBJECT_DEPENDENCY
Back to Top

Objects (DBA_OBJECTS)

[-]
#Object
Type
OwnerObject
Name
Sub
Object
Name
Object
ID
Data
Object
ID
CreatedLast
DDL
TimestampSegment
Blocks
ExtentsStatusTemporaryGeneratedSecondary Metadata
1TABLEUSER0MANAGERS740527405224-AUG-09 04:33:3724-AUG-09 04:33:372009-08-24:04:33:3712816VALIDNNN Metadata
2TABLEUSER0MASTER740557405524-AUG-09 04:33:3724-AUG-09 04:33:372009-08-24:04:33:375124VALIDNNN Metadata
3TABLEUSER0TEAMS740537405324-AUG-09 04:33:3724-AUG-09 04:33:372009-08-24:04:33:372562VALIDNNN Metadata
4INDEXUSER0TEAMS_PK740547405424-AUG-09 04:33:3724-AUG-09 04:33:372009-08-24:04:33:37162VALIDNNN Metadata
Back to Top

Tables (DBA_TABLES and DBA_TAB_STATISTICS)

[-]
CBO Statistics.
#Owner.Table Name Current
COUNT
Num*
Rows
Sample*
Size
Last*
Analyzed
Avg*
Row
Len
Chain*
Cnt
Segment
Blocks
Blocks*Empty*
Blocks
Avg*
Space
Global*
Stats
User*
Stats
CBO
Stats
Metadata Stats
Type
Locked
Data
Stats
Locked
Cache
Stats
Locked
MonitorStale
Stats
InsertsUpdatesDeletesTimestampAvg
Cached
Blocks
Avg
Cache
Hit
Ratio
1USER0.MANAGERS 2895 2895289524-AUG-09 05:12:2249012820108750NONO Stats Metadata NOYESNO
2USER0.MASTER 61400 614006140024-AUG-09 05:12:23430512376136685NONO Stats Metadata NOYESNO
3USER0.TEAMS 2327 2327232724-AUG-09 05:12:2225602561241323233NONO Stats Metadata NOYESNO
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics
Back to Top

[-]
Table Attributes.
#Owner.Table NameObject
ID
TablespaceTemporaryDurationPartitionedLoggingDegreeCacheIOT
Type
NestedDependenciesCompressionIni
Trans
Max
Trans
Freelist
Groups
Freelists
1USER0.MANAGERS74052USERSNNOYES 1 NNODISABLEDDISABLED1255
2USER0.MASTER74055USERSNNOYES 1 NNODISABLEDDISABLED1255
3USER0.TEAMS74053USERSNNOYES 1 NNODISABLEDDISABLED1255
Back to Top

Table Columns (DBA_TAB_COLUMNS)

Back to Top

Table Columns for USER0.MANAGERS

[-]
Table Columns Attributes for USER0.MANAGERS.
Col
ID
Column NameData TypeData
Type
Mod
Data
Type
Owner
Data
Length
Data
Precision
Data
Scale
NullableCharacter
Set Name
Char
Length
Char
Used
Index
Count
Leading
Index
Count
Avg*
Col
Len
Equality
Preds
Equijoin
Preds
Non
Equijoin
Preds
Range
Preds
LIKE
Preds
NULL
Preds
Timestamp
1RECNUMNUMBER22N03
2LAHMANIDVARCHAR212NCHAR_CS12B906000022-SEP-09 20:47:46
3DATE_YYYYNUMBER22Y0344000022-SEP-09 20:47:46
4SEASON_HALF_IDVARCHAR21YCHAR_CS1B1
5TEAM_IDVARCHAR23YCHAR_CS3B306000022-SEP-09 20:47:46
6LEAGUE_IDVARCHAR22YCHAR_CS2B204000022-SEP-09 20:47:46
7DIVISION_IDVARCHAR21YCHAR_CS1B1
8GAME_QTYNUMBER22Y03
9WIN_QTYNUMBER22Y03
10LOSS_QTYNUMBER22Y03
11WIN_PCTNUMBER2252Y03
12PLACE_FINISHED_NRNUMBER22Y02
13ORDER_MANAGED_IDVARCHAR210YCHAR_CS10B3
14PLAY_MANAGE_Y_NVARCHAR21YCHAR_CS1B1
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics
Back to Table Columns
Back to Top

[-]
CBO Statistics for USER0.MANAGERS.
Col
ID
Column NameNum*
Rows
Sample*
Size
Last*
Analyzed
Global*
Stats
User*
Stats
Num*
Nulls
Num*
Distinct
Num*
Popular
Values
Density*New*
Density
Histogram*
1RECNUM2895289524-AUG-09 05:12:22NONO02895 3.4542e-04 NONE
2LAHMANID2895289524-AUG-09 05:12:22NONO0614 1.6287e-03 NONE
3DATE_YYYY2895289524-AUG-09 05:12:22NONO0129 7.7519e-03 NONE
4SEASON_HALF_ID2895289524-AUG-09 05:12:22NONO28022 5.0000e-01 NONE
5TEAM_ID2895289524-AUG-09 05:12:22NONO063 1.5873e-02 NONE
6LEAGUE_ID2895289524-AUG-09 05:12:22NONO07 1.4286e-01 NONE
7DIVISION_ID2895289524-AUG-09 05:12:22NONO18883 3.3333e-01 NONE
8GAME_QTY2895289524-AUG-09 05:12:22NONO0165 6.0606e-03 NONE
9WIN_QTY2895289524-AUG-09 05:12:22NONO0114 8.7719e-03 NONE
10LOSS_QTY2895289524-AUG-09 05:12:22NONO0117 8.5470e-03 NONE
11WIN_PCT2895289524-AUG-09 05:12:22NONO01122 8.9127e-04 NONE
12PLACE_FINISHED_NR2895289524-AUG-09 05:12:22NONO013 7.6923e-02 NONE
13ORDER_MANAGED_ID2895289524-AUG-09 05:12:22NONO196129 3.4483e-02 NONE
14PLAY_MANAGE_Y_N2895289524-AUG-09 05:12:22NONO22631 1.0000e+00 NONE
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics
Back to Table Columns
Back to Top

[-]
Table Columns Data Distribution for USER0.MANAGERS.
Col
ID
Column NameLow Value*#High Value*# Num*
Buckets
Buckets*
Pop Vals
Histogram* Rows per
Bucket
Mutating
Number of
EndPoints
EndPoints1
History
Hidden
Column
Qualified
Col Name
Data
Default
1RECNUM"1""2895" 1 NONE NO 0: NO RECNUM
2LAHMANID"adairbi99""zimmedo01" 1 NONE NO 0: NO LAHMANID
3DATE_YYYY"1871""1999" 1 NONE NO 0: NO DATE_YYYY
4SEASON_HALF_ID"1""2" 1 NONE NO 0: NO SEASON_HALF_ID
5TEAM_ID"ALT""WOR" 1 NONE NO 0: NO TEAM_ID
6LEAGUE_ID"A""U" 1 NONE NO 0: NO LEAGUE_ID
7DIVISION_ID"C""W" 1 NONE NO 0: NO DIVISION_ID
8GAME_QTY"1""165" 1 NONE NO 0: NO GAME_QTY
9WIN_QTY"0""116" 1 NONE NO 0: NO WIN_QTY
10LOSS_QTY"0""120" 1 NONE NO 0: NO LOSS_QTY
11WIN_PCT"0""100" 1 NONE NO 0: NO WIN_PCT
12PLACE_FINISHED_NR"1""13" 1 NONE NO 0: NO PLACE_FINISHED_NR
13ORDER_MANAGED_ID"1 of 2""9 of 9" 1 NONE NO 0: NO ORDER_MANAGED_ID
14PLAY_MANAGE_Y_N"Y""Y" 1 NONE NO 0: NO PLAY_MANAGE_Y_N
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics
(#) Low and High values may be hidden using "skip_histograms" tool configuration parameter
(1) CBO Stats Snapshots count, followed by list of Histogram EndPoints counts per Snapshot from newest to oldest
Back to Table Columns
Back to Top

Table Columns for USER0.MASTER

[-]
Table Columns Attributes for USER0.MASTER.
Col
ID
Column NameData TypeData
Type
Mod
Data
Type
Owner
Data
Length
Data
Precision
Data
Scale
NullableCharacter
Set Name
Char
Length
Char
Used
Index
Count
Leading
Index
Count
Avg*
Col
Len
Equality
Preds
Equijoin
Preds
Non
Equijoin
Preds
Range
Preds
LIKE
Preds
NULL
Preds
Timestamp
1LAHMANIDVARCHAR212NCHAR_CS12B906000022-SEP-09 20:47:46
2LAST_NAME_TXVARCHAR225YCHAR_CS25B7
3FIRST_NAME_TXVARCHAR220YCHAR_CS20B5
4BATS_RIGHT_LEFT_IDVARCHAR21YCHAR_CS1B1
5THROWS_RT_LEFT_IDVARCHAR21YCHAR_CS1B1
6BIRTH_DATE_MMNUMBER22Y02
7BIRTH_DATE_DDNUMBER22Y02
8BIRTH_DATE_YYYYNUMBER22Y03
9DEBUT_SEASON_YYYYNUMBER22Y0360000022-SEP-09 20:47:46
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics
Back to Table Columns
Back to Top

[-]
CBO Statistics for USER0.MASTER.
Col
ID
Column NameNum*
Rows
Sample*
Size
Last*
Analyzed
Global*
Stats
User*
Stats
Num*
Nulls
Num*
Distinct
Num*
Popular
Values
Density*New*
Density
Histogram*
1LAHMANID614006140024-AUG-09 05:12:23NONO015350 6.5147e-05 NONE
2LAST_NAME_TX614006140024-AUG-09 05:12:23NONO08277 1.2082e-04 NONE
3FIRST_NAME_TX614006140024-AUG-09 05:12:23NONO2441839 5.4377e-04 NONE
4BATS_RIGHT_LEFT_ID614006140024-AUG-09 05:12:23NONO55403 3.3333e-01 NONE
5THROWS_RT_LEFT_ID614006140024-AUG-09 05:12:23NONO45282 5.0000e-01 NONE
6BIRTH_DATE_MM614006140024-AUG-09 05:12:23NONO52413 7.6923e-02 NONE
7BIRTH_DATE_DD614006140024-AUG-09 05:12:23NONO52432 3.1250e-02 NONE
8BIRTH_DATE_YYYY614006140024-AUG-09 05:12:23NONO524147 6.8027e-03 NONE
9DEBUT_SEASON_YYYY614006140024-AUG-09 05:12:23NONO524129 7.7519e-03 NONE
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics
Back to Table Columns
Back to Top

[-]
Table Columns Data Distribution for USER0.MASTER.
Col
ID
Column NameLow Value*#High Value*# Num*
Buckets
Buckets*
Pop Vals
Histogram* Rows per
Bucket
Mutating
Number of
EndPoints
EndPoints1
History
Hidden
Column
Qualified
Col Name
Data
Default
1LAHMANID"aaronha01""zwilldu01" 1 NONE NO 0: NO LAHMANID
2LAST_NAME_TX"AARON""ZWILLING" 1 NONE NO 0: NO LAST_NAME_TX
3FIRST_NAME_TX"A. J.""ZOILO" 1 NONE NO 0: NO FIRST_NAME_TX
4BATS_RIGHT_LEFT_ID"B""R" 1 NONE NO 0: NO BATS_RIGHT_LEFT_ID
5THROWS_RT_LEFT_ID"L""R" 1 NONE NO 0: NO THROWS_RT_LEFT_ID
6BIRTH_DATE_MM"0""12" 1 NONE NO 0: NO BIRTH_DATE_MM
7BIRTH_DATE_DD"0""31" 1 NONE NO 0: NO BIRTH_DATE_DD
8BIRTH_DATE_YYYY"1800""1979" 1 NONE NO 0: NO BIRTH_DATE_YYYY
9DEBUT_SEASON_YYYY"1871""1999" 1 NONE NO 0: NO DEBUT_SEASON_YYYY
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics
(#) Low and High values may be hidden using "skip_histograms" tool configuration parameter
(1) CBO Stats Snapshots count, followed by list of Histogram EndPoints counts per Snapshot from newest to oldest
Back to Table Columns
Back to Top

Table Columns for USER0.TEAMS

[-]
Table Columns Attributes for USER0.TEAMS.
Col
ID
Column NameData TypeData
Type
Mod
Data
Type
Owner
Data
Length
Data
Precision
Data
Scale
NullableCharacter
Set Name
Char
Length
Char
Used
Index
Count
Leading
Index
Count
Avg*
Col
Len
Equality
Preds
Equijoin
Preds
Non
Equijoin
Preds
Range
Preds
LIKE
Preds
NULL
Preds
Timestamp
1RECNUMNUMBER22N03
2DATE_YYYYNUMBER22N011344000022-SEP-09 20:47:46
3LEAGUE_IDVARCHAR22NCHAR_CS2B1204000022-SEP-09 20:47:46
4POSITION_PLAYED_IDNUMBER22Y02
5TEAM_IDVARCHAR23NCHAR_CS3B1306000022-SEP-09 20:47:46
6GAME_PLAYED_QTYNUMBER22Y03
7WIN_QTYNUMBER22Y03
8LOSS_QTYNUMBER22Y03
9WIN_PCTNUMBER2252Y0200060022-SEP-09 20:47:46
10GAMES_BEHIND_QTYNUMBER22Y0200040022-SEP-09 20:47:46
11RUN_QTYNUMBER22Y03
12OPPONENT_RUN_QTYNUMBER22Y03
13AT_BAT_QTYNUMBER22Y03
14HIT_QTYNUMBER22Y03
15DOUBLE_QTYNUMBER22Y03
16TRIPLE_QTYNUMBER22Y03
17HOME_RUN_QTYNUMBER22Y03
18WALK_QTYNUMBER22Y03
19STRIKE_OUT_QTYNUMBER22Y03
20BATTING_AVGNUMBER2243Y03
21ON_BASE_PCTNUMBER2243Y03
22SLUGGING_PCTNUMBER2243Y03
23STOLEN_BASE_QTYNUMBER22Y03
24CAUGHT_STEALING_QYNUMBER22Y02
25EARNED_RUN_AVGNUMBER2242Y03
26COMPLETE_GAME_QTYNUMBER22Y03
27SHUTOUT_QTYNUMBER22Y02
28SAVE_QTYNUMBER22Y02
29INNING_PITCHED_QTYNUMBER2262Y03
30HIT_BY_OPP_QTYNUMBER22Y03
Col
ID
Column NameData TypeData
Type
Mod
Data
Type
Owner
Data
Length
Data
Precision
Data
Scale
NullableCharacter
Set Name
Char
Length
Char
Used
Index
Count
Leading
Index
Count
Avg*
Col
Len
Equality
Preds
Equijoin
Preds
Non
Equijoin
Preds
Range
Preds
LIKE
Preds
NULL
Preds
Timestamp
31HOME_RUN_BY_OPP_QYNUMBER22Y03
32WALK_BY_OPP_QTYNUMBER22Y03
33STRIKE_OUT_OPP_QTYNUMBER22Y03
34PARK_BAT_FACTORNUMBER22Y03
35PARK_PITCH_FACTORNUMBER22Y03
36BALLPARK_NAMEVARCHAR2200YCHAR_CS200B124
37ATTENDENCE_QTYNUMBER22Y0400040022-SEP-09 20:47:46
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics
Back to Table Columns
Back to Top

[-]
CBO Statistics for USER0.TEAMS.
Col
ID
Column NameNum*
Rows
Sample*
Size
Last*
Analyzed
Global*
Stats
User*
Stats
Num*
Nulls
Num*
Distinct
Num*
Popular
Values
Density*New*
Density
Histogram*
1RECNUM2327232724-AUG-09 05:12:22NONO02327 4.2974e-04 NONE
2DATE_YYYY2327232724-AUG-09 05:12:22NONO0129 7.7519e-03 NONE
3LEAGUE_ID2327232724-AUG-09 05:12:22NONO07 1.4286e-01 NONE
4POSITION_PLAYED_ID2327232724-AUG-09 05:12:22NONO013 7.6923e-02 NONE
5TEAM_ID2327232724-AUG-09 05:12:22NONO061 1.6393e-02 NONE
6GAME_PLAYED_QTY2327232724-AUG-09 05:12:22NONO2122 8.1967e-03 NONE
7WIN_QTY2327232724-AUG-09 05:12:22NONO2112 8.9286e-03 NONE
8LOSS_QTY2327232724-AUG-09 05:12:22NONO2112 8.9286e-03 NONE
9WIN_PCT2327232724-AUG-09 05:12:22NONO272 1.3889e-02 NONE
10GAMES_BEHIND_QTY2327232724-AUG-09 05:12:22NONO268 1.4706e-02 NONE
11RUN_QTY2327232724-AUG-09 05:12:22NONO2603 1.6584e-03 NONE
12OPPONENT_RUN_QTY2327232724-AUG-09 05:12:22NONO2594 1.6835e-03 NONE
13AT_BAT_QTY2327232724-AUG-09 05:12:22NONO21069 9.3545e-04 NONE
14HIT_QTY2327232724-AUG-09 05:12:22NONO2722 1.3850e-03 NONE
15DOUBLE_QTY2327232724-AUG-09 05:12:22NONO2297 3.3670e-03 NONE
16TRIPLE_QTY2327232724-AUG-09 05:12:22NONO2123 8.1301e-03 NONE
17HOME_RUN_QTY2327232724-AUG-09 05:12:22NONO2231 4.3290e-03 NONE
18WALK_QTY2327232724-AUG-09 05:12:22NONO2561 1.7825e-03 NONE
19STRIKE_OUT_QTY2327232724-AUG-09 05:12:22NONO144865 1.1561e-03 NONE
20BATTING_AVG2327232724-AUG-09 05:12:22NONO2125 8.0000e-03 NONE
21ON_BASE_PCT2327232724-AUG-09 05:12:22NONO2159 6.2893e-03 NONE
22SLUGGING_PCT2327232724-AUG-09 05:12:22NONO2223 4.4843e-03 NONE
23STOLEN_BASE_QTY2327232724-AUG-09 05:12:22NONO148323 3.0960e-03 NONE
24CAUGHT_STEALING_QY2327232724-AUG-09 05:12:22NONO863136 7.3529e-03 NONE
25EARNED_RUN_AVG2327232724-AUG-09 05:12:22NONO2372 2.6882e-03 NONE
26COMPLETE_GAME_QTY2327232724-AUG-09 05:12:22NONO2146 6.8493e-03 NONE
27SHUTOUT_QTY2327232724-AUG-09 05:12:22NONO232 3.1250e-02 NONE
28SAVE_QTY2327232724-AUG-09 05:12:22NONO264 1.5625e-02 NONE
29INNING_PITCHED_QTY2327232724-AUG-09 05:12:22NONO2544 1.8382e-03 NONE
30HIT_BY_OPP_QTY2327232724-AUG-09 05:12:22NONO2720 1.3889e-03 NONE
Col
ID
Column NameNum*
Rows
Sample*
Size
Last*
Analyzed
Global*
Stats
User*
Stats
Num*
Nulls
Num*
Distinct
Num*
Popular
Values
Density*New*
Density
Histogram*
31HOME_RUN_BY_OPP_QY2327232724-AUG-09 05:12:22NONO2217 4.6083e-03 NONE
32WALK_BY_OPP_QTY2327232724-AUG-09 05:12:22NONO2557 1.7953e-03 NONE
33STRIKE_OUT_OPP_QTY2327232724-AUG-09 05:12:22NONO34883 1.1325e-03 NONE
34PARK_BAT_FACTOR2327232724-AUG-09 05:12:22NONO3241 2.4390e-02 NONE
35PARK_PITCH_FACTOR2327232724-AUG-09 05:12:22NONO3238 2.6316e-02 NONE
36BALLPARK_NAME2327232724-AUG-09 05:12:22NONO36180 5.5556e-03 NONE
37ATTENDENCE_QTY2327232724-AUG-09 05:12:22NONO02039 4.9044e-04 NONE
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics
Back to Table Columns
Back to Top

[-]
Table Columns Data Distribution for USER0.TEAMS.
Col
ID
Column NameLow Value*#High Value*# Num*
Buckets
Buckets*
Pop Vals
Histogram* Rows per
Bucket
Mutating
Number of
EndPoints
EndPoints1
History
Hidden
Column
Qualified
Col Name
Data
Default
1RECNUM"1""2327" 1 NONE NO 0: NO RECNUM
2DATE_YYYY"1871""1999" 1 NONE NO 0: NO DATE_YYYY 0
3LEAGUE_ID"A""U" 1 NONE NO 0: NO LEAGUE_ID
4POSITION_PLAYED_ID"1""13" 1 NONE NO 0: NO POSITION_PLAYED_ID
5TEAM_ID"ALT""WOR" 1 NONE NO 0: NO TEAM_ID
6GAME_PLAYED_QTY"6""165" 1 NONE NO 0: NO GAME_PLAYED_QTY
7WIN_QTY"0""116" 1 NONE NO 0: NO WIN_QTY
8LOSS_QTY"4""134" 1 NONE NO 0: NO LOSS_QTY
9WIN_PCT"0"".89" 1 NONE NO 0: NO WIN_PCT
10GAMES_BEHIND_QTY"0""84" 1 NONE NO 0: NO GAMES_BEHIND_QTY
11RUN_QTY"24""1220" 1 NONE NO 0: NO RUN_QTY
12OPPONENT_RUN_QTY"34""1252" 1 NONE NO 0: NO OPPONENT_RUN_QTY
13AT_BAT_QTY"211""5779" 1 NONE NO 0: NO AT_BAT_QTY
14HIT_QTY"33""1783" 1 NONE NO 0: NO HIT_QTY
15DOUBLE_QTY"3""373" 1 NONE NO 0: NO DOUBLE_QTY
16TRIPLE_QTY"0""150" 1 NONE NO 0: NO TRIPLE_QTY
17HOME_RUN_QTY"0""264" 1 NONE NO 0: NO HOME_RUN_QTY
18WALK_QTY"0""835" 1 NONE NO 0: NO WALK_QTY
19STRIKE_OUT_QTY"3""1268" 1 NONE NO 0: NO STRIKE_OUT_QTY
20BATTING_AVG".156"".349" 1 NONE NO 0: NO BATTING_AVG
21ON_BASE_PCT".156"".418" 1 NONE NO 0: NO ON_BASE_PCT
22SLUGGING_PCT".18"".489" 1 NONE NO 0: NO SLUGGING_PCT
23STOLEN_BASE_QTY"0""581" 1 NONE NO 0: NO STOLEN_BASE_QTY
24CAUGHT_STEALING_QY"1""191" 1 NONE NO 0: NO CAUGHT_STEALING_QY
25EARNED_RUN_AVG"1.22""8" 1 NONE NO 0: NO EARNED_RUN_AVG
26COMPLETE_GAME_QTY"1""148" 1 NONE NO 0: NO COMPLETE_GAME_QTY
27SHUTOUT_QTY"0""32" 1 NONE NO 0: NO SHUTOUT_QTY
28SAVE_QTY"0""68" 1 NONE NO 0: NO SAVE_QTY
29INNING_PITCHED_QTY"54""1506" 1 NONE NO 0: NO INNING_PITCHED_QTY
30HIT_BY_OPP_QTY"49""1993" 1 NONE NO 0: NO HIT_BY_OPP_QTY
Col
ID
Column NameLow Value*#High Value*# Num*
Buckets
Buckets*
Pop Vals
Histogram* Rows per
Bucket
Mutating
Number of
EndPoints
EndPoints1
History
Hidden
Column
Qualified
Col Name
Data
Default
31HOME_RUN_BY_OPP_QY"0""241" 1 NONE NO 0: NO HOME_RUN_BY_OPP_QY
32WALK_BY_OPP_QTY"0""827" 1 NONE NO 0: NO WALK_BY_OPP_QTY
33STRIKE_OUT_OPP_QTY"0""1245" 1 NONE NO 0: NO STRIKE_OUT_OPP_QTY
34PARK_BAT_FACTOR"54""129" 1 NONE NO 0: NO PARK_BAT_FACTOR
35PARK_PITCH_FACTOR"54""129" 1 NONE NO 0: NO PARK_PITCH_FACTOR
36BALLPARK_NAME"23rd Street Grounds Update the b""Yankee Stadium Update the ball p" 1 NONE NO 0: NO BALLPARK_NAME
37ATTENDENCE_QTY"0""4483350" 1 NONE NO 0: NO ATTENDENCE_QTY
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics
(#) Low and High values may be hidden using "skip_histograms" tool configuration parameter
(1) CBO Stats Snapshots count, followed by list of Histogram EndPoints counts per Snapshot from newest to oldest
Back to Table Columns
Back to Top

Indexes (DBA_INDEXES and DBA_IND_STATISTICS)

[-]
Index Type and indexed columns.
#Table Owner.Table NameIndex Owner.Index NameOper
ID
Index
Type
UniquenessNum
Cols
Idx
Col
Ids
Indexed Column NamesCBO
Stats
Metadata Stats
Type
Locked
Stale
Stats
Avg
Cached
Blocks
Avg
Cache
Hit
Ratio
1USER0.TEAMSUSER0.TEAMS_PKNORMALUNIQUE32
3
5
DATE_YYYY
LEAGUE_ID
TEAM_ID
Stats Metadata NO
Back to Top

[-]
CBO Statistics.
#Table Owner.Table NameIndex Owner.Index NameNum*
Rows
Sample*
Size
Last*
Analyzed
Global*
Stats
User*
Stats
Distinct*
Keys
Blevel*Segment
Blocks
Leaf*
Blocks
Avg*
Leaf
Blocks
per
Key
Avg*
Data
Blocks
per
Key
Clustering*
Factor
Table*
Blocks
Table*
Num
Rows
Max
Idx
Sel
1USER0.TEAMSUSER0.TEAMS_PK2327232724-AUG-09 05:12:22NONO232711611111141242327 0.1638
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics
Back to Top

[-]
Index Attributes.
#Table Owner.Table NameIndex Owner.Index NameObject
ID
TablespaceTemporaryPartitionedLoggingDegreeIni
Trans
Max
Trans
Freelist
Groups
FreelistsStatusDomidx
Status
Funcidx
Status
1USER0.TEAMSUSER0.TEAMS_PK74054USERSNNOYES12255VALID
Back to Top

[-]
CBO Statistics History (WRI$_OPTSTAT_IND_HISTORY).
#Table Owner.Table NameIndex Owner.Index NameSave TimeAnalyze TimeNum
Rows
Sample
Size
Distinct
Keys
BlevelLeaf
Blocks
Avg
Leaf
Blocks
per
Key
Avg
Data
Blocks
per
Key
Clustering
Factor
Global
Stats
User
Stats
GuessqCache
Dblk
Cache
Hit
Logical
Read
1USER0.TEAMSUSER0.TEAMS_PK24-AUG-09 05.12.22.890000 AM -05:00NONO
Back to Top

Indexed Columns (DBA_IND_COLUMNS)

Back to Top

Indexed Columns for USER0.TEAMS

[-]
Multi-column (composite) and function-based indexes for USER0.TEAMS.
Col
ID
Column NameTEAMS PKColumn NameCol
ID
2DATE_YYYY1DATE_YYYY2
3LEAGUE_ID2LEAGUE_ID3
5TEAM_ID3TEAM_ID5
Indexes are ordered left to right by leading columns. Index names are displayed vertical in IE.
Back to Indexed Columns
Back to Top


[-]
Indexed Columns Attributes for USER0.TEAMS.
Col
ID
Column NameData TypeData
Type
Mod
Data
Type
Owner
Data
Length
Data
Precision
Data
Scale
NullableCharacter
Set Name
Char
Length
Char
Used
Index
Count
Leading
Index
Count
Avg*
Col
Len
Equality
Preds
Equijoin
Preds
Non
Equijoin
Preds
Range
Preds
LIKE
Preds
NULL
Preds
Timestamp
2DATE_YYYYNUMBER22N011344000022-SEP-09 20:47:46
3LEAGUE_IDVARCHAR22NCHAR_CS2B1204000022-SEP-09 20:47:46
5TEAM_IDVARCHAR23NCHAR_CS3B1306000022-SEP-09 20:47:46
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics
Back to Indexed Columns
Back to Top


[-]
CBO Statistics for USER0.TEAMS.
Col
ID
Column NameNum*
Rows
Sample*
Size
Last*
Analyzed
Global*
Stats
User*
Stats
Num*
Nulls
Num*
Distinct
Num*
Popular
Values
Density*New*
Density
Histogram*
2DATE_YYYY2327232724-AUG-09 05:12:22NONO0129 7.7519e-03 NONE
3LEAGUE_ID2327232724-AUG-09 05:12:22NONO07 1.4286e-01 NONE
5TEAM_ID2327232724-AUG-09 05:12:22NONO061 1.6393e-02 NONE
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics
Back to Indexed Columns
Back to Top


[-]
Indexed Columns Data Distribution for USER0.TEAMS.
Col
ID
Column NameLow Value*#High Value*# Num*
Buckets
Buckets*
Pop Vals
Histogram* Rows per
Bucket
Mutating
Number of
EndPoints
EndPoints1
History
Hidden
Column
Qualified
Col Name
Data
Default
2DATE_YYYY"1871""1999" 1 NONE NO 0: NO DATE_YYYY 0
3LEAGUE_ID"A""U" 1 NONE NO 0: NO LEAGUE_ID
5TEAM_ID"ALT""WOR" 1 NONE NO 0: NO TEAM_ID
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics
(#) Low and High values may be hidden using "skip_histograms" tool configuration parameter
(1) CBO Stats Snapshots count, followed by list of Histogram EndPoints counts per Snapshot from newest to oldest
Back to Indexed Columns
Back to Top

Index Columns (DBA_IND_COLUMNS)

Back to Top

Index Columns for USER0.TEAMS_PK on USER0.TEAMS

[-]
Index Columns Attributes for USER0.TEAMS_PK on USER0.TEAMS.
Col
Pos
Col
ID
Column NameDescendData TypeData
Type
Mod
Data
Type
Owner
Data
Length
Data
Precision
Data
Scale
NullableCharacter
Set Name
Char
Length
Char
Used
Index
Count
Leading
Index
Count
Avg*
Col
Len
Equality
Preds
Equijoin
Preds
Non
Equijoin
Preds
Range
Preds
LIKE
Preds
NULL
Preds
Timestamp
12DATE_YYYYASCNUMBER22N011344000022-SEP-09 20:47:46
23LEAGUE_IDASCVARCHAR22NCHAR_CS2B1204000022-SEP-09 20:47:46
35TEAM_IDASCVARCHAR23NCHAR_CS3B1306000022-SEP-09 20:47:46
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics
Back to Index Columns
Back to Top


[-]
CBO Statistics for USER0.TEAMS_PK on USER0.TEAMS.
Col
Pos
Col
ID
Column NameNum*
Rows
Sample*
Size
Last*
Analyzed
Global*
Stats
User*
Stats
Num*
Nulls
Num*
Distinct
Num*
Popular
Values
Density*New*
Density
Histogram*
12DATE_YYYY2327232724-AUG-09 05:12:22NONO0129 7.7519e-03 NONE
23LEAGUE_ID2327232724-AUG-09 05:12:22NONO07 1.4286e-01 NONE
35TEAM_ID2327232724-AUG-09 05:12:22NONO061 1.6393e-02 NONE
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics
Back to Index Columns
Back to Top

[-]
Index Columns Data Distribution for USER0.TEAMS_PK on USER0.TEAMS.
Col
Pos
Col
ID
Column NameLow Value*#High Value*# Num*
Buckets
Buckets*
Pop Vals
Histogram* Rows per
Bucket
Mutating
Number of
EndPoints
EndPoints1
History
Hidden
Column
Qualified
Col Name
Data
Default
12DATE_YYYY"1871""1999" 1 NONE NO 0: NO DATE_YYYY 0
23LEAGUE_ID"A""U" 1 NONE NO 0: NO LEAGUE_ID
35TEAM_ID"ALT""WOR" 1 NONE NO 0: NO TEAM_ID
(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics
(#) Low and High values may be hidden using "skip_histograms" tool configuration parameter
(1) CBO Stats Snapshots count, followed by list of Histogram EndPoints counts per Snapshot from newest to oldest
Back to Index Columns
Back to Top

Metadata (DBMS_METADATA)

Back to Top

Metadata for Table: USER0.MANAGERS

[-]

  CREATE TABLE "USER0"."MANAGERS"
   (	"RECNUM" NUMBER NOT NULL ENABLE,
	"LAHMANID" VARCHAR2(12) NOT NULL ENABLE,
	"DATE_YYYY" NUMBER,
	"SEASON_HALF_ID" VARCHAR2(1),
	"TEAM_ID" VARCHAR2(3),
	"LEAGUE_ID" VARCHAR2(2),
	"DIVISION_ID" VARCHAR2(1),
	"GAME_QTY" NUMBER,
	"WIN_QTY" NUMBER,
	"LOSS_QTY" NUMBER,
	"WIN_PCT" NUMBER(5,2),
	"PLACE_FINISHED_NR" NUMBER,
	"ORDER_MANAGED_ID" VARCHAR2(10),
	"PLAY_MANAGE_Y_N" VARCHAR2(1)
   ) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 1024000 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;

Back to Metadata
Back to Top

Metadata for Table: USER0.MASTER

[-]

  CREATE TABLE "USER0"."MASTER"
   (	"LAHMANID" VARCHAR2(12) NOT NULL ENABLE,
	"LAST_NAME_TX" VARCHAR2(25),
	"FIRST_NAME_TX" VARCHAR2(20),
	"BATS_RIGHT_LEFT_ID" VARCHAR2(1),
	"THROWS_RT_LEFT_ID" VARCHAR2(1),
	"BIRTH_DATE_MM" NUMBER,
	"BIRTH_DATE_DD" NUMBER,
	"BIRTH_DATE_YYYY" NUMBER,
	"DEBUT_SEASON_YYYY" NUMBER
   ) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 3686400 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;

Back to Metadata
Back to Top

Metadata for Table: USER0.TEAMS

[-]

  CREATE TABLE "USER0"."TEAMS"
   (	"RECNUM" NUMBER NOT NULL ENABLE,
	"DATE_YYYY" NUMBER DEFAULT 0 NOT NULL ENABLE,
	"LEAGUE_ID" VARCHAR2(2) NOT NULL ENABLE,
	"POSITION_PLAYED_ID" NUMBER,
	"TEAM_ID" VARCHAR2(3) NOT NULL ENABLE,
	"GAME_PLAYED_QTY" NUMBER,
	"WIN_QTY" NUMBER,
	"LOSS_QTY" NUMBER,
	"WIN_PCT" NUMBER(5,2),
	"GAMES_BEHIND_QTY" NUMBER,
	"RUN_QTY" NUMBER,
	"OPPONENT_RUN_QTY" NUMBER,
	"AT_BAT_QTY" NUMBER,
	"HIT_QTY" NUMBER,
	"DOUBLE_QTY" NUMBER,
	"TRIPLE_QTY" NUMBER,
	"HOME_RUN_QTY" NUMBER,
	"WALK_QTY" NUMBER,
	"STRIKE_OUT_QTY" NUMBER,
	"BATTING_AVG" NUMBER(4,3),
	"ON_BASE_PCT" NUMBER(4,3),
	"SLUGGING_PCT" NUMBER(4,3),
	"STOLEN_BASE_QTY" NUMBER,
	"CAUGHT_STEALING_QY" NUMBER,
	"EARNED_RUN_AVG" NUMBER(4,2),
	"COMPLETE_GAME_QTY" NUMBER,
	"SHUTOUT_QTY" NUMBER,
	"SAVE_QTY" NUMBER,
	"INNING_PITCHED_QTY" NUMBER(6,2),
	"HIT_BY_OPP_QTY" NUMBER,
	"HOME_RUN_BY_OPP_QY" NUMBER,
	"WALK_BY_OPP_QTY" NUMBER,
	"STRIKE_OUT_OPP_QTY" NUMBER,
	"PARK_BAT_FACTOR" NUMBER,
	"PARK_PITCH_FACTOR" NUMBER,
	"BALLPARK_NAME" VARCHAR2(200),
	"ATTENDENCE_QTY" NUMBER
   ) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 2048000 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;

Back to Metadata
Back to Top

Metadata for Index: USER0.TEAMS_PK

[-]

  CREATE UNIQUE INDEX "USER0"."TEAMS_PK" ON "USER0"."TEAMS" ("DATE_YYYY", "LEAGUE_ID", "TEAM_ID")
  PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;

Back to Metadata
Back to Top

215187.1 SQLT XPLAIN 11.2.9.8 secs:27.000 22-SEP-09 20:48:57