任务定时导入定期优化Oracle数据库（oracle imp定时）

任务定时导入：定期优化Oracle数据库

Oracle数据库是业内公认最大的关系型数据库之一，它为了维持高性能而需要优化。优化Oracle数据库是一个持续性的过程，一般会有偏向于优化CPU、磁盘I/O、存储空间、OS及网络这5个方面。而本文要解决的是每日定时优化。

首先是CPU优化，通过配置Oracle参数，我们可以及时地监测系统CPU利用率，如果CPU持续高负荷运转，可能意味着SQL语句的性能出现了问题。所以，大家可以使用以下代码，创建一个名为CPU_ALERT.SQL的脚本文件，用于检测当前的CPU利用率。

DECLARE
  v_time    NUMBER(10);
  v_pointer NUMBER(10);
BEGIN
  v_time    := 5;
  v_pointer := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_pointer, 'DECLARE v_value NUMBER(10); begin select
((100-s.value)/100)*b.value INTO v_value from v$sysstat s, v$parameter b where
s.name='bg process count' and b.name='cpu_count'; dbms_output.put_line(v_value);
end;', DBMS_SQL.NATIVE);
  DBMS_SQL.CLOSE_CURSOR(v_pointer);
  DBMS_OUTPUT.GET_LINE(myline, mystatus);
END;

第二个优化方向是磁盘I/O优化，我们可以使用SGA_IO_STATS数据字典显示统计信息，观察每个文件的I/O状况，然后再定期清理归档日志、使用压缩技术来压缩数据、定期更新I/O子系统适合于磁盘容量和读写需求的布局等。这里给出基础的SGA_IO_STATS的查询语句：

SELECT d.file#, s.phyrds, s.phywrts, s.phyblkrd, s.phyblkwrt, s.readtim,
s.writetim, s.iotim, s.misstm FROM v$datafile d, v$system_io_stat s WHERE
d.file# = s.file# ORDER BY d.file#;

第三个优化方向就是存储空间优化，建议将不活动的分区或表空间移动到廉价的存储设备上，或者释放无效空间，清理文件、数据表或索引等。这里给出一个可用来检查未使用表空间的脚本：

SET LINESIZE 1000
COLUMN "Tablespace" FORMAT A20
COLUMN "Total MB" FORMAT 999,999,999
COLUMN "Used MB" FORMAT 999,999,999
COLUMN "Free MB" FORMAT 999,999,999
COLUMN "Total %" FORMAT 999.99
COLUMN "Free %" FORMAT 999.99
SELECT
   df.tablespace_name "Tablespace",
   fs.total_space_mb "Total MB",
   fs.used_space_mb "Used MB",
   fs.free_space_mb "Free MB",
   ROUND(100 * (fs.total_space_mb - fs.free_space_mb) / fs.total_space_mb) "Total %",
   ROUND(100 * fs.free_space_mb / fs.total_space_mb) "Free %",
   fs.free_space_mb + NVL(free_space,0) "Total Free MB"
FROM
   (SELECT
      tablespace_name,
      ROUND(SUM(bytes) / 1024 / 1024) total_space_mb
    FROM
      dba_data_files
    GROUP BY tablespace_name
   )  df,
   (SELECT
      tablespace_name,
      ROUND(SUM(bytes)/ 1024 / 1024) as used_space_mb,
      ROUND(SUM(bytes)/ 1024 / 1024) free_space_mb
    FROM
      dba_segments
    GROUP BY tablespace_name
   )  fs,
   (SELECT
      MAX(bytes/ 1024 / 1024)  free_space,tablespace_name
    FROM
      dba_free_space
    GROUP BY tablespace_name
   ) f
WHERE
   df.tablespace_name = fs.tablespace_name(+)
   AND df.tablespace_name = f.tablespace_name(+)
ORDER BY 4,1;

第四个优化方向是OS和网络优化。优化OS需要注意的是配置kernel参数以提升网络和磁盘I/O性能；另外，通过使用ORACLE SPECIFIC CALLS的一些性能监视工具来分析ORACLE数据库的瓶颈，比如TOP,VMSTAT,IOSTAT及NETSTAT等。下面是一个可以使用先知相关的TCP监控工具的示例：

SELECT a.sid, a.serial#, d.owner, d.object_name, a.cpu_time, a.elapsed_time FROM
(SELECT b.sid, b.serial#, SUM(b.cpu_time) cpu_time, SUM(b.elapsed_time)
elapsed_time FROM v$session_wt_history b WHERE b.wt_class IS NULL GROUP BY b.sid,
b.serial#) a, v$session c, dba_objects d WHERE a.sid = c.sid AND a.serial# =
c.serial# AND c.username IS NOT NULL AND c.username != 'SYSTEM' AND c.username != 'SYS'
AND c.program LIKE '%jdbc%' AND d.object_id IN (SELECT /*+ use_hash(a)*/
a.object_id FROM dba_objects a WHERE a.object_type LIKE '%LOB%' AND a.owner
IN (SELECT username FROM dba_users WHERE profile = 'DEFAULT_PROFILE') AND a.owner
!= 'SYS' AND a.owner != 'SYSTEM' AND a.object_name NOT LIKE
'%TMP_%' AND a.object_name NOT LIKE '%TEMP%' AND a.object_name NOT LIKE '%TEMP%'
AND a.object_name NOT LIKE '%DEF$_%' AND a.object_name NOT LIKE 'BIN$%' AND
a.object_name NOT LIKE '%PEND_EML_SERVICE')";

本文提供的优化方案无论是从监控还是操作上要能够覆盖相关Oracle数据库的点。但是，本文提供的也是基础性的优化方案，如果想要达到更加细粒度或强迫症级别的优化，还需要根据实际情况自行修改相关的代码和参数。