ORACLE SQL 10G Pour les Nuls & Zéro 
Commandes SQL pour DBA & Développeurs
Jeudi 15 mars 2012
4
15
/03
/Mars
/2012 22:01
Script Lister les paramètres cachés du INIT.ORA / SPFILE Oracle.
Comment modifier un paramètre caché dans Oracle ?.
Comment voir et lister les paramètres cachés Oracle ?.
Comment connaitre la valeur d'un paramètre caché ?.
Modifier un paramètre caché d'une instance Oracle.
SQL>
SQL> ALTER SYSTEM SET "_optimizer_squ_bottomup" = FALSE SCOPE=BOTH;
Système modifié.
SQL>
|
Voir les paramètres cachés de l'instance Oracle.
SQL> REPHEADER PAGE CENTER 'ORACLE HIDDEN PARAMETERS'
SQL> SET LINESIZE 80
SQL> SET PAGESIZE 900
SQL> COL NAME FORMAT A40
SQL> COL VALUE FORMAT A12
SQL> COL DEFAULT_VAL FORMAT A14
SQL> SELECT
PNAME_QKSCESYROW NAME,
PVALUE_QKSCESYROW VALUE,
DEFPVALUE_QKSCESYROW DEFAULT_VAL
FROM
X$QKSCESYS
ORDER BY
NAME;
|
Aperçu paramètres cachés Oracle.
ORACLE HIDDEN PARAMETERS
NAME VALUE DEFAULT_VAL
---------------------------------------- ------------ --------------
active_instance_count 1 1
_add_stale_mv_to_dependency_list true true
_always_anti_join choose choose
_always_semi_join choose choose
_always_star_transformation false false
bitmap_merge_area_size 1048576 1048576
_bloom_filter_enabled true true
_bt_mmv_query_rewrite_enabled true true
_b_tree_bitmap_plans true true
_complex_view_merging true true
_convert_set_to_join false false
_cost_equality_semi_join true true
cpu_count 2 2
_cpu_to_io 0 0
cursor_sharing exact exact
_db_file_optimizer_read_count 8 8
_default_non_equality_sel_check true true
_dimension_skip_null true true
_disable_datalayer_sampling false false
_disable_function_based_index false false
_distinct_view_unnesting false false
_dml_monitoring_enabled true true
_dm_max_shared_pool_pct 1 1
_eliminate_common_subexpr true true
_enable_dml_lock_escalation false false
_enable_type_dep_selectivity true true
_extended_pruning_enabled true true
_fast_full_scan_enabled true true
_fic_area_size 131072 131072
flashback_table_rpi non_fbt non_fbt
_force_datefold_trunc false false
_force_rewrite_enable false false
_force_temptables_for_gsets false false
_full_pwise_join_enabled true true
_gby_hash_aggregation_enabled true true
_generalized_pruning_enabled true true
_gs_anti_semi_join_allowed true true
hash_area_size 131072 131072
_hash_join_enabled true true
_hash_multiblock_io_count 0 0
_improved_outerjoin_card true true
_improved_row_length_enabled true true
_index_join_enabled true true
_left_nested_loops_random true true
_like_with_bind_as_equality false false
_local_communication_costing_enabled true true
_local_communication_ratio 50 50
_minimal_stats_aggregation true true
_mmv_query_rewrite_enabled true true
_nested_loop_fudge 100 100
_new_initial_join_orders true true
_new_sort_cost_estimate true true
_no_or_expansion false false
_oneside_colstat_for_equijoins true true
_optim_adjust_for_part_skews true true
_optim_enhance_nnull_detection true true
_optimizer_adjust_for_nulls true true
_optimizer_better_inlist_costing all all
_optimizer_block_size 8192 8192
_optimizer_cache_stats false false
_optimizer_cartesian_enabled true true
_optimizer_cbqt_factor 50 50
_optimizer_cbqt_no_size_restriction true true
_optimizer_complex_pred_selectivity true true
_optimizer_compute_index_stats true true
_optimizer_correct_sq_selectivity true true
_optimizer_cost_based_transformation linear linear
_optimizer_cost_filter_pred false false
_optimizer_cost_hjsmj_multimatch true true
_optimizer_cost_model choose choose
_optimizer_degree 0 0
_optimizer_dim_subq_join_sel true true
_optimizer_disable_strans_sanity_checks 0 0
optimizer_dynamic_sampling 2 2
_optimizer_enhanced_filter_push true true
_optimizer_extended_cursor_sharing udo udo
optimizer_features_enable 10.2.0.1 10.2.0.1
optimizer_features_hinted 0.0.0 0.0.0
_optimizer_filter_pred_pullup true true
_optimizer_ignore_hints false false
optimizer_index_caching 0 0
optimizer_index_cost_adj 100 100
_optimizer_join_elimination_enabled true true
_optimizer_join_order_control 3 3
_optimizer_join_sel_sanity_check true true
_optimizer_max_permutations 2000 2000
_optimizer_min_cache_blocks 10 10
_optimizer_mjc_enabled true true
optimizer_mode all_rows all_rows
_optimizer_mode_force true true
optimizer_mode_hinted false false
_optimizer_new_join_card_computation true true
_optimizer_order_by_elimination_enabled true true
_optimizer_or_expansion depth depth
_optimizer_outer_to_anti_enabled true true
_optimizer_percent_parallel 101 101
_optimizer_push_down_distinct 0 0
_optimizer_push_pred_cost_based true true
_optimizer_random_plan 0 0
_optimizer_rownum_pred_based_fkr true true
_optimizer_search_limit 5 5
optimizer_secure_view_merging true true
_optimizer_self_induced_cache_cost false false
_optimizer_skip_scan_enabled true true
_optimizer_sortmerge_join_enabled true true
_optimizer_squ_bottomup false true
_optimizer_starplan_enabled true true
_optimizer_star_tran_in_with_clause true true
_optimizer_system_stats_usage true true
_optimizer_transitivity_retain true true
_optimizer_undo_changes false false
_optimizer_undo_cost_change 10.2.0.1 10.2.0.1
_optim_new_default_join_sel true true
_optim_peek_user_binds true true
_ordered_nested_loop true true
_or_expand_nvl_predicate true true
_parallel_broadcast_enabled true true
parallel_ddl_forced_degree 0 0
parallel_ddl_forced_instances 0 0
parallel_ddl_mode enabled enabled
parallel_dml_forced_dop 0 0
parallel_dml_mode disabled disabled
parallel_execution_enabled false false
parallel_query_forced_dop 0 0
parallel_query_mode enabled enabled
parallel_threads_per_cpu 2 2
_partial_pwise_join_enabled true true
_partition_view_enabled true true
pga_aggregate_target 94208 KB 94208 KB
_pga_max_size 204800 KB 204800 KB
_predicate_elimination_enabled true true
_pred_move_around true true
_pre_rewrite_push_pred true true
_project_view_columns true true
_push_join_predicate true true
_push_join_union_view true true
_push_join_union_view2 true true
_px_broadcast_fudge_factor 100 100
_px_pwg_enabled true true
_query_cost_rewrite true true
_query_rewrite_drj true true
query_rewrite_enabled true true
_query_rewrite_expression true true
_query_rewrite_fpc true true
_query_rewrite_fudge 90 90
query_rewrite_integrity enforced enforced
_query_rewrite_jgmigrate true true
_query_rewrite_maxdisjunct 257 257
_query_rewrite_or_error false false
_query_rewrite_setopgrw_enable true true
_query_rewrite_vop_cleanup true true
_query_rewrite_1 true true
_query_rewrite_2 true true
_remove_aggr_subquery true true
_right_outer_hash_enable true true
_rowsrc_trace_level 0 0
_selfjoin_mv_duplicates true true
_simple_view_merging true true
skip_unusable_indexes true true
_slave_mapping_enabled true true
_smm_auto_cost_enabled true true
_smm_auto_max_io_size 248 KB 248 KB
_smm_auto_min_io_size 56 KB 56 KB
_smm_max_size 18841 KB 18841 KB
_smm_min_size 128 KB 128 KB
_smm_px_max_size 47104 KB 47104 KB
sort_area_retained_size 0 0
sort_area_size 65536 65536
_sort_elimination_cost_ratio 0 0
_sort_multiblock_read_count 2 2
_spr_push_pred_refspr true true
_sql_model_unfold_forloops run_time run_time
sqlstat_enabled false false
star_transformation_enabled false false
statistics_level typical typical
_subquery_pruning_enabled true true
_subquery_pruning_mv_enabled false false
_system_index_caching 0 0
_table_scan_cost_plus_one true true
_union_rewrite_for_gs yes_gset_mvs yes_gset_mvs
_unnest_subquery true true
_update_bji_ipdml_enabled 0 0
_use_column_stats_for_function true true
workarea_size_policy auto auto
184 ligne(s) sélectionnée(s).
SQL>
|
| Liens en rapport avec l'article et qui sont susceptibles de vous intéresser |
| • Liste des paramètres d'initialisation Oracle |
Rechercher
Recherche sur le site
ARTICLES ORACLE
- Structure base de données Oracle
- Audit et trace objets Oracle
- SQL CREATE DATABASE ORACLE
- Script Voir INIT HIDDEN PARAMETERS Oracle
- DBA Oracle
- Video Oracle Database Architecture Physique
- Oracle FLASHBACK Database Installation
- Supprimer base de données Oracle DROP DATABASE
- Optimiser le DB_FILE_MULTIBLOCK_READ_COUNT
- ALTER SYSTEM / SESSION SET RECYCLEBIN
Sujets Oracle
- INSTANCE - SGA (10)
- TABLESPACE (11)
- TABLES (13)
- INDEX ORACLE (3)
- SYNONYMS (4)
- REDOLOG / ARCHIVELOG (10)
- CONTROL FILES (5)
- SECURITE - USER (5)
- ROLES / PRIVILEGES (10)
- LISTENER - ORACLE NET (8)
- PROCESSUS (6)
- DATABASE / PARAMETER (13)
- Recovery Manager RMAN (3)
- IMPORT / EXPORT (9)
- SQLPLUS (5)
- SQL LOADER (4)
- SQL VIEW (2)
- FUNCTIONS - SQL (35)
- ORACLE PLSQL (15)
- JOINTURES SQL - SQL JOINS (9)
- OPÉRATEURS SQL (7)
- COURS ORACLE PDF (10)
- COMMANDES SQL DML-LMD (9)
- COMMANDES SQL DDL-LDD (12)
- TRANSACTION CONTROL LANGUAGES (1)
- ORACLE TRIGGERS (3)
- PLSQL Packages (7)
- COMMANDES UNIX / LINUX / OS (1)
- DBCA / OUI / INSTALL / PATCHSET (1)
- ORACLE SCRIPTS (2)
- ORACLE 10G / 11G RAC (2)
- OEM (1)
- OUTILS (5)
- SAUVEGARDES (1)
- DICTIONNAIRE DE DONNEES (1)
- TUNING (2)
- OS / TUNING (3)
- CERTIFICATIONS (2)
- ACTUALITES (4)
- DIVERS (1)