七、性能优化

7.1 执行计划

-- 查看执行计划
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE dept_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 获取真实执行计划（已执行SQL）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', NULL, 'ALLSTATS LAST'));

-- 设置SQL跟踪
ALTER SESSION SET SQL_TRACE = TRUE;
-- 执行SQL
SELECT * FROM employees WHERE salary > 5000;
ALTER SESSION SET SQL_TRACE = FALSE;

-- 使用TKPROF分析跟踪文件
-- tkprof trace_file output_file explain=username/password

-- 自动工作负载库（AWR）
-- 生成AWR报告
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

7.2 索引优化

-- 监控索引使用情况
ALTER INDEX idx_emp_name MONITORING USAGE;

-- 查询索引使用统计
SELECT * FROM v$object_usage;

-- 重建索引
ALTER INDEX idx_emp_name REBUILD;
ALTER INDEX idx_emp_name REBUILD ONLINE;  -- 在线重建

-- 索引合并
SELECT /*+ USE_MERGE(employees dept) */ * 
FROM employees, dept 
WHERE employees.dept_id = dept.dept_id;

-- 索引提示
SELECT /*+ INDEX(employees idx_emp_dept) */ *
FROM employees
WHERE dept_id = 10;

7.3 SQL优化

-- 使用绑定变量（避免硬解析）
DECLARE
    v_sql VARCHAR2(1000);
    v_emp_id NUMBER := 1001;
BEGIN
    -- 使用绑定变量
    v_sql := 'SELECT emp_name FROM employees WHERE emp_id = :id';
    EXECUTE IMMEDIATE v_sql INTO v_emp_name USING v_emp_id;
END;
/

-- 收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
EXEC DBMS_STATS.GATHER_DATABASE_STATS;

-- 查看统计信息
SELECT 
    table_name,
    num_rows,
    blocks,
    avg_row_len,
    last_analyzed
FROM user_tables;

-- 使用提示优化
-- 全表扫描提示
SELECT /*+ FULL(employees) */ * FROM employees;

-- 索引提示
SELECT /*+ INDEX(employees idx_emp_name) */ * FROM employees;

-- 并行查询
SELECT /*+ PARALLEL(employees, 4) */ * FROM employees;

-- 查询转换提示
SELECT /*+ USE_HASH(employees dept) */ * 
FROM employees, dept 
WHERE employees.dept_id = dept.dept_id;

7.4 内存优化

-- 查看SGA配置
SHOW PARAMETER sga;
SHOW PARAMETER memory;

-- 查看SGA使用情况
SELECT * FROM v$sgainfo;
SELECT * FROM v$sgastat;

-- 查看Buffer Cache命中率
SELECT 
    (1 - (physical_reads / (db_block_gets + consistent_gets))) * 100 AS hit_ratio
FROM v$buffer_pool_statistics;

-- 查看共享池命中率
SELECT 
    (SUM(pins - reloads) / SUM(pins)) * 100 AS hit_ratio
FROM v$librarycache;

-- 调整内存参数
ALTER SYSTEM SET sga_target = 4G SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=SPFILE;
ALTER SYSTEM SET db_cache_size = 2G SCOPE=SPFILE;

-- 重启数据库生效
SHUTDOWN IMMEDIATE;
STARTUP;

八、备份与恢复

8.1 RMAN备份

# 进入RMAN
rman target /

# 配置备份参数
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/%U';

# 全库备份
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

# 增量备份
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

# 备份表空间
RMAN> BACKUP TABLESPACE users;

# 备份归档日志
RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;

# 备份控制文件
RMAN> BACKUP CURRENT CONTROLFILE;

# 备份参数文件
RMAN> BACKUP SPFILE;

# 验证备份
RMAN> VALIDATE BACKUPSET;

# 列出备份
RMAN> LIST BACKUP;
RMAN> LIST COPY;

8.2 RMAN恢复

-- 完全恢复
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

-- 表空间恢复
RMAN> SQL 'ALTER TABLESPACE users OFFLINE IMMEDIATE';
RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> SQL 'ALTER TABLESPACE users ONLINE';

-- 数据文件恢复
RMAN> SQL 'ALTER DATABASE DATAFILE 5 OFFLINE';
RMAN> RESTORE DATAFILE 5;
RMAN> RECOVER DATAFILE 5;
RMAN> SQL 'ALTER DATABASE DATAFILE 5 ONLINE';

-- 表恢复（Oracle 12c+）
RMAN> RECOVER TABLE scott.employees
      UNTIL TIME '2024-01-01:12:00:00'
      AUXILIARY DESTINATION '/u01/aux'
      REMAP TABLE scott.employees:employees_restored;

8.3 逻辑备份（expdp/impdp）

# 导出
# 全库导出
expdp system/password full=Y directory=DATA_PUMP_DIR dumpfile=full_export.dmp logfile=full_export.log

# 按用户导出
expdp system/password schemas=scott directory=DATA_PUMP_DIR dumpfile=scott.dmp

# 按表导出
expdp scott/password tables=employees,dept directory=DATA_PUMP_DIR dumpfile=tables.dmp

# 按条件导出
expdp scott/password tables=employees query='"WHERE salary > 5000"' directory=DATA_PUMP_DIR dumpfile=emp_high.dmp

# 并行导出
expdp system/password schemas=scott directory=DATA_PUMP_DIR dumpfile=scott_%U.dmp parallel=4

# 导入
# 全库导入
impdp system/password full=Y directory=DATA_PUMP_DIR dumpfile=full_export.dmp

# 导入特定用户
impdp system/password schemas=scott directory=DATA_PUMP_DIR dumpfile=scott.dmp

# 表导入
impdp scott/password tables=employees directory=DATA_PUMP_DIR dumpfile=tables.dmp

# 表空间迁移
impdp system/password directory=DATA_PUMP_DIR dumpfile=transport.dmp transport_tablespaces=users

九、高可用架构

9.1 Data Guard

-- 主库配置
-- 启用强制日志
ALTER DATABASE FORCE LOGGING;

-- 开启归档模式
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- 创建备用日志组
ALTER DATABASE ADD STANDBY LOGFILE 
GROUP 4 '/u01/app/oracle/oradata/orcl/redo04.log' SIZE 200M,
GROUP 5 '/u01/app/oracle/oradata/orcl/redo05.log' SIZE 200M,
GROUP 6 '/u01/app/oracle/oradata/orcl/redo06.log' SIZE 200M;

-- 修改参数
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';
ALTER SYSTEM SET FAL_SERVER=standby;
ALTER SYSTEM SET FAL_CLIENT=primary;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

-- 创建密码文件
orapwd file=$ORACLE_HOME/dbs/orapwprimary password=oracle

-- 备库配置
-- 创建备库参数文件
CREATE PFILE FROM SPFILE;
-- 修改参数
*.DB_UNIQUE_NAME='standby'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.FAL_SERVER=primary
*.FAL_CLIENT=standby
*.STANDBY_FILE_MANAGEMENT=AUTO

-- 启动备库
STARTUP NOMOUNT;
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;

-- 启动日志应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

-- 切换角色
-- 主库切换到备库
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
SHUTDOWN ABORT;
STARTUP MOUNT;

-- 备库切换到主库
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE OPEN;

9.2 RAC（Real Application Clusters）

# 安装Grid Infrastructure
# 配置共享存储
# 创建ASM磁盘组

# 查看集群状态
crsctl stat res -t
crsctl stat cluster -all

# 查看RAC数据库状态
srvctl status database -d orcl

# 启动RAC数据库
srvctl start database -d orcl

# 停止RAC数据库
srvctl stop database -d orcl

# 查看实例信息
SELECT instance_name, host_name, status, version 
FROM v$instance;

-- 配置服务
srvctl add service -d orcl -s service_name -r "orcl1,orcl2"
srvctl start service -d orcl -s service_name

9.3 分区表

-- 范围分区
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
    PARTITION p_2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
    PARTITION p_2023_q3 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
    PARTITION p_2023_q4 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

-- 列表分区
CREATE TABLE customers (
    cust_id NUMBER,
    cust_name VARCHAR2(50),
    region VARCHAR2(20)
)
PARTITION BY LIST (region) (
    PARTITION p_north VALUES ('北京', '天津', '河北'),
    PARTITION p_south VALUES ('广东', '广西', '海南'),
    PARTITION p_east VALUES ('上海', '江苏', '浙江'),
    PARTITION p_other VALUES (DEFAULT)
);

-- 哈希分区
CREATE TABLE logs (
    log_id NUMBER,
    log_time TIMESTAMP,
    message CLOB
)
PARTITION BY HASH (log_id) PARTITIONS 16;

-- 复合分区
CREATE TABLE orders (
    order_id NUMBER,
    order_date DATE,
    region VARCHAR2(20),
    amount NUMBER
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE (
    SUBPARTITION p_north VALUES ('北京', '天津'),
    SUBPARTITION p_south VALUES ('广东', '广西'),
    SUBPARTITION p_other VALUES (DEFAULT)
) (
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

-- 分区管理
ALTER TABLE sales ADD PARTITION p_2024_q1 
VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD'));

ALTER TABLE sales DROP PARTITION p_2023_q1;

ALTER TABLE sales TRUNCATE PARTITION p_2023_q2;

ALTER TABLE sales MERGE PARTITIONS p_2023_q3, p_2023_q4 
INTO PARTITION p_2023_h2;

ALTER TABLE sales SPLIT PARTITION p_future 
AT (TO_DATE('2024-04-01', 'YYYY-MM-DD')) 
INTO (PARTITION p_2024_q1, PARTITION p_future);

来源：
https://app-agejuptkc5q9.appmiaoda.com/