Oracle优化器相关参数设置

加入收藏设为首页

Oracle优化: Oracle优化器相关参数设置
时间：2013/7/2 21:49:10 作者：solgle 来源：www.solgle.com 查看：731 评论：0
内容摘要：oracle优化器相关参数设置Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Applicat...
oracle优化器相关参数设置

本文出自：http://www.solgle.com/news/?123.html

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 版权所有，欢迎分享！！！

上一篇：使用rownum进行分页的探讨
下一篇：RAC业务分区小计

相关文章

相关评论

本类更新

本类推荐

本类排行

: 国家数据 | 中证指数 | Oracle官方文档 | 沪深行情 | 流量统计 | 深交所 | 上交所 | 中国证券登记 | 同业拆放利率 | 巨潮资讯 | 指数估值 | 看财报 |

联系我们 - 关于我们 - 网站同盟