Shared pool latch用来保护共享池内部结构，在分配和释放共享池时需要获得latch，在老化或释放空间时也需要 latch。Oracle9i之前，共享池内存结构是由单独的Shared pool latch保护，从9i开始，如果服务器有4个以上的cpu且shared_pool_size大于250M,Oracle会动态将共享池分为多个子池，最多可以达到7个sub pool。子池的数量也可以通过_kghdsidx_count参数来手动调整，每个子池有自己的结构，lru列表和shared pool latch。
查看Shared pool latch个数

Oracle9i之前，过大的共享池会增加Shared pool latch的争用，因为共享池中的空闲内存被分类，并保存在大量的存储桶或空闲列表上，较大的共享池趋向于较长的空闲列表，进程在获得共享池内存池之前，需要持有latch扫描很长的空闲列表，这在高度并发的系统中可能会生产 latch争用，特别是一些不使用绑定变量的系统。

Library cache latch用来保护库高速缓存内部结构，Oracle进程在库高速缓存中修改，检查，锁定，加载或执行对象时都需要先获得latch，latch的数量通常大于cpu_count的最小质数。可以通过_kgl_latch_count参数进行手动更改。
查看Library cache latch的个数

Library cache latch争用通常出现在具有高版本数的sql语句中，这些sql的表面字义是相同的如select * from test，但可能属于不同的底层如schema，因为这些sql具有相同的散列值和不同的版本，oracle需要去比较该语句和现有版本，这期间需要一直持有latch的，这可能会造成其他进程无法获得 latch。
查看高版本的sql语句：

总的来说，shared pool和library cache争用主要出现在频繁的硬解析上,而过多的硬解析通常又和绑定变量存在关系，说了半天还是回到应用上,共享池的健壮性主要还是取决于应用的设计，而不不仅仅是参数的调整。

当你接手一个系统时，通常是从检查系统负载，cpu、内存使用率开始，查看statspack top5等待事件，逻辑读、物理读排名靠前的sql语句等等，然后进行初步的优化。而随着业务的深入了解，你开始从系统的角度去考虑据库设计，考虑应用实现的合理性，是否有更好的改进方案等。假设通过statspack报表找到了很耗资源的sql，表分析过，执行计划也是走索引，这种情况下怎么去判断 sql是优化的呢？
1.提取逻辑读排名靠前的sql
  6,813,699          336       20,278.9   10.1    66.72     80.45 3039661161
module: java@admin1 (tns v1-v3)
  select b.biz_source, count(*) as counts from     tb_hanzgs_de
tail a, tb_business_info b    where a.id = b.hanzgs_id
and a.status = :1      and a.deal_id = :2      and a.create_date
>= to_date(:3,'yyyy-mm-dd hh24:mi:ss')     and a.create_date < to
_date(:4,'yyyy-mm-dd hh24:mi:ss')        group by b.biz_source

2.查看执行计划
sql> explain plan for
  2  select b.biz_source, count(*) as counts
  3    from tb_hanzgs_detail a, tb_business_info b
  4   where a.id = b.hanzgs_id
  5     and a.status = :1
  6     and a.deal_id = :2
  7     and a.create_date >= to_date(:3, 'yyyy-mm-dd hh24:mi:ss')
  8     and a.create_date < to_date(:4, 'yyyy-mm-dd hh24:mi:ss')
  9   group by b.biz_source;
Explained.
SQL> @?/rdbms/admin/utlxpls
Plan hash value: 1387434542
-----------------------------------------------------------------------
id  | operation           |  name               | rows  | bytes | cost  |
-----------------------------------------------------------------------
  0 | select statement         |                          |  1 | 31 |215
  1 |  sort group by           |                          |  1 | 31 |215
  2 |   filter                 |                          |    |    |  
  3 |    nested loops          |                          |  1 | 31 |199
  4 |     table access by index rowid| tb_hanzgs_detail   |   1| 21 |198
  5 |      index range scan    | ind_tb_hanzgs_create     | 231|    |397
  6 |     index range scan     | ind_tb_business_info_biz |   1| 10 |1
-----------------------------------------------------------------------
索引定义
create index ind_tb_hanzgs_create  on tb_hanzgs_detail (create_date, deal_id,status, id)tablespace tbs_tb_ind online compute statistics;
3.查看语句执行时间
sql>select b.biz_source, count(*) as counts
  3    from tb_hanzgs_detail a, tb_business_info b
  4   where a.id = b.hanzgs_id
  5     and a.status = 1
  6     and a.deal_id = 0
  7     and a.create_date >= to_date(sysdate-10, 'yyyy-mm-dd hh24:mi:ss')
  8     and a.create_date < to_date(sysdate-5, 'yyyy-mm-dd hh24:mi:ss')
  9   group by b.biz_source;

biz_source     counts
---------- ----------
       102        712
       501       7881
       701       1465

3 rows selected.

elapsed: 00:00:17.03
sql> /

biz_source     counts
---------- ----------
       102        713
       501       7882
       701       1465

3 rows selected.

elapsed: 00:00:05.32

这个语句查询时间在5.3秒左右，对于查询频繁的oltp系统中，毫无疑问全表扫描的代价是最高的，按时间索引扫描数据效率也是很低的，毕竟一个时间段的数据也是不少的。考虑到上面sql正好使用时间列索引，如果status，rule_id列稀疏读很高的话，这些列建立索引性能应该会有很大的提高。
4.查看表数据分布
sql>  select status, count(*) as counts
  2     from tb_hanzgs_detail
  3    where create_date >= to_date(sysdate - 50, 'yyyy-mm-dd hh24:mi:ss')
  4      and create_date <  to_date(sysdate - 49, 'yyyy-mm-dd hh24:mi:ss') + 1
  5    group by status;
    status     counts
--------- ----------
         0          2
         1        286
         2       3567
         3     123477

根据随机抽取几天数据分布结构，这个表中97%以上数据的status都等于3，如果status为3的查询应该会走全表扫描，消耗大量资源，查询频繁的话DBA是不允许开发部署到生产系统的，数据库压力太大。而status不等于3的数据量很少很少，以status列来建立索引，性能应该会有很大的提高。分析完就可以尝试的进行优化了。
5.重新建立索引
create index ind_tb_hanzgs_de_sta on tb_hanzgs_detail (status, deal_id, create_date,id)tablespace tbs_tb_ind online compute statistics;
新执行计划
sql> explain plan for
  2  select b.biz_source, count(*) as counts
  3    from tb_hanzgs_detail a, tb_business_info b
  4   where a.id = b.hanzgs_id
  5     and a.status = :1
  6     and a.deal_id = :2
  7     and a.create_date >= to_date(:3, 'yyyy-mm-dd hh24:mi:ss')
  8     and a.create_date < to_date(:4, 'yyyy-mm-dd hh24:mi:ss') + 1
  9   group by b.biz_source;
explained.
elapsed: 00:00:00.01
14:11:48 sql> @?/rdbms/admin/utlxpls
------------------------------------------------------------------
id  | operation            |  name        | rows  | bytes | cost |
------------------------------------------------------------------
  0 | select statement     |                          |1 |31 |19 |
  1 |  sort group by       |                          |1 |31 |19 |
  2 |   filter             |                          |  |   |   |
  3 |    nested loops      |                          |1 |31 | 3 |
  4 |     index range scan | ind_tb_hanzgs_de_sta     |1 |21 | 4 |
  5 |     index range scan | ind_tb_business_info_biz |1 |10 | 1 |
------------------------------------------------------------------
6.重新执行该语句
sql>select b.biz_source, count(*) as counts
  3    from tb_hanzgs_detail a, tb_business_info b
  4   where a.id = b.hanzgs_id
  5     and a.status = 1
  6     and a.deal_id = 0
  7     and a.create_date >= to_date(sysdate-10, 'yyyy-mm-dd hh24:mi:ss')
  8     and a.create_date < to_date(sysdate-5, 'yyyy-mm-dd hh24:mi:ss')
  9   group by b.biz_source;

biz_source     counts
---------- ----------
       102        713
       501       7881
       701       1465

3 rows selected.

elapsed: 00:00:00.24
sql> /

biz_source     counts
---------- ----------
       102        713
       501       7882
       701       1465

3 rows selected.

elapsed: 00:00:00.23

调整后的查询时间在0.2秒左右，速度提高了100倍左右，我只是简单的把索引列位置调换一下，性能就有了很大提高，statspack看不到这条语句了。总的来说，索引不是说create就可以了，通常需要考虑以下几点。
1.结合实际的应用。
2.考虑索引列的数据分布，如果distinct值很少且数据分布均匀的话，可能就不适合放在联合索引的最前面。
3.考虑索引的大小，在字段长度32的列和长度为7的列上建立索引大小肯定是不一样的，索引越大扫描的代价就越高。
4.考虑索引列冗余，可能你在索引中多冗余一个小字段，select就只走索引而不需要去扫描原表的数据。
5.考虑索引对其他sql的影响，是否其他的sql也可以使用这个索引。
6.考虑对是否可以对原有索引进行合并。

     对于oracle数据库性能调整，关注的总会是reponse time和wait time，共享服务器进程也不例外，我们需要通过视图收集数据来判断性能的好坏,参数设置是否合理。

1.Dispatcher
     v$dispatcher - general information about dispatcher processes
     v$dispatcher_rate - dispatcher processing statistics
To assess dispatcher performance, query the v$dispatcher_rate view and compare the current values with the maximums. If your present system throughput provides adequate response time and current values from this view are near the average and less than the maximum, then you likely have an optimally tuned shared server environment.
If the current and average rates are significantly less than the maximums, then consider reducing the number of dispatchers. Conversely, if current and average rates are close to the maximums, then you might need to add more dispatchers. A general rule is to examine v$dispatcher_rate statistics during both light and heavy system use periods. After identifying your shared server load patterns, adjust your parameters accordingly.

2.Shared servers
select decode(totalq, 0, 'no requests',wait/totalq || ' hundredths of seconds') "average wait time per requests"
   from v$queue
  where type = 'common';
 average wait time per request
 -----------------------------
 .090909 hundredths of seconds

From the result, you can tell that a request waits an average of 0.09 hundredths of a second in the queue before processing.
You can also determine how many shared servers are currently running by issuing the following query:
select count(*) "shared server processes"
  from v$shared_server
 where status != 'quit';

The result of this query could look like the following:
shared server processes
-----------------------
10

If you detect resource contention with shared servers, then first make sure that this is not a memory contention issue by examining the shared pool and the large pool. If performance remains poor, then you might want to create more resources to reduce shared server process contention.you can do this by modifying the optional server process initialization parameters
max_dispatchers
max_shared_servers
dispatchers
shared_servers

http://download.oracle.com/docs/cd/b19306_01/server.102/b14211/build_db.htm#g27214  

Dbms_stats是oracle8i新增的程序包，它使统计数据的生成和处理更加方便，很多人都在推荐使用dbms_stats替代analyze,我倒是不怎么用过，记录一下

estimate_percent --估算抽样百分比
method_opt：for table --只统计表　for all indexed columns --只统计有索引的表列 for all indexes --只分析统计相关索引

--创建统计信息历史保留表
sql> exec dbms_stats.create_stat_table(ownname => 'scott',stattab => 'stat_table') ;

pl/sql procedure successfully completed

--导出整个scheme的统计信息
sql> exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table') ;

pl/sql procedure successfully completed

--分析scheme
Exec dbms_stats.gather_schema_stats(
ownname => 'scott',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns ',
degree => 6 )

--分析表
sql> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'work_list',estimate_percent => 10,method_opt=> 'for all indexed columns') ;

pl/sql procedure successfully completed

--分析索引
SQL> exec dbms_stats.gather_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',estimate_percent => '10',degree => '4') ;

pl/sql procedure successfully completed

--如果发现执行计划走错，删除表的统计信息
SQL>dbms_stats.delete_table_stats(ownname => 'scott',tabname => 'work_list') ;

pl/sql procedure successfully completed

--导入表的历史统计信息
sql> exec dbms_stats.import_table_stats(ownname => 'scott',tabname => 'work_list',stattab => 'stat_table') ;

pl/sql procedure successfully completed

--如果进行分析后，大部分表的执行计划都走错，需要导回整个scheme的统计信息
sql> exec dbms_stats.import_schema_stats(ownname => 'scott',stattab => 'stat_table');

pl/sql procedure successfully completed

--导入索引的统计信息
SQL> exec dbms_stats.import_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',stattab => 'stat_table')

--检查是否导入成功
SQL> select table_name,num_rows,a.blocks,a.last_analyzed from all_tables a where a.table_name='WORK_LIST';

TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED

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

WORK_LIST 4005 186 2007-10-12 15

SQL>

analyze和dbms_stats不同的地方:
analyze是同时更新表和索引的统计信息，而dbms_stats会先更新表的统计信息，然后再更新索引的统计信息，这里就有一个问题，就是当表的统计信息更新后，而索引的统计信息没有被更新，这时候cbo就有可能选择错误的plan
参考：
http://www.dbafan.com/blog/?p=114
http://zhouwf0726.itpub.net/post/9689/288301
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/stats.htm#33861

Index Full Scans

A full scan is available if a predicate references one of the columns in the index. The predicate does not need to be an index driver. A full scan is also available when there is no predicate, if both the following conditions are met:

• All of the columns in the table referenced in the query are included in the index.
• At least one of the index columns is not null.

A full scan can be used to eliminate a sort operation, because the data is ordered by the index key. It reads the blocks singly.

Fast Full Index Scans

Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.

Fast full scan is available only with the CBO. You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.

A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.