【脚本】检测CPU计算能力

以下脚本可以用于Oracle db系统上线前检测单颗CPu运算能力频率：

SET SERVEROUTPUT ON



SET TIMING ON

DECLARE
  n NUMBER := 0;
BEGIN
  FOR f IN 1..10000000
  LOOP
    n := MOD (n,999999) + SQRT (f);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE ('Res = '||TO_CHAR (n,'999999.99'));
END;
/

create table cpu_speed tablespace users pctfree 99 pctused 0 cache as select * from dba_objects where rownum<=30000;





alter session set optimizer_dynamic_sampling=0;

set autotrace on;
set timing on;
select sum(object_id) from cpu_speed;
select sum(object_id) from cpu_speed;


set autotrace off;

set serveroutput on;

alter session set nls_date_format='DD-MM-YY hh24:mi:ss';
exec DBMS_STATS.CREATE_STAT_TABLE ('SYS','sys_stats');



BEGIN
   DBMS_STATS.GATHER_SYSTEM_STATS ('interval',interval => 1, stattab => 'sys_stats', statid => 'OLTP');
END;
/


exec dbms_lock.sleep(60);

DECLARE
  STATUS VARCHAR2(20);
  DSTART DATE;
  DSTOP  DATE;
  PVALUE NUMBER;
  PNAME  VARCHAR2(30);
BEGIN
  PNAME := 'cpuspeed';
  DBMS_STATS.GET_SYSTEM_STATS(status,
                              dstart,
                              dstop,
                              pname,
                              pvalue,
                              stattab => 'sys_stats',
                              statid  => 'OLTP',
                              statown => 'SYS');
  DBMS_OUTPUT.PUT_LINE('status : ' || status);
  DBMS_OUTPUT.PUT_LINE('cpu in mhz : ' || pvalue);
  DBMS_OUTPUT.PUT_LINE('start :' || dstart);
  DBMS_OUTPUT.PUT_LINE('stop :' || dstop);
  PNAME := 'sreadtim';
  DBMS_STATS.GET_SYSTEM_STATS(status,
                              dstart,
                              dstop,
                              pname,
                              pvalue,
                              stattab => 'sys_stats',
                              statid  => 'OLTP',
                              statown => 'SYS');
  DBMS_OUTPUT.PUT_LINE('single block readtime in ms : ' || pvalue);
  PNAME := 'mreadtim';
  DBMS_STATS.GET_SYSTEM_STATS(status,
                              dstart,
                              dstop,
                              pname,
                              pvalue,
                              stattab => 'sys_stats',
                              statid  => 'OLTP',
                              statown => 'SYS');
  DBMS_OUTPUT.PUT_LINE('multiblock readtime in ms : ' || pvalue);
  PNAME := 'mbrc';
  DBMS_STATS.GET_SYSTEM_STATS(status,
                              dstart,
                              dstop,
                              pname,
                              pvalue,
                              stattab => 'sys_stats',
                              statid  => 'OLTP',
                              statown => 'SYS');
  DBMS_OUTPUT.PUT_LINE('average multiblock readcount: ' || pvalue);
END;
/