sqlplus中影响sql查询的几个参数设置

今天为了解决一个大量的链表查询，简单来这么形容这条 sql
语句：
select 　count(*) 
from a ,b
where a.remi_id=b.remi_id and b.load_dt='20121201' and load_flag='0'
条件：
    1、 a表为每天70万数据，按日分区，一共保留4年数据；
    2、b表每日65万数据，按日分区；
    3、a表和b表remi_id字段均存在唯一索引；
    4、a表remi_id 字段和a表中日期没有必然关系，即a表的remi_id随机分布在a表中的每个分区中；

执行计划均以 a.remi=b.remi_id为access，其余条件均为filter
理想状态下我们是想实现b表作为驱动表，但是在sqlplus中反复调整均未达到理想状态。
在这个分析过程中我们测试了不同的方法固定驱动表，
方法有 :
     A  跟表连接有关的几个HINT
        （1）use_nl（t1,t2）：表示对表t1、t2关联时采用嵌套循环连接。
        （2）use_merge（t1,t2）：表示对表t1、t2关联时采用排序合并连接。
        （3）use_hash（t1,t2）：表示对表t1、t2关联时采用哈希连接。
        （4）leading(t)：表示在进行表连接时，选择t为驱动表。
        （5）ordred：要求优化器按from列出的表顺序进行连接。
    B 使用了并行parallel
但是我们均未达到理想状态。所以我们怀疑是我们疏忽了什么条件。

反过来我们重新查看了执行计划，重新分析业务，因为在本次业务中涉及两个大表的关联查询，我们首先会想到使用并行，其次是要调整sql达到优化标准，关联字段尽可能有索引字段，再次是尽量选择有效的驱动表。这三个均没有什么问题。
       我们发现，在实际的操作过程中，我们少了两项关键的设置：session的并行条件、session级别的排序。
       设置相关参数：
       alter session set sort_area_size=204800000   (200M,默认为64M)
       alter session enable parallel dml;
      再次执行，时间为12秒，最终这个结果还可以接受。
这次记录下来是为了对自己的一个提醒，其实这些参数是我们平常也用到的，在关键时候，每当我们拿到一个业务查询时，首先就是想到了编写语句，想到的是逻辑的正确，往往忽略了可以加快语句执行效果的方法。即使想到了很多方法，也会不经意的忽略掉一些细节，而往往这些细节却是最关键的，这里记录下来以鞭策自己不可再犯。
-------------------------------------------------------------------------------------------------------------
下面将别人记录的一些session级别常用的参数记录下来，作为附录
这个列表回答了一个问题：哪些参数是可以在session级别进行修改的，哪些参数必须在system级别进行修改。
session级别修改的含义是，可以使用SQL语句alter session set Oracle_Parameter='***'进行修改。
system级别修改的含义是，需要使用SQL语句alter system set Oracle_Parameter='***'进行修改。

统计列表如下：

NAME                          SES_MOD SYS_MOD  NAME                              SES_MOD SYS_MOD
O7_DICTIONARY_ACCESSIBILITY   NO      NO       log_archive_dest_state_3          YES     YES
active_instance_count         NO      NO       log_archive_dest_state_4          YES     YES  
aq_tm_processes               NO      YES      log_archive_dest_state_5          YES     YES  
archive_lag_target            NO      YES      log_archive_dest_state_6          YES     YES  
asm_diskgroups                NO      YES      log_archive_dest_state_7          YES     YES  
asm_diskstring                NO      YES      log_archive_dest_state_8          YES     YES  
asm_power_limit               YES     YES      log_archive_dest_state_9          YES     YES  
audit_file_dest               NO      NO       log_archive_duplex_dest           NO      YES  
audit_sys_operations          NO      NO       log_archive_format                NO      NO   
audit_trail                   NO      NO       log_archive_local_first           NO      YES  
background_core_dump          NO      NO       log_archive_max_processes         NO      YES  
background_dump_dest          NO      YES      log_archive_min_succeed_dest      YES     YES  
backup_tape_io_slaves         NO      NO       log_archive_start                 NO      NO   
bitmap_merge_area_size        NO      NO       log_archive_trace                 NO      YES  
blank_trimming                NO      NO       log_buffer                        NO      NO   
buffer_pool_keep              NO      NO       log_checkpoint_interval           NO      YES  
buffer_pool_recycle           NO      NO       log_checkpoint_timeout            NO      YES  
circuits                      NO      YES      log_checkpoints_to_alert          NO      YES  
cluster_database              NO      NO       log_file_name_convert             NO      NO   
cluster_database_instances    NO      NO       logmnr_max_persistent_sessions    NO      NO   
cluster_interconnects         NO      NO       max_commit_propagation_delay      NO      NO   
commit_point_strength         NO      NO       max_dispatchers                   NO      YES  
commit_write                  YES     YES      max_dump_file_size                YES     YES  
compatible                    NO      NO       max_enabled_roles                 NO      NO   
control_file_record_keep_time NO      YES      max_shared_servers                NO      YES  
control_files                 NO      NO       object_cache_max_size_percent     YES     NO   
core_dump_dest                NO      YES      object_cache_optimal_size         YES     NO   
cpu_count                     NO      YES      olap_page_pool_size               YES     NO   
create_bitmap_area_size       NO      NO       open_cursors                      NO      YES  
create_stored_outlines        YES     YES      open_links                        NO      NO   
cursor_sharing                YES     YES      open_links_per_instance           NO      NO   
cursor_space_for_time         NO      NO       optimizer_dynamic_sampling        YES     YES  
db_16k_cache_size             NO      YES      optimizer_features_enable         YES     YES  
db_2k_cache_size              NO      YES      optimizer_index_caching           YES     YES  
db_32k_cache_size             NO      YES      optimizer_index_cost_adj          YES     YES  
db_4k_cache_size              NO      YES      optimizer_mode                    YES     YES  
db_8k_cache_size              NO      YES      optimizer_secure_view_merging     NO      YES  
db_block_buffers              NO      NO       os_authent_prefix                 NO      NO   
db_block_checking             YES     YES      os_roles                          NO      NO   
db_block_checksum             NO      YES      parallel_adaptive_multi_user      NO      YES  
db_block_size                 NO      NO       parallel_automatic_tuning         NO      NO   
db_cache_advice               NO      YES      parallel_execution_message_size   NO      NO   
db_cache_size                 NO      YES      parallel_instance_group           YES     YES  
db_create_file_dest           YES     YES      parallel_max_servers              NO      YES  
db_create_online_log_dest_1   YES     YES      parallel_min_percent              YES     NO   
db_create_online_log_dest_2   YES     YES      parallel_min_servers              NO      YES  
db_create_online_log_dest_3   YES     YES      parallel_server                   NO      NO   
db_create_online_log_dest_4   YES     YES      parallel_server_instances         NO      NO   
db_create_online_log_dest_5   YES     YES      parallel_threads_per_cpu          NO      YES  
db_domain                     NO      NO       pga_aggregate_target              NO      YES  
db_file_multiblock_read_count YES     YES      plsql_ccflags                     YES     YES  
db_file_name_convert          YES     NO       plsql_code_type                   YES     YES  
db_files                      NO      NO       plsql_compiler_flags              YES     YES  
db_flashback_retention_target NO      YES      plsql_debug                       YES     YES  
db_keep_cache_size            NO      YES      plsql_native_library_dir          NO      YES  
db_name                       NO      NO       plsql_native_library_subdir_count NO      YES  
db_recovery_file_dest         NO      YES      plsql_optimize_level              YES     YES  
db_recovery_file_dest_size    NO      YES      plsql_v2_compatibility            YES     YES  
db_recycle_cache_size         NO      YES      plsql_warnings                    YES     YES  
db_unique_name                NO      NO       pre_page_sga                      NO      NO   
db_writer_processes           NO      NO       processes                         NO      NO   
dbwr_io_slaves                NO      NO       query_rewrite_enabled             YES     YES  
ddl_wait_for_locks            YES     YES      query_rewrite_integrity           YES     YES  
dg_broker_config_file1        NO      YES      rdbms_server_dn                   NO      NO   
dg_broker_config_file2        NO      YES      read_only_open_delayed            NO      NO   
dg_broker_start               NO      YES      recovery_parallelism              NO      NO   
disk_asynch_io                NO      NO       recyclebin                        YES     YES  
dispatchers                   NO      YES      remote_archive_enable             NO      NO   
distributed_lock_timeout      NO      NO       remote_dependencies_mode          YES     YES  
dml_locks                     NO      NO       remote_listener                   NO      YES  
drs_start                     NO      YES      remote_login_passwordfile         NO      NO   
event                         NO      NO       remote_os_authent                 NO      NO   
fal_client                    NO      YES      remote_os_roles                   NO      NO   
fal_server                    NO      YES      replication_dependency_tracking   NO      NO   
fast_start_io_target          NO      YES      resource_limit                    NO      YES  
fast_start_mttr_target        NO      YES      resource_manager_plan             NO      YES  
fast_start_parallel_rollback  NO      YES      resumable_timeout                 YES     YES  
file_mapping                  NO      YES      rollback_segments                 NO      NO   
fileio_network_adapters       NO      NO       serial_reuse                      NO      NO   
filesystemio_options          NO      NO       service_names                     NO      YES  
fixed_date                    NO      YES      session_cached_cursors            YES     NO   
gc_files_to_locks             NO      NO       session_max_open_files            NO      NO   
gcs_server_processes          NO      NO       sessions                          NO      NO   
global_context_pool_size      NO      NO       sga_max_size                      NO      NO   
global_names                  YES     YES      sga_target                        NO      YES  
hash_area_size                YES     NO       shadow_core_dump                  NO      NO   
hi_shared_memory_address      NO      NO       shared_memory_address             NO      NO   
hs_autoregister               NO      YES      shared_pool_reserved_size         NO      NO   
ifile                         NO      NO       shared_pool_size                  NO      YES  
instance_groups               NO      NO       shared_server_sessions            NO      YES  
instance_name                 NO      NO       shared_servers                    NO      YES  
instance_number               NO      NO       skip_unusable_indexes             YES     YES  
instance_type                 NO      NO       smtp_out_server                   YES     YES  
java_max_sessionspace_size    NO      NO       sort_area_retained_size           YES     NO   
java_pool_size                NO      YES      sort_area_size                    YES     NO   
java_soft_sessionspace_limit  NO      NO       spfile                            NO      NO   
job_queue_processes           NO      YES      sql92_security                    NO      NO   
large_pool_size               NO      YES      sql_trace                         YES     YES  
ldap_directory_access         NO      YES      sql_version                       YES     NO   
license_max_sessions          NO      YES      sqltune_category                  YES     YES  
license_max_users             NO      YES      standby_archive_dest              NO      YES  
license_sessions_warning      NO      YES      standby_file_management           NO      YES  
local_listener                NO      YES      star_transformation_enabled       YES     YES  
lock_name_space               NO      NO       statistics_level                  YES     YES  
lock_sga                      NO      NO       streams_pool_size                 NO      YES  
log_archive_config            NO      YES      tape_asynch_io                    NO      NO   
log_archive_dest              NO      YES      thread                            NO      YES  
log_archive_dest_1            YES     YES      timed_os_statistics               YES     YES  
log_archive_dest_10           YES     YES      timed_statistics                  YES     YES  
log_archive_dest_2            YES     YES      trace_enabled                     NO      YES  
log_archive_dest_3            YES     YES      tracefile_identifier              YES     NO   
log_archive_dest_4            YES     YES      transactions                      NO      NO   
log_archive_dest_5            YES     YES      transactions_per_rollback_segment NO      NO   
log_archive_dest_6            YES     YES      undo_management                   NO      NO   
log_archive_dest_7            YES     YES      undo_retention                    NO      YES  
log_archive_dest_8            YES     YES      undo_tablespace                   NO      YES  
log_archive_dest_9            YES     YES      use_indirect_data_buffers         NO      NO   
log_archive_dest_state_1      YES     YES      user_dump_dest                    NO      YES  
log_archive_dest_state_10     YES     YES      utl_file_dir                      NO      NO   
log_archive_dest_state_2      YES     YES      workarea_size_policy              YES     YES 

The following statement disables parallel DDL operations:

ALTER SESSION DISABLE PARALLEL DDL;
ALTER SESSION ENABLE PARALLEL DML;

Forcing Parallel SQL Execution

ALTER SESSION FORCE PARALLEL DDL PARALLEL 5;
参考
http://docs.oracle.com/cd/B28359_01/server.111/b28310/manproc006.htm#i1006798

 ----------------------------------------------------------------------------------------------

 增加hint,强制某个表为驱动表参考：

对于上面提到的情况，如果用户一定要求根据客户类型来查找联系业务员姓名，经分

析根据客户类型获得的数据结果集，可能比staff_info的业务员数据量要大得多，即使全表扫描staff_info表，效率也比先获得clinet_info的conta_agent,再查询联系业务员姓名要好的话，我们可以采用如下提示固定驱动表：

Select /*+ leading(b) full(b) use_hash(b,a)*/ emp_name from client_info a,staff_info b

Where a.client_type=:b1

And b.empno=a.conta_agent;

 

注意：分析思路如此，并不代表一定和实际的运行状况相同，SQL写好以后，最好看看执行计划，并带入实际的数据值进行测试验证。此外，示例仅为了说明问题，抛砖引玉，不一定与符合实际。