Overview of Oracle Database Performance Problems

表没有正确的创建索引 -> 错误的执行计划
表没有及时的分析 -> 错误的执行计划
热快 -> 数据库的争用（反向索引？）
锁的阻塞 -> 业务设计缺陷
SQL 解析消耗大量的 CPU -> 变量绑定
低效的 SQL -> SQL 自身的问题
数据库整体负载过程 -> 架构设计的问题

性能问题定位

SQL 层

如果能定位到 SQL，就不需要从会话层面分析。

execution plan, 10053, 10046
会话层

如果能定位到会话，就不需要从系统层面分析。

V $SESSION, V$ SESSTAT, V $SESSION_WAIT, V$ SQL, V$LOCK, SQL_TRACE
系统层

如果无法定位任何性能问题，从系统层面入手。

AWR，OS tools(top, iostat, vmstat)

不要迷恋优化器

优化器永远无法按照你的业务需求来重写你的 SQL。
优化器只能在数学（集合）逻辑上做 SQL 的重写。
高效的 SQL 来自于对业务的理解和对 SQL 执行过程的理解。

CBO 无能为力

SQL> SELECT * FROM table01;

        ID      VALUE
---------- ----------
         1         10
         2         20
         3         15

SQL> SET AUTOTRACE ON
SQL> SELECT t1.id, t1.value, sum(t2.value) FROM table01 t1 JOIN table01 t2 ON t2.id <= t1.id GROUP BY t1.id, t1.value;

        ID      VALUE SUM(T2.VALUE)
---------- ---------- -------------
         1         10            10
         3         15            45
         2         20            30


Execution Plan
----------------------------------------------------------
Plan hash value: 4047717168

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    52 |     9  (34)| 00:00:01 |
|   1 |  HASH GROUP BY       |         |     1 |    52 |     9  (34)| 00:00:01 |
|   2 |   MERGE JOIN         |         |     1 |    52 |     8  (25)| 00:00:01 |
|   3 |    SORT JOIN         |         |     3 |    78 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| TABLE01 |     3 |    78 |     3   (0)| 00:00:01 |
|*  5 |    SORT JOIN         |         |     3 |    78 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| TABLE01 |     3 |    78 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access(INTERNAL_FUNCTION("T2"."ID")<=INTERNAL_FUNCTION("T1"."ID")
              )
       filter(INTERNAL_FUNCTION("T2"."ID")<=INTERNAL_FUNCTION("T1"."ID")
              )

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         59  recursive calls
          0  db block gets
         78  consistent gets
          9  physical reads
          0  redo size
        510  bytes sent via SQL*Net to client
         48  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL> SELECT id, value, SUM(value) OVER(ORDER BY id) FROM table01;

        ID      VALUE SUM(VALUE)OVER(ORDERBYID)
---------- ---------- -------------------------
         1         10                        10
         2         20                        30
         3         15                        45


Execution Plan
----------------------------------------------------------
Plan hash value: 3324014494

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     3 |    78 |     4  (25)| 00:00:01 |
|   1 |  WINDOW SORT       |         |     3 |    78 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TABLE01 |     3 |    78 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        522  bytes sent via SQL*Net to client
         48  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

Lock

没有并发就没有锁！

Enqueues: 队列类型的锁，通常和业务相关。
Latches: 系统资源方面的锁，比如内存结构，SQL 解析。

锁的原则：

只有被修改时，行才会被锁定。
当一条语句修改了一条记录，只有这条记录上被锁定，在 Oracle 数据库中不存在锁升级。
当某行被修改时，它将阻塞别人对它的修改。
当一个事务修改一行时，将在这个行上加上行锁（TX），用于阻止其它事务对相同行的修改。
读永远不会阻止写。唯一例外：select .. for update。
写永远不会阻塞读。
当一行被修改后，Oracle 通过回滚段提供给数据的一致性读。

TM/TX 锁：

TM 锁，发生在 insert，update，delete 以及 select for update 操作时，目的是保证 DML 操作能够正常进行，并且阻止其他人对表执行 DDL 操作。
TX 锁，事务锁（行锁）对于正在修改的数据，阻止其他会话进行修改。

Example:

-- query session id.

SAKURA@127.0.0.1:15210/orcl> select distinct sid from v$mystat;

       SID
----------
        33

-- session 1, update row where id=1 without commit or rollback.

SAKURA@127.0.0.1:15210/orcl> select * from t;

        ID
----------
         1

SAKURA@127.0.0.1:15210/orcl> update t set id=2 where id=1;

1 row updated.


-- session 2, update row where id=1 with hung.

SAKURA@127.0.0.1:15210/orcl> select distinct sid from v$mystat;

       SID
----------
        160

SAKURA@127.0.0.1:15210/orcl> update t set id=3 where id=1;

-- query v$locak to show tx/tm lock.

SAKURA@127.0.0.1:15210/orcl> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TX','TM') order by 1,2;

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        33 TM      87367          0          3          0          0
        33 TX     655362        730          6          0          1
       160 TM      87367          0          3          0          0
       160 TX     655362        730          0          6          0

-- query dba_objests to show id1's object_name.

SAKURA@127.0.0.1:15210/orcl> select object_name from dba_objects where object_id=87367;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
T

-- query v$session_wait to show session wait event.

SAKURA@127.0.0.1:15210/orcl> select sid,event from v$session_wait where sid in (33,160);

       SID EVENT
---------- ----------------------------------------------------------------
        33 SQL*Net message from client
       160 enq: TX - row lock contention

锁的模式：

Row Share (RS) --2
Row Exclusive Table Lock (RX) --3
Share Table Lock (S) --4
Share Row Exclusive Table Lock (SRX) --5
Exclusive Table Lock (X) --6

RI 锁定

t1 主表 id int primary key
t2 从表 reference (t1 id)

BI 锁和外键索引

主表的主键操作时，如果外键没有索引，会扫描整个外键以便确认是否能够修改。

死锁

Oracle 会自动释放死锁。

Latch

保证资源的串行访问：

① 保护 SGA 的资源访问
② 保护内存的分配
保证执行的串行化：

① 保护关键资源的串行执行
② 防止内存结构损坏

Latch 位于 SGA 中。

共享池（SQL 解析、SQL 重用）
数据缓冲池（数据访问、修改、扩展）

绑定变量的作用。

Buffer cache 的机制

x$bh, nxt_hash, prv_hash

Latches -> Hash bucket -> Buffer Headers -> Data Blocks

热块争用。

v$latch，对每个 latch 的统计信息汇总，每条记录表示一种 latch。
v$latchholder，通过 PID/SID 关联视图 v$session，v$session_wait，可以定位会话持有资源信息。
v$latch_children，存储子 latch 信息的视图。

Latch 优化思路

AWR 报告
动态视图 v$latch，分析系统当前的 latch 资源情况
确定争用最大的 latch
分析可能原因
从应用层面和数据库层面考虑解决途径

执行计划和优化器

直接表的访问
- 并行
- 多数据库
通过索引
- index unique scan，唯一键值索引
- index range scan，条件范围位于索引
- index full scan，扫描全部索引
- index fast full scan，索引切块，并行扫描
- index skip scan
数据处理
- order by, group by, count, sum, average ...
- nested join, merge join, hash join
- px coordinator
理解执行计划
优化器
- RBO, 8i 以前
- CBO，8i 以后
CBO 工作模式，建议 SQL 级设置
- all_rows: 以结果集的全部处理完毕为目的
- first_rows (n): 以最快返回 n 行为目的

selectivity（选择性）

select column_name,num_distinct from user_tab_col_statistics where table_name='T' order by 2 desc;

num_distinct 数值越大，选择性越好，越适合建 B-tree 索引。

select index_name,distinct_keys from user_indexes where table_name='T';

distinct_keys 数值越大，选择性越好，效率越高。

cardinality（反馈）

在执行计划中表示每一步操作返回的记录数。
CBO 通过对这个值的权重计算，决定使用哪一种方式访问数据。
clustering factor

影响索引的 cost。
select index_name,clustering_factor from user_indexes where table_name='T';
clustering_factor: 索引访问时，数据块跳转越高，cost 越大。

SQL 成本估算，user_indexes, user_tab_columns
hints

用来约束优化器行为的一种技术。