七、性能优化
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);
