常用工具sql

【常规操作】
-------------查看表空间：
set lin 200 pagesize 2000
SELECT D.TABLESPACE_NAME,
       SPACE "SUM_SPACE(M)",
       BLOCKS SUM_BLOCKS,
       SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
       ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
       FREE_SPACE "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 "SUM_SPACE(M)",
       BLOCKS SUM_BLOCKS,
       USED_SPACE "USED_SPACE(M)",
       ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
       NVL(FREE_SPACE, 0) "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(+);
 
---------------扩表空间
ALTER TABLESPACE zxin_data ADD DATAFILE '/zxindata/zxin_file0/zxin_data1.dbf' SIZE 10240M;


alter tablespace zxin_temp add tempfile  '/zxindata/zxin_temp/zxin_temp3.dbf' size 10240m;


CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/zxindata/oracle/system/undo2.dbf' SIZE 10240M REUSE AUTOEXTEND ON;
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;
drop UNDO TABLESPACE UNDOTBS1;




---------------查询segment的大小
select segment_name, segment_type, sum(bytes) / (1024 * 1024) as "size(M)"
from   user_segments
where  segment_type <> 'INDEX'
group  by segment_name, segment_type
order  by "size(M)" desc;


---------------查询正在执行的sql
select sql_text from v$sql where users_executing>0;


【锁相关】
---------------解锁:
alter system kill session ‘sid,serial#’;
kill -9 PID


---------------锁与阻塞
select
 'Wait' "Status",
 a.username,
 a.machine,
 a.sid,
 a.serial#,
 a.last_call_et "Seconds",
 b.id1,
 c.sql_text "SQL"
from   v$session a, v$lock b, v$sqltext c
where  a.username is not null
and    a.lockwait = b.kaddr
and    c.hash_value = a.sql_hash_value
union
select
 'Lock' "Status",
 a.username,
 a.machine,
 a.sid,
 a.serial#,
 a.last_call_et "Seconds",
 b.id1,
 c.sql_text "SQL"
from   v$session a, v$lock b, v$sqltext c
where  b.id1 in (select 
                 distinct e.id1
                 from   v$session d, v$lock e
                 where  d.lockwait = e.kaddr)
and    a.username is not null
and    a.sid = b.sid
and    b.request = 0
and    c.hash_value = a.sql_hash_value; 
 
--------------查询被锁对象
SELECT /*+ rule */
lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#,p.spid
FROM v$locked_object l, dba_objects o, v$session s, v$process p
WHERE l.object_id = o.object_id
AND l.session_id = s.sid and s.paddr = p.addr
ORDER BY o.object_id, xidusn DESC;


---------------查看被锁对象及其持有时间
select a.username username, a.sid sid, a.serial# serial,b.id1 id1, c.sql_text sqltext
from v$session a, v$lock b, v$sqltext c
where b.id1 in
　　 (select distinct e.id1
　　 from v$session d, v$lock e
　　 where d.lockwait = e.kaddr)
　　 and a.sid = b.sid
　　 and c.hash_value = a.sql_hash_value
　　 and b.request = 0;




【troubleshooting】
----------------top event对应的sql
select distinct b.SQL_TEXT
  from dba_hist_active_sess_history a, v$sql b
 where a.event like '%gc current block busy%'
   and a.sql_id = b.SQL_ID
   and a.sample_time >=
       to_date('2012-10-31 16:44:43', 'yyyy-mm-dd hh24:mi:ss')
   and a.sample_time <= to_date('2012-10-31 16:48:20', 'yyyy-mm-dd hh24:mi:ss');
   
----------------查询历史执行计划
select a.INSTANCE_NUMBER,a.snap_id,a.sql_id,a.plan_hash_value,b.begin_interval_time
from dba_hist_sqlstat a, dba_hist_snapshot b 
where sql_id ='56s18gn1k19yp' 
and a.snap_id = b.snap_id 
order by instance_number, snap_id;

查询历史执行计划

select * from table(dbms_xplan.display_awr('sql_id'));

select * from DBA_HIST_SQL_PLAN；

----------------未提交的sql
select b.* from v$transaction a,v$session b where a.ADDR=b.TADDR ;


----------------通过进程号获取sql
SELECT   /*+ ORDERED */
         sql_text
    FROM v$sqltext a
   WHERE (a.hash_value, a.address) IN (
            SELECT DECODE (sql_hash_value,
                           0, prev_hash_value,
                           sql_hash_value
                          ),
                   DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
              FROM v$session b
             WHERE b.paddr = (SELECT addr
                                FROM v$process c
                               WHERE c.spid = &pid))
ORDER BY piece ASC;


-------------查询全表扫描
select sp.object_owner,
       sp.object_name,
       (select sql_text
        from   v$sqlarea sa
        where  sa.address = sp.address
        and    sa.hash_value = sp.hash_value) sqltext,
       (select executions
        from   v$sqlarea sa
        where  sa.address = sp.address
        and    sa.hash_value = sp.hash_value) no_of_full_scans,
       (select lpad(nvl(trim(to_char(num_rows)), ' '), 15, ' ') || ' | ' ||
               lpad(nvl(trim(to_char(blocks)), ' '), 15, ' ') || ' | ' || buffer_pool
        from   dba_tables
        where  table_name = sp.object_name
        and    owner = sp.object_owner) "rows|blocks|pool"
from   v$sql_plan sp
where  operation = 'TABLE ACCESS'
and    options = 'FULL'
and    object_owner IN ('ZXDBM_830')
order  by 1, 2;


------------表分析 
exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'work_list',estimate_percent => 10,method_opt=> 'for all indexed columns') ;  
exec dbms_stats.gather_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',estimate_percent => '10',degree => '4') ;
analyze index test.ind_desc compute statistics;
analyze table test compute statistics;  
analyze table test compute statistics sample 20 percent;    --20%采样


升级后数据库监控
1)查看alert.log
2)性能诊断报告
3)查看失效索引，
select index_name,status from all_indexes;


--------------当你想知道当前是哪条sql在占用temp表空间的时候，你可以这样：
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;


--------------查询undo的使用情况
SELECT DISTINCT STATUS, SUM(BYTES/1024/1024), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;


--------------linux 
1、 测试磁盘写能力
dd if=/dev/zero of=/1Gb.file bs=1024 count=1000000


2、 测试磁盘读能力
dd if=/root/1Gb.file bs=64k | dd of=/dev/null

诊断

SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug event 10053 trace name context forever, level 1
SQL> ...enter your query here...
SQL> oradebug event 10053 trace name context off
SQL> oradebug tracefile_name


tkprof