首页 > 代码库 > Oracle 下基于 DBMS_RESOURCE_MANAGER 包估算数据库存储 IO 性能
Oracle 下基于 DBMS_RESOURCE_MANAGER 包估算数据库存储 IO 性能
<style>html,body { font-size: 15px }
body { font-family: Helvetica, "Hiragino Sans GB", "微软雅黑", "Microsoft YaHei UI", SimSun, SimHei, arial, sans-serif; line-height: 1.6; margin: 0; padding: 1.33rem 1rem }
h1,h2,h3,h4,h5,h6 { margin: 1.33rem 0 0.667rem; padding: 0; font-weight: bold }
h1 { font-size: 1.4rem }
h2 { font-size: 1.33rem }
h3 { font-size: 1.2rem }
h4 { font-size: 1.13rem }
h5 { font-size: 1rem }
h6 { font-size: 1rem; color: #777777; margin: 1rem 0 }
div,p,ul,ol,dl,li { margin: 0 }
blockquote,table,pre,code { margin: 8px 0 }
ul,ol { padding-left: 2.13rem }
blockquote { padding: 0 0.8rem }
blockquote>:first-child { margin-top: 0 }
blockquote>:last-child { margin-bottom: 0 }
img { border: 0; max-width: 100%; height: auto !important; margin: 2px 0 }
table { border-collapse: collapse; border: 1px solid #bbbbbb }
td,th { padding: 4px 8px; border-collapse: collapse; border: 1px solid #bbbbbb }</style>
<style>html,body { font-size: 15px }
body { font-family: Arial, Helvetica, "Hiragino Sans GB", 微软雅黑, "Microsoft YaHei UI", SimSun, SimHei, arial, sans-serif; line-height: 1.5; color: ; background-color: ; margin: 0; padding: 1.33rem 1rem }
img { max-width: 100% }</style>
<style></style>
<style>html { height: 100% }
body { min-height: 100% }
a { }
.wiz-img-resize-handle { position: absolute; z-index: 1000; border: 1px solid black; background-color: white }
.wiz-img-resize-handle { width: 5px; height: 5px }
.wiz-img-resize-handle.lt { cursor: nw-resize }
.wiz-img-resize-handle.tm { cursor: n-resize }
.wiz-img-resize-handle.rt { cursor: ne-resize }
.wiz-img-resize-handle.lm { cursor: w-resize }
.wiz-img-resize-handle.rm { cursor: e-resize }
.wiz-img-resize-handle.lb { cursor: sw-resize }
.wiz-img-resize-handle.bm { cursor: s-resize }
.wiz-img-resize-handle.rb { cursor: se-resize }
.wiz-table-body.wiz-table-moving *,.wiz-table-body.wiz-table-moving *::before,.wiz-table-body.wiz-table-moving *::after { cursor: default !important }
td,th { position: relative }
#wiz-table-range-border { display: none; width: 0; height: 0; position: absolute; top: 0; left: 0; z-index: 105 }
#wiz-table-col-line,#wiz-table-row-line { display: none; background-color: #448aff; position: absolute; z-index: 120 }
#wiz-table-col-line { width: 1px; cursor: col-resize }
#wiz-table-row-line { height: 1px; cursor: row-resize }
#wiz-table-range-border_start,#wiz-table-range-border_range { display: none; width: 0; height: 0; position: absolute }
#wiz-table-range-border_start_top,#wiz-table-range-border_range_top { height: 2px; background-color: #448aff; position: absolute; top: 0; left: 0 }
#wiz-table-range-border_range_top { height: 1px }
#wiz-table-range-border_start_right,#wiz-table-range-border_range_right { width: 2px; background-color: #448aff; position: absolute; top: 0 }
#wiz-table-range-border_range_right { width: 1px }
#wiz-table-range-border_start_bottom,#wiz-table-range-border_range_bottom { height: 2px; background-color: #448aff; position: absolute; top: 0 }
#wiz-table-range-border_range_bottom { height: 1px }
#wiz-table-range-border_start_left,#wiz-table-range-border_range_left { width: 2px; background-color: #448aff; position: absolute; top: 0; left: 0 }
#wiz-table-range-border_range_left { width: 1px }
#wiz-table-range-border_start_dot,#wiz-table-range-border_range_dot { width: 5px; height: 5px; border: 2px solid rgb(255, 255, 255); background-color: #448aff; cursor: crosshair; position: absolute; z-index: 110 }
.wiz-table-tools { display: block; background-color: #fff; position: absolute; left: 0px; border: 1px solid #ddd; z-index: 130 }
.wiz-table-tools ul { list-style: none; padding: 0 }
.wiz-table-tools .wiz-table-menu-item { position: relative; float: left; margin: 5px 2px 5px 8px }
.wiz-table-tools .wiz-table-menu-item .wiz-table-menu-button { font-size: 15px; width: 20px; height: 20px; line-height: 20px; cursor: pointer; position: relative }
.wiz-table-tools i.editor-icon { font-size: 15px; color: #455a64 }
.wiz-table-tools .wiz-table-menu-item .wiz-table-menu-button i#wiz-menu-bg-demo { position: absolute; top: 1px; left: 0 }
.wiz-table-tools .wiz-table-menu-sub { position: absolute; display: none; width: 125px; padding: 5px 0; background: #fff; border: 1px solid #E0E0E0; top: 28px; left: -9px }
.wiz-table-tools .wiz-table-menu-sub>div { font-size: 15px }
.wiz-table-tools .wiz-table-menu-item.active .wiz-table-menu-sub { display: block }
.wiz-table-tools .wiz-table-menu-sub::before,.wiz-table-tools .wiz-table-menu-sub::after { position: absolute; content: " "; border-style: solid; border-color: transparent; border-bottom-color: #cccccc; left: 22px; margin-left: -14px; top: -8px; border-width: 0 8px 8px 8px; z-index: 10 }
.wiz-table-tools .wiz-table-menu-sub::after { border-bottom-color: #ffffff; top: -7px }
.wiz-table-tools .wiz-table-menu-sub-item { padding: 4px 12px; font-size: 14px }
.wiz-table-tools .wiz-table-menu-sub-item.split { border-top: 1px solid #E0E0E0 }
.wiz-table-tools .wiz-table-menu-sub-item:hover { background-color: #ececec }
.wiz-table-tools .wiz-table-menu-sub-item.disabled { color: #bbbbbb; cursor: default }
.wiz-table-tools .wiz-table-menu-sub-item.disabled:hover { background-color: transparent }
.wiz-table-tools .wiz-table-menu-item.wiz-table-cell-bg:hover .wiz-table-color-pad { display: block }
.wiz-table-tools .wiz-table-color-pad { display: none; padding: 10px; width: 85px; height: 88px; background-color: #fff; cursor: default }
.wiz-table-tools .wiz-table-color-pad>div { font-size: 15px }
.wiz-table-tools .wiz-table-color-pad .wiz-table-color-pad-item { display: inline-block; width: 15px; height: 15px; margin-right: 9px; position: relative }
.wiz-table-tools .wiz-table-color-pad .wiz-table-color-pad-item i.pad-demo { position: absolute; top: 3px; left: 0 }
.wiz-table-tools .wiz-table-color-pad .wiz-table-color-pad-item .icon-oblique_line { color: #cc0000 }
.wiz-table-tools .wiz-table-color-pad .wiz-table-color-pad-item:last-child { margin-right: 0 }
.wiz-table-tools .wiz-table-color-pad .wiz-table-color-pad-item.active i.editor-icon.icon-box { color: #448aff }
.wiz-table-tools .wiz-table-cell-align { display: none; padding: 10px; width: 85px; height: 65px; background-color: #fff; cursor: default }
.wiz-table-tools .wiz-table-cell-align .wiz-table-cell-align-item { display: inline-block; width: 15px; height: 15px; margin-right: 9px; position: relative }
.wiz-table-tools .wiz-table-cell-align .wiz-table-cell-align-item:last-child { margin-right: 0 }
.wiz-table-tools .wiz-table-cell-align .wiz-table-cell-align-item i.valign { position: absolute; top: 3px; left: 0; color: #d2d2d2 }
.wiz-table-tools .wiz-table-cell-align-item.active i.editor-icon.valign { color: #a1c4ff }
.wiz-table-tools .wiz-table-cell-align-item.active i.editor-icon.icon-box,.wiz-table-tools .wiz-table-cell-align-item.active i.editor-icon.align { color: #448aff }
.wiz-table-tools .wiz-table-color-pad .wiz-table-color-pad-item:last-child,.wiz-table-tools .wiz-table-cell-align .wiz-table-cell-align-item:last-child { margin-right: 0 }
th.wiz-selected-cell-multi,td.wiz-selected-cell-multi { background: rgba(0,102,255,.05) }
th::before,td::before,#wiz-table-col-line::before,#wiz-table-range-border_start_right::before,#wiz-table-range-border_range_right::before { content: " "; position: absolute; top: 0; bottom: 0; right: -5px; width: 9px; cursor: col-resize; background: transparent; z-index: 100 }
th::after,td::after,#wiz-table-row-line::before,#wiz-table-range-border_start_bottom::before,#wiz-table-range-border_range_bottom::before { content: " "; position: absolute; left: 0; right: 0; bottom: -5px; height: 9px; cursor: row-resize; background: transparent; z-index: 100 }
.wiz-table-container { }
.wiz-table-body { position: relative; padding: 0 0 10px }
.wiz-table-body table { margin: 0; outline: none }
td,th { height: 28px; outline: none }
body pre.prettyprint { padding: 0 }
body pre.prettyprint code { white-space: pre }
body pre.prettyprint.linenums { overflow: auto }
body pre.prettyprint.linenums ol.linenums { padding: 10px 10px 10px 40px !important }</style>
Oracle 下基于 DBMS_RESOURCE_MANAGER 包估算数据库存储 IO 性能
1.CALIBRATE_IO
使用 CALIBRATE_IO 存储过程估算数据库存储 IO 性能。该存储过程的输入输出如下。
| 参数名称 | 参数类型 | 参数描述 |
| num_physical_disks | IN | Approximate number of physical disks in the database storage |
| max_latency | IN |
Maximum tolerable latency in milliseconds for
database-block-sized IO requests |
| max_iops | OUT |
Maximum number of I/O requests per second that can be
sustained. The I/O requests |
| max_mbps | OUT |
Maximum throughput of I/O that can be sustained, expressed
in megabytes per second. The I/O requests are
randomly-distributed, 1 megabyte reads. |
| actual_latency | OUT |
Average latency of database-block-sized I/O requests at max_
iops rate, expressed in milliseconds |
测试语句类似如下。
SQL> set time on
set timing on
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (120, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE (‘max_iops = ‘ || iops);
DBMS_OUTPUT.PUT_LINE (‘latency = ‘ || lat);
dbms_output.put_line(‘max_mbps = ‘ || mbps);
end;
/
注意：num_physical_disks 参数可设置较大，如果设置过小，将影响测试结果。当该值超过一定的限度后，估算的 IO 性能值将不再变化。
注意：同一时刻仅能执行一次估算（Only one calibration can be run at a time. If another calibration is initiated at the same time, it will fail.）
注意：对于 Oracle RAC 而言，负载将被均衡至各个节点（For an Oracle Real Application Clusters (Oracle RAC) database, the workload is simultaneously generated from all instances.）
参考文档：
《PLSQL Packages and Types Reference》
Oracle 下基于 DBMS_RESOURCE_MANAGER 包估算数据库存储 IO 性能
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。