平生积累的常用SQL

查看全表扫描的表
select sw.sid,e.segment_name,e.segment_type
from dba_extents e, v$session_wait sw
where sw.p2 between e.block_id-1 and e.block_id+blocks
and sw.event='db file scattered read'
and e.file_id=sw.p1;

查看空闲表空间：表空间名、总量、最大连续空闲块、空闲块个数
select tablespace_name,sum(bytes)/1024/1024  Sum_MB,max(bytes)/1024/1024 Max_MB,count(*)
from dba_free_space
group by tablespace_name;

查看数据库的大小，和空间使用情况
 col tablespace format a20
 select b.file_id　　文件ID,
　　b.tablespace_name　　表空间,
　　b.file_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_name,b.file_id,b.bytes
　　order by b.tablespace_name
　　/
　--　dba_free_space --表空间剩余空间状况
　--　dba_data_files --数据文件空间占用情况

查看回滚段：回滚段名、当前字节数、已扩展数、最大扩展数
 select trim(segment_name),bytes,extents,max_extents
 from dba_segments
 where segment_type='ROLLBACK'
 
 查看一个表空间上的占用空间最大的段：对象的所有者、段名、字节数
 select owner,trim(segment_name),bytes/1024/1024
 from dba_segments
 where tablespace_name='BILLDATA1'
 and bytes>10000000
 order by bytes desc
 
 select SEGMENT_NAME
,PARTITION_NAME
,SEGMENT_TYPE
,TABLESPACE_NAME
,BLOCKS
,EXTENTS
,INITIAL_EXTENT
,NEXT_EXTENT
,MIN_EXTENTS
,MAX_EXTENTS
,PCT_INCREASE
,bytes/(1024*1024) as "size(M)"
from user_segments
where segment_name=upper('&table_name')

 查看某个表空间下有哪些对象
 select * from user_segments
 where TABLESPACE_NAME= 'BILLDATA';
 
 查看某个用户的会话：状态、sid、serial#、spid（进程号）、用户名、执行的程序、机器名
select s.status,sid,s.serial#,spid,s.username,s.program,machine,module
from v$session s,v$process p
where s.paddr=p.addr
and s.username='LBAS'
--AND S.STATUS = 'ACTIVE'
AND MACHINE LIKE '%&CCCNC%'
order by LOGON_TIME    

查看当前数据库中的锁：sid、serial#、用户名、机器名、被锁的对象、锁的类型、操作系统的进程号spid
select s.sid,s.serial#,s.username,machine,a.object_name,decode(locked_mode,0,'None',1,'Null',2,'Row share',
  3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type,paddr,p.addr,p.spid
from v$session s,v$locked_object l,all_objects a,v$process p
where s.sid=l.session_id
and p.addr = paddr
and l.object_id=a.object_id

查看oracle过程在操作系统的进程号码：
SELECT spid FROM v$process
WHERE addr IN (SELECT paddr FROM v$session
               WHERE sid = &a)
如果操作系统是UNIX，则复制spid
用oralce的用户登录到UNIX上，使用命令确定进程存在：
ps -ef | grep spid
如果有将进程号码复制使用命令杀掉进程：
kill -9 spid

查看在执行的sql：会话状态、sid、serial#、执行的程序、sql
select s.status,sid,s.serial#,s.program,v.sql_text
 from v$session s,v$sqltext v
where s.sql_hash_value=v.hash_value
 and v.sql_text like '%&a%'

查看环境变量：
select * from v$nls_parameters

查看定时器：job号、执行内容、是否暂停执行、上次执行的日期、上次执行的日期（秒）、下次执行的日期、下次执行的日期（秒）、
失败次数
select job,what,broken,last_date,last_sec,next_date,next_sec,failures
from user_jobs

手工命令回滚段收缩（RBS01处填写实际的回滚段名）：
alter rollback segment RBS01 shrink

查看在内存中进行全部或大部分排序使用磁盘排序与内存排序的比率
select a.value "disk sorts"
,b.value "Memory Sorts"
,round(a.value/(b.value+a.value)*100,2) "disk sort percentage"
from v$sysstat a
,v$sysstat b
where a.name = 'sorts (disk)'
  and b.name = 'sorts (memory)'
-------------------------------------------------------------------------------
--怎样确定代价最高的查询
/******************************************************************************
语句代价高低的判断根据：
a)、耗费的I/O资源（最大的磁盘读取）
b)、耗费的内存和CPU（最大的buffer_gets)
******************************************************************************/
查找耗费I/O最大的语句的脚本
SELECT b.sql_text "STATEMENT"
,a.disk_reads "DISK READS"
,a.executions "EXECUTIONS"
,a.disk_reads/decode(a.executions,0,1,a.executions) "RATIO"
,c.username
FROM v$sqlarea a
,v$sqltext_with_newlines b
,dba_users c
WHERE a.parsing_user_id = c.user_id
  AND a.address=b.address
  AND a.disk_reads >&Threshold_disk_reads  --磁盘读取
ORDER BY a.disk_reads DESC,b.piece;

查找耗费内存（近似CPU）最多的语句的脚本
SELECT b.sql_text "STATEMENT"
,a.buffer_gets "BUFFER GETS"
,a.executions "EXECUTIONS"
,a.buffer_gets/decode(a.executions,0,1,a.executions) "RATIO"
,c.username
FROM v$sqlarea a
,v$sqltext_with_newlines b
,dba_users c
WHERE a.parsing_user_id = c.user_id
  AND a.address = b.address
  AND a.buffer_gets >&Threshod_buffer_gets
ORDER BY a.buffer_gets DESC , b.piece;

怎样优化数据缓冲区高速缓存
/*数据缓冲区是oracle系统全局区域（SGA）的内存结构。oracle服务器可以从内存数据缓冲区中直接
访问高速缓存的数据块，而不是从磁盘中读取它们，这样减少了I/O的操作提高了性能*/
1、怎样计算数据缓冲区命中率

2、增加DB_BLOCK_BUFFERS

度量和优化高速缓存的性能

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

  select tablespace_name,table_name,next_extent
from dba_tables outer
where not exists (select 'X'
                 from sys.dba_free_space inner
                 where outer.tablespace_name = inner.tablespace_name
                   and bytes >=next_extent);

--指定多cpu运行
/*+parallel (kl 5) parallel(s 5) parallel(a 5) parallel(c 5) paralel(c1 5) parallel(sa 5)*/
--格式说明/*+parallel之间不能有空格，括号内的前边的是表名或者是表的别名，后边的数字是使用的cpu个数
--这个语句加在select之后。在这个参数之前要加上下边修改session的命令
EXEC('alter session enable parallel dml');

例如：
        EXEC('alter session enable parallel dml');
        insert into real_rpt_serv_charge_cw nologging
        (id,billing_cycle_id,area_id,exchange_id,serv_type_id,billing_type_id,serv_sts,
               cust_type_id,vip_serv,vip_cust,payment_method,credit_grade,
               acct_item_type_id,charge,user_nbr,new_old ,count_date,batch)
        select/*+parallel (r_temp 5) parallel(s 5) */
              lt_bc(v_counter).bc_ym,r_temp.billing_cycle_id,r_temp.area_id,r_temp.exchange_id,r_temp.serv_type_id,r_temp.billing_type_id,nvl(r_temp.serv_sts,'F0A'),
               NVL(r_temp.cust_type_id,0),s.vip_flag,'N','1',0,r_temp.acct_item_type_id,
        SUM(r_temp.charge),count(distinct r_temp.serv_id) user_nbr,'O',to_date(to_char(r_temp.batch),'yyyymmdd')-1, ls_riqi--2000/10/23 add ceil,2000/10/25 DEL CEIL
          from real_rpt r_temp,server s
         where r_temp.serv_id = s.serv_id
           and r_temp.serv_seq_nbr = s.serv_seq_nbr
           and start_day=i_day
         group by r_temp.billing_cycle_id,r_temp.area_id,r_temp.exchange_id,r_temp.serv_type_id,r_temp.billing_type_id,r_temp.serv_sts,
                  r_temp.cust_type_id,s.vip_flag,'N','1',0,
                  r_temp.acct_item_type_id,'O',to_date(to_char(r_temp.batch),'yyyymmdd'), ls_riqi;  
--排序前用，指定排序区域大小，这样提高带有order by 或者 group by 的排序SQL语句的效率
EXEC ('ALTER session SET sort_area_size = 40000000');  --排序用