I have seen most of the people who are Developer/DBA, they carry out customization of environment where ever the work. As it would feel better in order to carry out the work. Similarly, while working with oracle performance tuning related activities, it would be initial step to check /find the environment settings of Oracle Optimizer, which provides the information related to the Optimizer parameters and their values which was maintained during the life cycle of oracle database. Earlier to current versions, I guess oracle master’s and guru’s might be having their customized scripts to find the details about the Oracle Optimizer parameter settings at initial level and I say it would be a tedious job.
Looking into it Oracle provided handful number of views which provides the information about the Oracle Optimizer settings. Let’s see how to fetch out them and from where.
Oracle provided the view called v$sys_optimizer_env. This provides all the information with respect to Optimizer related settings and their values. How easy it is to fetch out the details and we can concentrate on the issue very much detail in just seconds.
1: SQL> col value format a13
2: SQL> /
3: 4: ID NAME SQL_FEATURE ISD VALUE DEFAULT_VALUE
5: ---- ------------------------------------ -------------------- --- ------------- ----------------
6: 2 parallel_execution_enabled QKSFM_CBO YES true true
7: 9 optimizer_features_enable QKSFM_CBO YES 11.2.0.1 11.2.0.1 8: 11 cpu_count QKSFM_ALL YES 2 2 9: 12 active_instance_count QKSFM_ALL YES 1 1 10: 13 parallel_threads_per_cpu QKSFM_CBO YES 2 2 11: 14 hash_area_size QKSFM_ALL YES 131072 131072 12: 15 bitmap_merge_area_size QKSFM_ALL YES 1048576 1048576 13: 16 sort_area_size QKSFM_ALL YES 65536 65536 14: 17 sort_area_retained_size QKSFM_ALL YES 0 0 15: 24 pga_aggregate_target QKSFM_ALL YES 499712 KB 499712 KB 16: 35 parallel_query_mode QKSFM_ALL YES enabled enabled 17: 36 parallel_dml_mode QKSFM_ALL YES disabled disabled 18: 37 parallel_ddl_mode QKSFM_ALL YES enabled enabled 19: 38 optimizer_mode QKSFM_ALL YES all_rows all_rows 20: 48 cursor_sharing QKSFM_CBO YES exact exact21: 50 star_transformation_enabled QKSFM_STAR_TRANS YES false false
22: 66 optimizer_index_cost_adj QKSFM_CBO YES 100 100 23: 67 optimizer_index_caching QKSFM_CBO YES 0 024: 70 query_rewrite_enabled QKSFM_TRANSFORMATION YES true true
25: 71 query_rewrite_integrity QKSFM_TRANSFORMATION YES enforced enforced 26: 101 workarea_size_policy QKSFM_ALL YES auto auto 27: 105 optimizer_dynamic_sampling QKSFM_CBO YES 2 2 28: 112 statistics_level QKSFM_CBO YES typical typical29: 114 skip_unusable_indexes QKSFM_CBO YES true true
30: 167 optimizer_secure_view_merging QKSFM_ALL YES true true
31: 212 result_cache_mode QKSFM_ALL YES MANUAL MANUAL 32: 218 transaction_isolation_level QKSFM_ALL YES read_commited read_commited33: 228 optimizer_use_pending_statistics QKSFM_CBO YES false false
34: 238 optimizer_capture_sql_plan_baselines QKSFM_CBO YES false false
35: 239 optimizer_use_sql_plan_baselines QKSFM_CBO YES true true
36: 245 parallel_degree_policy QKSFM_PQ YES manual manual 37: 246 parallel_degree QKSFM_PQ YES 0 0 38: 247 parallel_min_time_threshold QKSFM_PQ YES 10 10 39: 256 parallel_query_default_dop QKSFM_PQ YES 0 0 40: 257 is_recur_flags QKSFM_ALL YES 0 041: 258 optimizer_use_invisible_indexes QKSFM_INDEX YES false false
42: 262 cell_offload_processing QKSFM_EXECUTION YES true true
43: 264 db_file_multiblock_read_count QKSFM_ALL YES 128 128 44: 267 cell_offload_compaction QKSFM_EXECUTION YES ADAPTIVE ADAPTIVE 45: 268 parallel_degree_limit QKSFM_PQ YES 65535 6553546: 269 parallel_force_local QKSFM_PQ YES false false
47: 270 parallel_max_degree QKSFM_PQ YES 4 4 48: 271 total_cpu_count QKSFM_ALL YES 2 2 49: 272 cell_offload_plan_display QKSFM_EXECUTION YES AUTO AUTO50: 283 dst_upgrade_insert_conv QKSFM_ALL YES true true
51: 289 parallel_autodop QKSFM_PQ YES 0 0 52: 290 parallel_ddldml QKSFM_PQ YES 0 0 53: 54: 47 rows selected.
Let’s just concentrate on column SQL_FEATURE. It provides the details about Oracle Architecture. We can get information about the parameter effect on which part of the feature used by Oracle Optimizer.
Example:-
parallel_execution_enabled – QKSFM_CBO
The above parameter is true or enabled, which shows significant effect Oracle Optimizer. Now, let’s see how such SQL_Feature are available and list of parameters which effects on certain feature.
v$sql_feature view provides the information regarding the features available in the current versions.
1: SQL> select * from v$sql_feature
2: 2 where description not like '%Fix%'
3: 3 order by 1;
4: 5: SQL_FEATURE DESCRIPTION PROPERTY6: ------------------------------ ---------------------------------------- ----------
7: QKSFM_ACCESS_ADVISOR sql access advisor 0
8: QKSFM_ACCESS_PATH Query access path 0
9: QKSFM_ALL A Universal Feature 010: QKSFM_ALL_ROWS All rows (optimizer mode) 0
11: QKSFM_AND_EQUAL Index and-equal access path 0
12: QKSFM_AQ Advanced Queuing 013: QKSFM_BITMAP_TREE Bitmap tree access path 0
14: QKSFM_CARDINALITY Cardinality computation 015: QKSFM_CBO SQL Cost Based Optimization 0
16: QKSFM_CBQT Cost Based Query Transformation 017: QKSFM_CDC change data capture 0
18: QKSFM_CHECK_ACL_REWRITE Check ACL Rewrite 0
19: QKSFM_CHOOSE Choose (optimizer mode) 020: QKSFM_CNT Count(col) to count(*) 0
21: QKSFM_COALESCE_SQ coalesce subqueries 0
22: QKSFM_COLUMN_STATS Basic column statistics 0
23: QKSFM_COMPILATION SQL COMPILATION 0
24: QKSFM_COST_XML_QUERY_REWRITE Cost Based XML Query Rewrite 0 25: QKSFM_CPU_COSTING CPU costing 0 26: QKSFM_CSE Common Sub-Expression Elimination 027: QKSFM_CURSOR_SHARING Cursor sharing 0
28: QKSFM_CVM Complex View Merging 2
29: QKSFM_DBMS_STATS Statistics gathered by DBMS_STATS 0
30: QKSFM_DIST_PLCMT Distinct Placement 2
31: QKSFM_DML DML 032: QKSFM_DYNAMIC_SAMPLING Dynamic sampling 0
33: QKSFM_DYNAMIC_SAMPLING_EST_CDN Estimate CDN using dynamic sampling 0
34: QKSFM_EXECUTION SQL EXECUTION 0
35: QKSFM_FBA Flashback Data Archive 0
36: QKSFM_FILTER_PUSH_PRED Push filter predicates 037: QKSFM_FIRST_ROWS First rows (optimizer mode) 0
38: QKSFM_FULL Full table scan 0
39: QKSFM_GATHER_PLAN_STATISTICS Gather plan statistics 0
40: QKSFM_HEURISTIC Heuristic Query Transformation 041: QKSFM_INDEX Index 0
42: QKSFM_INDEX_ASC Index (ascending) 0
43: QKSFM_INDEX_COMBINE Combine index for bitmap access 0
44: QKSFM_INDEX_DESC Use index (descending) 0
45: QKSFM_INDEX_FFS Index fast full scan 0
46: QKSFM_INDEX_JOIN Index join 0
47: QKSFM_INDEX_RS_ASC Index range scan 0
48: QKSFM_INDEX_RS_DESC Index range scan descending 0
49: QKSFM_INDEX_SS Index skip scan 0
50: QKSFM_INDEX_SS_ASC Index skip scan ascending 0
51: QKSFM_INDEX_SS_DESC Index skip scan descending 0
52: QKSFM_INDEX_STATS Basic index statistics 0
53: QKSFM_JOINFAC Join Factorization 2
54: 55: SQL_FEATURE DESCRIPTION PROPERTY56: ------------------------------ ---------------------------------------- ----------
57: QKSFM_JOIN_METHOD Join methods 0
58: QKSFM_JOIN_ORDER Join order 0
59: QKSFM_JPPD Join Predicate Push Down 0
60: QKSFM_MVIEWS materialized views 061: QKSFM_OBYE Order-by Elimination 0
62: QKSFM_OLD_PUSH_PRED Old push predicate algorithm (pre-10.1.0 0
63: .3) 64: 65: QKSFM_ONLINE_REDEF online redefinition with mviews 0
66: QKSFM_OPT_ESTIMATE Optimizer estimates 0 67: QKSFM_OPT_MODE Optimizer mode 068: QKSFM_OR_EXPAND OR expansion 0
69: QKSFM_OUTER_JOIN_TO_INNER Join Conversion 4
70: QKSFM_OUTLINE Outlines 071: QKSFM_PARALLEL Parallel table 0
72: QKSFM_PARTITION Partition 073: QKSFM_PLACE_GROUP_BY Group-By Placement 0
74: QKSFM_PQ Parallel Query 0 75: QKSFM_PQ_DISTRIBUTE PQ Distribution method 0 76: QKSFM_PQ_MAP PQ slave mapper 0 77: QKSFM_PRED_MOVE_AROUND Predicate move around 0 78: QKSFM_PULL_PRED pull predicates 079: QKSFM_PX_JOIN_FILTER Bloom filtering for joins 0
80: QKSFM_QUERY_REWRITE query rewrite with materialized views 0
81: QKSFM_RBO SQL Rule Based Optimization 0
82: QKSFM_SET_TO_JOIN Transform set operations to joins 0
83: QKSFM_SORT_ELIM Sort Elimination Via Index 0
84: QKSFM_SQL_CODE_GENERATOR SQL Code Generator 0
85: QKSFM_SQL_PLAN_MANAGEMENT SQL Plan Management 0
86: QKSFM_STAR_TRANS Star Transformation 287: QKSFM_STATS Optimizer statistics 0
88: QKSFM_SVM Simple View Merging 2
89: QKSFM_TABLE_ELIM Table Elimination 4
90: QKSFM_TABLE_EXPANSION Table Expansion 4
91: QKSFM_TABLE_STATS Basic table statistics 0
92: QKSFM_TRANSFORMATION Query Transformation 193: QKSFM_UNNEST unnest query block 0
94: QKSFM_USE_CONCAT Or-optimization 0
95: QKSFM_USE_HASH Hash join 0
96: QKSFM_USE_MERGE Sort-merge join 0
97: QKSFM_USE_MERGE_CARTESIAN Merge join cartesian 0
98: QKSFM_USE_NL Nested-loop join 0
99: QKSFM_USE_NL_WITH_INDEX Nested-loop index join 0
100: QKSFM_XMLINDEX_REWRITE XMLIndex Rewrite 0 101: QKSFM_XML_REWRITE XML Rewrite 0 102: 103: 90 rows selected.
Vowee, we have 90 such Optimizer features available right now oracle 11g. It’s pretty nice that we know the certain parameter effects certain feature of oracle Optimizer. Is it possible to get to know which hints available , which provides some influence during execution of queries etc.,
v$sql_hint view provides information with respect to hints.
1: SQL> select name,sql_feature,inverse
2: 2 from v$sql_hint
3: 3 where sql_feature='QKSFM_CBO'
4: 4 order by sql_feature;
5: 6: NAME SQL_FEATURE INVERSE7: ----------------------------- ------------------------------ ----------------------------
8: APPEND QKSFM_CBO NOAPPEND 9: NOAPPEND QKSFM_CBO APPEND 10: REF_CASCADE_CURSOR QKSFM_CBO NO_REF_CASCADE 11: NO_REF_CASCADE QKSFM_CBO REF_CASCADE_CURSOR 12: SYS_DL_CURSOR QKSFM_CBO 13: SQLLDR QKSFM_CBO 14: DML_UPDATE QKSFM_CBO 15: VECTOR_READ QKSFM_CBO 16: VECTOR_READ_TRACE QKSFM_CBO 17: EXPR_CORR_CHECK QKSFM_CBO 18: STREAMS QKSFM_CBO 19: MERGE_CONST_ON QKSFM_CBO 20: FBTSCAN QKSFM_CBO 21: ORDERED QKSFM_CBO 22: ORDERED_PREDICATES QKSFM_CBO 23: DOMAIN_INDEX_SORT QKSFM_CBO DOMAIN_INDEX_NO_SORT 24: DOMAIN_INDEX_NO_SORT QKSFM_CBO DOMAIN_INDEX_SORT 25: SKIP_EXT_OPTIMIZER QKSFM_CBO 26: SEMIJOIN_DRIVER QKSFM_CBO 27: MERGE_AJ QKSFM_CBO 28: HASH_AJ QKSFM_CBO 29: NL_AJ QKSFM_CBO 30: MERGE_SJ QKSFM_CBO 31: HASH_SJ QKSFM_CBO 32: NL_SJ QKSFM_CBO 33: BYPASS_UJVC QKSFM_CBO 34: LOCAL_INDEXES QKSFM_CBO 35: HWM_BROKERED QKSFM_CBO 36: OVERFLOW_NOMOVE QKSFM_CBO 37: SYS_PARALLEL_TXN QKSFM_CBO 38: CUBE_GB QKSFM_CBO 39: SAVE_AS_INTERVALS QKSFM_CBO 40: RESTORE_AS_INTERVALS QKSFM_CBO 41: BITMAP QKSFM_CBO 42: NO_QKN_BUFF QKSFM_CBO 43: BUFFER QKSFM_CBO NO_BUFFER 44: NO_BUFFER QKSFM_CBO BUFFER 45: CURSOR_SHARING_EXACT QKSFM_CBO 46: NO_PARALLEL QKSFM_CBO SHARED 47: CACHE_CB QKSFM_CBO NOCACHE 48: QUEUE_CURR QKSFM_CBO 49: QUEUE_ROWP QKSFM_CBO 50: USE_ANTI QKSFM_CBO 51: USE_SEMI QKSFM_CBO 52: SUBQUERY_PRUNING QKSFM_CBO NO_SUBQUERY_PRUNING 53: NO_SUBQUERY_PRUNING QKSFM_CBO SUBQUERY_PRUNING 54: INDEX_RRS QKSFM_CBO 55: 56: NAME SQL_FEATURE INVERSE57: ----------------------------- ------------------------------ ----------------------------
58: SWAP_JOIN_INPUTS QKSFM_CBO NO_SWAP_JOIN_INPUTS 59: NO_SWAP_JOIN_INPUTS QKSFM_CBO SWAP_JOIN_INPUTS 60: CLUSTER QKSFM_CBO 61: ROWID QKSFM_CBO 62: X_DYN_PRUNE QKSFM_CBO 63: SKIP_UNQ_UNUSABLE_IDX QKSFM_CBO 64: NO_PARTIAL_COMMIT QKSFM_CBO 65: DOMAIN_INDEX_FILTER QKSFM_CBO NO_DOMAIN_INDEX_FILTER 66: NO_DOMAIN_INDEX_FILTER QKSFM_CBO DOMAIN_INDEX_FILTER 67: NUM_INDEX_KEYS QKSFM_CBO 68: APPEND_VALUES QKSFM_CBO NOAPPEND 69: 70: 58 rows selected.
Finally, we can able to view the hints applicable for specific feature. Now, using the hints we can able to troubleshoot or tune specific features of query. All we need to do is apply to work and perform practicals then we can able to get to know the things and how they will work. Apart from the above views we have certain other views which provides additional information too. Please find the details below.
Query to fetch out the hints and details with respect to Optimizer Feature
select sf.sql_feature,sh.name,sf.description,sh.class,version,sh.version_outline
from v$sql_feature sf, v$sql_hint sh
where sf.sql_feature in
(select sql_feature from v$sql_feature_hierarchy where parent_id='QKSFM_CBO')
and sf.sql_feature = sh.sql_feature
order by sql_feature
Kernel level tables for the views
x$qksfm - v$sql_feature
x$qksht - v$sql_hint
x$qksfmprt - v$sql_feature_hierarchy
x$qksfmdep - v$sql_feature_dependency
x$qksbgses - gv$session_fix_control
x$qksbgsys - gv$system_fix_control
x$qksceses - gv$ses_optimizer_env
x$qkscesys - gv$sys_optimizer_env
Hope this finds helpful.. !!
1 comments: on "Oracle Optimizer Environment Details–Oracle 11g"
hello i just installed oracle 10g on my laptop with windows 7 can u tell me how to use the default tables of oracle 9i(employees,departments etc.). Any script and how to run it. It would be a big help. Mail me plz on rashi.sandhu@gmail.com. Thanks.
Post a Comment