Curso de Exadata em Phoenix Arizona com Hans Forbrich
outubro 10, 2014

Essa semana se resumiu em uma só palavra: Exadata!

Pude participar de um ótimo treinamento de Oracle Exadata Database Machine ministrado em Phoenix – Arizona pelo professor, Oracle ACE Director e grande DBA Hans Forbrich. Hans dispensa apresentações, muitos de vocês já o conhecem pelo seu blog.

O curso abordou 23 tópicos do Exadata em 5 dias, temas como Monitoramento, Performance, Arquitetura, SmartScan,  Patching e Updating foram alguns desses tópicos. Foi um curso muito puxado, devido a vasta quantidade de conteúdo abordado em tão poucos dias.

Mais mesmo assim, sobrou tempo para uma foto com o Hans :)

Hans Forbrich and Flavio Soares Exadata Training

Não poderia de deixar meu muito obrigado a Discover, por proporcionar essa oportunidade e principalmente por acreditar no meu trabalho.

Os novos parâmetros do Oracle Database 12c
setembro 11, 2014

A cada nova versão do Oracle Database, novas funcionalidades, novas features e novas correções de bug’s são adicionados ao banco de dados e com isso novos parâmetros para controlar tudo isso. E não é de hoje que esses novos parâmetros são adicionados a uma nova versão de Oracle.

Exatamente na versão 9i Release 9.2.0.8.0 existiam cerca de 871 parâmetros de configuração do Oracle entre é claro parâmetros documentados e não documentados, já na versão 10g no Release 10.2.0.4.0 esse número passou para incríveis 1495 parâmetros, exatos 624 parâmetros a mais do que na versão anterior. Já com a versão 11g Release 11.2.0.4.0 que contém exatamente 2914 parâmetros, cerca de 2043 parâmetros a mais do que na versão 9i.

Com o lançamento do Oracle Database 12c, podemos mais uma vez fazer esse comparativo, isso através da table Oracle memory x$ksppi, que fornece todos os parâmetros documentados, parâmetros ocultos documentos e parâmetros ocultos não documentados pelo Oracle Database. É por intermédio dessa x$ table que podemos observar as diferenças de parâmetros entre uma versão e outra.

Veja o caso na versão 11g Release 11.2.0.4.0, temos exatos 2914 parâmetros:

SQ> select count(*) from x$ksppi;

  COUNT(*)
----------
      2914

1 row selected.

SQL>

Já na versão 12c (12.1.0.2.0), esse número passou para 3978:

SQL> select count(*) from x$ksppi;

  COUNT(*)
----------
      3978

SQL> 

Quase mil parâmetros a mais do que na versão anterior. Outro fato interessante que na versão 12.1.0.1.0 para a versão 12.1.0.2.0, já temos uma grande diferenças de novos parâmetros:

Oracle 12c (12.1.0.1.0)

SQL> select count(*) from x$ksppi;

  COUNT(*)
----------
      3351

1 row selected.

SQL>

Oracle 12c (12.1.0.2.0)


SQL> select count(*) from x$ksppi;

  COUNT(*)
----------
      3978

1 row selected.

SQL>

Abaixo está a lista completa de todos os parâmetros que estão somente na versão Oracle 12c, ou seja, todos os parâmetros a seguir são os novos parâmetros do Oracle 12c. Para conseguir isso, estou utilizando uma tabela temporária que criei chamada ksppi, que nada mais é que uma CTAS (Create Table AS) da x$ table x$ksppi, isso foi feito para conseguir realizar a consulta nos dados da x$ utilizando dblink.

Lembrando também que essa é a lista de todos os parâmetros documentos, ocultos documentados e ocultos não documentados. Nem preciso dizer que não é para você mudar qualquer um dos parâmetros ocultos abaixo, principalmente no seu banco de produção, sem antes ter a permissão do suporte Oracle. Tenha muito cuidado com qualquer um desses parâmetro ocultos.

Aqui a lista, com a descrição de cada parâmetro:

SQL> COLUMN PARAMETER_ONLY_12C FORMAT a50
SQL> COLUMN description FORMAT a80

SQL> SELECT 
2	KSPPINM PARAMETER_ONLY_12C, KSPPDESC description 
3    FROM ksppi 
4    WHERE KSPPINM NOT IN (SELECT KSPPINM parameter_name_11g FROM ksppi@db11g);

PARAMETER_ONLY_12C					     DESCRIPTION
-----------------------------------------------------        --------------------------------------------------------
_appqos_po_multiplier					     Multiplier for PC performance objective value
_appqos_cdb_setting					     QoSM CDB Performance Class Setting
_latch_wait_list_pri_sleep_secs 			     Time to sleep on latch wait list until getting priority
__wait_test_param					     wait test parameter
_system_state_runtime_limit				     runtime limit for system state dump
_system_state_cputime_limit				     cputime limit for system state dump
_enqueue_sync_retry_attempts				     max number of times the bg process to retry synchronous enqueue open if it faile
							     d because master could not allocate memory

_enqueue_sync_sim_mem_error				     simulate master instance running out of memory when synchronously getting a remo
							     tely mastered enqueue

_number_group_memberships_per_cache_line		     maximum number of group memberships per cache line
_ksdxw_stack_readable					     dump readable stack when watchpoint is hit
_touch_sga_pages_during_allocation			     touch SGA pages during allocation
_realfree_heap_pagesize 				     hint for real-free page size in bytes
_realfree_pq_heap_pagesize				     hint for pq real-free page size in bytes
_kgh_restricted_trace					     trace level for heap dump-restricted mode
_kgh_restricted_subheaps				     number of subheaps in heap restricted mode
_defer_sga_enabled					     Enable deferred shared memory allocation for SGA
_defer_sga_min_total_defer_segs_sz			     Minimum total deferred segs size for defer sga allocation
_defer_sga_alloc_chunk_size				     Chunk size for defer sga allocation
_defer_sga_min_spsz_at_startup				     Minimum shared pool size at startup with deferred sga enabled
_defer_sga_test_alloc_intv				     SA** sleeps for N secs before allocating a deferred segment
_sga_alloc_slaves_term_timeout_secs			     Termination timeout in secs for SA** slaves
_ksmlsaf						     KSM log alloc and free
_pga_limit_target_perc					     default percent of pga_aggregate_target for pga_aggregate_limit
_pga_limit_watch_perc					     percentage of limit to have processes watch
_pga_limit_time_to_interrupt				     seconds to wait until direct interrupt
_pga_limit_interrupt_smaller				     whether to interrupt smaller eligible processes
_pga_limit_time_until_idle				     seconds to wait before treating process as idle
_pga_limit_time_until_killed				     seconds to wait before killing session over limit
_pga_limit_use_immediate_kill				     use immediate kill for sessions over limit
_pga_limit_dump_summary 				     dump PGA summary when signalling ORA-4036
_pga_limit_watch_size					     bytes of PGA usage at which process will begin watching limit
_pga_limit_min_req_size 				     bytes of PGA usage below which process will not get ORA-4036
_pga_limit_check_wait_time				     microseconds to wait for over limit confirmation
_pga_limit_simulated_physmem_size			     bytes of physical memory to determine pga_aggregate_limit with
_pga_limit_physmem_perc 				     default percent of physical memory for pga_aggregate_limit and SGA
_sga_data_protection_disabled				     disable SGA data protection
_sga_dp_precise_errors					     enable precise SGA data protection errors
_sga_heap_chunk_alignment				     force SGA heap chunk alignment
_sga_heap_chunk_alignment_disabled			     force SGA heap chunks not to be aligned
_sga_dp_bytes_per_alloc 				     bytes per allocation that have SGA data protection
_NUMA_bind_mode 					     Numa bind mode
pga_aggregate_limit					     limit of aggregate PGA memory consumed by the instance
_pga_limit_tracing					     trace pga_aggregate_limit activity
_test_param_8						     test parameter 8 - cdb tests
_highest_priority_processes				     Highest Priority Process Name Mask
_os_sched_highest_priority				     OS highest priority level
_bg_spawn_diag_opts					     background processes spawn diagnostic options
_mpmt_enabled_backgrounds				     mpmt enabled backgrounds
_background_process_opts				     Misc BG procs parameter
_kse_snap_ring_suppress 				     List of error numbers to suppress in the snap error history
_kse_snap_ring_disable					     set to TRUE or FALSE to disable or enable error logging
_enqueue_deadlock_detect_all_global_locks		     enable deadlock detection on all global enqueues
_enable_kernel_io_outliers				     enable kernel I/O outlier feature
_dnfs_rdma_max						     Maximum size of dNFS RDMA transfer
_dnfs_rdma_min						     Minimum size of dNFS RDMA transfer
_dnfs_rdma_enable					     Enable dNFS RDMA transfers
_simulate_logical_sectorsize				     Enables skgfr to report simulated logical disk sector size
_io_outlier_threshold					     Latency threshold for io_outlier table
_lgwr_io_outlier					     LGWR I/O outlier frequency
_asm_access						     ASM File access mechanism
_ksfd_fob_pct						     percentage of FOB state objects allocation
_use_dynamic_shares					     use dynamic shares
_dynamic_share_range_factor				     dynamic share range factor
_cpu_util_adj_force					     force cpu util adjustment
_cpu_util_adj_target					     cpu utilization adjustment target
_high_threshold_delta					     high threshold delta
db_performance_profile					     Database performance category
_rm_force_caging
_ksv_slave_exit_timeout 				     slave exit timeout
_twenty-ninth_spare_parameter				     twenty-ninth spare parameter - integer
_thirty-ninth_spare_parameter				     thirty-ninth spare parameter - integer
_forty-first_spare_parameter				     forty-first spare parameter - integer
_forty-second_spare_parameter				     forty-second spare parameter - integer
_forty-third_spare_parameter				     forty-third spare parameter - integer
_forty-fourth_spare_parameter				     forty-fourth spare parameter - integer
_forty-fifth_spare_parameter				     forty-fifth spare parameter - integer
_forty-sixth_spare_parameter				     forty-sixth spare parameter - integer
_forty-seventh_spare_parameter				     forty-seventh spare parameter - integer
_forty-eighth_spare_parameter				     forty-eighth spare parameter - integer
_forty-ninth_spare_parameter				     forty-ninth spare parameter - integer
_fiftieth_spare_parameter				     fiftieth spare parameter - integer
_fifty-first_spare_parameter				     fifty-first spare parameter - integer
_fifty-second_spare_parameter				     fifty-second spare parameter - integer
_fifty-third_spare_parameter				     fifty-third spare parameter - integer
_fifty-fourth_spare_parameter				     fifty-fourth spare parameter - integer
_fifty-fifth_spare_parameter				     fifty-fifth spare parameter - integer
_fifty-sixth_spare_parameter				     fifty-sixth spare parameter - integer
_fifty-seventh_spare_parameter				     fifty-seventh spare parameter - integer
_fifty-eighth_spare_parameter				     fifty-eighth spare parameter - integer
_fifty-ninth_spare_parameter				     fifty-ninth spare parameter - integer
_sixtieth_spare_parameter				     sixtieth spare parameter - integer
_sixty-first_spare_parameter				     sixty-first spare parameter - integer
_sixty-second_spare_parameter				     sixty-second spare parameter - integer
_sixty-third_spare_parameter				     sixty-third spare parameter - integer
_sixty-fourth_spare_parameter				     sixty-fourth spare parameter - integer
_sixty-fifth_spare_parameter				     sixty-fifth spare parameter - integer
_sixty-sixth_spare_parameter				     sixty-sixth spare parameter - integer
_sixty-seventh_spare_parameter				     sixty-seventh spare parameter - integer
_sixty-eighth_spare_parameter				     sixty-eighth spare parameter - integer
_sixty-ninth_spare_parameter				     sixty-ninth spare parameter - integer
_seventieth_spare_parameter				     seventieth spare parameter - integer
_seventy-first_spare_parameter				     seventy-first spare parameter - integer
_seventy-second_spare_parameter 			     seventy-second spare parameter - integer
_seventy-third_spare_parameter				     seventy-third spare parameter - integer
_seventy-fourth_spare_parameter 			     seventy-fourth spare parameter - integer
_seventy-fifth_spare_parameter				     seventy-fifth spare parameter - integer
_seventy-sixth_spare_parameter				     seventy-sixth spare parameter - integer
_seventy-seventh_spare_parameter			     seventy-seventh spare parameter - integer
_seventy-eighth_spare_parameter 			     seventy-eighth spare parameter - integer
_seventy-ninth_spare_parameter				     seventy-ninth spare parameter - integer
_eightieth_spare_parameter				     eightieth spare parameter - integer
_eighty-first_spare_parameter				     eighty-first spare parameter - string
_eighty-second_spare_parameter				     eighty-second spare parameter - string
_eighty-third_spare_parameter				     eighty-third spare parameter - string
_eighty-fourth_spare_parameter				     eighty-fourth spare parameter - string
_eighty-fifth_spare_parameter				     eighty-fifth spare parameter - string
_eighty-sixth_spare_parameter				     eighty-sixth spare parameter - string
_eighty-seventh_spare_parameter 			     eighty-seventh spare parameter - string
_eighty-eighth_spare_parameter				     eighty-eighth spare parameter - string
_eighty-ninth_spare_parameter				     eighty-ninth spare parameter - string
_ninetieth_spare_parameter				     ninetieth spare parameter - string
_ninety-first_spare_parameter				     ninety-first spare parameter - string
_ninety-second_spare_parameter				     ninety-second spare parameter - string
_ninety-third_spare_parameter				     ninety-third spare parameter - string
_ninety-fourth_spare_parameter				     ninety-fourth spare parameter - string
_ninety-fifth_spare_parameter				     ninety-fifth spare parameter - string
_ninety-sixth_spare_parameter				     ninety-sixth spare parameter - string
_ninety-seventh_spare_parameter 			     ninety-seventh spare parameter - string
_ninety-eighth_spare_parameter				     ninety-eighth spare parameter - string
_ninety-ninth_spare_parameter				     ninety-ninth spare parameter - string
_one-hundredth_spare_parameter				     one-hundredth spare parameter - string
_one-hundred-and-first_spare_parameter			     one-hundred-and-first spare parameter - string
_one-hundred-and-second_spare_parameter 		     one-hundred-and-second spare parameter - string
_one-hundred-and-third_spare_parameter			     one-hundred-and-third spare parameter - string
_one-hundred-and-fourth_spare_parameter 		     one-hundred-and-fourth spare parameter - string
_one-hundred-and-fifth_spare_parameter			     one-hundred-and-fifth spare parameter - string
_one-hundred-and-sixth_spare_parameter			     one-hundred-and-sixth spare parameter - string
_one-hundred-and-seventh_spare_parameter		     one-hundred-and-seventh spare parameter - string
_one-hundred-and-eighth_spare_parameter 		     one-hundred-and-eighth spare parameter - string
_one-hundred-and-ninth_spare_parameter			     one-hundred-and-ninth spare parameter - string
_one-hundred-and-tenth_spare_parameter			     one-hundred-and-tenth spare parameter - string
_one-hundred-and-eleventh_spare_parameter		     one-hundred-and-eleventh spare parameter - string
_one-hundred-and-twelfth_spare_parameter		     one-hundred-and-twelfth spare parameter - string
_one-hundred-and-thirteenth_spare_parameter		     one-hundred-and-thirteenth spare parameter - string
_one-hundred-and-fourteenth_spare_parameter		     one-hundred-and-fourteenth spare parameter - string
_one-hundred-and-fifteenth_spare_parameter		     one-hundred-and-fifteenth spare parameter - string
_one-hundred-and-sixteenth_spare_parameter		     one-hundred-and-sixteenth spare parameter - string
_one-hundred-and-seventeenth_spare_parameter		     one-hundred-and-seventeenth spare parameter - string
_one-hundred-and-eighteenth_spare_parameter		     one-hundred-and-eighteenth spare parameter - string
_one-hundred-and-nineteenth_spare_parameter		     one-hundred-and-nineteenth spare parameter - string
_one-hundred-and-twentieth_spare_parameter		     one-hundred-and-twentieth spare parameter - string
_one-hundred-and-twenty-first_spare_parameter		     one-hundred-and-twenty-first spare parameter - string
_one-hundred-and-twenty-second_spare_parameter		     one-hundred-and-twenty-second spare parameter - string
_one-hundred-and-twenty-third_spare_parameter		     one-hundred-and-twenty-third spare parameter - string
_one-hundred-and-twenty-fourth_spare_parameter		     one-hundred-and-twenty-fourth spare parameter - string
_one-hundred-and-twenty-fifth_spare_parameter		     one-hundred-and-twenty-fifth spare parameter - string
_one-hundred-and-twenty-sixth_spare_parameter		     one-hundred-and-twenty-sixth spare parameter - string
_one-hundred-and-twenty-seventh_spare_parameter 	     one-hundred-and-twenty-seventh spare parameter - string
_one-hundred-and-twenty-eighth_spare_parameter		     one-hundred-and-twenty-eighth spare parameter - string
_one-hundred-and-twenty-ninth_spare_parameter		     one-hundred-and-twenty-ninth spare parameter - string
_one-hundred-and-thirtieth_spare_parameter		     one-hundred-and-thirtieth spare parameter - string
_one-hundred-and-thirty-first_spare_parameter		     one-hundred-and-thirty-first spare parameter - string list
_one-hundred-and-thirty-second_spare_parameter		     one-hundred-and-thirty-second spare parameter - string list
_one-hundred-and-thirty-third_spare_parameter		     one-hundred-and-thirty-third spare parameter - string list
_one-hundred-and-thirty-fourth_spare_parameter		     one-hundred-and-thirty-fourth spare parameter - string list
_one-hundred-and-thirty-fifth_spare_parameter		     one-hundred-and-thirty-fifth spare parameter - string list
_one-hundred-and-thirty-sixth_spare_parameter		     one-hundred-and-thirty-sixth spare parameter - string list
_one-hundred-and-thirty-seventh_spare_parameter 	     one-hundred-and-thirty-seventh spare parameter - string list
_one-hundred-and-thirty-eighth_spare_parameter		     one-hundred-and-thirty-eighth spare parameter - string list
_one-hundred-and-thirty-ninth_spare_parameter		     one-hundred-and-thirty-ninth spare parameter - string list
_one-hundred-and-fortieth_spare_parameter		     one-hundred-and-fortieth spare parameter - string list
_one-hundred-and-forty-first_spare_parameter		     one-hundred-and-forty-first spare parameter - boolean
_one-hundred-and-forty-second_spare_parameter		     one-hundred-and-forty-second spare parameter - boolean
_one-hundred-and-forty-third_spare_parameter		     one-hundred-and-forty-third spare parameter - boolean
_one-hundred-and-forty-fourth_spare_parameter		     one-hundred-and-forty-fourth spare parameter - boolean
_one-hundred-and-forty-fifth_spare_parameter		     one-hundred-and-forty-fifth spare parameter - boolean
_one-hundred-and-forty-sixth_spare_parameter		     one-hundred-and-forty-sixth spare parameter - boolean
_one-hundred-and-forty-seventh_spare_parameter		     one-hundred-and-forty-seventh spare parameter - boolean
_one-hundred-and-forty-eighth_spare_parameter		     one-hundred-and-forty-eighth spare parameter - boolean
_one-hundred-and-forty-ninth_spare_parameter		     one-hundred-and-forty-ninth spare parameter - boolean
_one-hundred-and-fiftieth_spare_parameter		     one-hundred-and-fiftieth spare parameter - boolean
_one-hundred-and-fifty-first_spare_parameter		     one-hundred-and-fifty-first spare parameter - boolean
_one-hundred-and-fifty-second_spare_parameter		     one-hundred-and-fifty-second spare parameter - boolean
_one-hundred-and-fifty-third_spare_parameter		     one-hundred-and-fifty-third spare parameter - boolean
_one-hundred-and-fifty-fourth_spare_parameter		     one-hundred-and-fifty-fourth spare parameter - boolean
_one-hundred-and-fifty-fifth_spare_parameter		     one-hundred-and-fifty-fifth spare parameter - boolean
_one-hundred-and-fifty-sixth_spare_parameter		     one-hundred-and-fifty-sixth spare parameter - boolean
_one-hundred-and-fifty-seventh_spare_parameter		     one-hundred-and-fifty-seventh spare parameter - boolean
_one-hundred-and-fifty-eighth_spare_parameter		     one-hundred-and-fifty-eighth spare parameter - boolean
_one-hundred-and-fifty-ninth_spare_parameter		     one-hundred-and-fifty-ninth spare parameter - boolean
_one-hundred-and-sixtieth_spare_parameter		     one-hundred-and-sixtieth spare parameter - boolean
_one-hundred-and-sixty-first_spare_parameter		     one-hundred-and-sixty-first spare parameter - boolean
_one-hundred-and-sixty-second_spare_parameter		     one-hundred-and-sixty-second spare parameter - boolean
_one-hundred-and-sixty-third_spare_parameter		     one-hundred-and-sixty-third spare parameter - boolean
_one-hundred-and-sixty-fourth_spare_parameter		     one-hundred-and-sixty-fourth spare parameter - boolean
_one-hundred-and-sixty-fifth_spare_parameter		     one-hundred-and-sixty-fifth spare parameter - boolean
_one-hundred-and-sixty-sixth_spare_parameter		     one-hundred-and-sixty-sixth spare parameter - boolean
_one-hundred-and-sixty-seventh_spare_parameter		     one-hundred-and-sixty-seventh spare parameter - boolean
_one-hundred-and-sixty-eighth_spare_parameter		     one-hundred-and-sixty-eighth spare parameter - boolean
_one-hundred-and-sixty-ninth_spare_parameter		     one-hundred-and-sixty-ninth spare parameter - boolean
_one-hundred-and-seventieth_spare_parameter		     one-hundred-and-seventieth spare parameter - boolean
_one-hundred-and-seventy-first_spare_parameter		     one-hundred-and-seventy-first spare parameter - boolean
_one-hundred-and-seventy-second_spare_parameter 	     one-hundred-and-seventy-second spare parameter - boolean
_one-hundred-and-seventy-third_spare_parameter		     one-hundred-and-seventy-third spare parameter - boolean
_one-hundred-and-seventy-fourth_spare_parameter 	     one-hundred-and-seventy-fourth spare parameter - boolean
_one-hundred-and-seventy-fifth_spare_parameter		     one-hundred-and-seventy-fifth spare parameter - boolean
_one-hundred-and-seventy-sixth_spare_parameter		     one-hundred-and-seventy-sixth spare parameter - boolean
_one-hundred-and-seventy-seventh_spare_parameter	     one-hundred-and-seventy-seventh spare parameter - boolean
_one-hundred-and-seventy-eighth_spare_parameter 	     one-hundred-and-seventy-eighth spare parameter - boolean
_one-hundred-and-seventy-ninth_spare_parameter		     one-hundred-and-seventy-ninth spare parameter - boolean
_one-hundred-and-eightieth_spare_parameter		     one-hundred-and-eightieth spare parameter - boolean
_one-hundred-and-eighty-first_spare_parameter		     one-hundred-and-eighty-first spare parameter - boolean
_one-hundred-and-eighty-second_spare_parameter		     one-hundred-and-eighty-second spare parameter - boolean
_one-hundred-and-eighty-third_spare_parameter		     one-hundred-and-eighty-third spare parameter - boolean
_one-hundred-and-eighty-fourth_spare_parameter		     one-hundred-and-eighty-fourth spare parameter - boolean
_one-hundred-and-eighty-fifth_spare_parameter		     one-hundred-and-eighty-fifth spare parameter - boolean
_one-hundred-and-eighty-sixth_spare_parameter		     one-hundred-and-eighty-sixth spare parameter - boolean
_one-hundred-and-eighty-seventh_spare_parameter 	     one-hundred-and-eighty-seventh spare parameter - boolean
_one-hundred-and-eighty-eighth_spare_parameter		     one-hundred-and-eighty-eighth spare parameter - boolean
_one-hundred-and-eighty-ninth_spare_parameter		     one-hundred-and-eighty-ninth spare parameter - boolean
_one-hundred-and-ninetieth_spare_parameter		     one-hundred-and-ninetieth spare parameter - boolean
_one-hundred-and-ninety-first_spare_parameter		     one-hundred-and-ninety-first spare parameter - boolean
_one-hundred-and-ninety-second_spare_parameter		     one-hundred-and-ninety-second spare parameter - boolean
_one-hundred-and-ninety-third_spare_parameter		     one-hundred-and-ninety-third spare parameter - boolean
_one-hundred-and-ninety-fourth_spare_parameter		     one-hundred-and-ninety-fourth spare parameter - boolean
_one-hundred-and-ninety-fifth_spare_parameter		     one-hundred-and-ninety-fifth spare parameter - boolean
_one-hundred-and-ninety-sixth_spare_parameter		     one-hundred-and-ninety-sixth spare parameter - boolean
_one-hundred-and-ninety-seventh_spare_parameter 	     one-hundred-and-ninety-seventh spare parameter - boolean
_one-hundred-and-ninety-eighth_spare_parameter		     one-hundred-and-ninety-eighth spare parameter - boolean
_one-hundred-and-ninety-ninth_spare_parameter		     one-hundred-and-ninety-ninth spare parameter - boolean
_two-hundredth_spare_parameter				     two-hundredth spare parameter - boolean
_two-hundred-and-first_spare_parameter			     two-hundred-and-first spare parameter - boolean
_two-hundred-and-second_spare_parameter 		     two-hundred-and-second spare parameter - boolean
_two-hundred-and-third_spare_parameter			     two-hundred-and-third spare parameter - boolean
_two-hundred-and-fourth_spare_parameter 		     two-hundred-and-fourth spare parameter - boolean
_two-hundred-and-fifth_spare_parameter			     two-hundred-and-fifth spare parameter - boolean
_two-hundred-and-sixth_spare_parameter			     two-hundred-and-sixth spare parameter - boolean
_two-hundred-and-seventh_spare_parameter		     two-hundred-and-seventh spare parameter - boolean
_two-hundred-and-eighth_spare_parameter 		     two-hundred-and-eighth spare parameter - boolean
_two-hundred-and-ninth_spare_parameter			     two-hundred-and-ninth spare parameter - boolean
_two-hundred-and-tenth_spare_parameter			     two-hundred-and-tenth spare parameter - boolean
_two-hundred-and-eleventh_spare_parameter		     two-hundred-and-eleventh spare parameter - boolean
_two-hundred-and-twelfth_spare_parameter		     two-hundred-and-twelfth spare parameter - boolean
_two-hundred-and-thirteenth_spare_parameter		     two-hundred-and-thirteenth spare parameter - boolean
_two-hundred-and-fourteenth_spare_parameter		     two-hundred-and-fourteenth spare parameter - boolean
_two-hundred-and-fifteenth_spare_parameter		     two-hundred-and-fifteenth spare parameter - boolean
_two-hundred-and-sixteenth_spare_parameter		     two-hundred-and-sixteenth spare parameter - boolean
_two-hundred-and-seventeenth_spare_parameter		     two-hundred-and-seventeenth spare parameter - boolean
_two-hundred-and-eighteenth_spare_parameter		     two-hundred-and-eighteenth spare parameter - boolean
_two-hundred-and-nineteenth_spare_parameter		     two-hundred-and-nineteenth spare parameter - boolean
_two-hundred-and-twentieth_spare_parameter		     two-hundred-and-twentieth spare parameter - boolean
_ksipc_mode						     ksipc mode
_ksipc_libipc_path					     over-ride default location of libipc
_ksipc_wait_flags					     tune ksipcwait
_ksipc_spare_param1					     ksipc spare param 1
_ksipc_spare_param2					     ksipc spare param 2
_ksxp_ksmsq_ip_config					     ksxp ksmsq ip config
_ksxp_ipclw_enabled					     enable ipclw for KSXP
_ipc_config_opts_stat					     static config for ipclw startup
_ipc_config_opts_dyn					     Dyn config for ipclw startup
_ksipc_efchecks 					     Check HCA/OS version for Exafusion
_datapump_metadata_buffer_size				     specifies buffer size for metadata file I/O
_datapump_tabledata_buffer_size 			     specifies buffer size for table data file I/O
_datapump_compressbas_buffer_size			     specifies buffer size for BASIC compression algorithm
_midtier_affinity_cluswait_prc_threshold		     cluster wait precentage threshold to enter affinity
_disable_modsvc_refresh 				     disable modify service cache refresh
threaded_execution					     Threaded Execution Mode
_mpmt_single_process_instance				     MPMT - single process instance mode enabled
_mpmt_procs_per_osp					     max procs per osp
_prespawn_batch_count					     prespawn batch count
_dataguard_prespawn_count				     prespawn foreground init count
_proc_grp_enabled					     proc-group enabled
_min_time_between_psp0_diag_secs			     minimum time between PSP0 diagnostic used for flow control
_disable_rolling_patch					     Disable Rolling Patch Feature
_deadlock_record_to_alert_log				     record resolved deadlocks to the alert log
_process_heartbeat_range				     Process heartbeat range
_lthread_cleanup_intv_secs				     interval for cleaning lightweight threads in secs
_lthread_enabled					     Enable lightweight threads
_lthread_debug						     Enable Debugging mode for lightweight threads
_lthread_max_spawn_time_csecs				     maximum time interval a spawner will wait for a lthread to get ready
_lthread_spawn_check_intv_ms				     time interval for a spawner to check for spawnee to get ready
_lthread_clnup_pmon_softkill_wait_secs			     wait timeout for PMON between soft kill and hard kill of lthreads
_lthread_clnup_spawner_sk_wait_secs			     timeout for spawner between soft kill and hard kill of lthreads
_lthread_clnup_hk_wait_secs				     timeout after hard killing operation for lthread to exit
_lthread_step_debugging 				     Enable Step wise Debugging mode for lightweight threads
_full_diag_on_rim					     rim nodes have full DIA* function
_hang_cross_boundary_hang_detection_enabled		     Hang Management Cross Boundary detection
_hang_resolution_percent_hung_sessions_threshold	     Hang Manager hang resolution percent hung sessions threshold
_hang_terminate_session_replay_enabled			     Hang Management terminates sessions allowing replay
_diag_xm_enabled					     If TRUE, DIAG allows message exchanges across DB/ASM boundary
_hm_xm_enabled						     If TRUE, DIA0 allows message exchanges across DB/ASM boundary
_lm_hash_control					     bit field controlling the hashing behavior of the lock manager
_ges_server_processes					     number of background global enqueue server processes
_lm_lms_opt_priority					     enable freeslot lms priority optimization
_lm_lms_priority_check_frequency			     frequency of LMS priority decisions in milliseconds
_lm_db_rank						     rank of this DB for process priority purposes
_lm_db_ranks						     ranks of DBs on this node
_lm_cache_enqueue					     string of enqueues to cache at the client: separate by |, use DECIMAL identifier
							     s or '*' as wildcard, eg. AK-12-*

_lm_reloc_use_mhint					     if TRUE, AR-/AH-enqueues use mastering hints
_lm_rcfg_kjcdump_time					     dlm reconfiguration communication dump interval
_lm_enqueue_blocker_dump_timeout_cnt			     enqueue blocker dump timeout count
_lm_num_bnft_stats_buckets				     number of buckets in the benefit stats hash table
_lm_exchange_opt					     exchange step optimization in reconfig
_lm_drm_we_size 					     size of drm wait events statistics
_lm_drm_we_interval					     drm wait events collection interval in seconds
_read_mostly_instance					     indicates this is a read_mostly instance
_read_mostly_instance_qa_control			     internal parameter to control read mostly instance QA
_read_mostly_slave_timeout				     Time to wait on read mostly node when hub not available
_read_mostly_enable_logon				     Read mostly instances enable non-privileged logons
_lm_big_cluster_optimizations				     enable certain big cluster optimizations in drm
_ges_lmd_mapping					     enqueue to lmd mapping
_ges_hash_groups					     enqueue hash table groups
_ges_default_lmds					     default lmds for enqueue hashing
_ges_nres_divide					     how to divide number of enqueue resources among hash tables
_lm_drmopt12						     enable drm scan optimizations in 12
_lm_adrm_options					     active drm options
_lm_adrm_scan_timeout					     active drm f2a/flush scan timeout in centisecs
_lm_adrm_interval					     active drm interval in centiseconds
_lm_adrm_time_out					     active drm timeout in minutes
_lm_resend_open_convert_timeout 			     timeout in secs before resubmitting the open-convert
_lm_process_lock_q_scan_limit				     limit on scanning process lock queue instead of resource convert lock queue
_ges_gather_res_reuse_stats				     if TRUE, gather resource reuse statistics
_ges_freeable_res_chunk_free				     if TRUE, free dynamic resource chunks which are freeable
_lm_share_lock_restype					     string of resource types using S-lock optimisation
_ges_freeable_res_chunk_free_interval			     time interval for freeing freeable dynamic resource chunks
_ges_dump_open_locks					     if TRUE, dump open locks for the LCK process during shutdown
_lm_exadata_fence_type					     if FALSE disable Exadata fence type
_rond_test_mode 					     rac one node test mode
_adg_distributed_lockmaster				     standby runs under ADG distributed lockmaster mode
_kjdd_call_stack_dump_enabled				     Enables printing of short call stack with the WFG
_kjdd_wfg_dump_cntrl					     To control the way Wait-For_Graph is dumped
_omni_enqueue_enable					     Enable Omni Enqueue feature (0 = disable, 1 = enable on ASM (default), 2 = enabl
							     e)

_lm_idle_connection_kill_max_skips			     GES idle connection max skip kill request
_lm_idle_connection_quorum_threshold			     GES idle connection health quorum threshold
_lm_msg_pool_dump_threshold				     GES message pool dump threshold in terms of buffer count
_lm_msg_pool_user_callstack_dump			     GES message pool call stack dump upon exceeding of threshold
_lm_idle_connection_max_ignore_kill_count		     GES maximum idle connection kill request ignore count
_lm_mp_avail_queue_threshold				     GES MP avail queue threshold
_lm_mp_bulk_mbuf_free					     GES MP bulk free message buffer queues
_lm_wait_pending_send_queue				     GES wait on pending send queue
_lm_hashtable_bkt_low					     Low element threshold in hash table bucket
_lm_hashtable_bkt_high					     High element threshold in hash table bucket
_lm_hashtable_bkt_thr					     Threshold for hash table resizing
_lm_comm_tkts_adaptive					     Adpative ticketing enabled
_lm_comm_tkts_calc_period_length			     Weighted average calculation interval length (second)
_lm_comm_tkts_max_periods				     Max number of periods used in weighted avearage calculation
_lm_comm_tkts_min_increase_wait 			     Seconds to wait before allowing an allocation increase
_lm_comm_tkts_min_decrease_wait 			     Seconds to wait before allowing an allocation decrease
_lm_comm_tkts_nullreq_threshold 			     Null request threshold
_lm_comm_tkts_mult_factor				     Ticket allocation multiplication factor
_lm_comm_tkts_sub_factor				     Ticket allocation subtraction factor
_lm_comm_tkts_max_add					     Ticket allocation maximum allotments
_exafusion_enabled					     enable exafusion
_lm_comm_channel					     GES communication channel type
_lm_comm_msgq_busywait					     busy wait time in microsecond for msgq
_lm_comm_reap_count					     message reap count for receive
_lm_gl_hash_scheme					     group lock table hashing scheme (0|1|2)
_cluster_flash_cache_slave_file 			     cluster flash cache slave file for default block size
_gcs_res_hash_buckets					     number of gcs resource hash buckets to be allocated
_gcs_reserved_resources 				     allocate the number of reserved resources in reconfiguration
_gcs_min_slaves 					     if non zero, it enables the minimum number of gcs slaves
_gcs_dynamic_slaves					     if TRUE, it enables dynamic adjustment of the number of gcs slaves
_gcs_reserved_shadows					     allocate the number of reserved shadows in reconfiguration
_broadcast_scn_mode					     broadcast-on-commit scn mode
_nameservice_consistency_check				     NameService Consistency check switch
_nameservice_request_batching				     NameService request batching switch
_cgs_ticket_sendback					     CGS ticket active sendback percentage threshold
_cgs_msg_batch_size					     CGS message batch size in bytes
_cgs_msg_batching					     CGS message batching
_cgs_comm_readiness_check				     CGS communication readiness check
_imr_rr_holder_kill_time				     IMR max time instance is allowed to hold RR lock in seconds
_imr_check_css_incarnation_number			     IMR verify the global consistency of CSS incarnation number
_cgs_memberkill_from_rim_instance			     allow a RIM instance to issue a CSS member kill
_cgs_os_level_connection_check				     allow OS level connection and interface check
_cgs_os_level_connection_reqno				     number of ping rqst to process at once, threads created
_cgs_big_group_enabled					     big group membership support
_cgs_support_rim_disc					     rim instance disconnect and reconnect event support
_lm_rm_slaves						     if non zero, it enables remastering slaves
_lm_timed_statistics_level				     if non zero, it enables timed statistics on lmd and lms
_shutdown_sync_enable					     if TRUE, shutdown sync is issued before shutdown normal
_crs_2phase						     crs 2phase
_ges_vbfreelists					     number of valueblock freelists (will be adjusted to power of 2)
_lm_hb_enable_acl_check 				     to enable the wait analysis with acceptable condition lists
_lm_hb_acceptable_hang_condition			     list of acceptable hang conditions in heartbeat check
_lm_hb_maximum_hang_report_count			     maximum heartbeat hang report count
_lm_hb_exponential_hang_time_factor			     heartbeat exponential hang time multiplier
_gcr_enable_high_memory_kill				     if TRUE, GCR may kill foregrounds under high memory load
_gcr_high_memory_threshold				     minimum amount of Memory process must consume to be kill target
_gcr_cpu_min_free					     minimum amount of free CPU to flag an anomaly
_gcr_mem_min_free					     minimum amount of free memory to flag an anomaly
_gcr_css_use_2group_lock				     if FALSE, GCR will not try to lock 2 CSS groups at the same time
_gcr_css_group_try_lock_delay				     minimum delay between group locking attempts, secs
_gcr_css_group_query_boost				     allowed number of serial multiple queries
_gcr_css_group_update_interval				     interval between CSS group updates, secs
_gcr_css_group_update2_interval 			     interval between CSS group updates when busy, secs
_gcr_css_group_large					     size of large CSS group above which query/update disabled
_gcr_tick						     duration of time tick used by state machine, centisecs
_gcr_enable_statistical_cpu_check			     if FALSE, revert to old cpu load metric
_gcr_enable_new_drm_check				     if FALSE, revert to old drm load metric
_gcr_max_rt_procs					     maximum number of RT DLM processes allowed by GCR
_kjac_force_outcome_current_session			     if TRUE, enable to run force outcome on the current session
_ka_enabled						     Enable/disable kernel accelerator
_ka_mode						     kernel accelerator mode
_ka_locks_per_sector					     locks per sector in kernel accelerator
_ka_msg_reap_count					     maximum number of KA messages to receive and process per wait
_ka_compatibility_requirement				     kernel accelerator compatibility operation requirement
_ka_allow_reenable					     reenability of kernel accelerator service after disable
_ka_pbatch_messages					     kernel accelerator perform pbatch messages
_ka_doorbell						     kernel accelerator doorbell mode
_ka_scn_enabled 					     KA SCN processing enabled
_ka_scn_accel_shrmem					     KA SCN accelerate shr memory
_ka_scn_use_ka_msgs					     KA SCN use KA type messages
_ka_grant_policy					     KA grant policy to favor fusion grant, make it automatic, or favor KA
_ka_msg_wait_count					     KA maximum number of requests to retrieve per OSD wait
_ka_max_wait_delay					     KA maximum amount of time before forcing OSD wait call
_gcs_cluster_flash_cache_mode				     cluster flash cache mode
_gcs_flash_cache_mode					     flash cache mode
_max_defer_gran_xfer_atonce				     Maximum deferred granules transferred by MMAN atonce
_asm_allow_small_memory_target				     Allow a small memory_target for ASM instances
_controlfile_split_brain_check				     Check for a split-brain in distributed lock manager domain
db_big_table_cache_percent_target			     Big table cache target size in percentage
_db_full_db_cache_diff_pct				     Cache at least this % larger than DB size for full db caching
_db_writer_coalesce_encrypted_buffers			     Coalecsing for encrypted buffers
_dbwr_stall_write_detection_interval			     dbwriter stall write detection interval
_pin_time_statistics					     if TRUE collect statistics for how long a current pin is held
_adg_buffer_wait_timeout				     Active Dataguard buffer wait time in cs
_db_full_caching					     enable full db implicit caching
_db_flash_cache_disable_write_batchsize 		     Flash cache disable writes processing batchsize
_switch_current_scan_scn				     switch current uses scan scn
_cleanout_shrcur_buffers				     if TRUE, cleanout shrcur buffers
_child_read_ahead_dba_check				     if TRUE, assert child read ahead dba to be continuous of parent
_data_warehousing_scan_buffers				     if TRUE, enable data warehousing scan buffers
_data_warehousing_scan_flash_buffers			     if TRUE, enable data warehousing scan flash buffers
_data_warehousing_serial_scan				     if TRUE, enable data warehousing serial scans
_db_dw_scan_obj_cooling_policy				     DW scan objtect cooling policy
_db_dw_scan_obj_cooling_interval			     DW Scan object cooling interval in number of scans, seconds, or pct of cache siz
							     e

_db_dw_scan_obj_cooling_factor				     DW Scan object cooling factor to cool all temperatures
_db_dw_scan_obj_warming_increment			     DW Scan object warming increment when an object is scanned
_db_dw_scan_adaptive_cooling				     if TRUE, enable adaptive DW scan cooling
_db_dw_scan_max_shadow_count				     DW Scan adaptive cooling max shadow count
_pq_numa_working_set_affinity				     if TRUE, enable pq slave NUMA affinity
_db_cache_advice_hash_latch_multiple			     cache advisory hash latch multiple
_redo_log_record_life					     Life time in hours for redo log table records
_redo_log_debug_config					     Various configuration flags for debugging redo logs
_redo_log_check_backup					     time interval in minutes between wakeups to check backup of redo logs
_cache_orl_during_open					     cache online logs
_target_log_write_size					     Do log write if this many redo blocks in buffer (auto=0)
_target_log_write_size_timeout				     How long LGWR will wait for redo to accumulate (csecs)
_use_single_log_writer					     Use a single process for redo log writing
_max_outstanding_log_writes				     Maximum number of outstanding redo log writes
_adaptive_scalable_log_writer_enable_worker_threshold	     Increase in redo generation rate as a percentage
_adaptive_scalable_log_writer_disable_worker_threshold	     Percentage of overlap across multiple outstanding writes
_adaptive_scalable_log_writer_sampling_count		     Evaluate single versus scalable LGWR every N writes
_adaptive_scalable_log_writer_sampling_time		     Evaluate single versus scalable LGWR every N seconds
_adaptive_scalable_log_writer_enable_worker_aging	     Per million of redo gen rate when LGWR workers were last used
_max_log_write_parallelism				     Maximum parallelism within a log write (auto=0)
_max_log_write_io_parallelism				     Maximum I/O parallelism within a log write (auto=0)
_serial_log_write_worker_io				     Serialize log write slave I/O
_simulated_log_write_usecs				     Simulated latency of log writes (usecs)
_adaptive_log_file_sync_use_postwait_threshold_aging	     Permille of foreground load from when post/wait was last used
_adaptive_log_file_sync_sampling_count			     Evaluate post/wait versus polling every N writes
_adaptive_log_file_sync_sampling_time			     Evaluate post/wait versus polling every N seconds
_check_pdbid_in_redo					     Enable checking of pluggable database ID in redo
_ping_wait_for_log_force				     Wait for log force before block ping
_log_writer_worker_dlm_hearbeat_update_freq		     LGWR worker DLM health-monitoring heartbeat update frequency (ms)
_hb_redo_interval					     generic heartbeat redo frequency
_disable_file_resize_logging				     disable file resize logging to alert log
_file_set_enqueue_timeout				     Timeout to acquire file set enqueue (secs)
_file_offline_sync_timeout				     Timeout to sync file offline enqueue (secs)
_gc_policy_rm_dirty_percent				     percent of cache which can be dirty for readmostly
_gc_dump_remote_lock					     if TRUE, dump remote lock
_gc_save_cleanout					     if TRUE, save cleanout to apply later
_gc_temp_affinity					     if TRUE, enable global temporary affinity
_gc_fg_spin_time					     foreground msgq spin time
_gc_async_send						     send blocks asynchronously
_gc_try_to_skip_imc_flush				     if TRUE, try to skip an imc populate flush
_gc_msgq_buffer_size					     MSGQ buffer size
_gc_msgq_buffers					     number of MSGQ buffers
_gc_serve_from_flash_cache				     if TRUE, try to serve a flash cache buffer
_gc_trace_freelist_empty				     if TRUE, dump a trace when we run out of lock elements
_kcbl_assert_reset_slot 				     assert slot state in kcblResetSlot
_thread_state_change_timeout_pnp			     Thread state change timeout for PnP instance (in sec)
_allow_file_1_offline_error_1245			     don't signal ORA-1245 due to file 1 being offline
_switchover_timeout					     Switchover timeout in minutes
_fast_psby_conversion					     Enable fast physical standby conversion
_ac_enable_dscn_in_rac					     Enable Dependent Commit SCN tracking
_ac_strict_SCN_check					     enforce strict SCN check for AC replay across DG failover
_allow_convert_to_standby				     allow convert to standby to go through
_early_flush_delta					     SCN delta to trigger early log flush
_backup_ksfq_bufmem_max 				     maximum amount of memory (in bytes) used for buffers for backup/restore
_backup_min_ct_unused_optim				     mimimun size in bytes of change tracking to apply unused space optimuzation
_krb_trace_buffer_size					     size of per-process I/O trace buffer
_catalog_foreign_restore				     catalog foreign file restore
_prefered_standby					     standby db_unique_name prefered for krb operations
_krbabr_trace_buffer_size				     size of I/O trace buffer
_backup_appliance_enabled				     Backup Appliance Enabled
_ba_container_filesystem_ausize 			     allocation unit size for Backup Appliance containers
_ba_max_groups						     maximum number of Backup Appliance container groups
_ba_max_containers					     maximum number of Backup Appliance containers
_ba_max_seg_bytes					     maximum number of bytes per array segment
_ba_cf_trace_buffer_size				     size of per-process I/O KBC trace buffer
_ba_timeouts_enabled					     enable timeouts
_aux_dfc_keep_time					     auxiliary datafile copy keep time in minutes
_undo_tbs_slave_percent 				     Percentage of redo slaves for undo tablespace
_clone_one_pdb_recovery 				     Recover ROOT and only one PDB in clone database
_time_based_rcv_ckpt_target				     time-based incremental recovery checkpoint target in sec
_time_based_rcv_hdr_update_interval			     time-based incremental recovery file header update interval in sec
_use_pdb_parselock					     use PDB level parselock on ADG
_snapshot_recovery_enabled				     enable/disable snapshot recovery
_bct_public_dba_buffer_dynresize			     allow dynamic resizing of public dba buffers, zero to disable
_bct_public_dba_buffer_maxsize				     max buffer size permitted for public dba buffers, in bytes
_krc_trace_buffer_size					     size of I/O trace buffer
_kra_trace_buffer_size					     size of I/O trace buffer
_kcfis_qm_prioritize_sys_plan				     Prioritize Quaranitine Manager system plan
_kcfis_qm_user_plan_name				     Quaranitine Manager user plan name
_kcfis_xtgran_prefetch_count				     External Table Smart Scan granule prefetch count
_nologging_sendbuf_ratio				     Nologging standby: outstanding send buffer ratio
_nologging_load_slotsz					     Nologging standby: direct load buffer size
_force_logging_in_upgrade				     force logging during upgrade mode
_nologging_kcnbuf_hash_buckets				     Number of nologging buffer hash buckets
_nologging_kcnbuf_hash_latches				     Number of nologging buffer hash latches
_nologging_sdcl_append_wait				     Nologging standby append sdcl wait time
_nologging_txn_cmt_wait 				     Nologging standby transaction commit wait time
_max_data_transfer_cache_size				     Maximum size of data transfer cache
_data_transfer_cache_bc_perc_x100			     Percentange * 100 of buffer cache to transfer to data transfer cache
_data_transfer_cache_size				     Size of data transfer cache
__data_transfer_cache_size				     Actual size of data transfer cache
_max_kcnibr_ranges					     Max number of nonlogged data block ranges
_log_undo_df_info					     generate marker to log file# that belong to undo tablespace
_cvmap_buffers						     Number of change vector buffers for multi instance media recovery
_txn_control_trace_buf_size				     size the in-memory buffer size of txn control
_collect_tempundo_stats 				     Collect Statistics v$tempundostat
temp_undo_enabled					     is temporary undo enabled
_temp_undo_disable_adg					     is temp undo disabled on ADG
_resumable_critical_alert				     raise critical alert for resumable failure
_securefile_log_num_latches				     Maximum number of open descriptors for securefile log
_securefile_log_shared_pool_size			     Size of securefile log buffer pool from SGA
_cli_cachebktalloc					     Percentage of memory to allocate
_ilm_mem_limit						     percentage of the max shared pool heat-map can use - internal
_ktilmsc_exp						     expiration time of ktilm segment cache (in second)
_inmemory_check_prot_meta				     If true, marks SMU area read only to prevent stray writes
_inmemory_private_journal_quota 			     quota for transaction in-memory private journals
_inmemory_private_journal_sharedpool_quota		     quota for transaction in-memory objects
_inmemory_private_journal_numbkts			     Number of priv jrnl ht bkts
_inmemory_private_journal_numgran			     Number of granules per HT node
_inmemory_jscan 					     inmemory jscan enable
_inmemory_pin_hist_mode 				     settings for IM pinned buffer history
_inmemory_txn_checksum					     checksum for SMUs and private journals
_inmemory_buffer_waittime				     wait interval for one SMU or IMCU to be freed
_inmemory_cu_timeout					     maximum wait time for one IMCU to be freed
_inmemory_cudrop_timeout				     maximum wait time for IMCU to be freed during drop
_inmemory_exclto_timeout				     maximum wait time to pin SMU for cleanout
_inmemory_num_hash_latches				     Maximum number of latches for IM buffers
_inmemory_strdlxid_timeout				     max time to determine straddling transactions
_inmemory_incremental_repopulation			     If true, incremental repopulation of IMCU will be attempted
_inmemory_lock_for_smucreate				     take object lock during smu creation
_old_extent_scheme					     Revert to old extent allocation
_trace_temp						     Trace Tempspace Management
_concurrency_chosen					     what is the chosen value of concurrency
_no_small_file						     Not to apply new extent scheme for small file temp spaces
_assm_test_force_rej					     assm min number of blocks to cbk-reject
_assm_test_force_rej2					     assm min number of blocks to kdt-reject
_assm_test_reentrant_gsp				     assm test reentrant gsp
_disable_flashback_recyclebin_opt			     Don't use the Flashback Recyclebin optimization
_enable_securefile_flashback_opt			     Enable securefile flashback optimization
_enable_12g_bft 					     enable 12g bigfile tablespace
_undotbs_stepdown_pcent 				     Undo Tablespace small allocation step down percentage
_undotbs_regular_tables 				     Create regular tables in undo tablespace
_minmax_spacebg_slaves					     min-max space management background slaves
_min_spacebg_slaves					     minimum space management background slaves
_securefiles_breakreten_retry				     segment retry before dishonoring retention
_securefiles_spcutl					     securefiles segment utl optimization
_trace_ktfs						     Trace ILM Stats Tracking
_trace_ktfs_mem 					     Debug memleak
heat_map						     ILM Heatmap Tracking
_enable_ilm_flush_stats 				     Enable ILM Stats Flush
_enable_ilm_testflush_stats				     Enable Test ILM Stats Flush
_disable_12cbigfile					     DIsable Storing ILM Statistics in 12cBigFiles
_enable_heatmap_internal				     heatmap related - to be used by oracle dev only
_heatmap_format_1block					     heatmap related - to be used by oracle dev only
_test_hm_extent_map					     heatmap related - to be used by oracle dev only
_ilmset_stat_limit					     ILM set statistics limit - Internal testing only
_ilmflush_stat_limit					     ILM flush statistics limit - Internal testing only
_heatmap_min_maxsize					     Internal testing only
_ilmstat_memlimit					     Percentage of shared pool for use by ILM Statistics
_flush_ilm_stats					     flush ilm stats
_create_stat_segment					     create ilm statistics segment
_drop_stat_segment					     drop ilm statistics segment
_print_stat_segment					     print ilm statistics segment
_print_inmem_heatmap					     print inmem ilm heatmap
_inmemory_auto_distribute				     If true, enable auto distribute
_inmemory_autodist_2safe				     If true, enable auto distribute with 2safe
_inmemory_distribute_timeout				     If true, enable auto distribute with 2safe
_inmemory_distribute_ondemand_timeout			     On demand timeout for redistribute
inmemory_size						     size in bytes of in-memory area
_inmemory_64k_percent					     percentage of in-memory area for 64k pools
_inmemory_min_ima_defersize				     Defer in-memory area allocation beyond this size
_inmemory_memprot					     enable or disable memory protection for in-memory
_trace_ktds						     Trace block reuse
_track_space_reuse					     SpaceReuse Tracking
_spacereuse_track_memlimit				     Percentage of shared pool for use by spacereuse tracking
_print_inmem_srmap					     print inmem spacereuse map
_sage_block_checking					     enable block checking of blocks returned by smartscan
_suppress_identifiers_on_dupkey 			     supress owner index name err msg
_widetab_comp_enabled					     wide table compression enabled
_force_sys_compress					     Sys compress
_oltp_comp_dbg_scan					     oltp compression scan debug
_cu_row_locking 					     CU row level locking
_oltp_spill						     spill rows for oltp compression if loader pga limit is exceeded
_pdb_use_sequence_cache 				     Use sequence cache in PDB mode
_index_max_inc_trans_pct				     max itl expand percentage soft limit during index insert
_advanced_index_compression_options			     advanced index compression options
_advanced_index_compression_options_value		     advanced index compression options2
_advanced_index_compression_trace			     advanced index compression trace
db_index_compression_inheritance			     options for table or tablespace level compression inheritance
_ldr_tempseg_threshold					     amount to buffer prior to allocating temp segment (extent sizing)
_kdli_descn_adj 					     coalesce extents with deallocation scn adjustment
_kdli_mts_so						     use state objects in shared server for asyncIO pipelines
_kdli_ralc_length					     lob length threshold to trigger rounded allocations
_kdli_ralc_rounding					     rounding granularity for rounded allocations
_kdli_space_cache_segments				     #segments in space cache
_sf_default_enabled					     enable 12g securefile default
_kdlf_read_flag 					     kdlf read flag
_kdz_pcode_flags					     pcode flags
_enable_columnar_cache					     Enable Columnar Flash Cache Rewrite
_kdz_proj_nrows 					     Number of rows to project at a time in kdzt
_kdz_pred_nrows 					     Number of rows to predicate at a time in kdzt
_kdzk_enable_init_trace 				     Enable kdzk initialization tracing
_kdzk_load_specialized_library				     Flags for loading HPK specialized library
_kdzk_trace_level					     HPK trace level
_hpk_project_cost_weighting				     hpk project cost weighting
_hpk_throughput_range					     hpk throughput range
_hpk_compression_range					     hpk compression range
_inmemory_analyzer_optimize_for 			     inmemory analyzer optimize for
_dbfs_modify_implicit_fetch				     DBFS Link allows implicit fetch on modify - only on SecureFiles
_ILM_FILTER_TIME					     Upper filter time for ILM block compression
_ILM_FILTER_TIME_LOWER					     Lower filter time for ILM block compression
_ILM_POLICY_NAME					     User specified ILM policy name
_inmemory_default_flags 				     Default flags based on inmemory_clause_default
_inmemory_default_new					     Force in-memory on new tables
inmemory_clause_default 				     Default in-memory clause for new tables
inmemory_force						     Force tables to be in-memory or not
inmemory_query						     Specifies whether in-memory queries are allowed
_inmemory_query_scan					     In-memory scan enabled
_inmemory_scan_override 				     In-memory scan override
_inmemory_scan_threshold_percent_noscan 		     In-memory scan threshold maximum percent dirty no scan
_inmemory_small_segment_threshold			     In-memory small segment threshold (must be larger for in-memory)
_inmemory_query_fetch_by_rowid				     In-memory fetch-by-rowid enabled
_inmemory_pruning					     In-memory pruning
_inmemory_enable_sys					     enable in-memory on system tablespace with sys user
_inmemory_populate_fg					     populate in foreground
_inmemory_pga_per_server				     minimum pga needed per inmemory populate server
inmemory_max_populate_servers				     maximum inmemory populate servers
_inmemory_servers_throttle_pgalim_percent		     In-memory populate servers throttling pga limit percentage
inmemory_trickle_repopulate_servers_percent		     inmemory trickle repopulate servers percent
_inmemory_populate_wait 				     wait for population to complete
_inmemory_populate_wait_max				     maximum wait time in seconds for segment populate
_inmemory_imco_cycle					     IMCO cycle in seconds (sleep period)
_inmemory_enable_population_verify			     verify in-memory population
_inmemory_log_level					     in-memory log level
_inmemory_fs_verify					     in-memory faststart verify
_inmemory_force_fs					     in-memory faststart force
_inmemory_force_fs_tbs					     in-memory faststart force tablespace
_inmemory_force_fs_tbs_size				     in-memory faststart force tablespace size
_inmemory_fs_raise_error				     in-memory faststart raise error
_inmemory_fs_nodml					     in-memory faststart assumes no dmls while populating
_inmemory_fs_enable					     in-memory faststart enable
_inmemory_fs_enable_blk_lvl_inv 			     in-memory faststart enable block level invalidation
_inmemory_fs_blk_inv_blkcnt				     in-memory faststart CU invalidation threshold(blocks)
_inmemory_fs_blk_inv_blk_percent			     in-memory faststart CU invalidation threshold(blocks)
_inmemory_enable_stat_alert				     dump in-memory stats in alert log file
_inmemory_imcu_align					     Enforce 8M IMCU alignment
_inmemory_max_populate_retry				     IM populate maximum number of retry
_inmemory_imcu_target_rows				     IMCU target number of rows
_inmemory_imcu_target_bytes				     IMCU target size in bytes
_inmemory_imcu_source_extents				     number of source extents per IMCU
_inmemory_imcu_source_blocks				     number of source blocks per IMCU
_inmemory_imcu_source_minbytes				     number of minimum source bytes per IMCU
_inmemory_imcu_populate_minbytes			     minimum free space in IMA for populating IMCU
_inmemory_imcu_source_analyze_bytes			     number of source analyze bytes per IMCU
_inmemory_imcu_target_maxrows				     IMCU maximum target number of rows
_inmemory_imcu_source_maxbytes				     IMCU maximum source size in bytes
_inmemory_max_queued_tasks				     Maximum queued populating tasks on the auxiliary queue
_inmemory_repopulate_threshold_rows			     In-memory repopulate threshold number of modified rows
_inmemory_repopulate_threshold_blocks			     In-memory repopulate threshold number of modified blocks
_inmemory_pct_inv_rows_invalidate_imcu			     In-memory percentage invalid rows for IMCU invalidation
_inmemory_pct_inv_blocks_invalidate_imcu		     In-memory percentage invalid blocks for IMCU invalidation
_inmemory_repopulate_threshold_mintime_factor		     In-memory repopulate minimum interval (N*timetorepop)
_inmemory_repopulate_threshold_mintime			     In-memory repopulate minimum interval (millisec)
_inmemory_repopulate_threshold_scans			     In-memory repopulate threshold number of scans
_inmemory_repopulate_priority_scale_factor		     In-memory repopulate priority threshold scale factor
_inmemory_repopulate_invalidate_rate_percent		     In-memory repopulate invalidate rate percent
_inmemory_repopulate_priority_threshold_row		     In-memory repopulate priority threshold row
_inmemory_repopulate_priority_threshold_block		     In-memory repopulate priority threshold block
_inmemory_repopulate_threshold_rows_percent		     In-memory repopulate threshold rows invalid percentage
_inmemory_repopulate_threshold_blocks_percent		     In-memory repopulate threshold blocks invalid percentage
_inmemory_repopulate_disable				     disable In-memory repopulate
_inmemory_check_protect 				     If true, marks in-memory area read only to prevent stray writes
_inmemory_checksum					     If true, checksums in-memory area to detect stray writes
_inmemory_validate_fetch				     If true, validate single-row fetch between in-memory and disk
_inmemory_journal_row_logging				     If true, log the entire row into the in-memory journal
_inmemory_journal_check 				     Depending on value does one of the DML verifications
_inmemory_rows_check_interrupt				     Number of rows buffered before interrupt check
_inmemory_dbg_scan					     In-memory scan debugging
_inmemory_segment_populate_verify			     In-memory segment populate verification
_inmemory_query_check					     In-memory query checking
_inmemory_test_verification				     In-memory verification testing
_inmemory_invalidate_cursors				     In-memory populate enable cursor invalidations
_inmemory_prepopulate_fg				     Force prepopulate of in-memory segment in foreground
_inmemory_prepopulate					     Enable inmemory populate by IMCO
_inmemory_trickle_repopulate				     Enable inmemory trickle repopulate
_inmemory_trickle_repopulate_threshold_dirty_ratio	     IMCO Trickle Repopulate threshold dirty ratio
_inmemory_trickle_repopulate_min_interval		     IMCO Trickle Repopulate Interval
_inmemory_trickle_repopulate_fg 			     Trickle Repopulate in the Foreground
_inmemory_force_non_engineered				     force non-engineered systems in-memory behavior on RAC
_inmemory_suppress_vsga_ima				     Suppress inmemory area in v$sga
_index_load_buf_oltp_sacrifice_pct			     index load buf oltp sacrifice pct
_index_load_buf_oltp_under_pct				     index load buf and comp oltp under-estimation pct
_kdizoltp_uncompsentinal_freq				     kdizoltp uncomp sentinal frequency
_kqdsn_min_instance_bits				     minimum bits used for instance value in sequence partition
_kqdsn_max_instance_bits				     maximum bits used for instance value in sequence partition
_kqdsn_partition_ratio					     ratio of instance to session bits in sequence partition
_kgl_large_heap_assert_threshold			     maximum heap size before KGL raises an internal error
_revoke_new_semantics					     Enable revoke semantics
_tsenc_obfuscate_key					     Encryption key obfuscation in memory
_db_discard_lost_masterkey				     discard lost masterkey handles
_db_generate_dummy_masterkey				     if TRUE, use old havior and generate dummy master key
_use_fips_mode						     Enable use of crypographic libraries in FIPS mode
DBFIPS_140						     Enable use of crypographic libraries in FIPS mode, public
_pbkdf2_sder_count					     The PBKDF2 count to use for session key derivation
_resource_includes_unlimited_tablespace 		     Whether RESOURCE role includes UNLIMITED TABLESPACE privilege
_xs_cleanup_task					     Triton Session Cleanup
_xds_max_child_cursors					     Maximum number of XDS user-specific child cursors
_xs_dispatcher_only					     XS dispatcher only mode
_unified_audit_policy_disabled				     Disable Default Unified Audit Policies on DB Create
unified_audit_sga_queue_size				     Size of Unified audit SGA Queue
_unified_audit_flush_threshold				     Unified Audit SGA Queue Flush Threshold
_unified_audit_flush_interval				     Unified Audit SGA Queue Flush Interval
_reco_sessions_max_percentage				     allowed RECO sessions as a percentage of total sessions allowed
_disable_duplicate_service_warning			     disable listener warning for duplicate service
_dispatcher_listen_on_vip				     Determines if dispatcher listens on VIP if no HOST is supplied
use_dedicated_broker					     Use dedicated connection broker
_mpmt_fg_enabled					     MPMT mode foreground enabled
connection_brokers					     connection brokers specification
_cursor_reload_failure_threshold			     Number of failed reloads before marking cursor unusable
_kks_obsolete_dump_threshold				     Number of parent cursor obsoletions before dumping cursor
_plsql_icd_arg_dump					     Dump arguments to ICD
_redef_on_statement					     Use on-statement refresh in online redefinition
_AllowMultInsteadofDDLTrigger				     Oracle internal parameter to allow multiple instead of DDL triggers
_kktAllowInsteadOfDDLTriggeronDDL			     Oracle internal parameter to allow instead of DDL triggers on specified DDLs
_lock_next_constraint_count				     max number of attempts to lock _NEXT_CONSTRAINT
_max_string_size_bypass 				     controls error checking for the max_string_size parameter
_bigdata_external_table 				     enables use of ORACLE_HIVE and ORACLE_HDFS access drivers
_kxscio_cap_stacks					     capture location when kxscio is set to null
_px_proactive_slave_alloc_threshold			     parallel proactive slave allocation threshold/unit
_px_load_balancing_policy				     parallel load balancing policy
_px_dp_array_size					     Max number of pq processes supported
_sort_sync_min_spillsize				     controls the size of mininum run size for synchronized spill (in kb)
_sort_sync_min_spill_threshold				     controls the mininum spill size for synchronized spill (in percent)
_cell_offload_expressions				     enable offload of expressions to cells
_cell_materialize_virtual_columns			     enable offload of expressions underlying virtual columns to cells
_cell_materialize_all_expressions			     Force materialization of all offloadable expressions on the cells
_cell_offload_sys_context				     enable offload of SYS_CONTEXT evaluation to cells
_gby_vector_aggregation_enabled 			     enable group-by and aggregation using vector scheme
_optimizer_vector_transformation			     perform vector transform
_optimizer_vector_fact_dim_ratio			     cost based vector transform dimension to fact ratio
_key_vector_max_size					     maximum key vector size (in KB)
_key_vector_predicate_enabled				     enables or disables key vector filter predicate pushdown
_key_vector_predicate_threshold 			     selectivity pct for key vector filter predicate pushdown
_key_vector_offload					     controls key vector offload to cells
_key_vector_caching					     Enables vector key vector caching
_vector_operations_control				     control different uses/algorithms related to vector transform
_optimizer_vector_min_fact_rows 			     min number of rows required for vector aggregation transform
_vector_serialize_temp_threshold			     threshold for serializing vector transform temp table writes
_always_vector_transformation				     always favor use of vector transformation
_vector_aggregation_max_size				     max size of vector aggregation space
_optimizer_key_vector_aggr_factor			     the required aggregation between IJK and DGK
_optimizer_vector_cost_adj				     cost adjustment for vector aggregation processing estimates
_crash_domain_on_exception				     allow domain to exit for exceptions in any thread
_odci_aggregate_save_space				     trade speed for space in user-defined aggregation
_cell_storidx_minmax_enabled				     enable Storage Index Min/Max optimization on the cells
_cell_offload_complex_processing			     enable complex SQL processing offload to cells
_optimizer_partial_join_eval				     partial join evaluation parameter
_optimizer_unnest_scalar_sq				     enables unnesting of of scalar subquery
_px_object_sampling					     parallel query sampling for base objects (100000 = 100%)
_utlmmig_table_stats_gathering				     enable/disable utlmmig table stats gathering at upgrade
_remove_exf_component					     enable/disable removing of components EXF and RUL during upgrade
_bloom_filter_size					     bloom filter vector size (in KB)
_bloom_predicate_offload				     enables or disables bloom filter predicate offload to cells
_bloom_rm_filter					     remove bloom predicate in favor of zonemap join pruning predicate
_dbop_enabled						     Any positive number enables automatic DBOP monitoring. 0 is disabled
_optimizer_ansi_join_lateral_enhance			     optimization of left/full ansi-joins and lateral views
_optimizer_multi_table_outerjoin			     allows multiple tables on the left of outerjoin
_optimizer_null_accepting_semijoin			     enables null-accepting semijoin
_optimizer_ansi_rearchitecture				     re-architecture of ANSI left, right, and full outer joins
_optimizer_aggr_groupby_elim				     group-by and aggregation elimination
_optimizer_gather_feedback				     optimizer gather feedback
_part_redef_global_index_update 			     online partition redefinition update global indexes
_optimizer_generate_transitive_pred			     optimizer generate transitive predicates
_optimizer_cube_join_enabled				     enable cube join
max_string_size 					     controls maximum size of VARCHAR2, NVARCHAR2, and RAW types in SQL
_dm_enable_legacy_dmf_output_types			     revert dmf output types to pre-12.1.0.1
_dm_dmf_details_compatibility				     set dm dmf details compatibility version
_optimizer_hybrid_fpwj_enabled				     enable hybrid full partition-wise join when TRUE
_px_message_compression 				     enable compression of control messages for parallel query
_px_object_sampling_enabled				     use base object sampling when possible for range distribution
_px_concurrent						     enables pq with concurrent execution of serial inputs
_px_back_to_parallel					     allow going back to parallel after a serial operation
_scalar_type_lob_storage_threshold			     threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB
_px_replication_enabled 				     enables or disables replication of small table scans
_zonemap_use_enabled					     enable the use of zonemaps for IO pruning
_zonemap_staleness_tracking				     control the staleness tracking of zonemaps via trigger
_zonemap_control					     control different uses/algorithms related to zonemaps
_sql_plan_directive_mgmt_control			     controls internal SQL Plan Directive management activities
_px_filter_parallelized 				     enables or disables correlated filter parallelization
_px_filter_skew_handling				     enable correlated filter parallelization to handle skew
_px_groupby_pushdown					     perform group-by pushdown for parallel query
_partition_advisor_srs_active				     enables sampling based partitioning validation
_px_parallelize_expression				     enables or disables expression evaluation parallelization
_fast_index_maintenance 				     fast global index maintenance during PMOPs
_optimizer_gather_stats_on_load 			     enable/disable online statistics gathering
_multi_transaction_optimization_enabled 		     reduce SGA memory use during create of a partitioned table
_optimizer_batch_table_access_by_rowid			     enable table access by ROWID IO batching
optimizer_adaptive_reporting_only			     use reporting-only mode for adaptive optimizations
_optimizer_ads_result_cache_life			     result cache shelf life for ADS queries
_px_wif_dfo_declumping					     NDV-aware DFO clumping of multiple window sorts
_px_wif_extend_distribution_keys			     extend TQ data redistribution keys for window functions
_px_wif_min_ndv_per_slave				     mininum NDV of TQ keys needed per slave for scalable WiF PX
_px_join_skew_handling					     enables skew handling for parallel joins
_px_join_skew_ratio					     sets skew ratio for parallel joins
_px_join_skew_minfreq					     sets minimum frequency(%) for skewed value for parallel joins
_px_adaptive_dist_method				     determines the behavior of adaptive distribution methods
_px_adaptive_dist_method_threshold			     Buffering / decision threshold for adaptive distribution methods
_parallel_fault_tolerance_enabled			     enables or disables fault-tolerance for parallel statement
_parallel_fault_tolerance_threshold			     total number of faults fault-tolerance will handle
_px_partial_rollup_pushdown				     perform partial rollup pushdown for parallel execution
_sql_diag_repo_retain					     retain sql diag repository to cursor or not
_sql_diag_repo_origin					     duarations where sql diag repository are retained
_optimizer_dsdir_usage_control				     controls optimizer usage of dynamic sampling directives
_px_cpu_autodop_enabled 				     enables or disables auto dop cpu computation
parallel_degree_level					     adjust the computed degree in percentage
_px_cpu_process_bandwidth				     CPU process bandwidth in MB/sec for DOP computation
_px_cpu_operator_bandwidth				     CPU operator bandwidth in MB/sec for DOP computation
_px_single_server_enabled				     allow single-slave dfo in parallel query
_optimizer_use_gtt_session_stats			     use GTT session private statistics
_optimizer_adaptive_plans				     enable adaptive plans
_optimizer_adaptive_plan_control			     internal controls for adaptive plans
_optimizer_adaptive_random_seed 			     random seed for adaptive plans
_optimizer_strans_adaptive_pruning			     allow adaptive pruning of star transformation bitmap trees
_optimizer_proc_rate_level				     control the level of processing rates
_px_hybrid_TSM_HWMB_load				     Enable Hybrid Temp Segment Merge/High Water Mark Brokered load method
_optimizer_use_histograms				     enable/disable the usage of histograms by the optimizer
_disable_directory_link_check				     Disable directory link checking
_add_nullable_column_with_default_optim 		     Allows add of a nullable column with default optimization
_emx_max_sessions					     Maximum number of sessions in the EM Express cache
_emx_session_timeout					     Session timeout (sec) in the EM Express cache
_emx_control						     EM Express control (internal use only)
_parallel_blackbox_sga					     true if blackbox will be allocated in SGA, false if PGA
_rowsets_enabled					     enable/disable rowsets
_rowsets_target_maxsize 				     target size in bytes for space reserved in the frame for a rowset
_rowsets_max_rows					     maximum number of rows in a rowset
_use_hidden_partitions					     use hidden partitions
_px_load_monitor_threshold				     threshold for pushing information to load slave workload monitor
_px_monitor_load					     enable consumer load slave workload monitoring
_px_adaptive_offload_percentage 			     percentage for PQ adaptive offloading of granules
_modify_column_index_unusable				     allow ALTER TABLE MODIFY(column) to violate index key length limit
_adaptive_window_consolidator_enabled			     enable/disable adaptive window consolidator PX plan
_sql_hvshare_threshold					     threshold to control hash value sharing across operators
_px_tq_rowhvs						     turn on intra-row hash valueing sharing in TQ
_sql_hvshare_debug					     control hash value sharing debug level
_sql_show_expreval					     show expression evalution as shared hash producer in plan
_parallel_ctas_enabled					     enable/disable parallel CTAS operation
_optimizer_performance_feedback 			     controls the performance feedback
_optimizer_proc_rate_source				     control the source of processing rates
_hashops_prefetch_size					     maximum no of rows whose relevant memory locations are prefetched
_stat_aggs_one_pass_algorithm				     enable one pass algorithm for variance-related functions
_px_onepass_slave_acquisition				     enable/disable one pass slave acquisition for parallel execution
_online_ctas_diag					     controls dumping diagnostic information for online ctas
_upddel_dba_hash_mask_bits				     controls masking of lower order bits in DBA
_px_pwmr_enabled					     parallel partition wise match recognize enabled
_px_cdb_view_enabled					     parallel cdb view evaluation enabled
optimizer_adaptive_features				     controls adaptive features
_partition_cdb_view_enabled				     partitioned cdb view evaluation enabled
_common_data_view_enabled				     common objects returned through dictionary views
_pred_push_cdb_view_enabled				     predicate pushdown enabled for CDB views
_rowsets_cdb_view_enabled				     rowsets enabled for CDB views
_distinct_agg_optimization_gsets			     Use Distinct Aggregate Optimization for Grouping Sets
_array_cdb_view_enabled 				     array mode enabled for CDB views
_mv_refresh_truncate_log				     materialized view refresh truncate MV log
_rc_sys_obj_enabled					     result cache enabled for Sys Objects
_px_scalable_invdist					     enable/disable px scalable plan for inverse distribution functions
_indexable_con_id					     indexing of CON_ID column enabled for X$ tables
_optimizer_reduce_groupby_key				     group-by key reduction
_optimizer_cluster_by_rowid_batched			     enable/disable the cluster by rowid batching feature
_optimizer_cluster_by_rowid_batch_size			     Sorting batch size for cluster by rowid feature
_object_link_fixed_enabled				     object linked views evaluated using fixed table
_optimizer_synopsis_min_size				     minimal synopsis size (kb)
_merge_monitor_threshold				     threshold for pushing information to MERGE monitoring
optimizer_inmemory_aware				     optimizer in-memory columnar awareness
_optimizer_inmemory_table_expansion			     optimizer in-memory awareness for table expansion
_optimizer_inmemory_gen_pushable_preds			     optimizer generate pushable predicates for in-memory
_optimizer_inmemory_autodop				     optimizer autoDOP costing for in-memory
_optimizer_inmemory_access_path 			     optimizer access path costing for in-memory
_optimizer_inmemory_quotient				     in-memory quotient (% of rows in in-memory format)
_optimizer_inmemory_pruning_ratio_rows			     in-memory pruning ratio for # rows (% of rows remaining after pruning)
_parallel_inmemory_min_time_threshold			     threshold above which a plan is a candidate for parallelization for in-memory ta
							     bles (in seconds)

_parallel_inmemory_time_unit				     unit of work used to derive the degree of parallelism for in-memory tables (in s
							     econds)

_px_external_table_default_stats			     the external table default stats collection enable/disable
_optimizer_nlj_hj_adaptive_join 			     allow adaptive NL Hash joins
_px_autodop_pq_overhead 				     adjust auto dop calculation using pq overhead
_px_overhead_init_slavealloc				     pq overhead on allocating per slave during initialization (in microseconds)
_px_overhead_init_endpoints				     pq overhead on setting up one end point during initialization (in microseconds)
_px_overhead_exec_ctrlmesg				     pq overhead on sending one control message during execution (in microseconds)
_px_overhead_teardown					     pq overhead on setting up one end point during initialization (in microseconds)
_grant_read_instead_of_select				     grant read privilege instead of select wherever applicable
_optimizer_inmemory_bloom_filter			     controls serial bloom filter for in-memory tables
_optimizer_inmemory_cluster_aware_dop			     Affinitize DOP for inmemory objects
_cdb_cross_container					     Debug flag for cross container operations
_cdb_view_parallel_degree				     Parallel degree for a CDB view query
_optimizer_hll_entry					     number of entries in hll hash table
_cross_con_row_count					     use actual row count for cross container views
_cdb_view_prefetch_batch_size				     Batch Size for Prefetch for a CDB view query
_cdb_view_rc_shelflife					     Result Cache Shelflife for a CDB view query
_approx_cnt_distinct_gby_pushdown			     perform group-by pushdown for approximate distinct count query
_approx_cnt_distinct_optimization			     settings for approx_count_distinct optimizations
_px_cdb_view_join_enabled				     disable parallelism cap on CDB view
_external_table_smart_scan				     External Table Smart Scan
_optimizer_inmemory_minmax_pruning			     controls use of min/max pruning for costing in-memory tables
_query_execution_time_limit				     Query execution time limit in seconds
_optimizer_ads_use_partial_results			     Use partial results of ADS queries
_alter_upgrade_signature_only				     alter table upgrade only sets signature
_max_clients_per_emon					     maximum number of clients per emon
_emon_pool_inc						     increment in EMON slaves per pool type
_emon_pool_min						     minimum number of EMON slaves per pool type
_emon_pool_max						     maximum number of EMON slaves per pool type
_aq_streaming_threshold 				     large payload threshold size
_re_fast_sql_operator					     enables fast boxable sql operator
_re_result_cache_keysiz 				     defines max number key for result cache hash table
_re_result_cache_size					     defines max number of cached elements for result cache
_re_independent_expression_cache_size			     defines max number of compiled cached expressions for iee
_enable_iee_stats					     enables IEE stats gathering
_re_num_complex_operator				     defines max number of compiled complex operator per ruleset-iee
_re_num_rowcache_load					     defines max number of complex operators loaded with row cache
_aqsharded_cache_limit					     Limit for cached enqueue/dequeue operations
_aq_Txn_ht_sz						     Message cache Txn Hash table size
_aq_shard_bitmap_child_latches				     Bitmap child latches
_aq_shard_retry_child_latches				     Retry child latches
_aq_shard_txn_child_latches				     Txn child latches
_aq_shard_sub_child_latches				     Subscriber child latches
_aq_shard_sub_child_Elem_latches			     Subscriber Element child latches
_aq_shard_child_latches 				     Shard child latches
_aq_shard_prty_latches					     Shard priority child latches
_aq_init_shards 					     Minimum enqueue shards per queue at an instance
_aq_disable_x						     AQ - Disable new cross processes at an instance
_aq_latency_relative_threshold				     Relative threshold of average latency
_aq_latency_absolute_threshold				     Absolute threshold greater than average latency
_aq_x_msg_size						     AQ cross single message buffer size
_aq_stop_backgrounds					     Stop all AQ background processes
_aq_lb_cycle						     Time(seconds) between consecutive AQ load balancing efforts
_aq_lb_stats_collect_cycle				     Time(seconds) between consecutive AQ load statistics collection
_aq_pt_processes					     Partition background processes
_aq_subshard_Size					     Sub Shard Size
_aq_subshards_per_qpartition				     SubShards Per Q Partition
_aq_subshards_per_dqpartition				     SubShards Per Deq Partition
_aq_lookback_size					     AQ PT Look Back Size
_aq_qt_prefetch_Size					     AQ PT QT prefech Size
_aq_dq_prefetch_Siz					     AQ PT DQ prefech Size
_aq_pt_statistics_window				     PT statistics sample window Size
_aq_pt_shrink_frequency 				     PT shrink window Size
_aq_addpt_batch_size					     Add PT batch Size
_aq_truncpt_batch_size					     Trunc PT batch Size
_aq_droppt_batch_size					     Drop PT batch Size
_shrd_que_tm_processes					     number of sharded queue Time Managers to start
_shrd_que_tm_statistics_duration			     Shaded queue statistics collection window duration
_aq_ipc_max_slave					     maximum number of slaves for knlpipcm
_ctx_doc_policy_stems					     enable ctx_doc.policy_stems api
_olap_row_load_time_precision				     OLAP Row Load Time Precision
_olap_disable_loop_optimized				     Disable LOOP OPTIMIZED directive in OLAP_TABLE
_remote_asm						     remote ASM configuration
_asm_relocation_trace					     enable extent relocation tracing
_asm_relocation_ignore_hard_failure			     ignore HARD for relocation
_asm_max_parallelios					     Maximum simultaneous outstanding IOs
_kffmlk_hash_size					     size of kffmlk_hash table
_asm_diskgroups2					     disk groups to mount automatically set 2
_asm_diskgroups3					     disk groups to mount automatically set 3
_asm_diskgroups4					     disk groups to mount automatically set 4
_asm_disable_dangerous_failgroup_checking		     Disable checking for dubious failgroup configurations
_asm_diskerr_traces					     Number of read/write errors per disk a process can trace
_asm_procs_trace_diskerr				     Number of processes allowed to trace a disk failure
_asm_trace_limit_timeout				     Time-out in milliseconds to reset the number of traces per disk and the number o
							     f processes allowed to trace

_asm_skip_dbfile_ios					     Skip I/Os to database files (do only ASM metadata I/O)
_asm_offload_all					     Offload all write operations to Exadata cells, when supported
_asm_read_cancel					     Read cancel timeout in milliseconds
_asm_read_cancel_back_out				     Time period in milliseconds when no reads are issued to a disk after a read is c
							     ancelled

_asm_write_cancel					     Write timeout in milliseconds
_afd_disable_fence					     Disable AFD fencing
_asm_network_timeout					     Keepalive timeout for ASM network connections
_asm_skip_diskval_check 				     skip client side discovery for disk revalidate
_asm_remote_client_timeout				     timeout before killing disconnected remote clients
_asm_allow_unsafe_reconnect				     attempt unsafe reconnect to ASM
_asm_disable_ufgmemberkill				     disable ufg member kill
_asm_nodekill_escalate_time				     secs until escalating to nodekill if fence incomplete
_asm_healthcheck_timeout				     seconds until health check takes action
_asm_resyncCkpt 					     number of extents to resync before flushing checkpoint
_asm_noevenread_diskgroups				     List of disk groups having even read disabled
_asm_networks						     ASM network subnet addresses
_asm_allowdegeneratemounts				     Allow force-mounts of DGs w/o proper quorum
_asm_proxy_startwait					     Maximum time to wait for ASM proxy connection
_asm_allow_dangerous_unprotected_volumes		     Disable checking for unprotected volumes in mirrored disk groups
_asm_appliance_ignore_oak				     Ignore OAK appliance library
_dirty_appliance_mode					     Enable appliance mode even on non-appliance
_asm_appliance_slot_from_path				     Get appliance disk slot from disk path
_asm_scrub_limit					     ASM disk scrubbing power
_asm_scrub_unmatched_dba				     Scrub maximum number of blocks with unmatched DBA
_asm_enable_xrov					     Enable XROV capability
_asm_xrov_single					     Enable single issues of IOs
_asm_xrov_rsnmod					     Specify 'reason' mode
_asm_xrov_nvios 					     Specify number of VIO processes
_asm_xrov_nstats					     Specify number of IOs before stats
_skip_acfs_checks					     Override checking if on an ACFS file system
_asm_force_vam						     force VAM for external redundancy
_usd_recent_read					     Allow to scan recent USD blocks
_usd_preload_blks					     USD preload block count for prefetch
_remote_awr_enabled					     Enable/disable Remote AWR Mode
_awr_disabled_purge_tables				     Disable purging of specified AWR tables
_awr_snapshot_level					     Set Default AWR snapshot level
_awr_remote_target_dblink				     AWR Remote Target DBLink for Flushing
_awr_pdb_registration_enabled				     Parameter to enable/disable AWR PDB Registration
_awr_cdbperf_threshold					     Setting for AWR CDBPERF Threshold
_awr_partition_interval 				     Setting for AWR Partition Interval
_kewm_simulate_oer4031					     Simulate OER(4031) for one or more Metric Groups
_enable_metrics_allpdb					     Enable/Disable Metrics for Root and all PDBs if applicable
_enable_metrics_pdb					     Enable/Disable Metrics for this Non-Root PDB
_autotask_test_name					     Name of current Autotask Test (or test step)
_max_queued_report_requests				     Maximum number of report requests that can be queued in a list
_report_capture_cycle_time				     Time (in sec) between two cycles of report capture daemon
_report_capture_dbtime_percent_cutoff			     100X Percent of system db time daemon is allowed over 10 cycles
_report_capture_timeband_length 			     Length of time band (in hours) in the reports time bands table
_report_capture_recharge_window 			     No of report capture cycles after which db time is recharged
_max_report_flushes_percycle				     Max no of report requests that can be flushed per cycle
_report_request_ageout_minutes				     Time (in min) after which a report request is deleted from queue
_kecap_cache_size					     Workload Replay INTERNAL parameter used to set memory usage in  Application Repl
							     ay

_rtaddm_trigger_enabled 				     To enable or disable Real-Time ADDM automatic trigger
_rtaddm_trigger_args					     comma-separated list of numeric arguments for RT addm trigger
spatial_vector_acceleration				     enable spatial vector acceleration
_diag_adr_trace_dest					     diagnosability trace directory path
_diag_patch_cap_enabled 				     Parameter to enable/disable Diag Patch Configuration Capture
_log_segment_dump_parameter				     Dump KSP on Log Segmentation
_log_segment_dump_patch 				     Dump Patchinfo on Log Segmentation
_max_incident_file_size 				     Maximum size (in KB, MB, GB, Blocks) of incident dump file
_uts_trace_segment_size 				     Maximum size (in bytes) of a trace segment
_uts_trace_segments					     Maximum number of trace segments
_uts_first_segment_size 				     Maximum size (in bytes) of first segments
_uts_first_segment_retain				     Should we retain the first trace segment
_uts_trace_disk_threshold				     Trace disk threshold parameter
_diag_test_seg_reinc_mode				     Sets trace segmentation to be in reincarnation mode
_kxdbio_ctx_init_count					     initial count of KXDBIO state object
_kxdbio_disable_offload_opcode				     KXDBIO Disable offload for the set opcodes.  Value is a Bitmap of	  0x00000001
							     - disable cell to cell data copy offload	 0x00000002 - disable disk scrubbing
							     offload to cell	0x00000004 - disable offloaded writes to cell

_kxdbio_enable_ds_opcode				     KXDBIO Enable Dumb storage simulation for the set opcodes.
_enable_offloaded_writes				     Enable offloaded writes for Unit Test
_block_level_offload_high_lat_thresh			     High Latency Threshold for Block Level Offload operations
_kxdbio_hca_loadavg_thresh				     HCA loadavg threshold at which writes need to get offloaded
_kxdbio_ut_ctl						     kxdbio unit test controls
_cell_offload_backup_compression			     enable offload of backup compression to cells
_enable_pluggable_database				     Enable Pluggable Database
enable_pluggable_database				     Enable Pluggable Database
_oracle_script						     Running an Oracle-supplied script
_discard_cmn_ddl_in_pdb_err				     Discard error when Common DDL is attempted in PDB
pdb_os_credential					     pluggable database OS credential to bind
pdb_lockdown						     pluggable database lockdown profile
pdb_file_name_convert					     PDB file name convert patterns and strings for create cdb/pdb
_pluggable_database_debug				     Debug flag for pluggable database related operations
noncdb_compatible					     Non-CDB Compatible
_cdb_compatible 					     CDB Compatible
_deferred_seg_in_seed					     Enable Deferred Segment Creation in Seed
common_user_prefix					     Enforce restriction on a prefix of a Common User/Role/Profile name
_common_user_prefix					     Enforce restriction on a prefix of a Common User/Role/Profile name
_relocate_pdb						     Relocate PDB to another RAC instance after it is closed in the current instance
_cdb_rac_affinity					     rac affinity for parallel cdb operations
_set_container_service					     set container service
_multiple_char_set_cdb					     Multiple character sets enabled in CDB
_cdb_spfile_inherit					     Inherit CDB Spfile enabled/disabled in a PDB
_enable_pdb_close_abort 				     Enable PDB shutdown abort (close abort)
_max_pdbs						     Parameter is a hint to adjust certain attributes of the CDB
_pdb_cluster_database					     This parameter can be turned-off when cluster_database is TRUE
_pdb_mask_cdb_info					     Enable masking CDB information within a PDB
exclude_seed_cdb_view					     exclude PDB$SEED from CDB View Result
_disable_cdb_view_rc_invalidation			     disable Result Cache invalidation for CDB View results
_gsm							     GSM descriptions
_gsm_region_list					     List of GSM Regions
_cloud_name						     gsm cloud name
_dbpool_name						     gsm database pool name
_region_name						     gsm region name
_db_num_gsm						     database number in gsm dbpool
_gsm_config_vers					     version of gsm config
_gsm_max_instances_per_db				     maximum number of instances per database in gsm cloud
_gsm_max_num_regions					     maximum number of regions in gsm cloud
_gsm_drv_interval					     metric derived values interval
_gsm_srlat_thresh					     Single block read latency threshold
_gsm_cpu_thresh 					     CPU busy threshold
_gsm_thresh_zone					     threshold zone
_gsm_thresh_respct					     threshold resource percentage
_gwm_spare1						     gsm spare 1
_gwm_spare2						     gsm spare 2
_gwm_spare3						     gsm spare 3
_rq_shm_max_size					     maximum size of an RQuery shared memory segement (in KB)

1106 rows selected.

SQL> 

E ae, já decorou todos :)

AWR – Medindo IOPS e Throughput de I/O do Oracle Database
agosto 27, 2014

Uma conseqüência natural do trabalho de um DBA é passar horas e horas em cima de um relatório AWR identificando problemas e causas de performance do banco de dados.

Um dos enganos mais comum da maioria é conseguir identificar o IOPS e throughput de I/O dentro do próprio Oracle AWR. Esse ponto é essencial para você conseguir definir a sua infra-estrutura de I/O para combinar com a sua aplicação e além de conseguir levantar dados para debater de forma inteligente com sua equipe de storage.

Antes, vamos realmente entender o que é IOPS e throughput de dados, que são termos completamente diferentes:

IOPS – É a unidade padrão que mensura uma operação de Input/Output (I/O) por segundo. Usado para descrever I/O rate de aplicações principalmente utilizado para acessos randômicos I/O (leituras de indexes) e aplicações do tipo OLTP.

Throughput (Mbytes/s) – Usado para mensurar grandes taxas de transferências de blocos. Usado principalmente para aplicações seqüenciais de dados como DSS e OLAP. (full table scan)

O relatório de AWR pode ser seu aliado para identificar essas métricas de I/O na sessão Instance Activity Stats:

IOPS = “physical read total I/O requests” + “physical write total I/O requests”
MBytes/s = “physical read total bytes” + “physical write total bytes”

Ou você pode utilizar a seguinte consulta para mostrar esses valores:

select min(begin_time), max(end_time),
sum(case metric_name when 'Physical Read Total Bytes Per Sec' then average end) Physical_Read_Total_Bps,
sum(case metric_name when 'Physical Write Total Bytes Per Sec' then average end) Physical_Write_Total_Bps,
sum(case metric_name when 'Physical Read Total IO Requests Per Sec' then average end) Physical_Read_IOPS,
sum(case metric_name when 'Physical Write Total IO Requests Per Sec' then average end) Physical_write_IOPS,
snap_id
from dba_hist_sysmetric_summary group by snap_id
order by snap_id;

Exemplo relatório AWR:

physical read total IO requests   =  1,257.23
physical write total IO requests  =  1,746.11
physical read total bytes         =  76,314,718.10
physical write total bytes        =  85,321,521.11

Com os dados acima do AWR reportado, temos: ~ 3003 IOPS e ~ 155 Mbytes/s

Esse método é interessante ser utilizado após mudança de equipamentos/aplicação ou até mesmo identificar gargalos do ambiente e resing necessários de Hardware. Com isso em mãos fica mais fácil dialogar sobre esses temas.

Com o Oracle 11g é possível obter dados de IOPS e throughput através do Oracle Calibrate (DBMS_RESOURCE_MANAGER.CALIBRATE_IO) que é utilizado para identificar até onde o meu Hardware pode chegar, assim o Oracle acaba utilizando esse método para várias operações internas, como por exemplo o AUTO DOP. Aqui o contexto é um pouco diferente, através das métricas de IOPS e throughput identificados pelo AWR, eu consigo além de definir por tempo, posso alcançar a métrica onde minha aplicação ou hardware está mais consumindo de I/O.

Considerações finais:

- Para ambientes RAC, você deve agregar os valores de IOPS e MBytes/s para todas as instâncias do RAC. Assim o IOPS e MBbytes no ambiente cluster será a soma de todos os valores de cada node.

- Utilize o AWR em cima dos horários de maior utilização do ambiente.

- Nunca utilize relatórios AWR mais do que 1 hora. É comum encontramos relatórios AWR das 09 as 18 horas por exemplo, isso pode mascarar o real problema do seu banco de dados. Utilize tempos curtos de 1 hora baseado nos picos de maior cargas no banco de dados.

- Para alcançar IOPS e Throughput desejado, não apenas discos pode ser a razão e sim uma série de fatores agrupados, como o próprio banco de dados, o block device manager, multipathing driver, SCSI driver, HBA, System Bus, Switch/iSCSI, Storage Array, Disks, RAID e etc .. por isso é importante avaliar cada quesito em particular quando se fala em performance de I/O.

Oracle Database 12.1.0.2 disponível
julho 22, 2014

É isso mesmo pessoal, Oracle Database 12.1.0.2.0 disponível agora no edelivery: http://edelivery.oracle.com

Hora de testar o Database In-memory :)

GUOB TECHDAY 2014 – Faça sua inscrição antecipada e aproveite o desconto
junho 26, 2014

Como vocês já devem saber aqui pelo banner do blog, A GUOB (Grupo de Usuários Oracle do Brasil) já anunciou seu principal evento na quinta edição que será realizada no dia 02 de Agosto no Hotel Blue Tree Morumbi, aqui em São Paulo.

Estarão presentes grandes palestrantes gringos e nacionais como: Alex Zaballa, Roy Swonger, Ronald Vargas, Francisco Alvarez, Gustavo Gonzalez, David Peake, Deiby Gomez, Gustavo Rene Antunez, Eduardo Guedes, Wagner Bianchi e Rodrigo Mufalani. Essa é uma ótima oportunidade para trocarmos conhecimentos, informações e mantermos atualizados com o que tem de mais novo no mundo Oracle.

E ainda fazendo sua inscrição até o dia 30 de Junho, você ainda tem desconto :)

Não tem como perder uma dessas, entre lá no site http://www.guob.com.br e faça já sua inscrição.

Facilitando seu Session Level Tracing com o script tracesid.sql
junho 9, 2014

Criei esse script para facilitar a realização do seu session level tracing, através dele fica muito fácil e rápido identificar que método de trace você pode escolher.

São apenas duas interações com nesse script. O primeiro parâmetro você apenas identifica o ID da sessão que deseja realizar o trace. Depois disso é apresentado todas as opções de trace, aí sim é só escolher qual utilizar:

——> Através da SYS.DBMS_SYSTEM.SET_EV

1 – LEVEL 12 (binds, waits)
2 – LEVEL 8 (no binds, waits)
3 – LEVEL 4 (binds, no waits)
4 – LEVEL 1 (no binds, no waits)

——> Através da DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION

5 – Usando DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION([sid], [serial#], true);

——> Através da DBMS_MONITOR_SESSION_TRACE_ENABLE

6 – (binds, waits)
7 – (no binds, waits)
8 – (binds, no waits)
9 – (binds, waits)

Veja abaixo como é fácil, execute o script apontando qual é session id, depois escolha a opção apresentada. No final ele ainda mostra o trace file da sessão escolhida:

SQL> @tracesid 146
Tracing the SESSION ID: 146

    INST# ............ 1						 SID   ............ 146
    SERIAL# .......... 9						 USERNAME ......... SYS
    SPID  ............ 3916						 OPID ............. 27
    PADDR  ........... 6B477B88 					 SADDR	........... 6B746F88
    AUDSID  .......... 4294967295					 OSUSER  .......... flaviosoares
    PROCESS  ......... 17544						 PROGRAM  ......... sqlplus@Flavios-MacBook-Pro.local (TNS V1-V3)
    MACHINE  ......... Flavios-MacBook-Pro.local			 MODULE  .......... sqlplus@Flavios-MacBook-Pro.local (TNS V1-V3)
    HASH_VALUE. ...... 942515969					 PREV_HASH_VALUE .. 942515969
    SQL_ID ........... a5ks9fhw2v9s1					 CHILD_NUMBER# .... 0
    PREV_SQL_ID ...... a5ks9fhw2v9s1					 PREV_CHILD# ...... 0


=========== DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION ===========

.	[1]*	SQL> EXEC SYS.DBMS_SYSTEM.SET_EV( SI=> 146, SE=> 9, EV=> 10046, LE=> 12, NM=>'''');    (12 = binds, waits)
.	[2]	SQL> EXEC SYS.DBMS_SYSTEM.SET_EV( SI=> 146, SE=> 9, EV=> 10046, LE=> 8,  NM=>'''');    (8  = no binds, waits)
.	[3]	SQL> EXEC SYS.DBMS_SYSTEM.SET_EV( SI=> 146, SE=> 9, EV=> 10046, LE=> 4,  NM=>'''');    (4  = binds, no waits)
.	[4]	SQL> EXEC SYS.DBMS_SYSTEM.SET_EV( SI=> 146, SE=> 9, EV=> 10046, LE=> 1,  NM=>'''');    (1  = no binds, no waits)


=========== DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION ===========

.	[5]	SQL> EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION( 146, 9, true);


=========== DBMS_MONITOR_SESSION_TRACE_ENABLE ===========

.	[6]	SQL> EXEC SYS.DBMS_MONITOR.SESSION_TRACE_ENABLE( SESSION_ID=> 146, SERIAL_NUM => 9, WAITS => TRUE  , BINDS => TRUE);
.	[7]	SQL> EXEC SYS.DBMS_MONITOR.SESSION_TRACE_ENABLE( SESSION_ID=> 146, SERIAL_NUM => 9, WAITS => FALSE , BINDS => TRUE);
.	[8]	SQL> EXEC SYS.DBMS_MONITOR.SESSION_TRACE_ENABLE( SESSION_ID=> 146, SERIAL_NUM => 9, WAITS => TRUE  , BINDS => FALSE);
.	[9]	SQL> EXEC SYS.DBMS_MONITOR.SESSION_TRACE_ENABLE( SESSION_ID=> 146, SERIAL_NUM => 9, WAITS => FALSE , BINDS => FALSE);


>>>>>>> Enter to the value of the trace type. [ ZERO to cancel]: 5 ---Escolha a opção do menu apresentado.

SQL> EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION( 146 ,9, true)

PL/SQL procedure successfully completed.


TRACE_FILE
-----------------------------------------------------------------------------------------------------------------------------------------------------------
/etc/oracle/diag/rdbms/dbtst/dbtst/trace/dbtst_ora_3916.trc

1 row selected.

SQL> 

Obs: Esse script foi testado apenas no Oracle 11g acima.

Espero que tenham gostado :)

DOWNLOAD DO SCRIPT: http://flaviosoares.com/scripts/tracesid.sql

Um caso de estudo do evento Direct Path Reads. Por que ele é tão importante para o Oracle Exadata?
maio 29, 2014

Você DBA provavelmente já deve ter ouvido falar do Oracle Smart Scan. Pois bem, o Smart Scan realmente é um dos segredos por trás da extrema velocidade de processamento das instruções dentro do Oracle Exadata Machine. Existe também o conceito de offloading dos dados dentro da arquitetura do Exadata, que refere ao fato do processamento dos dados ser feito a nível da camada de storage (storage layer) e não mais dentro da camada de banco de dados (database layer). O Smart Scan por outro lado é mais focado a nível de SQL e não de dados como o Offloading, mais como podemos ver nos documentos e manuais, a Oracle acabou que juntando esses dois conceitos e chamando apenas de “Smart Scan”.

Apesar de toda velocidade de processamento que a máquina Exadata possuí, ela não seria o que é sem a capacidade de realização de Offloading e Smart Scan. Sem essas features, o Exadata seria apenas mais uma máquina de alto poder de processamento, porém sem grande inteligência de manipulação dos dados … e como vemos, é essa inteligência que faz toda a diferença.

Mais afinal, o que o “Direct Path Reads” tem haver com o Offloading de Dados e o Exadata Smart Scan? A resposta para essa pergunta é simples: O offloading e/ou Smart Scan não acontecerá, caso sua instrução não utilize o Direct Path Reads.

Dois pré-requisitos básicos, são necessários para o Offloading/Smart Scan:

1. Obviamente, realizar a instrução em um storage Exadata.
2. Realizar o “Direct Path Reads” na sua instrução.

É claro que isso envolve algumas restrições, mais basicamente tendo esses requisitos, a sua consulta irá acabar realizando o Smart Scan.


O que é Direct Path Reads?

O Direct Path Reads foi criado pela Oracle para ignorar o Buffer Cache. O Buffer Cache como você já deve saber, é uma área da SGA destinada a manter os blocos recentes lidos, assim todos os usuários conectados na instância é capaz de ler e compartilhar desse cache sem a necessidade de ler esses blocos novamente do disco. Isso é um excelente ganho de performance, já que o evitamos o acesso a disco sempre que uma instrução é realizada. A Oracle fez um excelente trabalho ao longo dos anos, aperfeiçoando cada vez mais esse cache através dos algoritmos LRU e MRU, veja mais aqui: http://docs.oracle.com/cd/B28359_01/server.111/b28318/memory.htm#CNCPT1224

Realmente existe muito mais vantagem do que desvantagem em utilizar o Buffer Cache, porém a grande desvantagem, por ser um processo automático de gerenciamento de Buffer, o Oracle acaba por colocar “sujeiras” dentro desse cache, removendo dados que inclusive eram mais acessados pelas demais sessões. Imagina esse caso por exemplo, um relatório que é disparado uma única vez no mês para cálculos de fechamento que movimenta uma enorme quantidade de dados, por qual razão você gostaria de colocar todos esses dados gerados do relatório dentro do buffer cache do seu banco, sendo que essa instrução será executada apenas uma única vez e não será compartilhado com outras sessões dentro do banco. Inclusive, todo esses dados gerados, pode ser maior do que o próprio buffer cache, causando assim um extremo overhead em remover dados mais acessados e adicionar dados que nunca irá ser acessado. Será um tremendo trabalho em alocar, desalocar e realocar tudo novamente.

Foi aí que surgiu o Direct Path Reads.

O mecanismo de Direct Path Reads já está disponível no kernel do Oracle há muito tempo. Ele foi inicialmente implementado para trabalhar exclusivamente com os processos slaves sempre que uma instrução era disparada via paralelismo. Como os processos paralelos, como via de regra, devem ler grandes quantidades de dados o Direct Path Reads entrou na jogada para ignora completamente o mecanismo padrão do buffer. Foi decidido a partir daí, que os blocos deveriam ser armazenados em suas próprias memórias (PGA) e não mais na SGA quando se utiliza-se a consulta via DPR.

De acordo com o metalink, a partir do Oracle 11gR2, o kernel foi modificado para decidir realizar mais Direct Path Reads do que na versão 10g, ou seja na versão 10g o serial table scans tem muito mais chance de ser realizado no buffer compartilhado (scattered reads) do que na própria memória do processo (direct path reads).


Como identifico o evento Direct Path Reads?

Existem várias formas de se identificar o evento “Direct Path Reads”, uma delas é através das views de wait’s do Oracle, como por exemplo a v$session_wait.

A view v$session_wait mostra sempre o atual evento de espera ocorrido pela sessão. Usando o SQL abaixo, podemos identificar através da coluna EVENT, a utilização do evento “direct path read” para a consulta em questão (sql_id).

SELECT s.sid, w.state, w.event, s.sql_id, s.sql_child_number, 
	w.seq#, w.seconds_in_wait, w.p1text||'= '||w.p1 p1,
	w.p2text||'= '||w.p2 p2, w.p3text||'= '||w.p3 p3
FROM v$session s, v$session_wait w 
WHERE w.sid = s.sid AND w.sid = "";

Vamos a uma prova de teste. A partir de agora, vou utilizar incessantemente o parâmetro oculto chamado “_serial_direct_read” para forçar a utilização do direct reads. Vou falar mais desse parâmetro mais a frente, o importante agora é saber que através dele podemos forçar a utilização do evento Direct Path Reads.

Os testes serão feito através da tabela fss.hsk1. Essa tabela, nada mais é do que uma tabela de teste que sempre utilizo em meus testes com Oracle. A tabela é criada dentro do owner FSS e contém cerca de 4G (você pode mudar o tamanho da tabela criada, alterando a quantidade de linhas inseridas na tabela, veja os comentários dentro do script). Através dos links abaixo, você poderá utilizar também a mesma tabela que vou demonstrar os testes a seguir.

Criação do usuário FSS: fss_user.sql
Criação das tabelas do usuário FSS: fss_create_tables.sql

Lembrando também, que todos esses testes foram feitos em um Oracle 11.2.0.3.


Identificando o uso do Direct Read, através da view de espera v$session_wait

Vamos ao teste que interessa. Com a sessão 1, iremos executar a seguinte instrução SQL na tabela fss.hsk1. Primeiro vamos definir o parâmetro oculto _serial_direct_read para ALWAYS, dessa forma eu estou forçando com que todas as minhas consultas sejam executadas via “Direct Path Reads”

SQL> ALTER SESSION SET "_serial_direct_read"=ALWAYS;

Session altered.

SQL> select avg(length(col1) + length(col2)) from fss.hsk1 where col3 > 1;

Rapidamente, enquanto executa a consulta acima, com a sessão 2, vamos ver através da v$session_wait que o evento de espera atual é o “direct path”:

--> SESSÃO 2

SQL> SELECT 
1  s.sid, w.state, w.event, s.sql_id, s.sql_child_number, w.seq#, w.seconds_in_wait, 
2  w.p1text||'= '||w.p1 p1, w.p2text||'= '||w.p2 p2, w.p3text||'= '||w.p3 p3
3  FROM v$session s, v$session_wait w
4  WHERE w.sid = s.sid AND w.sid=152;
		
     SID STATE               EVENT                        SQL_ID          CH#      SEQ# SECONDS_IN_WAIT P1                      P2                 P3
-------- ------------------- ---------------------------- ------------- ----- --------- --------------- ----------------------- ------------------ -----------------
     152 WAITED SHORT TIME   direct path read             36b84f5s2yj4a     0     36081               0 file number= 5          first dba= 3307    block cnt= 1

1 row selected.

SQL> /

     SID STATE               EVENT                        SQL_ID          CH#      SEQ# SECONDS_IN_WAIT P1                      P2                 P3
-------- ------------------- ---------------------------- ------------- ----- --------- --------------- ----------------------- ------------------ -----------------
     152 WAITED SHORT TIME   direct path read             74kfrv5xqpbxf     0     52652               0 file number= 4          first dba= 157056  block cnt= 128

1 row selected.

SQL> /

     SID STATE               EVENT                        SQL_ID          CH#      SEQ# SECONDS_IN_WAIT P1                      P2                 P3
-------- ------------------- ---------------------------- ------------- ----- --------- --------------- ----------------------- ------------------ -----------------
     152 WAITED SHORT TIME   direct path read             74kfrv5xqpbxf     0     56786               0 file number= 4          first dba= 63360   block cnt= 128

Vamos agora voltar para a sessão 1, e mudar o parâmetro oculto “_serial_direct_read” para NEVER, e executar a mesma consulta, observe agora que não vamos mais ter o evento direct path read, mais sim o db file scattered read, ou seja a nossa consulta estará alocando todo os dados para a SGA:

--> SESSÃO 1

SQL> ALTER SESSION SET "_serial_direct_read"=NEVER;

Session altered.

SQL> select avg(length(col1) + length(col2)) from fss.hsk1 where col3 > 1;
--> SESSÃO 2

SQL> SELECT 
1  s.sid, w.state, w.event, s.sql_id, s.sql_child_number, w.seq#, w.seconds_in_wait, 
2  w.p1text||'= '||w.p1 p1, w.p2text||'= '||w.p2 p2, w.p3text||'= '||w.p3 p3
3  FROM v$session s, v$session_wait w
4  WHERE w.sid = s.sid AND w.sid=152;

     SID STATE               EVENT                     SQL_ID          CH#      SEQ# SECONDS_IN_WAIT P1          P2               P3
-------- ------------------- ------------------------- ------------- ----- --------- --------------- ----------- ---------------- --------------
     152 WAITED SHORT TIME   db file scattered read    74kfrv5xqpbxf     0     23902               0 file#= 4    block#= 37124    blocks= 128


1 row selected.

SQL> /

     SID STATE               EVENT                     SQL_ID          CH#      SEQ# SECONDS_IN_WAIT P1          P2               P3
-------- ------------------- ------------------------- ------------- ----- --------- --------------- ----------- ---------------- --------------
     152 WAITED SHORT TIME   db file scattered read    74kfrv5xqpbxf     0     26483               0 file#= 4    block#= 26500    blocks= 128


1 row selected.

SQL> /

     SID STATE               EVENT                     SQL_ID          CH#      SEQ# SECONDS_IN_WAIT P1          P2               P3
-------- ------------------- ------------------------- ------------- ----- --------- --------------- ----------- ---------------- --------------
     152 WAITED SHORT TIME   db file scattered read    74kfrv5xqpbxf     0     26977               0 file#= 4    block#= 80004    blocks= 128


Identificando o uso do Direct Read, através das views de estatísticas

Podemos também identificar o evento direct path através das views v$sesstat e v$mystat, no caso a v$sesstat representa todas as estatísticas de todas as sessões do banco, já a v$mystat representa apenas as estatísticas da minha atual sessão. Diferente da view v$session_wait que mostra o estado atual da sessão, as views de estatísticas são acumulativas para todas as estatísticas. Nesse caso, a estatística chamada “table scans (direct read)” representa a quantidade que o evento direct path foi utilizado dentre todas as instrução realizadas para a mesma sessão.

Pelo motivo das views de estatísticas v$sesstat e v$mystat serem acumulativas, precisamos realizar o antes e o depois e termos um delta para a comparação se aquela sessão sofreu ou não um aumento das estatísticas.

Podemos realizar o teste da seguinte maneira:

SQL> col value format 999999999999999
SQL> SELECT s.name, m.value
  2  FROM v$mystat m, v$statname s
  3  WHERE m.statistic# = s.statistic#
  4  AND s.name = 'table scans (direct read)';

NAME                                                          VALUE
-------------------------------------------------- ----------------
table scans (direct read)                                         0

1 row selected.

Veja acima, que a minha sessão está com a estatística “table scans (direct read)” com o valor zerado. Isso mostra que a sessão até o momento não realizou nenhuma leitura de bloco através do evento Direct Path Reads.

Vamos agora, alterar o parâmetro oculto “_SERIAL_DIRECT_READ” para NEVER, afim de forçar a leitura via FULL TABLE SCANS na tabela fss.hsk1 sem a utilização do DPR.

SQL> ALTER SESSION SET "_serial_direct_read"=NEVER;

Session altered.

SQL> select avg(length(col1)) from fss.hsk1 where col3 > 1;

AVG(LENGTH(COL1))
-----------------
       175.015666

1 row selected.

Após realizar a consulta, vamos novamente realizar a consulta para verificar o valor da estatística “table scans (direct read)”.

SQL> SELECT s.name, m.value
  2  FROM v$mystat m, v$statname s
  3  WHERE m.statistic# = s.statistic#
  4  AND s.name = 'table scans (direct read)';

NAME                                                          VALUE
-------------------------------------------------- ----------------
table scans (direct read)                                         0

1 row selected.

Veja que a estatística continua com o valor zero. Vamos executar a mesma instrução SQL, porém agora forçando o uso do Direct Reads.

SQL> ALTER SESSION SET "_serial_direct_read"=ALWAYS;

Session altered.

SQL> select avg(length(col1)) from fss.hsk1 where col3 > 1;

AVG(LENGTH(COL1))
-----------------
       175.015666

1 row selected.

Com a execução acima, voltemos a verificar o valor da estatística “table scans (direct read)”.

SQL> SELECT s.name, m.value
  2  FROM v$mystat m, v$statname s
  3  WHERE m.statistic# = s.statistic#
  4  AND s.name = 'table scans (direct read)';

NAME                                                          VALUE
-------------------------------------------------- ----------------
table scans (direct read)                                         1

Como você pode ver, a estatística passo de 0 para 1, isso aconteceu porque a instrução foi executada via Direct Path Reads. Para cada consulta então que realizo o evento de Direct Reads, o valor de 1 é adicionado a estatística “table scans (direct read)”. O mesmo procedimento é válido também para a estatística “index fast full scans (direct read)”

SQL> select avg(length(col1)) from fss.hsk1 where col3 > 1;

AVG(LENGTH(COL1))
-----------------
       175.015666

1 row selected.

Veja novamente que vamos ter a estatística “table scans (direct read)” com o valor agora de 2.

SQL> SELECT s.name, m.value
  2  FROM v$mystat m, v$statname s
  3  WHERE m.statistic# = s.statistic#
  4  AND s.name = 'table scans (direct read)';

NAME                                                          VALUE
-------------------------------------------------- ----------------
table scans (direct read)                                         2


Identificando o uso do Direct Read, através do evento de 10046

Um método muito rápido de identificar também se sua consulta está utilizando “db file scattered read” ao invés de “direct path reads” é através do evento 10046.

Setando esse evento para a sessão, e observando o trace, podemos identificar facilmente se a consulta está sendo feita via “db file scattered read” ou “direct path reads”.

Veja o exemplo abaixo:

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'

Session altered.

Com a sessão alterada para o evento 10046, vamos identificar o arquivo de trace da sessão:

SQL> SELECT tracefile 
2    FROM v$process WHERE addr = (
3        SELECT paddr FROM v$session 
4        WHERE sid = (SELECT sid FROM v$mystat WHERE rownum < 2)
5    );

TRACEFILE
----------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/dbtst/dbtst/trace/dbtst_ora_60173.trc

Com os mesmos testes realizados acima, onde forçamos a utilização do Direct Reads, seu arquivo de trace irá se parecer como a listagem abaixo para a instrução com o _serial_direct_read para ALWAYS.

WAIT #140675437128128: nam='direct path read' ela= 780 file number=6 first dba=42624 block cnt=128 obj#=76837 tim=1397656466688788
WAIT #140675437128128: nam='direct path read' ela= 824 file number=6 first dba=42752 block cnt=128 obj#=76837 tim=1397656466692249
WAIT #140675437128128: nam='direct path read' ela= 831 file number=6 first dba=42880 block cnt=128 obj#=76837 tim=1397656466696735
WAIT #140675437128128: nam='direct path read' ela= 757 file number=6 first dba=43008 block cnt=128 obj#=76837 tim=1397656466701094
WAIT #140675437128128: nam='direct path read' ela= 765 file number=6 first dba=43136 block cnt=128 obj#=76837 tim=1397656466705783
WAIT #140675437128128: nam='direct path read' ela= 574 file number=6 first dba=43268 block cnt=124 obj#=76837 tim=1397656466708691
WAIT #140675437128128: nam='direct path read' ela= 590 file number=6 first dba=43392 block cnt=128 obj#=76837 tim=1397656466711190
WAIT #140675437128128: nam='direct path read' ela= 568 file number=6 first dba=43520 block cnt=128 obj#=76837 tim=1397656466713200
WAIT #140675437128128: nam='direct path read' ela= 610 file number=6 first dba=43648 block cnt=128 obj#=76837 tim=1397656466715460
WAIT #140675437128128: nam='direct path read' ela= 562 file number=6 first dba=43776 block cnt=128 obj#=76837 tim=1397656466718398
WAIT #140675437128128: nam='direct path read' ela= 524 file number=6 first dba=43904 block cnt=128 obj#=76837 tim=1397656466720576
WAIT #140675437128128: nam='direct path read' ela= 489 file number=6 first dba=44032 block cnt=128 obj#=76837 tim=1397656466723296
WAIT #140675437128128: nam='direct path read' ela= 792 file number=6 first dba=44160 block cnt=128 obj#=76837 tim=1397656466726823
WAIT #140675437128128: nam='direct path read' ela= 726 file number=6 first dba=44292 block cnt=124 obj#=76837 tim=1397656466731733
WAIT #140675437128128: nam='direct path read' ela= 782 file number=6 first dba=44416 block cnt=128 obj#=76837 tim=1397656466736128
WAIT #140675437128128: nam='direct path read' ela= 786 file number=6 first dba=44544 block cnt=128 obj#=76837 tim=1397656466740659
WAIT #140675437128128: nam='direct path read' ela= 621 file number=6 first dba=44672 block cnt=128 obj#=76837 tim=1397656466743702
WAIT #140675437128128: nam='direct path read' ela= 808 file number=6 first dba=44800 block cnt=128 obj#=76837 tim=1397656466747454
WAIT #140675437128128: nam='direct path read' ela= 568 file number=6 first dba=44928 block cnt=128 obj#=76837 tim=1397656466751477
WAIT #140675437128128: nam='direct path read' ela= 553 file number=6 first dba=45056 block cnt=128 obj#=76837 tim=1397656466753675
WAIT #140675437128128: nam='direct path read' ela= 579 file number=6 first dba=45184 block cnt=128 obj#=76837 tim=1397656466758527
WAIT #140675437128128: nam='direct path read' ela= 610 file number=6 first dba=45316 block cnt=124 obj#=76837 tim=1397656466761760
WAIT #140675437128128: nam='direct path read' ela= 768 file number=6 first dba=45440 block cnt=128 obj#=76837 tim=1397656466765429
WAIT #140675437128128: nam='direct path read' ela= 751 file number=6 first dba=45568 block cnt=128 obj#=76837 tim=1397656466768958
WAIT #140675437128128: nam='direct path read' ela= 757 file number=6 first dba=45696 block cnt=128 obj#=76837 tim=1397656466772449
FETCH #140675437128128:c=342947,e=1194482,p=45285,cr=45289,cu=0,mis=0,r=1,dep=0,og=1,plh=3450470040,tim=1397656466776035
STAT #140675437128128 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=45289 pr=45285 pw=0 time=1194103 us)'
STAT #140675437128128 id=2 cnt=799999 pid=1 pos=1 obj=76837 op='TABLE ACCESS FULL HSK1 (cr=45289 pr=45285 pw=0 time=1363830 us cost=12370 size=145598544 card=799992)'
WAIT #140675437128128: nam='SQL*Net message from client' ela= 1640 driver id=1413697536 #bytes=1 p3=0 obj#=76837 tim=1397656466782990
FETCH #140675437128128:c=0,e=223,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3450470040,tim=1397656466785440
WAIT #140675437128128: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=76837 tim=1397656466788033

Agora a mesma execução sem a utilização do Direct Reads, deverá se parecer com o seguinte trace:

(Observe a grande quantidade de "db file scattered read")

WAIT #140675437130048: nam='db file scattered read' ela= 774 file#=6 block#=43396 blocks=128 obj#=76837 tim=1397656143526663
WAIT #140675437130048: nam='db file scattered read' ela= 476 file#=6 block#=43524 blocks=128 obj#=76837 tim=1397656143536192
WAIT #140675437130048: nam='db file scattered read' ela= 618 file#=6 block#=43652 blocks=128 obj#=76837 tim=1397656143545942
WAIT #140675437130048: nam='db file scattered read' ela= 569 file#=6 block#=43780 blocks=128 obj#=76837 tim=1397656143555649
WAIT #140675437130048: nam='db file scattered read' ela= 504 file#=6 block#=43908 blocks=128 obj#=76837 tim=1397656143564865
WAIT #140675437130048: nam='db file scattered read' ela= 817 file#=6 block#=44036 blocks=128 obj#=76837 tim=1397656143576862
WAIT #140675437130048: nam='db file scattered read' ela= 500 file#=6 block#=44164 blocks=124 obj#=76837 tim=1397656143589249
WAIT #140675437130048: nam='db file scattered read' ela= 721 file#=6 block#=44292 blocks=128 obj#=76837 tim=1397656143602144
WAIT #140675437130048: nam='db file scattered read' ela= 597 file#=6 block#=44420 blocks=128 obj#=76837 tim=1397656143612393
WAIT #140675437130048: nam='db file scattered read' ela= 710 file#=6 block#=44548 blocks=128 obj#=76837 tim=1397656143622807
WAIT #140675437130048: nam='db file scattered read' ela= 790 file#=6 block#=44676 blocks=128 obj#=76837 tim=1397656143631916
WAIT #140675437130048: nam='db file scattered read' ela= 518 file#=6 block#=44804 blocks=128 obj#=76837 tim=1397656143640901
WAIT #140675437130048: nam='db file scattered read' ela= 450 file#=6 block#=44932 blocks=128 obj#=76837 tim=1397656143649894
WAIT #140675437130048: nam='db file scattered read' ela= 998 file#=6 block#=45060 blocks=128 obj#=76837 tim=1397656143661462
WAIT #140675437130048: nam='db file scattered read' ela= 428 file#=6 block#=45188 blocks=124 obj#=76837 tim=1397656143671014
WAIT #140675437130048: nam='db file scattered read' ela= 537 file#=6 block#=45316 blocks=128 obj#=76837 tim=1397656143679979
WAIT #140675437130048: nam='db file scattered read' ela= 809 file#=6 block#=45444 blocks=128 obj#=76837 tim=1397656143705089
WAIT #140675437130048: nam='db file scattered read' ela= 743 file#=6 block#=45572 blocks=128 obj#=76837 tim=1397656143714724
WAIT #140675437130048: nam='db file scattered read' ela= 742 file#=6 block#=45700 blocks=124 obj#=76837 tim=1397656143752173
FETCH #140675437130048:c=13221989,e=13812881,p=45286,cr=45298,cu=0,mis=0,r=1,dep=0,og=1,plh=3450470040,tim=1397656143762546
STAT #140675437130048 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=45298 pr=45286 pw=0 time=13812802 us)'
STAT #140675437130048 id=2 cnt=799999 pid=1 pos=1 obj=76837 op='TABLE ACCESS FULL HSK1 (cr=45298 pr=45286 pw=0 time=7547357 us cost=12370 size=145598544 card=799992)'
WAIT #140675437130048: nam='SQL*Net message from client' ela= 1056 driver id=1413697536 #bytes=1 p3=0 obj#=76837 tim=1397656143767562
FETCH #140675437130048:c=0,e=566,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3450470040,tim=1397656143768778
WAIT #140675437130048: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=76837 tim=1397656143770042


Identificando o uso do Direct Read, via Oracle Internals (apenas para Oracle Geek guys : )

O evento Direct Path Reads é realizado através da função do sistema operacional chamada kcbldrget, que significa Kernel Block Direct Read Get.

Para identificar o uso do Direct Path Reads, basta identificar se a função kcbldrget foi disparada do processo do sistema operacional. Isso é possível através do comando pstack do Linux. Com esse comando, podemos identificar stack trace (pila do trace) da execução do processo, ou seja, todo os caminhos via chamadas de SO que o processo passou.

Através do SPID, podemos executar o seguinte procedimento após a execução do mesmo SQL com o parâmetro oculto "_serial_direct_read" para ALWAYS, forçando assim a execução via DPR. No momento da execução, conectado no sistema operacional (no meu caso o Linux), realizamos o comando pstack apontando para o SPID da SESSÃO B, que no caso é o número 50834.

[root@oralnx001 ~]# pstack 50834
#0  0x0000003f1960ee33 in __pread_nocancel () from /lib64/libpthread.so.0
#1  0x00000000093521fb in skgfqio ()
#2  0x0000000009222e03 in ksfd_skgfqio ()
#3  0x0000000009222b68 in ksfdgo ()
#4  0x000000000234e30e in ksfdaio ()
#5  0x00000000021ef424 in kcflbi ()
#6  0x0000000000ebc90a in kcbldio ()
#7  0x0000000000ebba84 in kcblrs ()
#8  0x0000000000ebb165 in kcblgt ()
#9  0x0000000000eb9941 in kcbldrget ()
#10 0x000000000907b554 in kcbgtcr ()
#11 0x000000000905ff29 in ktrget3 ()
#12 0x000000000905f784 in ktrget2 ()
#13 0x0000000009016ead in kdst_fetch ()
#14 0x0000000000c87f89 in kdstf00000010000kmP ()
#15 0x0000000008ffc6e8 in kdsttgr ()
#16 0x0000000009245970 in qertbFetch ()
#17 0x000000000926cc1f in qergsFetch ()
#18 0x0000000009136e83 in opifch2 ()
#19 0x00000000091404e8 in opiefn0 ()
#20 0x000000000914dfc4 in opipls ()
#21 0x000000000913d4d4 in opiodr ()
#22 0x00000000091e7043 in rpidrus ()
#23 0x0000000009354764 in skgmstack ()
#24 0x00000000091e8b5e in rpiswu2 ()
#25 0x00000000091e8188 in rpidrv ()
#26 0x00000000091d14d1 in psddr0 ()
#27 0x00000000091d10e7 in psdnal ()
#28 0x0000000003736b52 in pevm_EXIM ()
#29 0x000000000372831b in pfrinstr_EXIM ()
#30 0x00000000093eae35 in pfrrun_no_tool ()
#31 0x00000000093e9509 in pfrrun ()
#32 0x00000000093f0b61 in plsql_run ()
#33 0x000000000371cb6b in peicnt ()
#34 0x0000000002fa18b1 in kkxexe ()
#35 0x00000000091450f9 in opiexe ()
#36 0x0000000001b5cb07 in kpoal8 ()
#37 0x000000000913d4d4 in opiodr ()
#38 0x00000000092e02d6 in ttcpip ()
#39 0x00000000017ece01 in opitsk ()
#40 0x00000000017f19fa in opiino ()
#41 0x000000000913d4d4 in opiodr ()
#42 0x00000000017e8d3c in opidrv ()
#43 0x0000000001de40cb in sou2o ()
#44 0x0000000000a0b0c1 in opimai_real ()
#45 0x0000000001dea03c in ssthrdmain ()
#46 0x0000000000a0b02d in main ()

Esse monte de código acima, representa cada chamada de sistema operacional feita pelo processo em execução do SPID 50834. Como você pode ver na linha em negrito, temos uma chamada para o sistema operacional para a função kcbldrget, que significa que a instrução executada por esse processo utilizou o método Direct Reads :)


Condições para se utilizar o Direct Path Reads

Como mencionei acima, a partir do Oracle 11g é muito mais provável que sua consulta utilize o direct path reads, porém existe maneiras de "tentar" identificar se sua consulta irá ou não, utilizar automaticamente o evento.

Além dos processos parallel slaves que são sempre executados via direct path reads, para instruções não paralelas elas funcionam em certas condições. O calculo é baseado em diversos fatores como a quantidade de blocos do objeto e o tamanho do buffer. Existe ainda o parâmetro oculto _SMALL_TABLE_THRESHOLD que determina a quantidade mínima de blocos que uma tabela deve ter para a utilização do DPR. O valor default desse parâmetro é 2680, o que significa que caso uma tabela tenha a quantidade de blocos maior que 2680, o seu full table scan será mais favorável a utilizar direct path reads.

Veja um exemplo:

SQL> show parameter _small_table_threshold

NAME                           VALUE
------------------------------ -------
_small_table_threshold         2680

Acima como se pode ver, o parâmetro oculto "_SMALL_TABLE_THRESHOLD" está definido para o valor default de 2680 blocos. Vou agora, criar uma tabela buscando todos os dados da view dba_objects.

SQL> create table t as select * from dba_objects;

Table created.

SQL> select blocks from dba_segments where segment_name='T';

    BLOCKS
----------
      1152

Observe que a nossa tabela ficou com 1152 blocos, bem abaixo do valor do parâmetro _small_table_threshold. Vamos executar um FULL TABLE SCAN de encontro a tabela e verificar se foi realizado automaticamente o direct path reads de encontro a tabela.

SQL> select s.name, m.value 
2    from v$statname s, v$mystat m 
3    where m.statistic#=s.statistic# 
4    and s.name = 'table scans (direct read)';

NAME                                                    VALUE
-------------------------------------------------- ----------
table scans (direct read)                                   0

1 rows selected.

SQL> select count(*) from t;

  COUNT(*)
----------
     75214

1 row selected.

SQL> select 
2    s.name, m.value 
3    from v$statname s, v$mystat m 
4    where m.statistic#=s.statistic# 
5    and s.name = 'table scans (direct read)';

NAME                                                    VALUE
-------------------------------------------------- ----------
table scans (direct read)                                   0

1 rows selected.

Veja acima, que a estatística table scans (direct read) não foi alterado. Vamos aumentar a quantidade de blocos da tabela e realizar o mesmo teste.

SQL> insert into t (select * from t);

75214 rows created.

SQL> insert into t (select * from t);

150428 rows created.

SQL> commit;

Commit complete.

SQL> select blocks from dba_segments where segment_name='T';

    BLOCKS
----------
      4352

1 row selected.

Agora sim temos blocos acima da quantidade necessária especificada pelo parâmetro _small_table_threshold. Antes de fazer o teste, vamos limpar a área de memória do Oracle para que o teste anterior não interfira nesse novo.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

Feito a limpeza, vamos checar novamente a mesma consulta:

SQL> select count(*) from t;

  COUNT(*)
----------
    300856

1 row selected.

SQL> select 
2      s.name, m.value 
3      from v$statname s, v$mystat m 
4      where m.statistic#=s.statistic# 
5      and s.name = 'table scans (direct read)';

NAME                                                    VALUE
-------------------------------------------------- ----------
table scans (direct read)                                   1

1 row selected.

Agora sim a nossa consulta foi realizada via direct read.

Um outra ponto muito importante para o direct reads, é que ele apenas funciona quando um full scan acontece, ou seja, a função "direct path reads" (kcbldrget) somente é chamada após um full scans. Note que o termo full scans representa os termos TABLE ACCESS FULL e INDEX FAST FULL SCAN no seu plano de execução. Dessa forma, pelo simples fato de uma operação em sua consulta a uma tabela for do tipo UNIQUE SCAN, o Direct Path Reads irá acontecer.

Processos Parallel e o Direct Path Reads

Os processos paralelos são outro ponto importante, como comentei acima, não importa que tipo de processo parallel você tem utilizado (AUTO DOP, IN MEMORY PARALELL, QUEUEING PARALLEL, etc ..) ou o tipo de Degree, a sua consulta sempre que utilizar a operação FULL SCANS ela irá ser feita via Direct Path Reads.

Veja no exemplo abaixo, que mesmo definindo o parâmetro "_serial_direct_read" para "never" vamos ter nossa consulta paralela utilizando o direct reads:

SQL> ALTER SESSION SET "_serial_direct_read"=never;

Session altered.

SQL> select 
2    s.name, m.value 
3    from v$statname s, v$mystat m 
4    where m.statistic#=s.statistic#  
5    and s.name = 'table scans (direct read)';

NAME                                                    VALUE
-------------------------------------------------- ----------
table scans (direct read)                          0

1 row selected.

SQL> select count(*) from t;

  COUNT(*)
----------
    300856


1 row selected.

SQL> select 
2    s.name, m.value 
3    from v$statname s, v$mystat m 
4    where m.statistic#=s.statistic#  
5    and s.name = 'table scans (direct read)';

NAME                                                  VALUE
-------------------------------------------------- --------
table scans (direct read)                                 0

1 row selected.

SQL> alter table t parallel 2;

Table altered.

SQL> select count(*) from t;

  COUNT(*)
----------
    300856

1 row selected.

SQL> select 
2    s.name, m.value 
3    from v$statname s, v$mystat m 
4    where m.statistic#=s.statistic#  
5    and s.name = 'table scans (direct read)';

NAME                                                  VALUE
-------------------------------------------------- --------
table scans (direct read)                                26


1 row selected.

Ou veja, processos parallel ignoram completamente o parâmetro _serial_direct_read.


Um pouco sobre o parâmetro _serial_direct_read

Você já deve ter entendido bem a utilização do parâmetro oculto _serial_direct_read. Ele força ou ignora a utilização do "Direct Path Reads". Vale a pena lembrar que por se tratar de um parâmetro oculto, não é nem um pouco legal você definir em seu ambiente de produção sem antes consultar a Oracle.

O parâmetro _serial_direct_read ele possuiu esses valores a partir do Oracle 11g:

1 ALWAYS
2 AUTO
3 NEVER
4 TRUE
5 FALSE

Para cada um deles ele trata de um modo diferente a instrução via "Direct Path Reads". O modo default dele é AUTO, o que significa que ele será automático, seguindo toda aquela regra que já expliquei sobre a quantidade e blocos e vários outros fatores.

Através do suporte da Oracle do documento "Exadata Smartscan Is Not Being Used On Insert As Select (Doc ID 1348116.1)", a Oracle descreve um problema em que o Smartscan não acontece devida a não execução da instrução via "Direct Path Reads" (um dos caso mais comum de falta de performance do Exadata). Na nota ele informa o uso do parâmetro _serial_direct_read para TRUE como a resolução do problema.



....




Por hoje é isso, espero que tenha consigo demonstrar a importante do Direct Path Reads para o Oracle. Para os próximos posts, quero mostrar um pouco mais da utilização do Direct Path Reads junto com as features do Exadata (Storage Index, Predicate Filtering, Column Projection, etc ...). Um abraço e até lá :)

Protegendo o Oracle – Parte 2
janeiro 12, 2013

Parte 01 – 07_DICTIONARY_ACCESSIBILITY

Encontrando os password default

Não é novidade nenhuma que existem várias contas de usuários padrão no banco de dados Oracle, alguns deles até são criados com privilégios administrativos.

Usuários padrão são criados no momento da criação do banco e claro,  são registrados sem uma senha e já vem bloqueado por default, exceto para SYS e SYSTEM que são contas administrativas que quando criadas através do CREATE DATABASE, se não definidas as senhas, elas devem ficar:

SYS : CHANGE_ON_INSTALL)
SYSTEM : MANAGER

Se você acha que os usuários default do Oracle é uma lista pequena, está enganado, aqui é alguns dos usuários padrão que são instalado sempre que um banco ou uma feature do banco é instalada. Por exemplo, o usuário XDB somente é instalado quando o Oracle XMLDB é instalado:

SYS
SYSTEM
SYSMAN
OUTLN
TSMSYS
WKSYS
SCOTT
ADAMS
JONES
WKPROXY
OLAPSYS
OWBSYS
CLARK
BLAKE
HR
OE
SH
DEMO
ANONYMOUS
CSMIG
CTXSYS
DBSNMP
DIP
DMSYS
DSSYS
EXFSYS
LBACSYS
MDSYS
ORACLE_OCM
ORDPLUGINS
ORDSYS
PERFSTAT
XDB
MGMT_VIEW
SI_INFORMTN_SCHEMA

Porém para alguns deles (quase todos), existe uma senha padrão definida, como o famoso usuário SCOTT que possuí a senha padrão definida como TIGER.

Muitos ambientes Oracle tem o usuário SCOTT criado (e provavelmente até com a senha padrão definida), o que deixa o banco de dados muito vulnerável, imagina que com uma senha padrão definida, qualquer um a qualquer momento pode acessar o seu banco, somente acessando essa conta.

Ok, mais como me livro disto? Qual é a maneira de descobrir se os usuários na lista acima está com a senha padrão habilitada?

Somente a view DBA_USERS_WITH_DEFPWD pode te responder isso …

Logado no SQL*Plus com uma conta administrativa, execute a seguinte consulta de encontra a view DBA_USERS_WITH_DEFPWD

SQL> SELECT * FROM DBA_USERS_WITH_DEFPWD;

USERNAME
------------------------------
DIP
OUTLN
ORACLE_OCM
APPQOSSYS

SQL> select username, account_status from dba_users where username in (select username from dba_users_with_defpwd);

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
APPQOSSYS                      EXPIRED & LOCKED
ORACLE_OCM                     EXPIRED & LOCKED
DIP                            EXPIRED & LOCKED
OUTLN                          OPEN

A primeira consulta, mostra que temos a lista completa dos usuários em que a senha padrão está definida. Na segunda consulta, é informado que apenas o usuário OUTLN que tem uma senha padrão está com o status da conta habilitado.

Mesma com uma conta bloqueada, deixar o usuário com uma senha padrão pode ser muito perigoso, imagina se alguém habilita uma dessas contas sem querer e não troca a senha?

Para mudar a senha não é segredo:

SQL> PASSWORD OUTLN
Changing password for OUTLN
New password: *********
Retype new password: ************
Password changed

São maneiras simples como essa que podem te livrar de uma dor de cabeça incrível. O DBA devem estar atento a tudo, inclusive a pequenos detalhes como este. Nem quero pensar que uma conta dessas caia em mãos erradas, imagina que uma conta dessas com privilégio administrativo, o de create/drop tablespaces por exemplo, esteja com a conta habilitada e password default e algum abelhudo consegue conectar com essa conta, já penso o estrago? …

É! … você pode dar adeus ao seu banco e rezar para ter backup …

Performance Tuning Utilities
janeiro 9, 2013

Já ouviram falar de OSWatcher? LTOM? SQLTXPLAIN e OPDG?

Bom, se vocês nunca ouviram  falar dessas ferramentas chegou a hora de conhece-las.

A nota do metalink 438452.1 mostra essas e diversas outras ferramentas sobre performance tuning.

Bom apetite, aproveitem :)

Tudo no ar novamente!
janeiro 4, 2013

O último post tinha avisado vocês sobre o problema da hospedagem!

Enfim, os links com as imagens quebradas, os links para os scripts e algumas outras coisas foram tudo restaurado!

E claro, os e-mails também voltaram: dba@flaviosoares.com

Um abraço e feliz 2013!

Próxima Página »