oracle概念学习

#$1、Row Source Generation（行源生成）

    优化器在确定执行计划后，生成执行代码的阶段。这个代码（行源树）告诉数据库引擎如何实际执行查询,过程如下：

    SQL 文本------解析 (Parsing) → 语法/语义检查------优化 (Optimization) → 选择最佳执行计划------**行源生成 (Row Source Generation)** → 生成执行代码----执行 (Execution) → 实际执行SQL，以下列SQL为例：

SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
  AND e.sal > 5000;

   产生执行计划

SELECT STATEMENT
   |
   |-- HASH JOIN
   |     |
   |     |-- TABLE ACCESS FULL (EMP)  → 行源#1
   |     |
   |     |-- TABLE ACCESS FULL (DEPT) → 行源#2
   |
   |-- FILTER (sal > 5000)           → 行源#3

行源代码参考如下：

// 伪代码表示
class RowSource {
    open();
    fetch(row);  // 获取下一行
    close();
}

class HashJoin : RowSource {
    open() {
        build_hash_table(left_child);
    }
    
    fetch(row) {
        probe_hash_table(right_child);
        return joined_row;
    }
}

访问方法

-- TABLE ACCESS FULL
-- TABLE ACCESS BY INDEX ROWID
-- INDEX FULL SCAN
-- INDEX RANGE SCAN
-- INDEX UNIQUE SCAN

-- 示例：不同的访问方法生成不同的行源
EXPLAIN PLAN FOR
SELECT * FROM emp WHERE empno = 7900;
-- 可能生成: TABLE ACCESS BY INDEX ROWID (索引行号访问)

SELECT * FROM emp WHERE sal > 3000;
-- 可能生成: TABLE ACCESS FULL (全表扫描)

连接方法 

-- NESTED LOOPS
-- HASH JOIN
-- MERGE JOIN
-- CARTESIAN JOIN

-- 查看执行计划中的连接方法
SELECT /*+ USE_HASH(e d) */ e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

监控源产生

-- 使用 DBMS_XPLAN
EXPLAIN PLAN FOR
SELECT * FROM emp WHERE deptno = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 输出示例：
---------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost |
---------------------------------------------------------
|  0 | SELECT STATEMENT   |      |    3 |   114 |     3|
|* 1 |  TABLE ACCESS FULL | EMP  |    3 |   114 |     3|
---------------------------------------------------------
-- 每个 Operation 对应一个行源

实际执行统计

-- 启用行源执行统计
ALTER SESSION SET statistics_level = ALL;

-- 执行查询
SELECT e.ename, d.dname 
FROM emp e JOIN dept d ON e.deptno = d.deptno
WHERE e.sal > 3000;

-- 查看详细的执行统计
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  format => 'ALLSTATS LAST'
));

行源产生执行统计

-- 关键统计信息
SELECT 
  operation,              -- 操作类型（行源类型）
  options,                -- 选项（如 FULL, BY INDEX ROWID）
  object_name,            -- 操作对象
  cardinality,            -- 预估行数
  bytes,                  -- 预估字节数
  cost,                   -- 预估成本
  cpu_cost,               -- CPU成本
  io_cost,                -- IO成本
  time                    -- 预估时间
FROM v$sql_plan
WHERE sql_id = 'your_sql_id'
ORDER BY id;

行源高级特性

-- 自适应连接方法
SELECT /*+ ADAPTIVE */ e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- 查看自适应决策
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  format => 'ADAPTIVE'
));

识别问题行源

-- 查找执行时间最长的行源
WITH sql_plan_stats AS (
  SELECT 
    sql_id,
    plan_hash_value,
    id,
    operation || ' ' || options as operation,
    object_name,
    cardinality,
    last_output_rows as actual_rows,
    last_elapsed_time as elapsed_time
  FROM v$sql_plan_monitor
  WHERE last_elapsed_time > 1000000  -- 超过1秒
)
SELECT * FROM sql_plan_stats
ORDER BY elapsed_time DESC;

生成行源等待事件

-- 常见的行源执行等待
SELECT event, total_waits, time_waited
FROM v$system_event
WHERE event IN (
  'db file sequential read',      -- 索引读取等待
  'db file scattered read',       -- 全表扫描等待
  'direct path read',             -- 直接路径读取
  'PX qref latch',                -- 并行查询等待
  'row cache lock'                -- 字典缓存等待
)
ORDER BY time_waited DESC;

监控行源统计信息

-- 定期收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCOTT',
  tabname => 'EMP',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
);

-- 检查统计信息质量
SELECT table_name, num_rows, last_analyzed
FROM user_tables
WHERE table_name = 'EMP';

#$1、执行计划（从下往上看）

计划示例：
---------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Cost | Time      |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |   100 |   45 |  00:00:01 |
|   1 |  SORT ORDER BY                 |           |   100 |   45 |  00:00:01 |
|   2 |   HASH GROUP BY                |           |   100 |   44 |  00:00:01 |
|   3 |    HASH JOIN                   |           |   500 |   30 |  00:00:01 |
|   4 |     TABLE ACCESS FULL          | EMPLOYEES |  1000 |   15 |  00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID| DEPTS     |   100 |    2 |  00:00:01 |
|   6 |      INDEX RANGE SCAN          | DEPT_IDX  |   100 |    1 |  00:00:01 |
---------------------------------------------------------------------------------

阅读顺序：6 → 5 → 4 → 3 → 2 → 1 → 0

统计信息收集

-- 收集表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'HR',
  tabname => 'EMPLOYEES',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  cascade => TRUE  -- 收集索引统计
);

-- 收集列直方图（数据倾斜时）
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'HR',
  tabname => 'SALES',
  method_opt => 'FOR COLUMNS SIZE 254 customer_id, product_id'
);

-- 锁定统计信息（防止变化）
EXEC DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');

SQL实时监控

-- 监控长时间运行的SQL
SET LINES 200 PAGES 1000
COL sql_text FOR a60
COL status FOR a15

SELECT 
  sql_id,
  status,
  sql_text,
  elapsed_time/1000000 as elapsed_sec,
  cpu_time/1000000 as cpu_sec,
  buffer_gets,
  disk_reads,
  rows_processed
FROM v$sql_monitor
WHERE status = 'EXECUTING'
ORDER BY elapsed_time DESC;

-- 生成HTML监控报告
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  sql_id => 'monitored_sql',
  type => 'HTML',
  report_level => 'ALL'
) AS report FROM dual;

日常监控脚本

-- 查找性能差的SQL执行计划
SELECT 
  s.sql_id,
  substr(s.sql_text, 1, 100) as sql_text,
  s.executions,
  s.elapsed_time/1000000/ NULLIF(s.executions, 0) as avg_elapsed_sec,
  s.buffer_gets/ NULLIF(s.executions, 0) as avg_buffer_gets,
  s.rows_processed/ NULLIF(s.executions, 0) as avg_rows,
  p.plan_hash_value
FROM v$sql s
JOIN v$sql_plan p ON s.sql_id = p.sql_id
WHERE s.last_active_time > SYSDATE - 1/24  -- 过去1小时
  AND s.elapsed_time > 10000000  -- 总耗时超过10秒
  AND s.executions > 0
  AND p.id = 1  -- 获取执行计划哈希
ORDER BY avg_elapsed_sec DESC
FETCH FIRST 20 ROWS ONLY;

 #$3、Real-Time ADDM   &&  AWR  &&  SQL Tuning Advisor  &&  SQLAccess Advisor  &&  End to End Application tracing  &&  Server-generated alerts  && 

 

#$4、调优分成以下三部分：

  •  Performance Planning

  • Instance Tuning

  • SQL Tuning

首先应该建议性能基线，应包括以下信息

Application statistics (transaction volumes, response time)

• Database statistics

• Operating system statistics

• Disk I/O statistics

• Network statistics

常见的症状有：Slow physical I/O、Latch contention、Excessive CPU usage，有关特性需设置初始化参数，推荐使用EM来查看。

STATISTICS_LEVEL initialization
parameter must be set to TYPICAL or ALL

##性能经常出现问题的地方

1、Bad connection management：This problem is common with stateless middleware in application servers

2、Bad use of cursors and the shared pool：

　　　Not using cursors results in repeated parses. If bind variables are not used, then

there is hard parsing of all SQL statements.

  3、Bad SQL：ADDM identifies high load SQL. SQL Tuning Advisorcan provide recommendations for improvement

  4、Use of nonstandard initialization parameters

  5、Getting database I/O wrong

  6、Online redo log setup problems

  7、Serialization of data blocks in the buffer cache due to lack of free lists, free list groups, transaction slots (INITRANS), or shortage of rollback segments

     Use automatic segment-spacemanagement (ASSM) and automatic undo management to solve this problem

  8、Long full table scans

  9、High amounts of recursive (SYS) SQL

  10、Deployment and migration errors     

     When migrating applications of known performance, export the schema statistics to maintain plan stability using the DBMS_STATS package.

##相关视图

 V$SESS_TIME_MODEL   ##for database CPU usage
 V$Sys_TIME_MODEL   ##for database CPU usage
 V$SESSTAT and V$SQLSTATS   ## for session
 V$SESSION_WAIT             ##
 V$UNDOSTAT
 V$ROLLSTAT
 V$INSTANCE_RECOVERY       ##redo_log size OPTIMAL_LOGFILE_SIZE column

 V$DISPATCHER              ##: general information about dispatcher processes
 V$DISPATCHER_RATE         ##: dispatcher processing statistics
 V$QUEUE　　　　　　　　　　　 ##contains statistics showing request queue activity for shared servers