拓冰网站定制 
简介一份面向数据库运维、开发与架构师人群的Oracle性能优化PDF文档围绕大数据量、高并发场景下的调优难题从数据库服务器内存参数调整与SQL语句优化两条主线切入并延展至索引、分区、回滚段、执行计划控制以及操作系统、网络层面的性能影响因素。全篇为1个PDF文件压缩包仅128KB内容紧凑适合需要快速抓住调优重点的工程师阅读。文档重点讲解SGA内存参数设置共享池、数据缓冲区与日志缓冲区的合理取值与调整原则强调过大或过小都可能导致性能下降并给出系统内存为1G时共享池建议150M-200M、最高不超过500M的具体经验值同时给出基于规则优化器下驱动表选择、WHERE条件书写顺序、避免SELECT *、用WHERE替代HAVING等可落地的SQL写法。索引、分区策略、回滚段管理与执行计划引导部分可帮助读者从存储机制和执行路径层面进一步消除瓶颈整体形成从内存、语句到系统环境的优化思路。目前已有1500人学习下载适合需要系统梳理Oracle调优要点的初中级工程师参考。1. 遇到Oracle变慢先别调参先把时间耗在哪搞清楚遇到Oracle数据库变慢很多人的第一反应是加内存、改参数结果钱花了库还是慢。我在生产环境处理过不少这类问题得出的经验是oracle数据库性能优化真正的第一步不是调参而是先回答“时间到底耗在哪”。这份主题对应的是从诊断、参数、SQL到例行巡检的一整套可落地路径适合被慢SQL折磨的开发、需要扛住业务高峰的DBA以及接手一个没人维护的旧库想理清头绪的运维。优化不是玄学是把排队问题一个个解开。数据库变慢本质上就是两类事要么某个等待特别长要么某类SQL消耗了绝大多数资源。下面按我实际排查的顺序展开每一步都能直接照着做也能帮你判断别人给的调优方案靠不靠谱。2. 拿到一份慢库先做诊断三件事别急着改参数接手一个生产库的第一天我最忌讳的就是直接翻出ALTER SYSTEM改参数。Oracle性能优化不是撞运气而是先回答三个问题瓶颈在CPU还是IO、哪类等待事件在排队、Top SQL是谁。这三件事做完方案基本就出来了。2.1 用AWR报告定位瓶颈先看哪几个段AWRAutomatic Workload Repository是Oracle内置的体检报告默认每小时自动生成一次快照并保留8天。平时觉得库慢可以先手动生成一份当前时段的AWR报告用系统脚本就行。-- 以DBA身份登录调用awrrpt脚本生成报告 SQL ?/rdbms/admin/awrrpt.sql -- 按提示依次输入报告类型html/text、过去天数、起始快照号、结束快照号 -- 生成的HTML报告可以直接用浏览器打开便于定位瓶颈逻辑说明?/rdbms/admin/awrrpt.sql里的?指$ORACLE_HOME脚本会读取dba_hist_snapshot中的历史快照把你指定的两个快照之间的负载汇总成报告。报告类型选HTML是因为它自带表格和链接点起来比文本方便得多。AWR报告很长不要从头翻到尾。我一般只看四个段按顺序记在下面AWR报告段落看什么异常信号Reports SummaryDB Time与Elapsed TimeDB Time接近Elapsed Time说明CPU满载Top 10 Foreground Events by Total Wait Time等待事件占比单一事件占比超过30%要深挖SQL ordered by Elapsed Time最耗时的SQL清单前几条占DB Time的70%以上Instance Activity每秒事务数、解析次数、物理读每秒物理读持续几千次通常有大问题提示生产环境生成AWR本身有轻微开销不要在业务高峰反复跑。一份报告覆盖1到2小时就够了跨度太长反而看不出瞬时的峰值问题。如果发现DB Time远大于Elapsed Time说明CPU已经被吃满核心矛盾在SQL或内部竞争如果DB Time很小但用户还是觉得慢问题更可能在应用层或网络不在数据库本身。这个判断决定了你后面要不要动数据库避免给Oracle乱扣帽子。2.2 等待事件与Top SQL把排队翻译成“改哪里”AWR的等待事件部分是最直接的诊断入口。我常跟团队说等待事件就是数据库在跟你说“我卡在哪个环节了”不用猜。常见的几类要记住db file sequential read代表单块读多半是索引回表太勤db file scattered read代表多块读多为全表扫描log file sync表示提交太慢跟写日志和磁盘有关enq: TX - row lock contention则是行锁竞争通常指向并发事务打同一行。只看AWR里的汇总还不够实时排查慢SQL要用v$sql。这个视图里存着当前实例里所有仍在内存中的SQL执行统计按累计耗时排序就能抓到“大胃王”。-- 找出累计执行时间最长的前10条SQL12c及以上 SELECT sql_id, executions, ROUND(elapsed_time / 1000000, 2) AS elapsed_sec, ROUND(cpu_time / 1000000, 2) AS cpu_sec, buffer_gets, disk_reads FROM v$sql WHERE elapsed_time 1000000 ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;参数说明elapsed_time是这条SQL从解析到执行结束的累计微秒数executions是执行次数。如果一条SQL执行了十万次每次0.01秒累计也能排到前面但它并不是最值得优化的对象。我一般会再用elapsed_time / executions算单次平均耗时优先处理“单次又慢、执行次数又多”的SQL。11g及更早版本没有FETCH FIRST改成WHERE ROWNUM 10包一层子查询即可。拿到sql_id后可以直接用它去生成执行计划也可以到AWR报告的SQL段里查它的历史表现。2.3 基线快照留好优化前后对比的后悔药很多人一上来就改改完发现更慢却拿不出“改之前到底什么样”的证据。所以我接手任何库第一件事就是打快照、做基线。这是性价比最高的动作相当于给自己留一颗后悔药。-- 优化前手动打一个快照 EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); -- 把100到110号快照固定为基线 EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE( start_snap_id 100, end_snap_id 110, baseline_name BEFORE_OPT_20250120);逻辑说明CREATE_SNAPSHOT是主动触发一次负载采集采完才能对比。CREATE_BASELINE把指定范围内的快照包成一条基线日后可以在AWR页签里把当前负载同基线并排对比直接看DB Time和Top SQL的变化。参数说明start_snap_id、end_snap_id要取自dba_hist_snapshot建议前后覆盖一个完整的业务周期baseline_name命名要带日期和目的我习惯写成BEFORE_OPT_日期这种格式避免三个月后认不出来。优化做完后再打一次快照、生成对比报告。如果DB Time降了、Top SQL的单次耗时降了说明优化有效如果没降至少知道要回退到基线状态而不是继续盲目加参数。这个习惯在夜间批处理优化里尤其有用因为批处理的性能波动往往要到第二天白天才会暴露。3. 参数与内存调优SGA/PGA怎么给才算合适诊断做完如果等待事件指向内存类问题或者Top SQL显示共享池命中率异常才轮到参数调优。Oracle的内存参数不是越大越好给多了反而可能引起内存换页和频繁的GC式内部操作这是新手最容易踩的坑。3.1 SGA与PGA的配比逻辑先从命中率说起SGA包含数据库缓冲区、共享池、大池、Redo日志缓冲区等PGA则服务于排序、哈希连接和PL/SQL运行。OLTP和OLAP的配比完全不同别拿一套公式套所有库。-- 查看当前SGA/PGA配置 SHOW PARAMETER sga_target; SHOW PARAMETER pga_aggregate_target; -- 直接看关键计数器 SELECT name, value FROM v$sysstat WHERE name IN (physical reads, consistent gets, db block gets);逻辑说明physical reads是实际从磁盘读的块数consistent gets与db block gets是内存中的逻辑读。命中率的大致算法是1 - physical_reads / (consistent_gets db_block_gets)AWR报告里会直接给出但我不建议把它当唯一指标。很多走索引的SQL命中率极高依然慢因为慢在单块读的物理IO次数太多。我常用的配比参考物理内存典型SGA_TARGET典型PGA_AGGREGATE_TARGET32G12G16G4G6G64G24G32G8G12G128G64G左右16G左右SGA和PGA加起来不要超过物理内存的70%到80%剩下留给操作系统文件缓存和网络栈。如果跑的是高并发OLTPPGA就可以往下压如果是报表分析型负载PGA要适当多给。血泪经验是PGA给少了排序会落临时表空间慢起来比磁盘读还可怕给多了又会挤占SGA导致缓冲命中率下降。3.2 自动内存管理与手动调整的取舍11g引入的MEMORY_TARGET让Oracle自动调配SGA和PGA看起来省心但在生产环境我通常选择半自动或全手动。原因有两个一是Linux下配置HugePages时自动内存管理和大页存在相互排斥启动容易报ORA-27102二是自动管理在你制定了SGA目标后并不会主动帮你优化等待事件出了问题反而更难排查。-- 关闭自动内存管理改用半自动AUM 手动PGA目标 ALTER SYSTEM SET memory_target0 SCOPESPFILE; ALTER SYSTEM SET sga_target16G SCOPESPFILE; ALTER SYSTEM SET pga_aggregate_target4G SCOPESPFILE; ALTER SYSTEM SET pga_aggregate_limit8G SCOPESPFILE;参数说明memory_target0表示关闭自动内存管理的总开关这个改动必须在SPFILE里重启实例生效。sga_target是SGA的上限动态目标pga_aggregate_target是PGA的软目标pga_aggregate_limit是PGA的硬上限可以防止某个极端会话把PGA撑爆。改完之后检查一下大页配置和锁定限制# 查看当前大页分配情况 grep HugePages_Total /proc/meminfo grep HugePages_Free /proc/meminfo如果HugePages_Free长期很低说明SGA锁占了大页但没被充分利用常见原因是shared_pool或buffer cache分配过大。Oracle的SGA要锁在大页里数量不够时进程启动会直接失败。配置大页是Linux下Oracle调优绕不开的一关别只看参数好看落地时要把/etc/security/limits.conf的memlock一起调到位。注意改sga_target属于静态参数要重启实例才生效。重启前一定先备份SPFILE生成一份PFILE作为启动兜底避免参数写错后连库都起不来。3.3 常用性能参数的查看与修改命令参数调优不是背命令而是要有自己的“检查单”。我上任何一台生产库都会先把这几个参数过一遍-- 查看核心性能参数 SHOW PARAMETER shared_pool_size; SHOW PARAMETER db_cache_size; SHOW PARAMETER open_cursors; SHOW PARAMETER session_cached_cursors; SHOW PARAMETER cursor_sharing; SHOW PARAMETER optimizer_index_cost_adj;这些参数里open_cursors和session_cached_cursors与游标复用直接相关OLTP库里打开游标过多会导致共享池碎片和解析压力。通常我会把session_cached_cursors调到100到300之间具体看会话数。ALTER SYSTEM SET open_cursors1000 SCOPEBOTH; ALTER SYSTEM SET session_cached_cursors200 SCOPEBOTH;说明SCOPEBOTH表示立即生效并写入SPFILE但只适用于动态参数。像shared_pool_size这类静态参数SCOPE只能写SPFILE等重启生效。optimizer_index_cost_adj我一般不动除非执行计划已经明显偏向全表扫描而索引应该更快这个参数会整体缩放索引访问的代价改不好会让所有SQL的计划乱套。参数影响生效方式sga_targetSGA总大小重启pga_aggregate_target排序/哈希内存动态pga_aggregate_limitPGA硬上限动态open_cursors单会话游标上限动态session_cached_cursors会话游标缓存动态血的教训是不要一次改多个参数。我见过同事一口气改了六个参数重启后跑批性能掉了20%根本不知道是哪条改出来的问题。正确的做法是一次只改一个观察一个业务周期用AWR基线对比得出结论后再动下一个。4. SQL优化一条慢SQL从定位到改写的关键步骤参数没问题、内存也正常那慢SQL十有八九是执行计划走偏。Oracle性能优化到了这一步重心从数据库转向SQL本身。一条慢SQL的优化核心动作就三个看清真实执行计划、检查索引是否能用上、判断统计信息是否过期。4.1 执行计划怎么看从全表扫描到索引选择的判断执行计划是Oracle对这条SQL的操作蓝图。先解释一条还没跑的SQL用EXPLAIN PLAN FOR是新手最容易上手的办法EXPLAIN PLAN FOR SELECT order_id, cust_id, amount FROM order_t WHERE cust_id 100 AND create_time DATE2025-01-01; -- 展示刚才生成的执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());逻辑说明EXPLAIN PLAN FOR会把计划写入计划表DBMS_XPLAN.DISPLAY()负责格式化输出。但要注意它展示的是“估算”计划实际执行时会受绑定变量窥探、统计信息影响计划和预算往往对不上。生产环境我建议直接看真实执行计划-- 通过AWR或v$sql拿到sql_id查真实执行计划和实际行数 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(b7x2k9r1a3m4n, 0, ALLSTATS LAST));参数说明三个参数分别是sql_id、child_cursor_number和格式化选项。ALLSTATS LAST会输出实际执行次数、读取行数A-Rows、缓冲块数Buffers。重点比较E-Rows估算行和A-Rows实际行。两者如果差两个数量级这个执行计划基本废了原因大概率是统计信息过期或数据倾斜。看到执行计划里的TABLE ACCESS FULL先别急着骂全表扫描。小表全表扫描可能比走索引更划算真正要警惕的是大表上的全表扫描尤其是db file scattered read等待事件大量出现时。判断标准是过滤条件能筛掉90%以上数据且表在千万行级别还不走索引那才需要干预。4.2 索引设计与隐式转换让SQL能用上索引的常见细节索引建了SQL还是不用最常见的原因就是隐式转换。Oracle的规则是比较时如果两端类型不一致它会先把列本身转成目标类型这就导致列上隐式套了一层函数索引直接失效。网上常见的“oracle 过滤不可转为数字的字符串”问题就是这么来的。-- mobile_num是varchar2类型这里和数字比较Oracle会隐式转数字 -- 列上套了转换函数后索引不生效还会对全表逐行尝试to_number SELECT * FROM user_info WHERE mobile_num 13800138000; -- 正确的写法右边也写成字符串 SELECT * FROM user_info WHERE mobile_num 13800138000;类似的坑在日期列上更隐蔽。很多人习惯先把日期转成字符串再比较这是在列上套函数索引照样失效-- 坏写法create_time列上套了TO_CHAR无法走索引 SELECT * FROM order_t WHERE TO_CHAR(create_time,YYYY-MM-DD) 2025-01-01; -- 好写法使用范围条件让优化器可以走索引 SELECT * FROM order_t WHERE create_time DATE2025-01-01 AND create_time DATE2025-01-02;参数说明写成范围条件后列上没有函数索引范围扫描才可能被选中。DATE2025-01-01是SQL标准的日期字面量比to_date(2025-01-01,yyyy-mm-dd)短且不容易写错。设计组合索引时等值条件的列放前面范围条件放后面这是基本规则。至于Oracle内置函数能用的就用别在SQL里写一堆自造函数比如过滤字符串就用REGEXP_LIKE、截取就用SUBSTR这些内置函数的执行计划可控性比自定义函数强太多。存储过程里如果靠字符串拼SQL再EXECUTE IMMEDIATE每次都要硬解析能改成绑定变量就绑。4.3 分页查询与统计信息OLTP里最容易翻车的两个点oracle分页的热门程度和翻车率一样高。12c开始提供OFFSET FETCH语法写起来很舒服但深分页会把前面的所有行都扫一遍体验极差-- 12c 写法页码越深越慢OFFSET会扫过前面20万行 SELECT * FROM big_table ORDER BY id OFFSET 200000 ROWS FETCH NEXT 20 ROWS ONLY;参数说明OFFSET指定跳过行数FETCH NEXT 20 ROWS ONLY是取20行。优化器为了拿到这20行得先把前20万行排好序再丢掉代价随页码线性增长。对前端分页跳页的场景我的推荐是限制最大页数或用基于主键的分页方案-- keyset分页记住上一页最后一条记录的id性能稳定 SELECT * FROM big_table WHERE id :last_id ORDER BY id FETCH NEXT 20 ROWS ONLY;说明last_id是上一页最后一条的id靠它定位起点只扫描目标区间页码多深都不会额外放大开销。适合下拉加载、列表滚动这类场景如果是那种必须直接跳转到第10000页的报表建议后端限制最大可跳页数别硬刚硬刚一般刚不过这个数据量级别的排序。统计信息是执行计划的地基。表数据从100万涨到2000万统计信息没刷新优化器还用老基数算成本执行计划自然走偏。手动收集的常用命令-- 收集单表统计信息及索引统计并自动生成直方图 EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname APP, tabname BIG_TABLE, cascade TRUE, estimate_percent DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt FOR ALL COLUMNS SIZE AUTO);参数说明cascade TRUE表示同时收集索引统计信息estimate_percent用AUTO_SAMPLE_SIZE让Oracle自己决定采样比例10亿行的大表也能接受method_opt里的FOR ALL COLUMNS SIZE AUTO会让Oracle对有数据倾斜的列自动建直方图避免等值条件估算偏差过大。收集操作有开销不要在业务高峰对大表硬跑。更稳妥的做法是依赖11g以上的自动统计信息收集任务并定期检查last_analyzed是否过期。这个检查动作我会每天看一次放到下文的巡检习惯里。5. 避坑性能优化中常见的5个翻车现场优化做得越多越发现真正的难题不是不知道技巧而是踩坑时没法快速回退。下面这五个场景都是我见过或处理过的真实翻车现场按“现象 → 原因 → 解决”写出遇到同类问题可以直接照着解。5.1 现象改了参数后实例启动失败现象执行完ALTER SYSTEM SET sga_target24G SCOPESPFILE后重启实例起不来报ORA-27102: out of memory或ORA-00845: MEMORY_TARGET not supported on this system。原因SGA目标超过物理内存扣除内核占用后的可用量或者Linux启用了HugePages但没有分配足够的大页导致进程无法锁定所需内存。解决先备份PFILE再改参数给每次调参留后悔药。-- 修改前先生成PFILE备份 CREATE PFILE/tmp/pfile_before_opt.ora FROM SPFILE; -- 若启动失败用备份PFILE拉起实例 STARTUP PFILE/tmp/pfile_before_opt.ora; -- 起来后修正参数重新生成SPFILE CREATE SPFILE/u01/app/oracle/product/19c/dbs/spfileorcl.ora FROM PFILE/tmp/pfile_before_opt.ora;提醒一下HugePages不是单靠sga_target就能配好的还要同步检查/etc/sysctl.conf里的vm.nr_hugepages和用户memlock上限。5.2 现象加了索引反而更慢现象某条SQL执行慢你以为没索引加完复合索引后执行时间反而从3秒变成8秒。看执行计划发现Oracle真的用了新索引但table access by index rowid的回表次数高得吓人。原因索引的选择性太差。比如在status列上建索引这个列只有3个不同值其中某个值占了90%的行优化器根据统计信息估算后仍然选了索引范围扫描结果每拿到一个rowid就回一次表物理读比全表扫描还多。解决先看列基数区分度低的列不要建索引。执行计划已经走偏时可以先用Hint验证回表成本确认索引确实不合适就直接删掉。核心业务SQL还可以建SQL计划基线SPM固定住正确计划防止优化器在统计信息波动时再换到坏路上。5.3 现象AWR报告里全是log file sync现象业务高峰期事务量不大但响应时间突然变长。AWR的Top等待事件里log file sync占比超过40%CPU利用率却不高磁盘IO也不饱和。原因log file sync是前台会话等待Redo日志写入磁盘的时间。常见诱因是应用每处理一条数据就COMMIT一次或者日志文件和其他高IO文件放在同一块磁盘上导致写日志排队。解决先改应用端的提交频率把循环里的单条提交改成批量提交。再确认Redo日志是否放在独立的SSD上log_buffer是否太小。注意别病急乱投医把log_buffer调到好几G这个参数过大并不能线性减少log file sync反而浪费SGA空间。合理的做法是把日志组分布到不同磁盘并检查ARCHIVELOG归档进程是否拖慢了写日志。5.4 现象sqlplus登录oracle数据库出现缓慢现象网络没问题监听lsnrctl status正常但sqlplus user/pwdhost:1521/orcl要等十几秒才能连上连上后操作速度正常。原因客户端连接时监听器做DNS反解超时或者listener.log长期不清理导致监听处理变慢。这个问题常被误判成数据库负载高白白做了一轮性能优化。解决在sqlnet.ora里关掉多余的反解和日志增长约速。# sqlnet.ora SQLNET.INBOUND_CONNECT_TIMEOUT5 NAMES.DIRECTORY_PATH(TNSNAMES,EZCONNECT)说明SQLNET.INBOUND_CONNECT_TIMEOUT控制连接建立的超时上限NAMES.DIRECTORY_PATH限定名字解析只走TNSNAMES和EZCONNECT避免依赖系统DNS。监听日志如果单文件已经到好几个G停监听、轮转日志后重启监听即可。5.5 现象统计信息过期导致执行计划走偏现象应用上线时SQL很快跑了两周后突然变慢。AWR里能看到执行计划里全表扫描但表上明明有索引。原因统计信息没跟上数据变化。比如表从100万行涨到2000万行自动收集任务没在维护窗口内跑完优化器还拿旧的行数和数据分布估算成本。解决先查统计信息的最后收集时间确认是否过期。SELECT table_name, last_analyzed, stale_stats FROM dba_tab_statistics WHERE owner APP AND table_name ORDER_T;stale_stats为YES时直接手动收集EXEC DBMS_STATS.GATHER_TABLE_STATS(APP,ORDER_T, CASCADETRUE);如果这个问题反复出现给核心表固定收集窗口或对经常被误估的列单独收集直方图。统计信息调优是慢SQL排查里最容易被忽略的一环很多“SQL昨天还好好的”都是它惹的祸。6. 把优化做成例行机制两个能落地的习惯性能优化不是一次性救火而是周期性维护。到了这一步工具、参数、SQL技巧都有了剩下的是怎么让优化成果不倒退。第一个习惯是固定月度的AWR基线对比。我每月初会创建一个覆盖上个月全业务的基线模板用系统级的自动化任务生成避免手工选快照号。BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE( start_time TO_TIMESTAMP(2025-02-01 00:00:00, YYYY-MM-DD HH24:MI:SS), end_time TO_TIMESTAMP(2025-02-28 23:59:59, YYYY-MM-DD HH24:MI:SS), baseline_name BASELINE_FEB_2025 ); END; /参数说明start_time和end_time指定基线的起止范围模板会自动把区间内的快照包成基线下个月再建一条新基线两条并排放到 [AWR Baselines] 页签里对比。这个动作做三个月你就能把业务增长、SQL退化与版本变更关联起来而不是每次都从零排查。第二个习惯是每天看一条“Top SQL趋势”查询替代每天翻AWR。我常用一条SQL从dba_hist_sqlstat里捞近7天平均单次执行最慢的20条SQL。SELECT s.sql_id, SUM(s.executions_delta) AS exec_count, ROUND(SUM(s.elapsed_time_delta) / 1000000, 2) AS total_sec, ROUND(SUM(s.elapsed_time_delta) / NULLIF(SUM(s.executions_delta), 0) / 1000000, 4) AS avg_sec FROM dba_hist_sqlstat s, dba_hist_snapshot sn WHERE s.snap_id sn.snap_id AND sn.begin_interval_time SYSDATE - 7 GROUP BY s.sql_id HAVING SUM(s.executions_delta) 0 ORDER BY avg_sec DESC FETCH FIRST 20 ROWS ONLY;逻辑说明dba_hist_sqlstat存的是每个快照区间内SQL执行统计的增量executions_delta和elapsed_time_delta是区间内的变化量。用NULLIF防止除零按avg_sec排序才能筛出“真正慢”的SQL而不是累计执行时间大但单次很快的SQL。我自己值班的习惯是每周一早上先跑这条SQL再花五分钟过一遍上周的月度基线新出现的Top SQL或平均耗时突然翻倍的SQL就会暴露在周报里。曾经有一张订单表就是被这条SQL在周一一早抓出来提前发现了统计信息过期的问题免得业务投诉之后再去救火。优化工具再多也比不上一个固定的检查和复盘节奏。希望这篇文章的方法论能帮你在自己的库里少走点弯路。本文还有配套的精品资源点击获取 Oracle性能优化实战：先诊断再调参，AWR+SQL优化让数据库变快
简介一份面向数据库运维、开发与架构师人群的Oracle性能优化PDF文档围绕大数据量、高并发场景下的调优难题从数据库服务器内存参数调整与SQL语句优化两条主线切入并延展至索引、分区、回滚段、执行计划控制以及操作系统、网络层面的性能影响因素。全篇为1个PDF文件压缩包仅128KB内容紧凑适合需要快速抓住调优重点的工程师阅读。文档重点讲解SGA内存参数设置共享池、数据缓冲区与日志缓冲区的合理取值与调整原则强调过大或过小都可能导致性能下降并给出系统内存为1G时共享池建议150M-200M、最高不超过500M的具体经验值同时给出基于规则优化器下驱动表选择、WHERE条件书写顺序、避免SELECT *、用WHERE替代HAVING等可落地的SQL写法。索引、分区策略、回滚段管理与执行计划引导部分可帮助读者从存储机制和执行路径层面进一步消除瓶颈整体形成从内存、语句到系统环境的优化思路。目前已有1500人学习下载适合需要系统梳理Oracle调优要点的初中级工程师参考。1. 遇到Oracle变慢先别调参先把时间耗在哪搞清楚遇到Oracle数据库变慢很多人的第一反应是加内存、改参数结果钱花了库还是慢。我在生产环境处理过不少这类问题得出的经验是oracle数据库性能优化真正的第一步不是调参而是先回答“时间到底耗在哪”。这份主题对应的是从诊断、参数、SQL到例行巡检的一整套可落地路径适合被慢SQL折磨的开发、需要扛住业务高峰的DBA以及接手一个没人维护的旧库想理清头绪的运维。优化不是玄学是把排队问题一个个解开。数据库变慢本质上就是两类事要么某个等待特别长要么某类SQL消耗了绝大多数资源。下面按我实际排查的顺序展开每一步都能直接照着做也能帮你判断别人给的调优方案靠不靠谱。2. 拿到一份慢库先做诊断三件事别急着改参数接手一个生产库的第一天我最忌讳的就是直接翻出ALTER SYSTEM改参数。Oracle性能优化不是撞运气而是先回答三个问题瓶颈在CPU还是IO、哪类等待事件在排队、Top SQL是谁。这三件事做完方案基本就出来了。2.1 用AWR报告定位瓶颈先看哪几个段AWRAutomatic Workload Repository是Oracle内置的体检报告默认每小时自动生成一次快照并保留8天。平时觉得库慢可以先手动生成一份当前时段的AWR报告用系统脚本就行。-- 以DBA身份登录调用awrrpt脚本生成报告 SQL ?/rdbms/admin/awrrpt.sql -- 按提示依次输入报告类型html/text、过去天数、起始快照号、结束快照号 -- 生成的HTML报告可以直接用浏览器打开便于定位瓶颈逻辑说明?/rdbms/admin/awrrpt.sql里的?指$ORACLE_HOME脚本会读取dba_hist_snapshot中的历史快照把你指定的两个快照之间的负载汇总成报告。报告类型选HTML是因为它自带表格和链接点起来比文本方便得多。AWR报告很长不要从头翻到尾。我一般只看四个段按顺序记在下面AWR报告段落看什么异常信号Reports SummaryDB Time与Elapsed TimeDB Time接近Elapsed Time说明CPU满载Top 10 Foreground Events by Total Wait Time等待事件占比单一事件占比超过30%要深挖SQL ordered by Elapsed Time最耗时的SQL清单前几条占DB Time的70%以上Instance Activity每秒事务数、解析次数、物理读每秒物理读持续几千次通常有大问题提示生产环境生成AWR本身有轻微开销不要在业务高峰反复跑。一份报告覆盖1到2小时就够了跨度太长反而看不出瞬时的峰值问题。如果发现DB Time远大于Elapsed Time说明CPU已经被吃满核心矛盾在SQL或内部竞争如果DB Time很小但用户还是觉得慢问题更可能在应用层或网络不在数据库本身。这个判断决定了你后面要不要动数据库避免给Oracle乱扣帽子。2.2 等待事件与Top SQL把排队翻译成“改哪里”AWR的等待事件部分是最直接的诊断入口。我常跟团队说等待事件就是数据库在跟你说“我卡在哪个环节了”不用猜。常见的几类要记住db file sequential read代表单块读多半是索引回表太勤db file scattered read代表多块读多为全表扫描log file sync表示提交太慢跟写日志和磁盘有关enq: TX - row lock contention则是行锁竞争通常指向并发事务打同一行。只看AWR里的汇总还不够实时排查慢SQL要用v$sql。这个视图里存着当前实例里所有仍在内存中的SQL执行统计按累计耗时排序就能抓到“大胃王”。-- 找出累计执行时间最长的前10条SQL12c及以上 SELECT sql_id, executions, ROUND(elapsed_time / 1000000, 2) AS elapsed_sec, ROUND(cpu_time / 1000000, 2) AS cpu_sec, buffer_gets, disk_reads FROM v$sql WHERE elapsed_time 1000000 ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;参数说明elapsed_time是这条SQL从解析到执行结束的累计微秒数executions是执行次数。如果一条SQL执行了十万次每次0.01秒累计也能排到前面但它并不是最值得优化的对象。我一般会再用elapsed_time / executions算单次平均耗时优先处理“单次又慢、执行次数又多”的SQL。11g及更早版本没有FETCH FIRST改成WHERE ROWNUM 10包一层子查询即可。拿到sql_id后可以直接用它去生成执行计划也可以到AWR报告的SQL段里查它的历史表现。2.3 基线快照留好优化前后对比的后悔药很多人一上来就改改完发现更慢却拿不出“改之前到底什么样”的证据。所以我接手任何库第一件事就是打快照、做基线。这是性价比最高的动作相当于给自己留一颗后悔药。-- 优化前手动打一个快照 EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); -- 把100到110号快照固定为基线 EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE( start_snap_id 100, end_snap_id 110, baseline_name BEFORE_OPT_20250120);逻辑说明CREATE_SNAPSHOT是主动触发一次负载采集采完才能对比。CREATE_BASELINE把指定范围内的快照包成一条基线日后可以在AWR页签里把当前负载同基线并排对比直接看DB Time和Top SQL的变化。参数说明start_snap_id、end_snap_id要取自dba_hist_snapshot建议前后覆盖一个完整的业务周期baseline_name命名要带日期和目的我习惯写成BEFORE_OPT_日期这种格式避免三个月后认不出来。优化做完后再打一次快照、生成对比报告。如果DB Time降了、Top SQL的单次耗时降了说明优化有效如果没降至少知道要回退到基线状态而不是继续盲目加参数。这个习惯在夜间批处理优化里尤其有用因为批处理的性能波动往往要到第二天白天才会暴露。3. 参数与内存调优SGA/PGA怎么给才算合适诊断做完如果等待事件指向内存类问题或者Top SQL显示共享池命中率异常才轮到参数调优。Oracle的内存参数不是越大越好给多了反而可能引起内存换页和频繁的GC式内部操作这是新手最容易踩的坑。3.1 SGA与PGA的配比逻辑先从命中率说起SGA包含数据库缓冲区、共享池、大池、Redo日志缓冲区等PGA则服务于排序、哈希连接和PL/SQL运行。OLTP和OLAP的配比完全不同别拿一套公式套所有库。-- 查看当前SGA/PGA配置 SHOW PARAMETER sga_target; SHOW PARAMETER pga_aggregate_target; -- 直接看关键计数器 SELECT name, value FROM v$sysstat WHERE name IN (physical reads, consistent gets, db block gets);逻辑说明physical reads是实际从磁盘读的块数consistent gets与db block gets是内存中的逻辑读。命中率的大致算法是1 - physical_reads / (consistent_gets db_block_gets)AWR报告里会直接给出但我不建议把它当唯一指标。很多走索引的SQL命中率极高依然慢因为慢在单块读的物理IO次数太多。我常用的配比参考物理内存典型SGA_TARGET典型PGA_AGGREGATE_TARGET32G12G16G4G6G64G24G32G8G12G128G64G左右16G左右SGA和PGA加起来不要超过物理内存的70%到80%剩下留给操作系统文件缓存和网络栈。如果跑的是高并发OLTPPGA就可以往下压如果是报表分析型负载PGA要适当多给。血泪经验是PGA给少了排序会落临时表空间慢起来比磁盘读还可怕给多了又会挤占SGA导致缓冲命中率下降。3.2 自动内存管理与手动调整的取舍11g引入的MEMORY_TARGET让Oracle自动调配SGA和PGA看起来省心但在生产环境我通常选择半自动或全手动。原因有两个一是Linux下配置HugePages时自动内存管理和大页存在相互排斥启动容易报ORA-27102二是自动管理在你制定了SGA目标后并不会主动帮你优化等待事件出了问题反而更难排查。-- 关闭自动内存管理改用半自动AUM 手动PGA目标 ALTER SYSTEM SET memory_target0 SCOPESPFILE; ALTER SYSTEM SET sga_target16G SCOPESPFILE; ALTER SYSTEM SET pga_aggregate_target4G SCOPESPFILE; ALTER SYSTEM SET pga_aggregate_limit8G SCOPESPFILE;参数说明memory_target0表示关闭自动内存管理的总开关这个改动必须在SPFILE里重启实例生效。sga_target是SGA的上限动态目标pga_aggregate_target是PGA的软目标pga_aggregate_limit是PGA的硬上限可以防止某个极端会话把PGA撑爆。改完之后检查一下大页配置和锁定限制# 查看当前大页分配情况 grep HugePages_Total /proc/meminfo grep HugePages_Free /proc/meminfo如果HugePages_Free长期很低说明SGA锁占了大页但没被充分利用常见原因是shared_pool或buffer cache分配过大。Oracle的SGA要锁在大页里数量不够时进程启动会直接失败。配置大页是Linux下Oracle调优绕不开的一关别只看参数好看落地时要把/etc/security/limits.conf的memlock一起调到位。注意改sga_target属于静态参数要重启实例才生效。重启前一定先备份SPFILE生成一份PFILE作为启动兜底避免参数写错后连库都起不来。3.3 常用性能参数的查看与修改命令参数调优不是背命令而是要有自己的“检查单”。我上任何一台生产库都会先把这几个参数过一遍-- 查看核心性能参数 SHOW PARAMETER shared_pool_size; SHOW PARAMETER db_cache_size; SHOW PARAMETER open_cursors; SHOW PARAMETER session_cached_cursors; SHOW PARAMETER cursor_sharing; SHOW PARAMETER optimizer_index_cost_adj;这些参数里open_cursors和session_cached_cursors与游标复用直接相关OLTP库里打开游标过多会导致共享池碎片和解析压力。通常我会把session_cached_cursors调到100到300之间具体看会话数。ALTER SYSTEM SET open_cursors1000 SCOPEBOTH; ALTER SYSTEM SET session_cached_cursors200 SCOPEBOTH;说明SCOPEBOTH表示立即生效并写入SPFILE但只适用于动态参数。像shared_pool_size这类静态参数SCOPE只能写SPFILE等重启生效。optimizer_index_cost_adj我一般不动除非执行计划已经明显偏向全表扫描而索引应该更快这个参数会整体缩放索引访问的代价改不好会让所有SQL的计划乱套。参数影响生效方式sga_targetSGA总大小重启pga_aggregate_target排序/哈希内存动态pga_aggregate_limitPGA硬上限动态open_cursors单会话游标上限动态session_cached_cursors会话游标缓存动态血的教训是不要一次改多个参数。我见过同事一口气改了六个参数重启后跑批性能掉了20%根本不知道是哪条改出来的问题。正确的做法是一次只改一个观察一个业务周期用AWR基线对比得出结论后再动下一个。4. SQL优化一条慢SQL从定位到改写的关键步骤参数没问题、内存也正常那慢SQL十有八九是执行计划走偏。Oracle性能优化到了这一步重心从数据库转向SQL本身。一条慢SQL的优化核心动作就三个看清真实执行计划、检查索引是否能用上、判断统计信息是否过期。4.1 执行计划怎么看从全表扫描到索引选择的判断执行计划是Oracle对这条SQL的操作蓝图。先解释一条还没跑的SQL用EXPLAIN PLAN FOR是新手最容易上手的办法EXPLAIN PLAN FOR SELECT order_id, cust_id, amount FROM order_t WHERE cust_id 100 AND create_time DATE2025-01-01; -- 展示刚才生成的执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());逻辑说明EXPLAIN PLAN FOR会把计划写入计划表DBMS_XPLAN.DISPLAY()负责格式化输出。但要注意它展示的是“估算”计划实际执行时会受绑定变量窥探、统计信息影响计划和预算往往对不上。生产环境我建议直接看真实执行计划-- 通过AWR或v$sql拿到sql_id查真实执行计划和实际行数 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(b7x2k9r1a3m4n, 0, ALLSTATS LAST));参数说明三个参数分别是sql_id、child_cursor_number和格式化选项。ALLSTATS LAST会输出实际执行次数、读取行数A-Rows、缓冲块数Buffers。重点比较E-Rows估算行和A-Rows实际行。两者如果差两个数量级这个执行计划基本废了原因大概率是统计信息过期或数据倾斜。看到执行计划里的TABLE ACCESS FULL先别急着骂全表扫描。小表全表扫描可能比走索引更划算真正要警惕的是大表上的全表扫描尤其是db file scattered read等待事件大量出现时。判断标准是过滤条件能筛掉90%以上数据且表在千万行级别还不走索引那才需要干预。4.2 索引设计与隐式转换让SQL能用上索引的常见细节索引建了SQL还是不用最常见的原因就是隐式转换。Oracle的规则是比较时如果两端类型不一致它会先把列本身转成目标类型这就导致列上隐式套了一层函数索引直接失效。网上常见的“oracle 过滤不可转为数字的字符串”问题就是这么来的。-- mobile_num是varchar2类型这里和数字比较Oracle会隐式转数字 -- 列上套了转换函数后索引不生效还会对全表逐行尝试to_number SELECT * FROM user_info WHERE mobile_num 13800138000; -- 正确的写法右边也写成字符串 SELECT * FROM user_info WHERE mobile_num 13800138000;类似的坑在日期列上更隐蔽。很多人习惯先把日期转成字符串再比较这是在列上套函数索引照样失效-- 坏写法create_time列上套了TO_CHAR无法走索引 SELECT * FROM order_t WHERE TO_CHAR(create_time,YYYY-MM-DD) 2025-01-01; -- 好写法使用范围条件让优化器可以走索引 SELECT * FROM order_t WHERE create_time DATE2025-01-01 AND create_time DATE2025-01-02;参数说明写成范围条件后列上没有函数索引范围扫描才可能被选中。DATE2025-01-01是SQL标准的日期字面量比to_date(2025-01-01,yyyy-mm-dd)短且不容易写错。设计组合索引时等值条件的列放前面范围条件放后面这是基本规则。至于Oracle内置函数能用的就用别在SQL里写一堆自造函数比如过滤字符串就用REGEXP_LIKE、截取就用SUBSTR这些内置函数的执行计划可控性比自定义函数强太多。存储过程里如果靠字符串拼SQL再EXECUTE IMMEDIATE每次都要硬解析能改成绑定变量就绑。4.3 分页查询与统计信息OLTP里最容易翻车的两个点oracle分页的热门程度和翻车率一样高。12c开始提供OFFSET FETCH语法写起来很舒服但深分页会把前面的所有行都扫一遍体验极差-- 12c 写法页码越深越慢OFFSET会扫过前面20万行 SELECT * FROM big_table ORDER BY id OFFSET 200000 ROWS FETCH NEXT 20 ROWS ONLY;参数说明OFFSET指定跳过行数FETCH NEXT 20 ROWS ONLY是取20行。优化器为了拿到这20行得先把前20万行排好序再丢掉代价随页码线性增长。对前端分页跳页的场景我的推荐是限制最大页数或用基于主键的分页方案-- keyset分页记住上一页最后一条记录的id性能稳定 SELECT * FROM big_table WHERE id :last_id ORDER BY id FETCH NEXT 20 ROWS ONLY;说明last_id是上一页最后一条的id靠它定位起点只扫描目标区间页码多深都不会额外放大开销。适合下拉加载、列表滚动这类场景如果是那种必须直接跳转到第10000页的报表建议后端限制最大可跳页数别硬刚硬刚一般刚不过这个数据量级别的排序。统计信息是执行计划的地基。表数据从100万涨到2000万统计信息没刷新优化器还用老基数算成本执行计划自然走偏。手动收集的常用命令-- 收集单表统计信息及索引统计并自动生成直方图 EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname APP, tabname BIG_TABLE, cascade TRUE, estimate_percent DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt FOR ALL COLUMNS SIZE AUTO);参数说明cascade TRUE表示同时收集索引统计信息estimate_percent用AUTO_SAMPLE_SIZE让Oracle自己决定采样比例10亿行的大表也能接受method_opt里的FOR ALL COLUMNS SIZE AUTO会让Oracle对有数据倾斜的列自动建直方图避免等值条件估算偏差过大。收集操作有开销不要在业务高峰对大表硬跑。更稳妥的做法是依赖11g以上的自动统计信息收集任务并定期检查last_analyzed是否过期。这个检查动作我会每天看一次放到下文的巡检习惯里。5. 避坑性能优化中常见的5个翻车现场优化做得越多越发现真正的难题不是不知道技巧而是踩坑时没法快速回退。下面这五个场景都是我见过或处理过的真实翻车现场按“现象 → 原因 → 解决”写出遇到同类问题可以直接照着解。5.1 现象改了参数后实例启动失败现象执行完ALTER SYSTEM SET sga_target24G SCOPESPFILE后重启实例起不来报ORA-27102: out of memory或ORA-00845: MEMORY_TARGET not supported on this system。原因SGA目标超过物理内存扣除内核占用后的可用量或者Linux启用了HugePages但没有分配足够的大页导致进程无法锁定所需内存。解决先备份PFILE再改参数给每次调参留后悔药。-- 修改前先生成PFILE备份 CREATE PFILE/tmp/pfile_before_opt.ora FROM SPFILE; -- 若启动失败用备份PFILE拉起实例 STARTUP PFILE/tmp/pfile_before_opt.ora; -- 起来后修正参数重新生成SPFILE CREATE SPFILE/u01/app/oracle/product/19c/dbs/spfileorcl.ora FROM PFILE/tmp/pfile_before_opt.ora;提醒一下HugePages不是单靠sga_target就能配好的还要同步检查/etc/sysctl.conf里的vm.nr_hugepages和用户memlock上限。5.2 现象加了索引反而更慢现象某条SQL执行慢你以为没索引加完复合索引后执行时间反而从3秒变成8秒。看执行计划发现Oracle真的用了新索引但table access by index rowid的回表次数高得吓人。原因索引的选择性太差。比如在status列上建索引这个列只有3个不同值其中某个值占了90%的行优化器根据统计信息估算后仍然选了索引范围扫描结果每拿到一个rowid就回一次表物理读比全表扫描还多。解决先看列基数区分度低的列不要建索引。执行计划已经走偏时可以先用Hint验证回表成本确认索引确实不合适就直接删掉。核心业务SQL还可以建SQL计划基线SPM固定住正确计划防止优化器在统计信息波动时再换到坏路上。5.3 现象AWR报告里全是log file sync现象业务高峰期事务量不大但响应时间突然变长。AWR的Top等待事件里log file sync占比超过40%CPU利用率却不高磁盘IO也不饱和。原因log file sync是前台会话等待Redo日志写入磁盘的时间。常见诱因是应用每处理一条数据就COMMIT一次或者日志文件和其他高IO文件放在同一块磁盘上导致写日志排队。解决先改应用端的提交频率把循环里的单条提交改成批量提交。再确认Redo日志是否放在独立的SSD上log_buffer是否太小。注意别病急乱投医把log_buffer调到好几G这个参数过大并不能线性减少log file sync反而浪费SGA空间。合理的做法是把日志组分布到不同磁盘并检查ARCHIVELOG归档进程是否拖慢了写日志。5.4 现象sqlplus登录oracle数据库出现缓慢现象网络没问题监听lsnrctl status正常但sqlplus user/pwdhost:1521/orcl要等十几秒才能连上连上后操作速度正常。原因客户端连接时监听器做DNS反解超时或者listener.log长期不清理导致监听处理变慢。这个问题常被误判成数据库负载高白白做了一轮性能优化。解决在sqlnet.ora里关掉多余的反解和日志增长约速。# sqlnet.ora SQLNET.INBOUND_CONNECT_TIMEOUT5 NAMES.DIRECTORY_PATH(TNSNAMES,EZCONNECT)说明SQLNET.INBOUND_CONNECT_TIMEOUT控制连接建立的超时上限NAMES.DIRECTORY_PATH限定名字解析只走TNSNAMES和EZCONNECT避免依赖系统DNS。监听日志如果单文件已经到好几个G停监听、轮转日志后重启监听即可。5.5 现象统计信息过期导致执行计划走偏现象应用上线时SQL很快跑了两周后突然变慢。AWR里能看到执行计划里全表扫描但表上明明有索引。原因统计信息没跟上数据变化。比如表从100万行涨到2000万行自动收集任务没在维护窗口内跑完优化器还拿旧的行数和数据分布估算成本。解决先查统计信息的最后收集时间确认是否过期。SELECT table_name, last_analyzed, stale_stats FROM dba_tab_statistics WHERE owner APP AND table_name ORDER_T;stale_stats为YES时直接手动收集EXEC DBMS_STATS.GATHER_TABLE_STATS(APP,ORDER_T, CASCADETRUE);如果这个问题反复出现给核心表固定收集窗口或对经常被误估的列单独收集直方图。统计信息调优是慢SQL排查里最容易被忽略的一环很多“SQL昨天还好好的”都是它惹的祸。6. 把优化做成例行机制两个能落地的习惯性能优化不是一次性救火而是周期性维护。到了这一步工具、参数、SQL技巧都有了剩下的是怎么让优化成果不倒退。第一个习惯是固定月度的AWR基线对比。我每月初会创建一个覆盖上个月全业务的基线模板用系统级的自动化任务生成避免手工选快照号。BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE( start_time TO_TIMESTAMP(2025-02-01 00:00:00, YYYY-MM-DD HH24:MI:SS), end_time TO_TIMESTAMP(2025-02-28 23:59:59, YYYY-MM-DD HH24:MI:SS), baseline_name BASELINE_FEB_2025 ); END; /参数说明start_time和end_time指定基线的起止范围模板会自动把区间内的快照包成基线下个月再建一条新基线两条并排放到 [AWR Baselines] 页签里对比。这个动作做三个月你就能把业务增长、SQL退化与版本变更关联起来而不是每次都从零排查。第二个习惯是每天看一条“Top SQL趋势”查询替代每天翻AWR。我常用一条SQL从dba_hist_sqlstat里捞近7天平均单次执行最慢的20条SQL。SELECT s.sql_id, SUM(s.executions_delta) AS exec_count, ROUND(SUM(s.elapsed_time_delta) / 1000000, 2) AS total_sec, ROUND(SUM(s.elapsed_time_delta) / NULLIF(SUM(s.executions_delta), 0) / 1000000, 4) AS avg_sec FROM dba_hist_sqlstat s, dba_hist_snapshot sn WHERE s.snap_id sn.snap_id AND sn.begin_interval_time SYSDATE - 7 GROUP BY s.sql_id HAVING SUM(s.executions_delta) 0 ORDER BY avg_sec DESC FETCH FIRST 20 ROWS ONLY;逻辑说明dba_hist_sqlstat存的是每个快照区间内SQL执行统计的增量executions_delta和elapsed_time_delta是区间内的变化量。用NULLIF防止除零按avg_sec排序才能筛出“真正慢”的SQL而不是累计执行时间大但单次很快的SQL。我自己值班的习惯是每周一早上先跑这条SQL再花五分钟过一遍上周的月度基线新出现的Top SQL或平均耗时突然翻倍的SQL就会暴露在周报里。曾经有一张订单表就是被这条SQL在周一一早抓出来提前发现了统计信息过期的问题免得业务投诉之后再去救火。优化工具再多也比不上一个固定的检查和复盘节奏。希望这篇文章的方法论能帮你在自己的库里少走点弯路。本文还有配套的精品资源点击获取