1、优化器默认为CBO,OPTIMIZER_MODE默认值为ALL_ROWS。不再使用古老的RBO模式,但RULE、CHOOSE并没有彻底消失,有些时候仍然可以作为我们调试的工具。
from a1 where id in (2,10,12));
Execution Plan
----------------------------------------------------------
Plan hash value: 173249654
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 34 | 3 (34)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| A1 | 1 | 14 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 34 | 3 (34)| 00:00:01 |
| 3 | SORT UNIQUE | | 2 | 6 | 1 (0)| 00:00:01 |
| 4 | INLIST ITERATOR | | | | | |
|* 5 | INDEX RANGE SCAN | IDX_A1_ID | 2 | 6 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_A1_ID | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
---------------
select * from a1 where id in (select /*+ qb_name(sub1) full(@sub1 a1) */ id
from a1 where id in (2,10,12));
Plan hash value: 1882950619
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 34 | 17 (6)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| A1 | 1 | 14 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 34 | 17 (6)| 00:00:01 |
| 3 | SORT UNIQUE | | 2 | 6 | 15 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | A1 | 2 | 6 | 15 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_A1_ID | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
-----------
修改成错误的queryblockname
select * from a1 where id in (select /*+ qb_name(sub1) full(@sub2 a1) */ id from a1 where id in (2,10,12));
Execution Plan
----------------------------------------------------------
Plan hash value: 173249654
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 34 | 3 (34)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| A1 | 1 | 14 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 34 | 3 (34)| 00:00:01 |
| 3 | SORT UNIQUE | | 2 | 6 | 1 (0)| 00:00:01 |
| 4 | INLIST ITERATOR | | | | | |
|* 5 | INDEX RANGE SCAN | IDX_A1_ID | 2 | 6 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_A1_ID | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
·自动调节DB_FILE_MULTIBLOCK_READ_COUNT参数,Oracle会根据数据库的访问自动调节该参数
·增加了SQL的优化模式,提供了SQL Tuning Adsivor,SQL Profile等工具.可自动优化sql语句
·两个比较重要的视图:v$PROCESS_MEMORY,动态监控每个进程的pga使用,v$sqlstats某种情况下可以替换v$sql视图