Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=303858
查看全表扫描的表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_spacegroup 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_segmentswhere 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,modulefrom v$session s,v$process pwhere s.paddr=p.addrand s.username='LBAS'--AND S.STATUS = 'ACTIVE'AND MACHINE LIKE '%&CCCNC%'order by LOGON_TIME
查看当前数据库中的锁：sid、serial#、用户名、机器名、被锁的对象、锁的类型、操作系统的进程号spidselect 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.spidfrom v$session s,v$locked_object l,all_objects a,v$process pwhere s.sid=l.session_idand p.addr = paddrand l.object_id=a.object_id
查看oracle过程在操作系统的进程号码：SELECT spid FROM v$processWHERE addr IN (SELECT paddr FROM v$session WHERE sid = &a)如果操作系统是UNIX，则复制spid用oralce的用户登录到UNIX上，使用命令确定进程存在：ps -ef | grep spid如果有将进程号码复制使用命令杀掉进程：kill -9 spid
查看在执行的sql：会话状态、sid、serial#、执行的程序、sqlselect s.status,sid,s.serial#,s.program,v.sql_text from v$session s,v$sqltext vwhere 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,failuresfrom 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 bwhere 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.usernameFROM v$sqlarea a,v$sqltext_with_newlines b,dba_users cWHERE 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.usernameFROM v$sqlarea a,v$sqltext_with_newlines b,dba_users cWHERE a.parsing_user_id = c.user_id AND a.address = b.address AND a.buffer_gets >&Threshod_buffer_getsORDER BY a.buffer_gets DESC , b.piece;
怎样优化数据缓冲区高速缓存/*数据缓冲区是oracle系统全局区域（SGA）的内存结构。oracle服务器可以从内存数据缓冲区中直接访问高速缓存的数据块，而不是从磁盘中读取它们，这样减少了I/O的操作提高了性能*/1、怎样计算数据缓冲区命中率
2、增加DB_BLOCK_BUFFERS
度量和优化高速缓存的性能
-------------------------------------------------------------------------------
select tablespace_name,table_name,next_extentfrom dba_tables outerwhere 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'); --排序用
