日常维护SQL
- 格式：doc
- 大小：56.00 KB
- 文档页数：13
1.查询有enqueue等待的事件SELECT b.SID, b.serial#, ername, machine, event, wait_time,CHR (BITAND (p1, -16777216) / 16777215)|| CHR (BITAND (p1, 16711680) / 65535) "Enqueue Type"FROM v$session_wait a, v$session bWHERE a.event NOT LIKE 'SQL*N%'AND a.event NOT LIKE 'rdbms%'AND a.SID = b.SIDAND b.SID > 8AND a.event = 'enqueue'ORDER BY username;2如何确定哪个表空间读写频繁?select name,phyrds,phywrts,readtim,writetimfrom v$filestat a,v$dbfile bwhere a.file# = b.file#order by readtim desc3Library Cache Pin/Lock Pile UpSELECT s.sid, kglpnmod “Mode”, kglpnreq “Req”, SPID “OS Process”FROM v$session_wait w, x$kglpn p, v$session s ,v$process oWHERE p.kglpnuse=s.saddrAND kglpnhdl=w.p1rawan d w.event like ‘%library cache pin%’and s.paddr=o.addr4全表扫描的表SQL>col name for a30SQL>select name,value from v$sysstat2 where name in (’table scans(short tables)’,'table scans(long tables)’);5查询SQL语句执行时,硬语法分析的次数select name,valuefrom v$sysstatwhere name like ‘parse count%’;6该项显示buffer cache大小是否合适。
公式：1-((physical reads-physical reads direct-physical reads direct(lob)) / session logical reads)执行：select 1-((a.value-b.value-c.value)/d.value)from v$sysstat a,v$sysstat b,v$sysstat c,v$sysstat dwhere =’physical reads’ and=’physical reads direct’ and=’physical reads direct (lob)’ and=’session logical reads’;7该项显示buffer命中率。
公式：1-(physical reads/ (db block gets+consistent gets))执行：select 1 - (sum(decode(name, ‘physical reads’, val ue, 0)) /(sum(decode(name, ‘db block gets’, value, 0)) +sum(decode(name, ‘consistent gets’, value, 0))))“Buffer Hit Ratio”from v$sysstat;8Soft parse ratio：这项将显示系统是否有太多硬解析。
该值将会与原始统计数据对比以确保精确。
例如，软解析率仅为0.2则表示硬解析率太高。
不过，如果总解析量(parse count total)偏低，这项值可以被忽略。
公式：1 - ( parse count (hard) / parse count (total) )执行：select 1-(a.value/b.value)from v$sysstat a,v$sysstat bWhere =’parse count (hard)’ and =’parse count (total)’;9In-memory sort ratio：该项显示内存中完成的排序所占比例。
最理想状态下，在OLTP系统中，大部分排序不仅小并且能够完全在内存里完成排序。
公式：sorts (memory) / ( sorts (memory) + sorts (disk) )执行：select a.value/(b.value+c.value)from v$sysstat a,v$sysstat b,v$sysstat cwhere =’sorts (memory)’ and=’sorts (memory)’ and=’sorts (disk)’;10Parse to execute ratio：在生产环境，最理想状态是一条sql语句一次解析多数运行。
公式：1 - (parse count/execute count)执行：select 1-(a.value/b.value)from v$sysstat a,v$sysstat bwhere =’parse count (total)’ and =’execute count’;11Parse CPU to total CPU ratio：该项显示总的CPU花费在执行及解析上的比率。
如果这项比率较低，说明系统执行了太多的解析。
公式：1 - (parse time cpu / CPU used by this session)执行：select 1-(a.value/b.value)from v$sysstat a,v$sysstat bwhere =’parse time cpu’ and=’CPU used by this session’;12Parse time CPU to parse time elapsed：通常，该项显示锁竞争比率。
这项比率计算是否时间花费在解析分配给CPU进行周期运算(即生产工作)。
解析时间花费不在CPU周期运算通常表示由于锁竞争导致了时间花费公式：parse time cpu / parse time elapsed执行：select a.value/b.valuefrom v$sysstat a,v$sysstat bwhere =’parse time cpu’ and =’parse time elapsed’;13从V$SYSSTAT获取负载间档(Load Profile)数据负载间档是监控系统吞吐量和负载变化的重要部分，该部分提供如下每秒和每个事务的统计信息：logons cumulative, parse count (total), parse count (hard), executes, physical reads, physical writes, block changes, and redo size.被格式化的数据可检查’rates’是否过高，或用于对比其它基线数据设置为识别system profile在期间如何变化。
例如，计算每个事务中block changes可用如下公式：db block changes / ( user commits + user rollbacks )执行：select a.value/(b.value+c.value)from v$sysstat a,v$sysstat b,v$sysstat cwhere =’db block changes’ and=’user commits’ and=’user rollbacks’;其它计算统计以衡量负载方式，如下：l Blocks changed for each read：这项显示出block changes在block reads中的比例。
它将指出是否系统主要用于只读访问或是主要进行诸多数据操作(如：inserts/updates/deletes)公式：db block changes / session logical reads执行：select a.value/b.valuefrom v$sysstat a,v$sysstat bwhere =’db block changes’ and=’session logical reads’ ;14Rows for each sort：公式：sorts (rows) / ( sorts (memory) + sorts (disk) )执行：select a.value/(b.value+c.value)from v$sysstat a,v$sysstat b,v$sysstat cwhere =’sorts (rows)’ and=’sorts (memory)’ and=’sorts (disk)’;15查看某表的约束条件SQL>select constraint_name,constraint_type,search_condition, r_constraint_namefrom user_constraints where table_name =upper(’&table_name’);SQL>select c.constraint_name,c.constraint_type,cc.column_name from user_constraints c,user_cons_columns ccwhere c.owner = upper(’&table_owner’) and c.table_name = upper(’&table_name’)and c.owner = cc.owner and c.constraint_name =cc.constraint_nameorder by cc.position;16查看表空间的名称及大小select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size from dba_tablespaces t, dba_data_files dwhere t.tablespace_name = d.tablespace_namegroup by t.tablespace_name;17查看回滚段名称及大小select segment_name, tablespace_name, r.status,(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,max_extents, v.curext CurExtentFrom dba_rollback_segs r, v$rollstat vWhere r.segment_id = n(+)order by segment_name ;18查看表空间的使用情况select sum(bytes)/(1024*1024) as free_space,tablespace_namefrom dba_free_spacegroup by tablespace_name;SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,(B.BYTES*100)/A.BYTES ”% USED”,(C.BYTES*100)/A.BYTES ”% FREE”FROM SYS.SM$TS_A V AIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE CWHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME ANDA.TABLESPACE_NAME=C.TABLESPACE_NAME;19当移动一个表的多个分区时的脚本BEGINFOR x IN (SELECT partition_nameFROM user_tab_partitionsW HERE table_name = ‘BIG_TABLE2′)LOOPEXECUTE IMMEDIATE ‘alter table big_table2 move partition ‘ || x.partition_name;END LOOP;END;/20SELECT /*+ ORDERED USE_HASH(H,R) */H.SID HOLD_SID,R.SID WAIT_SID,decode(H.type,”MR”, ”Media Recovery”,”RT”, ”Redo Thread”,”UN”, ”User Name”,”TX”, ”Transaction”,”TM”, ”DML”,”UL”, ”PL/SQL User Lock”,”DX”, ”Distributed Xaction”,”CF”, ”Control File”,”IS”, ”Instance State”,”FS”, ”File Set”,”IR”, ”Instance Recovery”,”ST”, ”Disk Space Transaction”,”TS”, ”Temp Segment”,”IV”, ”Library Cache Invalidation”,”LS”, ”Log Start or Switch”,”RW”, ”Row Wait”,”SQ”, ”Sequence Number”,”TE”, ”Extend Table”,”TT”, ”Temp Table”,H.type) type,decode(H.lmode,0, ”None”,1, ”Null”,2, ”Row-S (SS)”,3, ”Row-X (SX)”,4, ”Share”,5, ”S/Row-X (SSX)”,6, ”Exclusive”,to_char(H.lmode)) hold, decode(r.request, 0, ”None”,1, ”Null”,2, ”Row-S (SS)”,3, ”Row-X (SX)”,4, ”Share”,5, ”S/Row-X (SSX)”,6, ”Exclusive”,to_char(R.request)) request,R.ID1,R.ID2,R.CTIMEFROM VLOCK H,VLOCK RWHERE H.BLOCK = 1 AND R.BLOCK=0and H.TYPE <> ”MR” AND R.TYPE <> ”MR”AND H.ID1 = R.ID1 AND H.ID2 = R.ID221查看ORACLE运行的OS平台1 begin2 dbms_output.put_line(3 dbms_utility.port_string);4* end;Linuxi386/Linux-2.0.34-8.1.0PL/SQL 过程已成功完成。
