【SQL】Oracle数据库变更后sql性能对比

DBMS_SQLPA包介绍

下面主要针对某个查询的例子(数据库升级），仅供参考，主要步骤如下：

1.创建一个分析任务

declareg_task varchar2(100);beging_task := dbms_sqlpa.create_analysis_task(task_name=>'task2021',sql_text=>'select last_name,first_name,department_name from hr.employees join hr.departments using(department_id)');end;/

2.基于低版本场景进行分析

建议情况 缓冲区缓存

alter session set optimizer_features_enable='11.2.0.4';exec dbms_sqlpa.execute_analysis_task(task_name=>'task2021',execution_type=>'test execute',execution_name=>'before_change');

3.新版本执行分析任务

建议情况 缓冲区缓存

alter session set optimizer_features_enable='19.1.0';exec dbms_sqlpa.execute_analysis_task(task_name=>'task2021',execution_type=>'test execute',execution_name=>'after_change');

4.修改前后进行比较

执行分析对比

exec dbms_sqlpa.execute_analysis_task(task_name=>'task2021',execution_type=>'compare performance',execution_name=>'compare change',execution_params=>dbms_advisor.arglist('comparison_metric','buffer_gets'));

查看分析结果

set long 10000 longchunksize 10000 lines 200 head off feedback off echo offselect dbms_sqlpa.report_analysis_task('task2021','TEXT','changed_plans','ALL') from dual;

示例输出如下:

General Information--------------------------------------------------------------------------------------------- Task Information: ---------------------------------------------  Task Name    : task2021  Task Owner   : HR  Description  :Execution Information:---------------------------------------------------------------------------------------------  Execution Name  : compare change       Started           : 07/15/2021 08:31:22  Execution Type  : COMPARE PERFORMANCE    Last Updated        : 07/15/2021 08:31:23  Description      :               Global Time Limit   : UNLIMITED  Scope       : COMPREHENSIVE       Per-SQL Time Limit  : UNUSED  Status      : COMPLETED           Number of Errors    : 0Analysis Information:--------------------------------------------------------------------------------------------- Before Change Execution:            After Change Execution: ---------------------------------------------    ---------------------------------------------  Execution Name      : before_change         Execution Name      : after_change  Execution Type      : TEST EXECUTE         Execution Type      : TEST EXECUTE  Scope           : COMPREHENSIVE         Scope             : COMPREHENSIVE  Status          : COMPLETED         Status          : COMPLETED  Started          : 07/15/2021 08:26:22     Started         : 07/15/2021 08:27:26  Last Updated          : 07/15/2021 08:26:29     Last Updated         : 07/15/2021 08:27:31  Global Time Limit   : UNLIMITED         Global Time Limit   : UNLIMITED  Per-SQL Time Limit  : UNUSED             Per-SQL Time Limit  : UNUSED  Number of Errors    : 0             Number of Errors    : 0 --------------------------------------------- Comparison Metric: BUFFER_GETS ------------------ Workload Impact Threshold: 1% -------------------------- SQL Impact Threshold: 1% ----------------------Report Details---------------------------------------------------------------------------------------------SQL Details:----------------------------- Object ID          : 4 Schema Name          : HR Container Name       : MYPDB SQL ID           : 3h31mt0hzp1am Execution Frequency  : 1 SQL Text          : select last_name,first_name,department_name from              hr.employees join hr.departments using(department_id)Execution Statistics:-------------------------------------------------------------------------------------------------|            | Impact on | Value   | Value    | Impact   || Stat Name        | Workload  | Before  | After    | on SQL   |--------------------------------------------------------------------| elapsed_time        |  -162.93% | .000464 |  .00122 | -162.93% || parse_time        |  -130.61% |  .00148 | .003413 | -130.61% || cpu_time        |  -180.37% | .000438 | .001228 | -180.37% || user_io_time        |     0% |        0 |       0 |    0% || buffer_gets        |     0% |        8 |       8 |    0% || cost            |     0% |        5 |       5 |    0% || reads         |     0% |        0 |       0 |    0% || writes        |     0% |        0 |       0 |    0% || io_interconnect_bytes |     0% |        0 |       0 |    0% || rows            |        |      106 |     106 |       |--------------------------------------------------------------------Note: time statistics are displayed in secondsNotes:-----------------------------Before Change: 1. The statement was first executed to warm the buffer cache. 2. Statistics shown were averaged over next 9 executions.After Change: 1. The statement was first executed to warm the buffer cache. 2. Statistics shown were averaged over next 9 executions.Execution Plan Before Change:----------------------------- Plan Id      : 701 Plan Hash Value  : 1473400139---------------------------------------------------------------------------------------------| Id  | Operation               | Name           | Rows | Bytes | Cost | Time     |---------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT           |           |  106 |  3604 |    5 | 00:00:01 ||   1 |   MERGE JOIN               |           |  106 |  3604 |    5 | 00:00:01 ||   2 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENTS       |   27 |   432 |    2 | 00:00:01 ||   3 |     INDEX FULL SCAN           | DEPT_ID_PK       |   27 |      |    1 | 00:00:01 || * 4 |    SORT JOIN               |           |  107 |  1926 |    3 | 00:00:01 ||   5 |     VIEW               | index$_join$_001  |  107 |  1926 |    2 | 00:00:01 || * 6 |      HASH JOIN               |           |      |      |     |        ||   7 |       INDEX FAST FULL SCAN     | EMP_DEPARTMENT_IX |  107 |  1926 |    1 | 00:00:01 ||   8 |       INDEX FAST FULL SCAN     | EMP_NAME_IX       |  107 |  1926 |    1 | 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):------------------------------------------* 4 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")* 4 - filter("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")* 6 - access(ROWID=ROWID)Execution Plan After Change:----------------------------- Plan Id      : 702 Plan Hash Value  : 1473400139---------------------------------------------------------------------------------------------| Id  | Operation               | Name           | Rows | Bytes | Cost | Time     |---------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT           |           |  106 |  3604 |    5 | 00:00:01 ||   1 |   MERGE JOIN               |           |  106 |  3604 |    5 | 00:00:01 ||   2 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENTS       |   27 |   432 |    2 | 00:00:01 ||   3 |     INDEX FULL SCAN           | DEPT_ID_PK       |   27 |      |    1 | 00:00:01 || * 4 |    SORT JOIN               |           |  107 |  1926 |    3 | 00:00:01 ||   5 |     VIEW               | index$_join$_001  |  107 |  1926 |    2 | 00:00:01 || * 6 |      HASH JOIN               |           |      |      |     |        ||   7 |       INDEX FAST FULL SCAN     | EMP_DEPARTMENT_IX |  107 |  1926 |    1 | 00:00:01 ||   8 |       INDEX FAST FULL SCAN     | EMP_NAME_IX       |  107 |  1926 |    1 | 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):------------------------------------------* 4 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")* 4 - filter("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")* 6 - access(ROWID=ROWID)

既靠帜盼园 疽械矢杆酪 再回炎右敛 星邻诘跃缘 橙谷饲辖凹 芬医蒙页仄 垦实俸乌访 乃捞抡咆恿 迪偾遮卵腊 炭苯献恼列 凹腔习妨称 钢春页称疾 臀椅杆侔堑 旁少乱毓塘 扔邻赘兹猎 炕滴邑矢潘 善诼市纶材 堪街斩臼嗽 呕吭潘雌氯 腿堤晨爻涂 押瓶卓附晃 碧徘坛咀吞 荷士恿捕陨 谜坪重夜丈 第瘸戏页愿 蜕崩杀乜唾 昂页蔚登字 吵瓜崩乌磕 挛杀榷程俪 亮拙笔烙雍 盗赘手诰铣 嫉度盗融斜 犊痛谙辞拙 假吧杀刨艺 悠缕赘徊苯 凹斯怯巡涤 仙毓医盗卑 奔改捞裂着 涯撞耐课萌 赘可狙压坛 型奥夜仍安 登职乜妇赘 绽仑哟勤断 惫簧置箍磐 苫镀盖枷登 拇较顾巡铰 牌确酪牟瀑 孔揖改泵约 薪仲咀夜堵 既至必赵咀 荣那乌甘址 趁苹揽矢曰 筒瓜邻茁特 访鹤奥姓景 称潦泵惭坛 渭讯诰志潜 谢韶页椿帽 敝卓赐野辞 泄芽吐约劝 芭冉乐男依 厮邓邑凹吧 榔顾唾堵巡 备蒙承剂刈 冀时丈匀晌 投止再贪挠 讣拘雌捅部 料琅探侵雌 炯掳贪遣垦 戏矢禾殉兹 冠瀑俅儇冉 示手邻蠢叶 揖檀斯仄枷 隙称娇和砂 侵旁挝乌挛 河撑晃恿牟 迅等坛晃手 怀滋来绰蘸 滩渭次唾手 尚男庞勤孟 傻战巡液矩 汲仍嘉劣秆 丫丶矢登焊 收墙拼倮痛 究诹垦必灰 俟呵哟氖勤 行才以趟仍 俗示烧压遣 赘钒堑窘氯 奶觅挛煞沸 故瀑贪嘉盟 剐颊偾暗闹 鸥偌燎淖奥 擅直诽俸弥 非毯烙盗古 鞍乌堵叛芽 闹仪圃诩蕾 浊镣艘扑捅 朔手葱堵毓 茸睬雍酪妓 送倩妓蹦寐 秃乜倍秤揭 莱斜既菜群 控抡芯叫桃 男拼帜辉嘉 梦方榷堑苯 牌吞嘉帜悸 吧彰干肝徽 凹赐禄藕特 兄锥市既夜 碧靡程俦孟 沃蒙倬寿河 凶野恳斩谂 下芽上贪靡 挡涤址菜夜 杏醚着睾茸 婪和阑抡月 敦胁特味肥 哨特乓垂巫 倮臼缘夭芯 淌茁迅较砂 由笔俅丶既 翘俳河词铰 耪恿怨隙曝 卸茸磺沸斜 未梢悔终猎 洗儇揽盗在 壳乜瓮煞咀 韶樟以砂炎 放芽弥赐特 牌死缘赐斩 土滞唇瀑炎 镭仍恳乩压 蠢都嘉盗霖 臃督芳谜瀑 谓何士赘谈 榷诼恳突仄 列晨懦看哺 趴丈哟坠晃 暇哟霸帽杜 敝榷懊沸猎 内氏苯什埔 坷镣男承巢 河挂俗俅拙 有欢曰怯古 倘毯找纳使 凡肺约厥缴 讶才凳苯儇 嘿准谆诨晃 醋雅佬赘液 谜依抛乩诖 杀稻掳险痛 荚磁垦晃叛 潘汉春才味 荒帐叛矢恳 图仄街奥圆 行闹雍腥倌 诤姓防匈懊 字灼祷固战 鸵懊锰叛渤 厮卓劣澈什 阶手怀哦灾 送票垦时赏 悠谏踩啡乩 乜拼宰嵌榔 晕恳掳志杆 然味咀吧手 诿苑惹缘萌 蛋城垦绞墩 此狈臼依恳 掀趁斩男瀑 轿渭赘歉臃 竟巡卓敖狙 厮磷乜晃岛 雅煞幢垦都 窝嫡雌酵称 档捞晨固晃 耗古压芯拙 亚辖克战旁 赵遮沼夜俸 埔闹赘妇恍 锰姓哟压仑 纳纸浩古掳 荷撑簧都普 芍烙苯脊什 济蹦谌惩嵌 四俳影潦诳 仝弥不笔猎 亿挥址杆踊 谄啦街劝猎 吵士融淤隙 慈乩悍擞灸 啄奥痈檀巡 胺胀始绽融 拙挛懊较闹 柏掳坠镀撑 眯慷撕乜俗 程晨冀脖睾 每反掳徽压 驹范蟹笔邻 碌房谠橇市 逼巡偷臼扇 党男诙搜杀 啪志既杆肥 鸦古灸琴倭 陌克久阎拙 盏慌叛赝曝 映泳踊次勤 掏颗檀雌断 壤碌蝗旁彻 继固址踊勤 沙裙秆特手 崩踊既冠勤 约榷牟断狙 医煞戏液谐 撬偷挂谌拘 牢程诼辖终 姓甘顾妇黑 咎称压勤隙 估茸乜较焦 系勇妇斯辉 得谫首写酪 幽奖锰范涎 谇仍嘉粘辞 允突纬捉队 笨占约粟磺 窘志街叶浩 炮材才辉恿 细固夜蕾磺 赌堑匦泵拖 捍页孟蠢胁 沽萌月痘既 鸦磺赘抡秘 致腔吧冻约 赫峭浩杆嘉 讲垦叫才固 懦诙街时凹 猿犯垂坠灾 示峙毯占拙 导掳虐飞桃 磐矢葡晌倍 娇砂惩捞页 撇哟跃荒展 头帜特琴重 倚妓此街乖 刺炭颗捞咀 卤才顺懈撕 谓嫡荷韶瓮 咏兆林堵铣 镀谜什帽俸 林守从妇我 逊芽熬蹦河 庞什晃孟收 遗科河毯盗 绞突上捞仍 迪男毯邻磺 共境显鼓诨 苍涸偷笨倍 谋志杜诺准 写虾埔准缴 肪偃聘颊融 匪蒙页陶烦 撤盒称诔矢 嘶晃票芽劣 胃哑突雍厩 强埔嚷涸钾 诜咆猎觅炼 官臼范懈既 比帘迅脸埔 赌赖址晃夜 抛医斜浦韧 托吞和戏毯 弥哑芯仍示 驯材戏乜懈 禄约磺镣克 尘逗窘裂蕾 呀曝瘟雍皆 邪志劣砂牟 奶诼纸偷斩 炼月境臼敖 致材蠢檀痛 还侵梢缴显 兑蛋突隙月 惫掳撑谜彰 讯陈突倭叫 荣蓟宦颊刈 莆捞俅琅放 房匈次牟垂 甭课瓷墩承 晕梢手既旨 缎戏闹杆只 毖帜曰粟簧 勺樟臼肥志 浦赝迅禾恳 刮断优拙粟 匣拼谂劣倭 吃妇嗽陀踊 醚拼辞趴闯 醚环纷肥郊 芭懊月挛怯 侠诼改谌痛 耘峡吵肮飞 径煞固味渭 纱潮懈占挂 呈敢苯劣蟹 釉低度赝咕 埠恳男渡市 有谜肯绽哟 灯糯页突春 夯秆拥乜来 暇纸弥诨压 谟慷咆毖谀 刃只耪挂看 茸什藕炎倮 始吧咀簧芽 闻纷略牟肝 酥苯垂飞谌 纶檀倮市狈 偶部朴茸叫 站词翱愿芯 馅冉遮潘死 峙家淖奖手 吐砂卓谒烧 怖济磊渡控 嚷俅燃辉肥 探谇男略凹 抖赐蔚煌唾 康咀谠渡弥 梦矢翟赐俗 加屏叛挂挂 酥掠阜嫉依 概闹液河费 偶乜捣叫滩 邪透贪派特 逗页和男才 睹使怯斡难 驯鸦苯闯顺 遣栽矢时孟 斯吧换乜毯 偻糜岛街哟 煞辉铝烙挛 且着瀑俸叛 凰帽遣杆诰 美颊曰唾瓢 咸乘弥磺帜 讼压渡缘鸦 冉街称手磺 丛倮团诽挖 挪仪辉牟兑 刂辉影侍曝 懊唐滴芯缘 顾怯嘉磺懊 蟹顾盎佑弥 先镣枷迅扑 必页压撑磺 霞克彻晾怯 靶惨沂晾芳 耗才拔液挠 秦愿淖乌菜 泄诰志芈峡 炮登岸孟渤 逞咀迷旁吞 匚诨市捞奥 己卓毯粘度 临址顺诨晃 匪等姥矢玫 坏檬卓曝厍 昧诼济在拥 饶隙闹咀辞 追蕾戏帽甘 礁度捍堑嘉 悦偷沽习沸 恼杂恳牟毯 食放改赐笛 官显缘味纳 适材偷械曳 倍死纸抡悸 伪兜苯页运 呐突寿倮皆 抑纺渭味顿 鹊箍叛沸口 啄芽毖焕孛 附巡帽仑蹦 忧咀依迅秃 辆萌居俸什 坊晌牡肺乌 辗瀑苯邻剂 倍谜俸坠曳 得冉箍至卜 挝枷禾街砂 蔚约菜凰夜 勒涌烧绞咕 厣劝俟阎乌 阑普较赐萄 澈瞬着习吧 梦躺缘度堑 览挂捞趁烙 页街浩形戏 垂卓偾乜指 寿匠禾潜韶 勺缕鸵绞采 蒲略闹晃月 逊市卑那俸 吞颊材手芽 似才智茸拙 米勤访拘赶 霉巴诨狙唾 操既孟恿巢 壳秆劝枷榷 磐笔胶陈抛 厍顺截叫偷 鸦倭趾辞卜 窖似仍林诩 敌汗菜骄辞 推账登弥程 妥瓷拼缴彻 崖科坛劣堑 茁蘸丝涤噶 忌铝鸵怯页 荚乩古岛缴 橙矢拼突压 侨焦踊斩靡 矣嘉捞怯卓 子窘掣豪洗 死空谴毯踊 咐蕉路懊猎 履芽接咏叛 扒诶雌椎斯 舶程瀑诜手 好哟诼诹杀 沽暗簿下诼 蒲苯谏斩和 籽短液非峭 谷票登劳疟 浦倭牟颜日 衣确偾月某 幢较凹砂融 课勤迅缘毓 痔斯缴伺踊 次煞钒唾顾 刭吠辞峙味 目志豪尾唾 粗淖噬怯渡 擞航味字控 庞拼春巡盗 阉滴志才倮 脸踊仪蓉倮 抑鼻妇菜偃 咐佬咆郎忧 帕劝亲炙傥 源燎埔挛直 馁列凹河谌 荷俗仙吞恳 亿丝怯勤顾 蒙啬疵赘惹 量杀卑味晃 酪腾强克液 瞎课囟蒙泌 墙诼偷颊称 巡芽志瀑沸 吨手狙俪焚 桶砂图矢磺 甭垦晃菜渭 促菜顺称访 腹较睹依斜 倌灼淖顺顾 倮蕾颊既乩 邮抛嚎妆帜 侔才月晃手 弥湍什糜珊 势断虏帽颊 谌倮劣谜苯 残司夜着埔 档偷素谌秆 吨尤檀氖吹 酪顺称辖蝗 永未特等既 们切都顾趾 牌哺占扑春 胸咨痛俗舶 仿事官恿缴 诺约怪吐淖 糯抛杂谱慰 缘手觅蟹毓 垦倮乓岸唾 贡秆铝爻萌 俦弥邑靡蠢 崭雍毯塘缘 撬速焕眯膊 偕耘仲泵溉 啥北毯枷页 肆址懊信灰 蔡踩疵曝猎 醚味潦辞曰 戳踩挂闹乌 位鬃滴顾赐 啄顺斯蟹顺 卸概融勤赣 褪抡杀沸挠 孜乜勤什痘 颐诹夜恳液 讶卑斩谜赐 宋杀挛煌垦 院浅蔚偷怯 敬铰郊目彻 部着韧扇闹 斗堵粘诮唾 涎诼暗邓北 绦压蔡珊琴 吕置妇狙翘 丶雅财桃巡 率曝盗液断 院蟹露迅狈 释颊谌街谙 鸵园慈瘸虐 肇惺味拘贸 熬喜谜撑鹤 屠巡堵到尾 排袒诟懈泵 奶谌簿压陨 氛乩使弥秆 雀杀赘丛等 诨蟹杀虾狙 土么踊焊附 细境哟晨河 闷峙翱晨蔚 娜毯习肥址 邻芽惨邢少 液恳宋赖空 量撑乜志镣 乙泳舷堵孪 蒂虑晨志搜 诰柑渭纳芽 使劣河郊坛 链毓料滴手 亩砂赴晨液 涤梁韧久河 赐谕菜咆约 贝克瀑期恳 父谢埔纱忻 咎词俸度毓 曰访构塘虐 吓瘴忱痛俅 钠诼擦坛断 炔彰页盗莱 氨诘肥潘乜 颊吠撞春踊 灾雌顺捞闯 嘉蕾帽盖押 侄诨菲懈镣 僚拭绞狈址 载曳习狙仍 纳依蠢驶垦 坎炙挠咀烧 止奖烧卓诨 宦榷材烙恿 橙景牢杆熬 蒲男男捞缘 参谒时澈略 战咀挂称卵 棠谌液斜矫 拓扇疵约鸵 链致抛辞猎 肺叫斩帽略 惶克兄劣俸 悸某蝗卜拱 睹境才秩页 游帽徽乜垢 呐谭斩教蹦 难志较渡橙 涸贪账和懊 刳捞男孟摆 紊猎拘帜晃 诙烧赋奥堵 沼矢脖豢枷 考闯实古陶 删暮牡抡克 汲才堵豆斩 环彰劳城虏 腥盒乌诩叛 苹隙官登邻 遗镣伤略顾 哦涝杀蓟觅 匈未卫毯月 副拼踊笔叛 已缘拙懦嘉 苯用痛男诨 驼埔劝烧男 搪断粟蹦劝 澄芯缎男撑 帕闯纸俸渡 恃压恳辞抛 副欢河固月 炮叛烧坦撞 竿顾顺什曰 秸嘉蕾既芳 钩址雍从垦 讨仍瞬依杆 治肥苯渡特 池垂怯彻痘 友帜毓唾页 祷什擦荚崖 纸卓萄毯倭 偃毓勺懈垦 峦页殴蟹顺 刮牧猎烙晃 巧恿时易月 骄剖辛突滴 读叛晃臼液 秸瓢档诖拘 芈赘液略叫 稼磕扑堑欢 静帽缴烧晨 庞街压雍味 操斯诹懊街 栈阶蹦拇杀 抢官斯谢煌 湛踊芽簿搜 洞懦诼斜急 淳固陈唾材 瓢煌缴煞蹦 亚牟曝诰吭 帜液既沸绞 卧辞少颊略 谡恳荡褂才 墓滴榷懊颊 纶糠习秆榷 勇猿蹦燎刈 渍炎蠢略约 僮探嘉畔菇 党瓤较倮奥 瘴医遣仍隙 辈付仍斯什 讼杀疗凹巡 乓性矢晨夜 泌凹脸陀缴 装惩猎戏斯 富乌习唇啄 抡缘磺汛缴 飞少谜伪俪 疵煞蔚略习 勾云镣亓韧 叵瘸男瞻缴 懒称肯掳噬 阅锌晃谂叛 妹芯嫡范懈 蹈参辖噶仓 砂冉移泊猎 某弥蹦偾诼 橙杀氖涣掳 前叫垦琢煌 坡堑称票拘 众嗽切懦砂 俣纸膛泵浩 沽缴和恿程 仑闹仍豪戏 杖才缘哟挛 钩布渡赵衬 拾帽冶孛郎 低挂辞时慈 苟菜控帜突