Oracle运维常用语句

hzp666

已于 2023-04-10 14:00:02 修改

阅读量220

点赞数

分类专栏： Oracle 文章标签：数据库 sql Oracle 性能优化

于 2022-10-12 13:29:39 首次发布

原文链接：https://blog.csdn.net/hzp666/article/details/127277327?csdn_share_tail=%7B%22type%22%3A%22blog%22%2C%22rType%22%3A%22article%22%2C%22rId%22%3A%22127277327%22%2C%22source%22%3A%22hzp666%22%7D

版权

Oracle 专栏收录该内容

18 篇文章 2 订阅

订阅专栏

查询server name

select value from v$parameter where name like '%service_name%';

查看表的大小，倒序排列

每张表都是作为“段”来存储的，可以通过user_segments视图查看其相应信息。
段（segments）的定义：如果创建一个堆组织表，则该表就是一个段

SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MBYTESE
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE'
GROUP BY SEGMENT_NAME
order by MBYTESE desc;

数据表占用空间大小情况

select segment_name, tablespace_name, bytes, blocks
from user_segments
where segment_type = 'TABLE'
ORDER BY bytes DESC, blocks DESC;

查看表空间占用磁盘情况

    select
             b.file_id                                 文件ID号,
             b.tablespace_name                         表空间名,
             b.bytes                                 字节数,
             (b.bytes-sum(nvl(a.bytes,0)))                 已使用,
             sum(nvl(a.bytes,0))                         剩余空间,
             sum(nvl(a.bytes,0))/(b.bytes)*100         剩余百分比
             from dba_free_space a,dba_data_files b
             where a.file_id=b.file_id
             group by b.tablespace_name,b.file_id,b.bytes
             order by b.file_id;

查看Oracle 表空间使用率

SELECT D.TABLESPACE_NAME,
       SPACE || 'M' "SUM_SPACE(M)",
       SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
       ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
          "USED_RATE(%)",
       FREE_SPACE || 'M' "FREE_SPACE(M)"
  FROM ( SELECT TABLESPACE_NAME,
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
                 SUM (BLOCKS) BLOCKS
            FROM DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME) D,
       ( SELECT TABLESPACE_NAME,
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
            FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL                                                           --如果有临时表空间
SELECT D.TABLESPACE_NAME,
       SPACE || 'M' "SUM_SPACE(M)",
       USED_SPACE || 'M' "USED_SPACE(M)",
       ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
       NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
  FROM ( SELECT TABLESPACE_NAME,
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
                 SUM (BLOCKS) BLOCKS
            FROM DBA_TEMP_FILES
        GROUP BY TABLESPACE_NAME) D,
       ( SELECT TABLESPACE_NAME,
                 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
                 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
            FROM V$TEMP_SPACE_HEADER
        GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1;

SELECT a.tablespace_name "表空间名",
       total "表空间大小",
       free "表空间剩余大小",
       (total - free) "表空间使用大小",
       total / (1024 * 1024 * 1024) "表空间大小(G)",
       free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
       (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
       round((total - free) / total, 4) * 100 "使用率 %"
  FROM (SELECT tablespace_name, SUM(bytes) free
          FROM dba_free_space
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) total
          FROM dba_data_files
         GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name

查看表空间碎片大小

select tablespace_name,round(sqrt(max(blocks)/sum(blocks))*
           (100/sqrt(sqrt(count(blocks)))),2) FSFI
    from dba_free_space
    group by tablespace_name order by 1;

性能最差的SQL

SELECT * FROM ( SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text
FROM v$sqlarea
ORDER BY disk_reads DESC)
WHERE ROWNUM<100;

读磁盘数超100次的sql

select * from sys.v_$sqlarea where disk_reads>100;

查找消耗资源比较的sql语句

  Select se.username,
          se.sid,
          su.extents,
          su.blocks * to_number(rtrim(p.value)) as Space,
          tablespace,
          segtype,
          sql_text
     from v$sort_usage su, v$parameter p, v$session se, v$sql s
    where p.name = 'db_block_size'
      and su.session_addr = se.saddr
      and s.hash_value = su.sqlhash
      and s.address = su.sqladdr
    order by se.username, se.sid;

最频繁执行的sql

select * from sys.v_$sqlarea where executions>100;

查看数据库的版本

SELECT version
FROM product_component_version
WHERE substr(product, 1, 6) = 'Oracle';

查看占io较大的正在运行的session

SELECT se.sid,
se.serial#,
pr.SPID,
se.username,
se.status,
se.terminal,
se.program,
se.MODULE,
se.sql_address,
st.event,
st. p1text,
si.physical_reads,
si.block_changes
FROM v$session se, v$session_wait st, v$sess_io si, v$process pr
WHERE st.sid = se.sid
AND st. sid = si.sid
AND se.PADDR = pr.ADDR
AND se.sid > 6
AND st. wait_time = 0
AND st.event NOT LIKE '%SQL%'
ORDER BY physical_reads DESC

查询没有索引的表

Select table_name
from user_tables
where table_name not in (select table_name from user_indexes)

或

Select table_name
from user_tables
where table_name not in (select table_name from user_ind_columns)

马上该谁等待–查询V$SESSION_WAIT / V$SESSION

select event,
       sum(decode(wait_time, 0, 1, 0)) "Waiting Now",
       sum(decode(wait_time, 0, 0, 1)) "Previous Waits",
       count(*) "Total"
  from v$session_wait
group by event
order by count(*);