平生积累的常用SQL

原创 2005年02月27日 12:20:00

coffee.gif查看全表扫描的表
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;

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

coffee.gif查看数据库的大小,和空间使用情况
 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 --数据文件空间占用情况

coffee.gif查看回滚段:回滚段名、当前字节数、已扩展数、最大扩展数
 select trim(segment_name),bytes,extents,max_extents
 from dba_segments
 where segment_type='ROLLBACK'
 
 coffee.gif查看一个表空间上的占用空间最大的段:对象的所有者、段名、字节数
 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')

 coffee.gif查看某个表空间下有哪些对象
 select * from user_segments
 where TABLESPACE_NAME= 'BILLDATA';
 
 coffee.gif查看某个用户的会话:状态、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    

coffee.gif查看当前数据库中的锁: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

coffee.gif查看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

coffee.gif查看在执行的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%'

coffee.gif查看环境变量:
select * from v$nls_parameters

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

coffee.gif手工命令回滚段收缩(RBS01处填写实际的回滚段名):
alter rollback segment RBS01 shrink

coffee.gif查看在内存中进行全部或大部分排序使用磁盘排序与内存排序的比率
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)'
-------------------------------------------------------------------------------
coffee.gif--怎样确定代价最高的查询
/******************************************************************************
语句代价高低的判断根据:
a)、耗费的I/O资源(最大的磁盘读取)
b)、耗费的内存和CPU(最大的buffer_gets)
******************************************************************************/
coffee.gif查找耗费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;

coffee.gif查找耗费内存(近似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;

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

2、增加DB_BLOCK_BUFFERS

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

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

  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);

coffee.gif--指定多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');  --排序用    

SQL培训文档 SQL常用介绍 SQL经验积累 SQL基本介绍 SQL入门语法

  • 2012年01月05日 16:26
  • 365KB
  • 下载

Unity3D API 知识点积累

一、通过脚本添加 动画事件   AnimationEvent         AnimationEvent evt=new AniamtionEvent();        evt.intPare...
  • yuyingwin
  • yuyingwin
  • 2017-07-28 10:09:48
  • 481

常用js 片段

1:点击超链接 同样选中checkbox
  • kalision
  • kalision
  • 2014-08-24 19:49:22
  • 977

sql常用语句积累

  • 2012年07月19日 14:25
  • 44KB
  • 下载

MYSQL时间查询积累

今天select * from 表名 where to_days(时间字段名) = to_days(now());昨天SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) ...
  • qq_32259579
  • qq_32259579
  • 2018-01-18 17:21:16
  • 353

平生不会相思,才会相思,便害相思

平生不会相思,才会相思,便害相思。 译为:我从出生到现在都不知道什么是相思,才刚刚懂了什么是相思,却深受着相思的折磨。 出自:《蟾宫曲·春情》(元 徐再思) 原文: 平生不会相...
  • cos_sin_tan
  • cos_sin_tan
  • 2012-12-08 09:59:47
  • 445

sql常用语句集锦 查询技巧

  • 2009年07月01日 08:59
  • 69KB
  • 下载

实习第一周小记------生活不易

从自己的上一篇博客《第一次实习面试感受----苦逼程序员生活初体验》已经是第二篇实习的文章了。现在自己已经实习一周,最明显的感受就是生活不易。 我觉得还是按照上一篇为文章套路来吧,在文章开始的时候还是...
  • yisuowushinian
  • yisuowushinian
  • 2013-08-04 11:50:34
  • 2445

英文的写作 —— 词汇的积累(环境的描写、写人)

名词 (1)thumbnail:拇指指甲,极小的东西
  • lanchunhui
  • lanchunhui
  • 2016-05-24 18:14:52
  • 701

乔布斯平生

  • 2011年10月21日 23:42
  • 16KB
  • 下载
收藏助手
不良信息举报
您举报文章:平生积累的常用SQL
举报原因:
原因补充:

(最多只允许输入30个字)