Oracle运维常用SQL

￡神魔★判官ぃ 2023-07-22 05:54 13阅读 0赞

> Oracle处理数据缓慢，系统相应缓慢，除了排查代码、网络，数据库也不可忽视，下面一些语句可以筛查需要进行调优的SQL、Oracle设置等。

*  目录
    
     *  查询执行最慢的SQL
     *  查询表空间
     *  查询使用最多的SQL
     *  查看IO最大的前N张表
     *  查看总消耗时间最多的前N条SQL语句
     *  查看CPU消耗时间最多的前N条SQL语句
     *  查看消耗磁盘读取最多的前N条SQL语句
     *  查看锁表/解锁

--------------------

*  **查询执行最慢的SQL**

select *
     from (select sa.SQL_TEXT,
            sa.SQL_FULLTEXT,
            sa.EXECUTIONS "执行次数",
            round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
            round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
            sa.COMMAND_TYPE,
            sa.PARSING_USER_ID "用户ID",
            u.username "用户名",
            sa.HASH_VALUE
         from v$sqlarea sa
         left join all_users u
          on sa.PARSING_USER_ID = u.user_id
         where sa.EXECUTIONS > 0
         order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
     where rownum <= 50;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MjA2MjQ0_size_16_color_FFFFFF_t_70]

*  **查询表空间**

SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;

*  **查询使用最多的sql**

select *
     from (select s.SQL_TEXT,
            s.EXECUTIONS "执行次数",
            s.PARSING_USER_ID "用户名",
            rank() over(order by EXECUTIONS desc) EXEC_RANK
         from v$sql s
         left join all_users u
          on u.USER_ID = s.PARSING_USER_ID) t
     where exec_rank <= 100;

*  **查看IO最大的前10张表**

SELECT TABLE_NAME,TOTAL_PHYS_IO
    FROM ( SELECT OWNER||'.'||OBJECT_NAME AS TABLE_NAME,
    SUM(VALUE) AS TOTAL_PHYS_IO
    FROM V$SEGMENT_STATISTICS
    WHERE OWNER!='SYS' AND OBJECT_TYPE='TABLE'
    AND STATISTIC_NAME IN ('physical reads','physical reads direct',
    'physical writes','physical writes direct')
    GROUP BY OWNER||'.'||OBJECT_NAME
    ORDER BY TOTAL_PHYS_IO DESC)
    WHERE ROWNUM <=10;

*  **查看总消耗时间最多的前10条SQL语句**

select *
    from (select v.sql_id,
    v.child_number,
    v.sql_text,
    v.elapsed_time,
    v.cpu_time,
    v.disk_reads,
    rank() over(order by v.elapsed_time desc) elapsed_rank
    from v$sql v) a
    where elapsed_rank <= 10;

*  **查看CPU消耗时间最多的前10条SQL语句**

select *
    from (select v.sql_id,
    v.child_number,
    v.sql_text,
    v.elapsed_time,
    v.cpu_time,
    v.disk_reads,
    rank() over(order by v.cpu_time desc) elapsed_rank
    from v$sql v) a
    where elapsed_rank <= 10;

*  **查看消耗磁盘读取最多的前10条SQL语句**

select *
    from (select v.sql_id,
    v.child_number,
    v.sql_text,
    v.elapsed_time,
    v.cpu_time,
    v.disk_reads,
    rank() over(order by v.disk_reads desc) elapsed_rank
    from v$sql v) a
    where elapsed_rank <= 10;

*  **查看锁表/解锁**

1.查找被锁的表：
    
    select b.owner,b.object_name,a.session_id,a.locked_mode
    from v$locked_object a,dba_objects b
    
    where b.object_id = a.object_id
    
    2.查找锁表的进程
    
    select b.username,b.sid,b.serial#,logon_time
    from v$locked_object a,v$session b
    
    where a.session_id = b.sid order by b.logon_time
    
    3.kill进程
    
    alter system kill session 'sid,serial#';
    
    4.原被锁的oracle用户提示“已断开连接，请重新登录"，解锁完成。

> 记录部分常用SQL，持续更新。希望对各位读者有所帮助。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MjA2MjQ0_size_16_color_FFFFFF_t_70]: /images/20230528/5bc4c74d0c5e462c9ec07e7bff9687b7.png