Oracle 作业记录

autotrace

We can observe the execution plan Oracle choses for a given query by using the menu AUTOTRACE

 

LAST_CR_BUFFER_GETS (number of pages from the RAM buffer)       logical reads/consistent gets

LAST_DISK READS (number of pages read from disk)                            physical reads

如果不是第一次执行，LAST_DISK READS会是0，会从cache里面读了

但是这是我们应该避免的，make sure the buffer is empty 

清除缓存命令

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH GLOBAL CONTEXT;

或者重启oracle

 

 

Autotrace and Explain的区别

• Autotrace runs the query and shows the executed plan and its main statistics
• Explain shows an estimated best plan and its estimated statistics without running the query

我们将不会使用 Explain Plan

 

 

关于index的clustered和unclustered

By default all indexes in Oracle are unclustered. The only clustered indexes in Oracle are the Index-Organized tables (IOT) primary key indexes.

You can determine if a table is an IOT by looking at the IOT_TYPE column in the ALL_TABLES view

SELECT IOT_TYPE FROM ALL_TABLES WHERE TABLE_NAME='MOVIE4';

https://stackoverflow.com/questions/6290422/is-an-index-clustered-or-unclustered-in-oracle

 

RULE mode 和 CHOOSE mode

RULE和CHOOSE是两种不同的optimization mode

默认是CHOOSE mode

ALTER SESSION SET OPTIMIZER_MODE=RULE;
ALTER SESSION SET OPTIMIZER_MODE=CHOOSE;

 

INDEX RANGE SCAN & INDEX UNIQUE SCAN & FULL SCAN

如果表上有唯一索引, 搜索索引列时会用上INDEX UNIQUE SCAN

 

index range scan是对索引的有序扫描