Oracle优化

Oracle优化器相关参数设置

时间:2013-7-2 21:49:10  作者:solgle  来源:www.solgle.com  查看:395  评论:0
内容摘要:oracle优化器相关参数设置Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Applicat...
oracle优化器相关参数设置
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data Mining and Real Application Testing options
 
SQL> show parameter optimizer
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.2.0.4
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                               string      FIRST_ROWS_1000
optimizer_secure_view_merging      boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL>
 
SQL> col event for a30
SQL> select event,average_wait from v$system_event where event like '%db file%';
 
EVENT                          AVERAGE_WAIT
------------------------------ ------------
db file sequential read                 1.5
db file scattered read                 6.06
db file single write                    .89
db file parallel write                  .55
db file async I/O submit            0
db file parallel read                  8.86
 
6 rows selected.
SQL>
 
SQL> select 1.5/6.06 from dual;
 
  1.5/6.06
----------
.247524752
 
---接近25% (全表扫描代价与执行单块的相对比例,默认值100)
SQL> alter system set optimizer_index_cost_adj=25 scope=both;
 
System altered.
SQL>
 
---索引在SGA被缓存化的百分比
SQL> alter system set optimizer_index_caching=40 scope=both;
 
System altered.
SQL>
 
---------------------------------------------
select event,average_wait from v$system_event where event like '%db file%';
 
--------------------------------------------------
1:高水位的回收
    alter table my_solgle enable row movement;
    alter table my_solgle shrink space;
2:数据密度问题
alter tablespace TBS_INDEX_DATA_1 coalesce;
 
 
 
-----查询计划查看
SQL> set autotrace traceonly;
SQL> select count(1) from my_solgle;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 75121460
 
------------------------------------------------------------------------------------------------------
 
| Id  | Operation                 | Name             | Rows  | Cost (%CPU)| Time
 
     | Pstart| Pstop |
 
------------------------------------------------------------------------------------------------------
 
|   0 | SELECT STATEMENT          |                  |     1 |   120   (0)| 00:00:02 |       |       |
 
|   1 |  SORT AGGREGATE           |                  |     1 |            ||       |       |
 
|   2 |   PARTITION RANGE ALL     |                  |  4647K|   120   (0)| 00:00:02 |     1 |1048575|
 
|   3 |    BITMAP CONVERSION COUNT|                  |  4647K|   120   (0)| 00:00:02 |       |       |
 
|   4 |     BITMAP INDEX FULL SCAN| NIDX_OSA_IS_FJMM |       |            |     |     1 |1048575|
 
------------------------------------------------------------------------------------------------------
 
Statistics
----------------------------------------------------------
        605    recursive calls    --内部递归调用次数
          0      db block gets        --请求的缓存数据块数
       2265   consistent gets  --当前查询读的缓存块数
        471    physical reads    --磁盘读取数据块数 
          0      redo size                --生成日志大小
        349    bytes sent via SQL*Net to client        
        363    bytes received via SQL*Net from client
          2      SQL*Net roundtrips to/from client   --SQL*Net通信收发次数统计
         25     sorts (memory)                       --内存中排序大小
          0      sorts (disk)                        ---磁盘中排序大小
          1      rows processed                                        
 
标签:Oracle优化器相关参数设置 

solgle.com 版权所有,欢迎分享!!!

相关文章
    相关评论
       Copyright © 2013-2020 solgle.com,All rights reserved.[solgle.com] 公安机关备案号:51010802000219
    Email:solgle@solgle.com; weixin:cd1008610000 ICP:蜀ICP备14011070号-1