查看数据字典命中率

Select 1-sum(gets)/sum(getmisses) from v$rowcache

数据字典命中率应该在95%以上，如果命中率过低要考虑增加shared_pool,简单介绍一下v$rowcache数据字典视图的column：

Parameter 缓存名

Count：缓存项总数

Gets：请求总数

Getmisses:请求失败次数

Scans：扫描请求数

Scanmisses：扫描请求失败数

Usage：包含有效数据的缓存项数

查看共享池中sql语句或pl/sql命中率

select sum(gethits)/sum(gets),(sum(pinhits)-sum(reloads))/sum(pins) from v$librarycache

查看buffer命中率

Select name,1-physical_reads/(db_block_gets+consistent_gets) buffer+hits from v$buffer_pool_statistics;

Sort排序率:

排序是一个很消耗资源的操作，sql语句排序时会先在pga的内存中排序，当内存无法满足时才会在磁盘中进行排序，而磁盘比内存慢很多。

Select values, name from v$sysstat where name like ‘%sort%’;

sorts (memory) 21584

sorts (disk) 1

sorts (rows) 223033

内存的排序率：sorts(memory)/(sorts(memory)+sorts(disk))

查看与log_buffer有关的信息

Select name,value from v$sysstat where name=’redo buffer allocation retries’

该参数表示等待获取log buffer space，接近0，如果过大需要增加log_buffer日志缓存区

与log_buffer有关的等待事件

Select name,value from v$sysstat where name=’redo log space requests’

日志缓冲空间分配重试等待

1 lgwr写出redo entries的速度慢于服务进程产生的redo entries。

2 发生日志切换，在log switch 这个过程中，任何进程将不能尝试去log buffer 中分配空间。

Pub上的听说淘宝面试题

create table test34 as select * from dba_objects;

create index index_test34 on test34(object_id);

优化下列sql

select max(object_Id),min(object_id) from test34

SQL> select max(object_Id),min(object_id) from test34;

执行计划

----------------------------------------------------------

Plan hash value: 3450461665

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 144 (3)| 00:00:02 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| 2 | TABLE ACCESS FULL| TEST34 | 51020 | 249K| 144 (3)| 00:00:02 |

-----------------------------------------------------------------------------

统计信息

----------------------------------------------------------

1 recursive calls

0 db block gets

706 consistent gets

0 physical reads

0 redo size

483 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

select (select min(object_id) min_id from test34 a),

(select max(object_id) max_id from test34 b)

from dual;

执行计划

----------------------------------------------------------

Plan hash value: 1545095403

--------------------------------------------------------------------------------

-----------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

Time |

--------------------------------------------------------------------------------

-----------

| 0 | SELECT STATEMENT | | 1 | | 2 (0)|

00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | |

|

| 2 | INDEX FULL SCAN (MIN/MAX)| INDEX_TEST34 | 51020 | 249K| |

|

| 3 | SORT AGGREGATE | | 1 | 5 | |

|

| 4 | INDEX FULL SCAN (MIN/MAX)| INDEX_TEST34 | 51020 | 249K| |

|

| 5 | FAST DUAL | | 1 | | 2 (0)|

00:00:01 |

--------------------------------------------------------------------------------

-----------

统计信息

----------------------------------------------------------

1 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

533 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

select (select /*+index_asc(test34 index_test34)*/

object_id

from test34

where rownum = 1) min,

(select /*+ index_desc(test34 index_test34) */

object_id

from test34

where rownum = 1) max

from dual;

执行计划

----------------------------------------------------------

Plan hash value: 1697884143

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | TABLE ACCESS FULL| TEST34 | 1 | 5 | 2 (0)| 00:00:01 |

|* 3 | COUNT STOPKEY | | | | | |

| 4 | TABLE ACCESS FULL| TEST34 | 1 | 5 | 2 (0)| 00:00:01 |

| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM=1)

3 - filter(ROWNUM=1)

统计信息

----------------------------------------------------------

1 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

459 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

利用的是分开扫描时只查询了leaf 中的第一条记录或者最后一条记录！注意COUNT STOPKEY这个是扫描索引到前几行就结束，当有rownum或者分析函数时出现。