Oracle DBA的SQL编写技能提升宝典（含SQL资源）

最新推荐文章于 2023-06-24 13:23:01 发布

数据和云

最新推荐文章于 2023-06-24 13:23:01 发布

阅读量690

点赞数 2

文章标签：数据库大数据 python java 数据分析

本文链接：https://blog.csdn.net/enmotech/article/details/120681313

版权

本文整理了Oracle DBA在实际运维中的SQL应用案例，包括创建表空间、巡检异常检测、表空间分析等，介绍了如何使用分析函数、统计分析和SQL技巧来提升效率。分享了SQL资源，涵盖异常检测、性能监控和数据生成等方面。

摘要由CSDN通过智能技术生成

点击上方"蓝字"

关注我们，享更多干货！

8月5日，云和恩墨高级技术顾问范计杰老师，在墨天轮上分享了《经典知识库：Oracle DBA的SQL编写技能提升宝典》的直播。老师围绕实际运维场景下的案例，向大家介绍了提升SQL编写技能的干货和小技巧。

当晚，朋友们对这场直播的反响十分热烈，考虑到有些朋友没有来得及参与，这里小编为大家整理了文字版干货重点、PPT资源以及直播回放，有需要的朋友们可以收藏、查看。（文末还有部分SQL的资源下载包哦~）

直播干货

一、运维场景SQL实例

案例一：创建表空间

背景：要迁移数据库，需要创建与源库相同的表空间，大小与源库相同。由于个别表空间较大，手工添加可能需要写很多的脚本，于是同事通过PL/SQL解决了问题。

但实际上通过一条SQL就可以搞定，步骤👇

创建表空间——添加数据文件，直到与源库大小相同

查询源表空间的大小；
生成文件列表<最大文件数为1024>；
进行关联。

SQL如下：

col sqltext for a999
with t as
(select tablespace_name tsname, round(sum(bytes) / 1024 / 1024 / 1024) gb  
from dba_data_files  group by tablespace_name),
t2 as
(select rownum n from dual
        connect by rownum < 1024)
select decode(b.n, 1,'create tablespace','alter tablespace')||
       a.tsname || ' datafile''+DATADG''size 30G;'sqltxt
  from t a, t2 b
where30 * b.n < a.gb
orderby a.tsname, b.n;

案例二：巡检-异常检测-周期数据

数据库主机CPU一般每天随着上下班时间进行规律性波动。在这种数据中，怎么找出CPU使用率异常的主机、时间点？

1、从一堆数据库中找出异常的数据库节点

2、找出波动区域后，针对异常节点Excel画图，以观察其与正常负载的区别
3、异常主机查询-主要SQL介绍

a. 以天、小时为单位对数据分组。

b. 用分析函数取平均值分析标准方差，以更加精准定位问题主机。

with t as
 (select to_char(exectime, 'yyyy-mm-dd') d,
         to_char(exectime, 'hh24') h,
         hostname,
         round(avg(100 - id)) cpu
    from sys_vmstat
   where exectime between trunc(sysdate - 7, 'dd') and trunc(sysdate, 'dd')
     and (hostname like 'yyyy%' or hostname like 'zzzz%')
   group by to_char(exectime, 'yyyy-mm-dd'),
            to_char(exectime, 'hh24'),
            hostname),
t2 as
 (select d,
         h,
         hostname,
         cpu,
         round(avg(cpu) over(partition by hostname, h)) avg_cpu,
         round(STDDEV(cpu) over(partition by hostname, h)) cpu_stddev
    from t)
select *
  from t2
 where cpu_stddev < 15
   and cpu - avg_cpu > 20
 order by cpu - avg_cpu desc;

c. 生成7天对比图，使用pivot函数将每天每小时的SQL使用率作图。

with t as (select to_char(exectime,'yyyy-mm-dd')day,to_char(exectime,'hh24') hour,hostname,round(avg(100-id)) cpu from gm.sys_vmstat  
where exectime between trunc(sysdate-7,'dd') and  trunc(sysdate,'dd') and hostname='testb2'
group by to_char(exectime,'yyyy-mm-dd'),to_char(exectime,'hh24') ,hostname)
SELECT day,
       "00-01_ ",
       "01-02_ ",
       "02-03_ ",
       "03-04_ ",
       "04-05_ ",
       "05-06_ ",
       "06-07_ ",
       "07-08_ ",
       "08-09_ ",
       "09-10_ ",
       "10-11_ ",
       "11-12_ ",
       "12-13_ ",
       "13-14_ ",
       "14-15_ ",
       "15-16_ ",
       "16-17_ ",
       "17-18_ ",
       "18-19_ ",
       "19-20_ ",
       "20-21_ ",
       "21-22_ ",
       "22-23_ ",
       "23-24_ "
 From  t pivot(sum(CPU) as " " for hour in('00' AS "00-01",
                                         '01' AS "01-02",
                                         '02' AS "02-03",
                                         '03' AS "03-04",
                                         '04' AS "04-05",
                                         '05' AS "05-06",
                                         '06' AS "06-07",
                                         '07' AS "07-08",
                                         '08' AS "08-09",
                                         '09' AS "09-10",
                                         '10' AS "10-11",
                                         '11' AS "11-12",
                                         '12' AS "12-13",
                                         '13' AS "13-14",
                                         '14' AS "14-15",
                                         '15' AS "15-16",
                                         '16' AS "16-17",
                                         '17' AS "17-18",
                                         '18' AS "18-19",
                                         '19' AS "19-20",
                                         '20' AS "20-21",
                                         '21' AS "21-22",
                                         '22' AS "22-23",
                                         '23' AS "23-24"))

案例三：巡检-异常检测-异常波动

平时维护数据库较多时，若想通过人工找出某一数据库在何时间发生过较大的负载波动，会比较麻烦，通过以下SQL筛查的方式会相对高效。

具体查询SQL可从后文“SQL资源下载”中的资源包下载。

1、通过函数将前后几分钟的负载变化选出

2、通过发生问题的时间点、实例采用LISTAGG生成负载变化图，通过excel生成直观曲线图