公司dba大佬给的Oracle的一些sql

midoll 896 2023-03-17

性能相关

活动会话

二合一
rac:
SELECT a.inst_id ins,a.sid, a.serial#, a.username, a.sql_id, a.event, a.final_blocking_session fblk, trunc(TO_NUMBER(sysdate - a.SQL_EXEC_START) * 24 * 60 * 60) time, a.last_call_et call, b.sql_text, c.object_name obj,e.object_name plsql, d.USED_UBLK*8/1024 tram, d.start_date trad, a.status, a.prev_sql_id, a.program, a.machine, a.taddr, a.osuser, a.client_info FROM gv$session a, gv$sqlarea b, dba_objects c, gv$transaction d,dba_objects e WHERE a.inst_id=b.inst_id(+) and a.sql_id = b.sql_id(+) and a.ROW_WAIT_OBJ# = c.object_id(+) and a.inst_id=d.inst_id(+) and a.taddr=d.addr(+) and a.plsql_entry_object_id=e.object_id(+) AND a.type = 'USER' AND (a.status = 'ACTIVE' or taddr is not null) ORDER BY a.status,a.last_call_et desc;

单实例
SELECT a.sid, a.serial#, a.username, a.sql_id, a.event, a.final_blocking_session fblk, trunc(TO_NUMBER(sysdate - a.SQL_EXEC_START) * 24 * 60 * 60) time, a.last_call_et call, b.sql_text, c.object_name obj,e.object_name plsql, d.USED_UBLK*8/1024 tram, d.start_date trad, a.status, a.prev_sql_id, a.program, a.machine, a.taddr, a.osuser, a.client_info FROM v$session a, v$sqlarea b, dba_objects c, v$transaction d,dba_objects e WHERE a.sql_id = b.sql_id(+) and a.ROW_WAIT_OBJ# = c.object_id(+) and a.taddr=d.addr(+) and a.plsql_entry_object_id=e.object_id(+) AND a.type = 'USER' AND (a.status = 'ACTIVE' or taddr is not null) ORDER BY a.status ,a.last_call_et desc;

SELECT b.sid oracleID,
       b.username 用户名,
       b.serial#,
       paddr,
       sql_text 正在执行的SQL,c.SQL_FULLTEXT,b.STATUS,
       b.machine 计算机名称
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
   AND b.sql_hash_value = c.hash_value and (b.machine = '1-16-flink' or b.machine = '1-17-flink')

执行计划

select * from table(dbms_xplan.display_cursor('35v8qfq016hms',null,'ADVANCED ALLSTATS LAST +PEEKED_BINDS')); 

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'35v8qfq016hms',TYPE=> 'TEXT',REPORT_LEVEL =>'ALL') AS REPORT FROM dual;

活动事务

SELECT a.inst_id inst, A.sid, a.serial#, a.final_blocking_session blk, ROUND(TO_NUMBER(sysdate - a.SQL_EXEC_START)*24*60*60) time,a.last_call_et call, a.status, a.sql_id, a.PLSQL_ENTRY_OBJECT_ID plsql, PREV_SQL_ID, b.start_date, a.event, a.machine, b.USED_UBLK*8/1024 m, a.seconds_in_wait wait, a.osuser, a.program, a.logon_time, b.xid, a.machine FROM gv$session a,gv$transaction b WHERE a.taddr=b.addr AND b.addr is NOT null AND a.inst_id=b.inst_id ORDER BY start_date;

ash查询

SELECT sample_time, session_id AS sid, sql_id, event, plsql_entry_object_id AS plsql, sql_plan_hash_value AS plan, sql_plan_line_id AS line, sql_exec_id AS execid, blocking_session blk, program AS program, user_id , machine FROM gv$active_session_history WHERE sql_id='8t0c30m1rxug6' AND sample_time BETWEEN timestamp '2020-10-14 10:16:00' AND timestamp '2022-12-23 12:05:00' ORDER BY 1 desc

表的信息

SELECT owner, table_name, num_rows, last_analyzed, degree FROM dba_tables WHERE table_name=upper('T_WH_PHYSICAL_WAREHOUSE');

列的选择度

SELECT column_name, num_distinct FROM dba_tab_col_statistics WHERE table_name=upper('wms.T_SO_SALES_ORDER') ORDER BY 1

批量kill

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from gv$session where sql_id='2wbqsuarb9axb';

sql最新信息，比如查看执行计划有没有更新

SELECT sql_id, sql_text, plan_hash_value, last_active_time FROM v$sqlarea WHERE sql_text LIKE 'select%sel,a.djid%acl_user%' ORDER BY 4 desc;
SELECT sql_text, last_active_time, elapsed_time/executions time, executions, buffer_gets, plan_hash_value, cpu_time, elapsed_time, last_load_time FROM v$sql WHERE sql_id='f8cydwqxssqxb' AND last_active_time > timestamp '2020-10-14 13:00:00'

收集统计信息

execute sys.dbms_stats.gather_table_stats(ownname => 'C##MAPS_MUJI_SH',tabname => 'MAPS_EC_TRAN_SELLS ' ,method_opt => 'for all indexed columns' ,cascade => true,degree => 8,no_invalidate => false);

删除执行计划

select 'exec sys.dbms_shared_pool.purge('''||address||','||hash_value||''',''C'');' from v$sqlarea where sql_id='6931sc2u7jsn5';

跟踪会话sql_trace

begin

 dbms_system.set_sql_trace_in_session(1716,16319,false);

   end;

查绑定变量

select last_captured,position,datatype_string,value_string from v$sql_bind_capture where sql_id='7w18ghhqurm8p'

运维相关

表空间

SELECT a.tablespace_name tbsname,
         nvl(trunc(((a.total-b.free)/a.total_all)*100),0) used_percent,
         nvl(trunc(a.total_all),0) total_GB,
         nvl(trunc(a.total),0) all_GB,
         nvl(trunc(a.total-b.free),0) used_GB,
         nvl(trunc(a.total_all-(a.total-b.free)),0) free_GB
FROM 
    (SELECT tablespace_name,
        sum(
        CASE
        WHEN maxbytes=0 THEN BYTES
        WHEN BYTES>=MAXBYTES THEN BYTES
        WHEN maxbytes>bytes THEN maxbytes
        END )/1024/1024/1024 total_all,
        sum(bytes)/1024/1024/1024 total
    FROM dba_data_files
    GROUP BY  tablespace_name) a, 
    (SELECT tablespace_name,
         sum(nvl(bytes,
        0))/1024/1024/1024 free
    FROM dba_free_space
    GROUP BY  tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY  2 desc;

查看历史sql

select sql_text from dba_hist_sqltext where sql_id='05fmsncxqvpub';
select * from table(dbms_xplan.display_awr('8t0c30m1rxug6'));

统计信息过期的表

select * from dba_tab_statistics where table_name=upper('t_wh_sta') and stale_stats='YES'
select * from dba_tab_modifications where table_name='wms.T_MQ_SO_TRANS_DETAIL';

归档日志

SELECT recid,
        stamp,
        name,
        sequence#,
        first_time,
        next_time,
        applied,
        completion_time
FROM v$archived_log
ORDER BY  recid desc;

分区表信息

SELECT TABLE_NAME,
        PARTITION_NAME,
        HIGH_VALUE,
        NUM_ROWS
FROM dba_tab_partitions
WHERE table_name='T_WH_REALTIME_INV_LOG'
ORDER BY  PARTITION_POSITION;

每个分区多少G

SELECT b.table_name,
        trunc(a.g),
        b.num_rows,
        a.partition_name
FROM 
    (SELECT segment_name,
        partition_name,
        bytes/1024/1024/1024 g
    FROM dba_segments
    WHERE tablespace_name='WMSDAT'
    ORDER BY  3 desc) a, 
    (SELECT table_name,
        num_rows
    FROM dba_tables ) b
WHERE a.segment_name=b.table_name;

lob信息

SELECT b.table_name,
        b.column_name,
        trunc(a.G),
        a.tablespace_name
FROM 
    (SELECT segment_name,
        bytes/1024/1024/1024 g,
        tablespace_name
    FROM dba_segments
    WHERE segment_name LIKE 'SYS_LOB%'
    ORDER BY  bytes desc） a, 
        (SELECT *
        FROM dba_lobs
        WHERE owner='WMS') b
        WHERE a.segment_name=b.segment_name;

大招

某个索引对应的sql

select a.sql_id,b.sql_text from 
(select distinct sql_id from v$sql_plan where object_name='IDX_ST_LOG_WHID_TRANSTYPE_TT') a,
v$sqlare b where a.sql_id=b.sql_id;

备库慢sql统计

with a as 
(
select sql_id,sql_exec_id,program,count(*) cnt
from v$active_session_history where sample_time between 
timestamp '2022-04-26 09:00:00' and timestamp '2022-04-26 20:50:00'
and sql_id is not null
group by sql_id,sql_exec_id,program having count(*) >3
)
select 
b.total_time as 总执行时间,
c.exec_cnt as 执行次数,
round((b.total_time/c.exec_cnt),2) as 平均执行时间,
b.sql_id,
d.sql_text
from
(select sql_id,sum(cnt) as total_time from a group by sql_id) b,
(select sql_id,count(sql_exec_id) as exec_cnt from a group by sql_id) c,
v$sqlarea d
where b.sql_id=c.sql_id and b.sql_id=d.sql_id(+) order by 3 desc;

查找未使用过的索引-简版

 select index_name from dba_indexes where owner='ABC'
  minus
(select object_name from v$sql_plan where object_type LIKE '%INDEX%' and object_owner='ABC'
union
 select object_name from DBA_HIST_SQL_PLAN where object_type LIKE '%INDEX%'and object_owner='ABC')

查找未使用过的索引-完美版

SELECT b.table_name,
        c.num_rows,
        a.index_name,
        d.g,
        e.columns
FROM 
    (SELECT index_name
    FROM dba_indexes
    WHERE owner='ABC' minus 
        (SELECT object_name
        FROM v$sql_plan
        WHERE object_type LIKE '%INDEX%'
                AND object_owner='ABC'
        UNION
        SELECT object_name
        FROM DBA_HIST_SQL_PLAN
        WHERE object_type LIKE '%INDEX%'and object_owner='ABC')) a, 
  dba_indexes b,dba_tables c,
    (select segment_name,sum(bytes)/1024/1024/1024 g from dba_segments where owner='ABC' group by segment_name) d,
    (select index_name,listagg(column_name,',') within group(order by column_position) columns from dba_ind_columns where index_owner='ABC' group by index_name) e
    WHERE a.index_name=b.index_name
        AND b.table_name=c.table_name
        and a.index_name=d.segment_name
        and a.index_name=e.index_name
        AND b.owner='ABC'
        AND C.OWNER='ABC'
ORDER BY  2 desc;

查全表扫的sql

SELECT a.PLAN_HASH_VALUE,
        a.SQL_ID,
        a.TIMESTAMP,
        a.OBJECT_NAME,
        b.num_rows,
        a.FILTER_PREDICATES
FROM v$sql_plan a,dba_tables b
WHERE a.object_name=b.table_name(+)
        AND a.options='FULL'
        AND a.OBJECT_OWNER<>'SYS'
        AND a.timestamp > timestamp '2022-05-29 15:30:00'
ORDER BY  b.num_rows desc,a.TIMESTAMP DESC

统计ash里的慢sql，执行次数和总时间

SELECT sql_plan_hash_value AS plan,
         xmlagg(xmlparse(content sql_id||',')
ORDER BY  sql_id).getclobval() AS sqltext,count(sql_exec_id) exec,sum(cnt) time FROM
    (SELECT sql_plan_hash_value,
         sql_id,
         sql_exec_id,
         count(*) cnt
    FROM v$active_session_history
    WHERE sample_time >= timestamp '2022-05-29 08:00:00'
            AND sql_id is NOT null
    GROUP BY  sql_plan_hash_value,sql_id,sql_exec_id)
GROUP BY  sql_plan_hash_value
ORDER BY  3 desc;

# oracle