- 浏览: 960197 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
孤星119:
好熟悉的数据库字段啊, 上家公司做的项目每天都跟这些字段打招呼 ...
Oracle exp compress参数引起的空间浪费 -
itspace:
quxiaoyong 写道遇到个问题,网上一搜,全他妈这篇文章 ...
数据库连接错误ORA-28547 -
quxiaoyong:
遇到个问题,网上一搜,全他妈这篇文章。你转来转去的有意思吗?
数据库连接错误ORA-28547 -
hctech:
关于version count过高的问题,不知博主是否看过ey ...
某客户数据库性能诊断报告 -
itspace:
invalid 写道写的不错,我根据这个来安装,有点理解错误了 ...
AIX 配置vncserver
我们知道影响Oracle cbo优化器做出正确的执行计划影响因素有很多,如CPU,I/O cost等。在Oracle的执行计划中也将CPU,IO,TEMP的COST列入其中。通过下图就可以清晰的看到影响CBO的各种因素。
为获得CBO的一些统计值,Oracle 9i需要手工执行dbms_stat.gather_system_stats,在Oracle 10g中,采取默认收集。
从Oracle 9i开始我们可以通过隐含参数_optimizer_cost_model来设置CBO选择CPU COST优先还是IO优先。
设置CBO CPU COST方法如下:
如果是Oracle 9i设置参数之前还需手动统计系统信息dbms_stat.gather_system_stats。
关闭CBO CPU COST方法如下:
1、在SQL中加 "no_cpu_costing" hint
2、alter session set "_optimizer_cost_model"=io;
3、在 init.ora 中加参数_optimizer_cost_model=io
在这里需要注意optimizer_index_cost_adj应设为默认值 (Oracle9i bug 2820066)
通过以上基本知识,我们再通过一个详细的案例来进一步说明_optimizer_cost_model参数的作用。
测试版本为
创建测试表格,并分析测试表格
查看其执行计划,发现走的是NESTED LOOPS
如果我们启用CPU COST,就走HASH JOIN了,这主要的原因是Oracle 10g以前,CBO优化器的缺省是IO COST优先的因此在内表很小,并且可以通过索引访问,外表较大情况下,优先选择NESTED LOOP。
再进一步同样的例子在Oracle 10g中测试
可以发现Oracle 10g已经默认启用 cpu cost。
我们可以在进一步探讨CPU因素对COST的影响。
SQL> select * from aux_stats$;
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
--------------------------------------------------------------------------------
SYSSTATS_INFO STATUS
COMPLETED
SYSSTATS_INFO DSTART
05-30-2009 03:11
SYSSTATS_INFO DSTOP
05-30-2009 03:11
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
--------------------------------------------------------------------------------
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1104.475
SYSSTATS_MAIN IOSEEKTIM 12.006
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
--------------------------------------------------------------------------------
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
--------------------------------------------------------------------------------
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
--------------------------------------------------------------------------------
SYSSTATS_MAIN SLAVETHR
13 rows selected.
附各字段意思,在这里我们使用的是No Workload
加大cpuspeednw速度,观察执行计划,可以看到执行计划走nested loop,CPU的影响可见一斑啊
再一进步我们可以通过10053事件获得跟踪
跟踪文件显示:
为获得CBO的一些统计值,Oracle 9i需要手工执行dbms_stat.gather_system_stats,在Oracle 10g中,采取默认收集。
从Oracle 9i开始我们可以通过隐含参数_optimizer_cost_model来设置CBO选择CPU COST优先还是IO优先。
引用
alter session set "_optimizer_cost_model"=choose; -- default value
alter session set "_optimizer_cost_model"=io;
alter session set "_optimizer_cost_model"=cpu;
alter session set "_optimizer_cost_model"=io;
alter session set "_optimizer_cost_model"=cpu;
设置CBO CPU COST方法如下:
引用
alter session set "_optimizer_cost_model"=cpu;
如果是Oracle 9i设置参数之前还需手动统计系统信息dbms_stat.gather_system_stats。
关闭CBO CPU COST方法如下:
1、在SQL中加 "no_cpu_costing" hint
2、alter session set "_optimizer_cost_model"=io;
3、在 init.ora 中加参数_optimizer_cost_model=io
在这里需要注意optimizer_index_cost_adj应设为默认值 (Oracle9i bug 2820066)
引用
Notes on Bug 2820066:
CPU cost is computed when optimizer_index_cost_adj is set to a non-default value.
Range of versions believed to be affected: Versions < 10.1.0.2
Platforms affected: Generic (all / most platforms affected)
This issue is fixed in 9.2.0.6 (Server Patch Set) and 10.1.0.2
Bug description: If optimizer_index_cost_adj is set to a non-default value CPU costs are calculated regardless of the optimizer cost model used. If you have optimizer_index_cost_adj set and you are not using the optimizer CPU cost model, but explain plan shows that for queries not using domain indexes CPU costs are being calculated, you are probably hitting this bug.
In sum, CPU cost is always computed regardless of optimizer mode when optimizer_index_cost_adj is set in un-patched Oracle versions less than 10.1.0.2.
CPU cost is computed when optimizer_index_cost_adj is set to a non-default value.
Range of versions believed to be affected: Versions < 10.1.0.2
Platforms affected: Generic (all / most platforms affected)
This issue is fixed in 9.2.0.6 (Server Patch Set) and 10.1.0.2
Bug description: If optimizer_index_cost_adj is set to a non-default value CPU costs are calculated regardless of the optimizer cost model used. If you have optimizer_index_cost_adj set and you are not using the optimizer CPU cost model, but explain plan shows that for queries not using domain indexes CPU costs are being calculated, you are probably hitting this bug.
In sum, CPU cost is always computed regardless of optimizer mode when optimizer_index_cost_adj is set in un-patched Oracle versions less than 10.1.0.2.
通过以上基本知识,我们再通过一个详细的案例来进一步说明_optimizer_cost_model参数的作用。
测试版本为
引用
SQL> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
创建测试表格,并分析测试表格
引用
SQL> create table dba_t as select * from dba_objects;
Table created.
SQL> select count(*) from dba_t;
COUNT(*)
----------
33017
SQL> create table user_t as select * from user_objects;
Table created.
SQL> select count(*) from user_t;
COUNT(*)
----------
8
SQL> create unique index idx_USER_T on USER_T(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'DBA_T' ,CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'USER_T',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
Table created.
SQL> select count(*) from dba_t;
COUNT(*)
----------
33017
SQL> create table user_t as select * from user_objects;
Table created.
SQL> select count(*) from user_t;
COUNT(*)
----------
8
SQL> create unique index idx_USER_T on USER_T(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'DBA_T' ,CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'USER_T',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
查看其执行计划,发现走的是NESTED LOOPS
引用
SQL> set AUTOT TRACEONLY EXP STAT
SQL> SELECT * FROM dba_t WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM user_t);
7 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=45 Card=8 Bytes=792)
1 0 NESTED LOOPS (Cost=45 Card=8 Bytes=792)
2 1 TABLE ACCESS (FULL) OF 'DBA_T' (Cost=45 Card=33017 Bytes
=3103598)
3 1 INDEX (UNIQUE SCAN) OF 'IDX_USER_T' (UNIQUE)
Statistics
----------------------------------------------------------
47 recursive calls
0 db block gets
471 consistent gets
0 physical reads
0 redo size
1391 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
7 rows processed
SQL> SELECT * FROM dba_t WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM user_t);
7 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=45 Card=8 Bytes=792)
1 0 NESTED LOOPS (Cost=45 Card=8 Bytes=792)
2 1 TABLE ACCESS (FULL) OF 'DBA_T' (Cost=45 Card=33017 Bytes
=3103598)
3 1 INDEX (UNIQUE SCAN) OF 'IDX_USER_T' (UNIQUE)
Statistics
----------------------------------------------------------
47 recursive calls
0 db block gets
471 consistent gets
0 physical reads
0 redo size
1391 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
7 rows processed
如果我们启用CPU COST,就走HASH JOIN了,这主要的原因是Oracle 10g以前,CBO优化器的缺省是IO COST优先的因此在内表很小,并且可以通过索引访问,外表较大情况下,优先选择NESTED LOOP。
引用
SQL> alter session set "_optimizer_cost_model" = "cpu";
Session altered.
SQL> SELECT * FROM dba_t WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM user_t);
7 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=120 Card=8 Bytes=792
)
1 0 HASH JOIN (Cost=120 Card=8 Bytes=792)
2 1 INDEX (FULL SCAN) OF 'IDX_USER_T' (UNIQUE) (Cost=2 Card=
8 Bytes=40)
3 1 TABLE ACCESS (FULL) OF 'DBA_T' (Cost=115 Card=33017 Byte
s=3103598)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
462 consistent gets
0 physical reads
0 redo size
1391 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed
Session altered.
SQL> SELECT * FROM dba_t WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM user_t);
7 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=120 Card=8 Bytes=792
)
1 0 HASH JOIN (Cost=120 Card=8 Bytes=792)
2 1 INDEX (FULL SCAN) OF 'IDX_USER_T' (UNIQUE) (Cost=2 Card=
8 Bytes=40)
3 1 TABLE ACCESS (FULL) OF 'DBA_T' (Cost=115 Card=33017 Byte
s=3103598)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
462 consistent gets
0 physical reads
0 redo size
1391 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed
再进一步同样的例子在Oracle 10g中测试
引用
SQL> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
可以发现Oracle 10g已经默认启用 cpu cost。
引用
SQL> create table dba_t as select * from dba_objects;
Table created.
SQL> select count(*) from dba_t;
COUNT(*)
----------
63713
SQL> create table user_t as select * from user_objects;
Table created.
SQL> select count(*) from user_t;
COUNT(*)
----------
8
SQL> create unique index idx_USER_T on USER_T(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'DBA_T' ,CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'USER_T',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> set AUTOT TRACEONLY EXP STAT
SQL> SELECT * FROM dba_t WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM user_t);
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3073270263
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 8 | 800 | 185 (2)| 00:00:03
|
|* 1 | HASH JOIN | | 8 | 800 | 185 (2)| 00:00:03
|
| 2 | INDEX FULL SCAN | IDX_USER_T | 8 | 40 | 1 (0)| 00:00:01
|
| 3 | TABLE ACCESS FULL| DBA_T | 63713 | 5910K| 183 (2)| 00:00:03
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"="OBJECT_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
898 consistent gets
0 physical reads
0 redo size
1459 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed
Table created.
SQL> select count(*) from dba_t;
COUNT(*)
----------
63713
SQL> create table user_t as select * from user_objects;
Table created.
SQL> select count(*) from user_t;
COUNT(*)
----------
8
SQL> create unique index idx_USER_T on USER_T(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'DBA_T' ,CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'USER_T',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> set AUTOT TRACEONLY EXP STAT
SQL> SELECT * FROM dba_t WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM user_t);
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3073270263
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 8 | 800 | 185 (2)| 00:00:03
|
|* 1 | HASH JOIN | | 8 | 800 | 185 (2)| 00:00:03
|
| 2 | INDEX FULL SCAN | IDX_USER_T | 8 | 40 | 1 (0)| 00:00:01
|
| 3 | TABLE ACCESS FULL| DBA_T | 63713 | 5910K| 183 (2)| 00:00:03
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"="OBJECT_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
898 consistent gets
0 physical reads
0 redo size
1459 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed
我们可以在进一步探讨CPU因素对COST的影响。
SQL> select * from aux_stats$;
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
--------------------------------------------------------------------------------
SYSSTATS_INFO STATUS
COMPLETED
SYSSTATS_INFO DSTART
05-30-2009 03:11
SYSSTATS_INFO DSTOP
05-30-2009 03:11
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
--------------------------------------------------------------------------------
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1104.475
SYSSTATS_MAIN IOSEEKTIM 12.006
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
--------------------------------------------------------------------------------
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
--------------------------------------------------------------------------------
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
--------------------------------------------------------------------------------
SYSSTATS_MAIN SLAVETHR
13 rows selected.
附各字段意思,在这里我们使用的是No Workload
引用
No Workload (NW) stats:
CPUSPEEDNW - CPU speed
IOSEEKTIM - The I/O seek time in milliseconds
IOTFRSPEED - I/O transfer speed in milliseconds
Workload-related stats:
SREADTIM - Single block read time in milliseconds
MREADTIM - Multiblock read time in ms
CPUSPEED - CPU speed
MBRC - Average blocks read per multiblock read (see db_file_multiblock_read_count)
MAXTHR - Maximum I/O throughput (for OPQ only)
SLAVETHR - OPQ Factotum (slave) throughput (OPQ only)
CPUSPEEDNW - CPU speed
IOSEEKTIM - The I/O seek time in milliseconds
IOTFRSPEED - I/O transfer speed in milliseconds
Workload-related stats:
SREADTIM - Single block read time in milliseconds
MREADTIM - Multiblock read time in ms
CPUSPEED - CPU speed
MBRC - Average blocks read per multiblock read (see db_file_multiblock_read_count)
MAXTHR - Maximum I/O throughput (for OPQ only)
SLAVETHR - OPQ Factotum (slave) throughput (OPQ only)
加大cpuspeednw速度,观察执行计划,可以看到执行计划走nested loop,CPU的影响可见一斑啊
引用
SQL> execute DBMS_STATS.SET_SYSTEM_STATS (pname => 'cpuspeednw', pvalue => 8000);
PL/SQL procedure successfully completed.
SQL> set AUTOT TRACEONLY EXP STAT
SQL> SELECT * FROM dba_t WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM user_t);
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1130854696
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 8 | 800 | 182 (1)| 00:00:03
|
| 1 | NESTED LOOPS | | 8 | 800 | 182 (1)| 00:00:03
|
| 2 | TABLE ACCESS FULL| DBA_T | 63713 | 5910K| 181 (0)| 00:00:03
|
|* 3 | INDEX UNIQUE SCAN| IDX_USER_T | 1 | 5 | 0 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"="OBJECT_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
900 consistent gets
0 physical reads
0 redo size
1459 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed
PL/SQL procedure successfully completed.
SQL> set AUTOT TRACEONLY EXP STAT
SQL> SELECT * FROM dba_t WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM user_t);
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1130854696
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 8 | 800 | 182 (1)| 00:00:03
|
| 1 | NESTED LOOPS | | 8 | 800 | 182 (1)| 00:00:03
|
| 2 | TABLE ACCESS FULL| DBA_T | 63713 | 5910K| 181 (0)| 00:00:03
|
|* 3 | INDEX UNIQUE SCAN| IDX_USER_T | 1 | 5 | 0 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"="OBJECT_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
900 consistent gets
0 physical reads
0 redo size
1459 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed
再一进步我们可以通过10053事件获得跟踪
引用
SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 2';
Session altered.
Session altered.
跟踪文件显示:
引用
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 8000 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 12 milliseconds (default is 10)
...
Best:: JoinMethod: Hash
Cost: 182.84 Degree: 1 Resp: 182.84 Card: 8.00 Bytes: 100
...
Best:: JoinMethod: NestedLoop
Cost: 181.88 Degree: 1 Resp: 181.88 Card: 8.00 Bytes: 100
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 8000 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 12 milliseconds (default is 10)
...
Best:: JoinMethod: Hash
Cost: 182.84 Degree: 1 Resp: 182.84 Card: 8.00 Bytes: 100
...
Best:: JoinMethod: NestedLoop
Cost: 181.88 Degree: 1 Resp: 181.88 Card: 8.00 Bytes: 100
发表评论
-
buffer cache 的内部结构
2020-03-18 14:21 519BUFFER CACHE作为数据块的 ... -
Oracle OMC介绍
2020-03-18 13:19 441Oracle管理云服务(OMC)的大数据平台,自动收集的企业 ... -
参加Oracle勒索病毒防范专题培训会议
2019-09-27 17:15 4232019年7月22日,受邀参加Oracle勒索病毒防范专题培训 ... -
记一次内存换IO的Oracle优化
2019-09-27 16:50 787某客户数据库从P595物理 ... -
如何定位Oracle SQL执行计划变化的原因
2019-07-03 14:49 1363性能优化最难的是能够 ... -
如何定位Oracle SQL执行计划变化的原因
2018-10-30 09:24 1185性能优化最难的是能够 ... -
数据库性能优化目标
2018-10-08 10:59 472从数据库性能优化的场 ... -
数据库无法打开的原因及解决办法
2018-10-05 20:45 1917数据库的启动是一个相当复杂的过程。比如,Oracle在启动之前 ... -
怎么样彻底删除数据库?
2018-09-18 11:10 555Oracle提供了drop database命令用来删除数据库 ... -
Oracle减少日志量的方法
2018-09-10 10:17 811LGWR进程将LOG BUFFER中的 ... -
如何快速关闭数据库
2018-09-09 13:14 1196“一朝被蛇咬,十年怕井绳”。在没被“蛇”咬之前,很多DBA喜欢 ... -
关于《如何落地智能化运维》PPT
2018-05-17 10:19 1089在DTCC 2018发表《如何落地智能化运维》演讲,主要内容如 ... -
记录在redhat5.8平台安装oracle11.2容易忽视的几个问题
2018-05-11 19:58 531问题一:ping不通问题 在虚拟机上安装好linux系统后, ... -
《Oracle DBA实战攻略》第一章
2018-05-11 10:42 893即日起,不定期更新《OracleDBA实战攻略》一书电子版,请 ... -
Oracle 12c新特性
2018-05-11 10:33 857查询所有pdb [oracle@gj4 ~]$ sqlplu ... -
关于修改memory_target的值后数据库无法启动的问题
2017-02-28 12:24 3924操作系统:RHEL6.5 数据库版本:11.2.0.4 ... -
10g rac安装error while loading shared libraries libpthread.so.0 问题
2017-02-28 12:22 63311g rac安装在二节点跑脚本一般会报此错误: 解决这个问 ... -
记一次Oracle会话共享模式故障处理过程
2017-02-27 19:16 757故障简述 XXX第八人民医院HIS数据库7月13日11点左右从 ... -
RESMGR:cpu quantum等待事件处理过程
2017-02-27 18:23 2482由于数据库上线过程中出现大量的RESMGR:cpu quant ... -
谈谈log file sync
2014-03-19 14:18 1680数据库中的log file sync等待事件指的是,当user ...
相关推荐
【Maclean Liu技术分享】拨开Oracle CBO优化器迷雾,探究Histogram直方图之秘_0321.pdf
ORACLE CBO RBO 优化
其中主要包括收集数据库统计系统、收集列直方图、分析SQL执行计划、如何让CBO优化器选择最优的执行计划,以及如何使用Hint提示认为改变CBO优化器的执行计划等,希望此文档能够帮助大家更深入地理解Oracle优化!
Oracle的优化器共有3种:RULE(基于规则)、COST(基于成本)、CHOOSE(选择性)。设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS 。你当然也在SQL句...
cbooracle cbo
ORACLE的优化器共有3种 A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS 。 ...
平安Oracle CBO 资料.doc
ORACLE的优化器共有3种: a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你...
主要介绍了Oracle CBO优化模式中的5种索引访问方法浅析,包含索引唯一扫描、索引范围扫描、索引全扫描、索引跳跃扫描、索引快速全扫描等内容,需要的朋友可以参考下
选用适合的ORACLE优化器 ORACLE的优化器共有3种: a.RULE(基于规则) b.COST(基于成本) c.CHOOSE(选择性) 设置缺省的优化器,可以通过对init.ora 文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST, CHOOSE,...
Oracle CBO 学习笔记之(1) : 深入理解Oracle Hash Join的代价模型及其执行流程:word,pdf,图例
优化器产生的执行计划会因“优化器目标”的不同而不同。如果以最佳吞吐量为目标, 结果更倾向于使用全表扫描而不是索引扫描,或者使用排序合并连接而不是嵌套循环连接; 如果以最快的响应速度为目标,其结果则通常...
【Maclean Liu技术分享】拨开Oracle CBO优化器迷雾,探究Histogram直方图之秘 20130429
ORACLE的优化器共有3种:A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS 。 ...
cbo数据库优化,数据性能,oracle性能分析优化
关于oracle的CBO下的HINT总结!
Oracle语句优化53个规则详解,我从网上看到整理出来的,希望大家养成良好的SQL书写习惯,会让我们受益终身,写出高效率SQL语句
oracle性能优化 ORACLE SQL性能优化系列 (一) 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和... 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器.
Oracle的优化器有两种优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO),在Oracle8及以后的版本,Oracle强列推荐用CBO的方式 ...