Oracle 常用性能查看語句

2019-11-08 20:37:17

字體：大中小

來源：轉載

供稿：網友

判斷回滾段競爭的SQL語句：（當Ratio大于2時存在回滾段競爭，需要增加更多的回滾段）select rn.name, rs.GETS, rs.WAITS, (rs.WAITS / rs.GETS) * 100 ratiofrom v$rollstat rs, v$rollname rnwhere rs.USN = rn.usn 判斷恢復日志競爭的SQL語句：（immediate_contention或wait_contention的值大于1時存在競爭）select name,(t.IMMEDIATE_MISSES /decode((t.IMMEDIATE_GETS + t.IMMEDIATE_MISSES),0,-1,(t.IMMEDIATE_GETS + t.IMMEDIATE_MISSES))) * 100 immediate_contention,(t.MISSES / decode((t.GETS + t.MISSES), 0, -1, (t.GETS + t.MISSES))) * 100 wait_contentionfrom v$latch twhere name in ('redo copy', 'redo allocation')判斷表空間碎片：（如果最大空閑空間占總空間很大比例則可能不存在碎片，如果比例較小，且有許多空閑空間，則可能碎片很多）select t.tablespace_name,sum(t.bytes),max(t.bytes),count(*),max(t.bytes) / sum(t.bytes) radiofrom dba_free_space tgroup by t.tablespace_nameorder by t.tablespace_name確定命中排序域的次數：select t.NAME, t.VALUE from v$sysstat t where t.NAME like 'sort%'查看當前SGA值：select * from v$sga確定高速緩沖區命中率：（如果命中率低于70％，則應該加大init.ora參數中的DB_BLOCK_BUFFER的值）select 1 - sum(decode(name, 'physical reads', value, 0)) /(sum(decode(name, 'db block gets', value, 0)) +sum(decode(name, 'consistent gets', value, 0))) hit_ratiofrom v$sysstat twhere name in ('physical reads', 'db block gets', 'consistent gets')確定共享池中的命中率：（如果ratio1大于1時，需要加大共享池，如果ratio2大于10％時，需要加大共享池SHARED_POOL_SIZE）select sum(pins) pins,sum(reloads) reloads,(sum(reloads) / sum(pins)) * 100 ratio1from v$librarycacheselect sum(gets) gets,sum(getmisses) getmisses,(sum(getmisses) / sum(gets)) * 100 ratio2from v$rowcache查詢INIT.ORA參數：select * from v$parameter/////Oracle性能參數查看（轉）0、數據庫參數屬性col PROPERTY_NAME format a25col PROPERTY_VALUE format a30col DESCRipTION format a100select * from database_properties;select * from v$version;1、求當前會話的SID，SERIAL#SELECT Sid, Serial# FROM V$sessionWHERE Audsid = Sys_Context('USERENV', 'SESSIONID');2、查詢session的OS進程IDSELECT p.Spid "OS Thread", b.NAME "Name-User", s.Program, s.Sid, s.Serial#,s.Osuser, s.MachineFROM V$process p, V$session s, V$bgprocess bWHERE p.Addr = s.PaddrAND p.Addr = b.Paddr And (s.sid=&1 or p.spid=&1)UNION ALLSELECT p.Spid "OS Thread", s.Username "Name-User", s.Program, s.Sid,s.Serial#, s.Osuser, s.MachineFROM V$process p, V$session sWHERE p.Addr = s.PaddrAnd (s.sid=&1 or p.spid=&1)AND s.Username IS NOT NULL;3、根據sid查看對應連接正在運行的sql SELECT /*+ PUSH_SUBQ */ Command_Type, Sql_Text, Sharable_Mem, Persistent_Mem, Runtime_Mem, Sorts,Version_Count, Loaded_Versions, Open_Versions, Users_Opening, Executions,Users_Executing, Loads, First_Load_Time, Invalidations, Parse_Calls,Disk_Reads, Buffer_Gets, Rows_Processed, SYSDATE Start_Time,SYSDATE Finish_Time, '>' || Address Sql_Address, 'N' StatusFROM V$sqlarea WHERE Address = (SELECT Sql_AddressFROM V$session WHERE Sid = &sid );4、查找object為哪些進程所用SELECT p.Spid, s.Sid, s.Serial# Serial_Num, s.Username User_Name,a.TYPE Object_Type, s.Osuser Os_User_Name, a.Owner,a.OBJECT Object_Name,Decode(Sign(48 - Command), 1, To_Char(Command), 'Action Code #' || To_Char(Command)) Action,p.Program Oracle_Process, s.Terminal Terminal, s.Program Program,s.Status Session_StatusFROM V$session s, V$access a, V$process pWHERE s.Paddr = p.AddrAND s.TYPE = 'USER'AND a.Sid = s.SidAND a.OBJECT = '&obj'ORDER BY s.Username, s.Osuser5、查看有哪些用戶連接SELECT s.Osuser Os_User_Name,Decode(Sign(48 - Command),1,To_Char(Command),'Action Code #' || To_Char(Command)) Action,p.Program Oracle_Process, Status Session_Status, s.Terminal Terminal,s.Program Program, s.Username User_Name,s.Fixed_Table_Sequence Activity_Meter, '' Query, 0 Memory,0 Max_Memory, 0 Cpu_Usage, s.Sid, s.Serial# Serial_NumFROM V$session s, V$process pWHERE s.Paddr = p.AddrAND s.TYPE = 'USER'ORDER BY s.Username, s.Osuser6、根據v.sid查看對應連接的資源占用等情況SELECT n.NAME, v.VALUE, n.CLASS, n.Statistic# FROM V$statname n, V$sesstat vWHERE v.Sid = &sidAND v.Statistic# = n.Statistic#ORDER BY n.CLASS, n.Statistic#7、查詢耗資源的進程（top session）SELECT s.Schemaname Schema_Name,Decode(Sign(48 - Command),1, To_Char(Command), 'Action Code #' || To_Char(Command)) Action,Status Session_Status, s.Osuser Os_User_Name, s.Sid, p.Spid,s.Serial# Serial_Num, Nvl(s.Username, '[Oracle process]') User_Name,s.Terminal Terminal, s.Program Program, St.VALUE Criteria_ValueFROM V$sesstat St, V$session s, V$process pWHERE St.Sid = s.SidAND St.Statistic# = To_Number('38')AND ('ALL' = 'ALL' OR s.Status = 'ALL')AND p.Addr = s.PaddrORDER BY St.VALUE DESC, p.Spid ASC, s.Username ASC, s.Osuser ASC8、查看鎖（lock）情況SELECT /*+ RULE */ Ls.Osuser Os_User_Name, Ls.Username User_Name,Decode(Ls.TYPE,'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock','TX', 'Transaction enqueue lock', 'UL', 'User supplied lock') Lock_Type,o.Object_Name OBJECT,Decode(Ls.Lmode,1, NULL, 2, 'Row Share', 3, 'Row Exclusive',4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive',NULL) Lock_Mode,o.Owner, Ls.Sid, Ls.Serial# Serial_Num, Ls.Id1, Ls.Id2 FROM Sys.Dba_Objects o, (SELECT s.Osuser, s.Username, l.TYPE, l.Lmode, s.Sid, s.Serial#, l.Id1,l.Id2 FROM V$session s, V$lock lWHERE s.Sid = l.Sid) LsWHERE o.Object_Id = Ls.Id1AND o.Owner <> 'SYS'ORDER BY o.Owner, o.Object_Name;9、查看等待（wait）情況SELECT Ws.CLASS, Ws.COUNT COUNT, SUM(Ss.VALUE) Sum_ValueFROM V$waitstat Ws, V$sysstat SsWHERE Ss.NAME IN ('db block gets', 'consistent gets')GROUP BY Ws.CLASS, Ws.COUNT;10、求process/session的狀態SELECT p.Pid, p.Spid, s.Program, s.Sid, s.Serial#FROM V$process p, V$session sWHERE s.Paddr = p.Addr;11、求誰阻塞了某個session(10g)SELECT Sid, Username, Event, Blocking_Session, Seconds_In_Wait, Wait_TimeFROM V$sessionWHERE State IN ('WAITING')AND Wait_Class != 'Idle';12、查會話的阻塞col user_name format a32SELECT /*+ rule */ Lpad(' ', Decode(l.Xidusn, 0, 3, 0)) || l.Oracle_Username User_Name,o.Owner, o.Object_Name, s.Sid, s.Serial#FROM V$locked_Object l, Dba_Objects o, V$session sWHERE l.Object_Id = o.Object_IdAND l.Session_Id = s.SidORDER BY o.Object_Id, Xidusn DESC;col username format a15col lock_level format a8col owner format a18col object_name format a32SELECT /*+ rule */ s.Username,Decode(l.TYPE, 'tm', 'table lock', 'tx', 'row lock', NULL) Lock_Level,o.Owner, o.Object_Name, s.Sid, s.Serial#FROM V$session s, V$lock l, Dba_Objects oWHERE l.Sid = s.SidAND l.Id1 = o.Object_Id(+)AND s.Username IS NOT NULL;13、求等待的事件及會話信息/求會話的等待及會話信息SELECT Se.Sid, s.Username, Se.Event, Se.Total_Waits, Se.Time_Waited,Se.Average_WaitFROM V$session s, V$session_Event SeWHERE s.Username IS NOT NULLAND Se.Sid = s.SidAND s.Status = 'ACTIVE'AND Se.Event NOT LIKE '%SQL*Net%'ORDER BY s.Username;SELECT s.Sid, s.Username, Sw.Event, Sw.Wait_Time, Sw.State,Sw.Seconds_In_WaitFROM V$session s, V$session_Wait SwWHERE s.Username IS NOT NULLAND Sw.Sid = s.SidAND Sw.Event NOT LIKE '%SQL*Net%'ORDER BY s.Username;14、求會話等待的file_id/block_idcol event format a24col p1text format a12col p2text format a12col p3text format a12SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3FROM V$session_WaitWHERE Event NOT LIKE '%SQL%'AND Event NOT LIKE '%rdbms%'AND Event NOT LIKE '%mon%'ORDER BY Event;SELECT NAME, Wait_TimeFROM V$latch lWHERE EXISTS (SELECT 1FROM (SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3FROM V$session_WaitWHERE Event NOT LIKE '%SQL%'AND Event NOT LIKE '%rdbms%'AND Event NOT LIKE '%mon%') xWHERE x.P1 = l.Latch#);15、求會話等待的對象col owner format a18col segment_name format a32col segment_type format a32SELECT Owner, Segment_Name, Segment_TypeFROM Dba_ExtentsWHERE File_Id = &File_IdAND &Block_Id BETWEEN Block_Id AND Block_Id + Blocks - 1;16、求出某個進程，并對它進行跟蹤SELECT s.Sid, s.Serial#FROM V$session s, V$process pWHERE s.Paddr = p.AddrAND p.Spid = &1;Exec Dbms_System.Set_Sql_Trace_In_Session(&1, &2, TRUE);Exec Dbms_System.Set_Sql_Trace_In_Session(&1, &2, FALSE);17、求當前session的跟蹤文件SELECT P1.VALUE || '/' || P2.VALUE || '_ora_' || p.Spid || '.ora' FilenameFROM V$process p, V$session s, V$parameter P1, V$parameter P2WHERE P1.NAME = 'user_dump_dest'AND P2.NAME = 'instance_name'AND p.Addr = s.PaddrAND s.Audsid = Userenv('SESSIONID')AND p.Background IS NULLAND Instr(p.Program, 'CJQ') = 0;18、求出鎖定的對象SELECT Do.Object_Name, Session_Id, Process, Locked_ModeFROM V$locked_Object Lo, Dba_Objects DoWHERE Lo.Object_Id = Do.Object_Id;19、DB_Cache建議SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_readsFROM V$DB_CACHE_ADVICEWHERE name = 'DEFAULT'AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')AND advice_status = 'ON';20、查看各項SGA相關參數：SGA，SGASTATselect substr(name,1,10) name,substr(value,1,10) value from v$parameter where name = 'log_buffer';select * from v$sgastat ;select * from v$sga;show parameters area_size #查看各項區域內存參數，其中sort_area為排序參數用；各項視圖建議參數值：V$DB_CACHE_ADVICE、V$SHARED_POOL_ADVICE），關于PGA也有相關視圖V$PGA_TARGET_ADVICE 等。21、內存使用鎖定在物理內存：AIX 5L（AIX 4.3.3 以上）logon aix as rootcd /usr/samples/kernel./vmtune (信息如下) v_pingshm已經是1./vmtune -S 1然后oracle用戶修改initSID.ora 中 lock_sga = true重新啟動數據庫HP UNIXRoot身份登陸Create the file "/etc/privgroup": vi /etc/privgroupAdd line "dba MLOCK" to fileAs root, run the command "/etc/setprivgrp -f /etc/privgroup":$/etc/setprivgrp -f /etc/privgrouporacle用戶修改initSID.ora中lock_sga=true重新啟動數據庫SOLARIS (solaris2.6以上)8i版本以上數據庫默認使用隱藏參數 use_ism = true ，自動鎖定SGA于內存中,不用設置lock_sga, 如果設置 lock_sga =true 使用非 root 用戶啟動數據庫將返回錯誤。WINDOWS （作用不大）不能設置lock_sga=true,可以通過設置pre_page_sga=true,使得數據庫啟動的時候就把所有內存頁裝載，這樣可能起到一定的作用。22、內存參數調整數據緩沖區命中率select value from v$sysstat where name ='physical reads';select value from v$sysstat where name ='physical reads direct';select value from v$sysstat where name ='physical reads direct (lob)';select value from v$sysstat where name ='consistent gets';select value from v$sysstat where name = 'db block gets';這里命中率的計算應該是令 x = physical reads direct + physical reads direct (lob)命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100通常如果發現命中率低于90%,則應該調整應用可可以考慮是否增大數據緩沖區；共享池的命中率select sum(pinhits)/sum(pins)*100 "hit radio" from v$librarycache;假如共享池的命中率低于95%,就要考慮調整應用（通常是沒使用bind var ）或者增加內存；關于排序部分select name,value from v$sysstat where name like '%sort%';假如我們發現sorts (disk)/ (sorts (memory)+ sorts (disk))的比例過高，則通常意味著sort_area_size 部分內存較小，可考慮調整相應的參數。關于log_bufferselect name,value from v$sysstatwhere name in('redo entries','redo buffer allocation retries');假如 redo buffer allocation retries/ redo entries 的比例超過1%我們就可以考慮增大log_buffer /////July 28oracle 常用SQL查詢，望對大家有所啟示1、查看表空間的名稱及大小select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_sizefrom dba_tablespaces t, dba_data_files dwhere t.tablespace_name = d.tablespace_namegroup by t.tablespace_name;2、查看表空間物理文件的名稱及大小select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_spacefrom dba_data_filesorder by tablespace_name;3、查看回滾段名稱及大小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 = v.usn(+)order by segment_name ;4、查看控制文件select name from v$controlfile;5、查看日志文件select member from v$logfile;6、查看表空間的使用情況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_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE CWHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;7、查看數據庫庫對象select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;8、查看數據庫的版本　Select version FROM Product_component_versionWhere SUBSTR(PRODUCT,1,6)='Oracle';9、查看數據庫的創建日期和歸檔方式Select Created, Log_Mode, Log_Mode From V$Database;10、捕捉運行很久的SQLcolumn username format a12column opname format a16column progress format a8select username,sid,opname,round(sofar*100 / totalwork,0) || '%' as progress,time_remaining,sql_textfrom v$session_longops , v$sqlwhere time_remaining <> 0and sql_address = addressand sql_hash_value = hash_value/11。查看數據表的參數信息SELECT partition_name, high_value, high_value_length, tablespace_name,pct_free, pct_used, ini_trans, max_trans, initial_extent,next_extent, min_extent, max_extent, pct_increase, FREELISTS,freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,last_analyzedFROM dba_tab_partitions--WHERE table_name = :tname AND table_owner = :townerORDER BY partition_position12.查看還沒提交的事務select * from v$locked_object;select * from v$transaction;13。查找object為哪些進程所用selectp.spid,s.sid,s.serial# serial_num,s.username user_name,a.type object_type,s.osuser os_user_name,a.owner,a.object object_name,decode(sign(48 - command),1,to_char(command), 'Action Code #' || to_char(command) ) action,p.program oracle_process,s.terminal terminal,s.program program,s.status session_statusfrom v$session s, v$access a, v$process pwhere s.paddr = p.addr ands.type = 'USER' anda.sid = s.sid anda.object='SUBSCRIBER_ATTR'order by s.username, s.osuser14。回滾段查看select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extentsExtents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes,sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs,v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name andv$rollstat.usn (+) = v$rollname.usn order by rownum15。耗資源的進程（top session）select s.schemaname schema_name, decode(sign(48 - command), 1,to_char(command), 'Action Code #' || to_char(command) ) action, statussession_status, s.osuser os_user_name, s.sid, p.spid , s.serial# serial_num,nvl(s.username, '[Oracle process]') user_name, s.terminal terminal,s.program program, st.value criteria_value from v$sesstat st, v$session s , v$process pwhere st.sid = s.sid and st.statistic# = to_number('38') and ('ALL' = 'ALL'or s.status = 'ALL') and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc16。查看鎖（lock）情況select /*+ RULE */ ls.osuser os_user_name, ls.username user_name,decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX','Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,o.object_name object, decode(ls.lmode, 1, null, 2, 'Row Share', 3,'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)lock_mode, o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2from sys.dba_objects o, ( select s.osuser, s.username, l.type,l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s,v$lock l where s.sid = l.sid ) ls where o.object_id = ls.id1 and o.owner<> 'SYS' order by o.owner, o.object_name17。查看等待（wait）情況SELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_valueFROM v$waitstat, v$sysstat WHERE v$sysstat.name IN ('db block gets','consistent gets') group by v$waitstat.class, v$waitstat.count18。查看sga情況SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC19。查看catched objectSELECT owner, name, db_link, namespace,type, sharable_mem, loads, executions,locks, pins, kept FROM v$db_object_cache20。查看V$SQLAREASELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS,VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS,USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS,BUFFER_GETS, ROWS_PROCESSED FROM V$SQLAREA21。查看object分類數量select decode (o.type#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5 ,'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) object_type , count(*) quantity fromsys.obj$ o where o.type# > 1 group by decode (o.type#,1,'INDEX' , 2,'TABLE' , 3, 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) union select'COLUMN' , count(*) from sys.col$ union select 'DB LINK' , count(*) from22。按用戶查看object種類select u.name schema, sum(decode(o.type#, 1, 1, NULL)) indexes,sum(decode(o.type#, 2, 1, NULL)) tables, sum(decode(o.type#, 3, 1, NULL))clusters, sum(decode(o.type#, 4, 1, NULL)) views, sum(decode(o.type#, 5, 1,NULL)) synonyms, sum(decode(o.type#, 6, 1, NULL)) sequences,sum(decode(o.type#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1))others from sys.obj$ o, sys.user$ u where o.type# >= 1 and u.user# =o.owner# and u.name <> 'PUBLIC' group by u.name order bysys.link$ union select 'CONSTRAINT' , count(*) from sys.con$23。有關connection的相關信息1）查看有哪些用戶連接select s.osuser os_user_name, decode(sign(48 - command), 1, to_char(command),'Action Code #' || to_char(command) ) action, p.program oracle_process,status session_status, s.terminal terminal, s.program program,s.username user_name, s.fixed_table_sequence activity_meter, '' query,0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_numfrom v$session s, v$process p where s.paddr=p.addr and s.type = 'USER'order by s.username, s.osuser2）根據v.sid查看對應連接的資源占用等情況select n.name,v.value,n.class,n.statistic#from v$statname n,v$sesstat vwhere v.sid = 71 andv.statistic# = n.statistic#order by n.class, n.statistic#3）根據sid查看對應連接正在運行的sqlselect /*+ PUSH_SUBQ */command_type,sql_text,sharable_mem,persistent_mem,runtime_mem,sorts,version_count,loaded_versions,open_versions,users_opening,executions,users_executing,loads,first_load_time,invalidations,parse_calls,disk_reads,buffer_gets,rows_processed,sysdate start_time,sysdate finish_time,'>' || address sql_address,'N' statusfrom v$sqlareawhere address = (select sql_address from v$session where sid = 71)24．查詢表空間使用情況select a.tablespace_name "表空間名稱",100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "占用率(%)",round(a.bytes_alloc/1024/1024,2) "容量(M)",round(nvl(b.bytes_free,0)/1024/1024,2) "空閑(M)",round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)",Largest "最大擴展段(M)",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采樣時間"from (select f.tablespace_name,sum(f.bytes) bytes_alloc,sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytesfrom dba_data_files fgroup by tablespace_name) a,(select f.tablespace_name,sum(f.bytes) bytes_freefrom dba_free_space fgroup by tablespace_name) b,(select round(max(ff.length)*16/1024,2) Largest,ts.name tablespace_namefrom sys.fet$ ff, sys.file$ tf,sys.ts$ tswhere ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts#group by ts.name, tf.blocks) cwhere a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name25. 查詢表空間的碎片程度select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_namehaving count(tablespace_name)>10;alter tablespace name coalesce;alter table name deallocate unused;create or replace view ts_blocks_v asselect tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_spaceunion allselect tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;select * from ts_blocks_v;select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_spacegroup by tablespace_name;26.查看有哪些實例在運行：select * from v$active_instances;12:15 PM | Add a comment | Permalink | Blog it | OracleORACLE性能調優原則數據庫的硬件配置：CPU、內存、網絡條件1. CPU：在任何機器中CPU的數據處理能力往往是衡量計算機性能的一個標志，并且ORACLE是一個提供并行能力的數據庫系統，在CPU方面的要求就更高了，如果運行隊列數目超過了CPU處理的數目，性能就會下降，我們要解決的問題就是要適當增加CPU的數量了，當然我們還可以將需要許多資源的進程KILL掉；2. 內存：衡量機器性能的另外一個指標就是內存的多少了，在ORACLE中內存和我們在建數據庫中的交換區進行數據的交換，讀數據時，磁盤I/O必須等待物理I/O操作完成，在出現ORACLE的內存瓶頸時，我們第一個要考慮的是增加內存，由于I/O的響應時間是影響ORACLE性能的主要參數，我將在這方面進行詳細的講解3. 網絡條件：NET*SQL負責數據在網絡上的來往，大量的SQL會令網絡速度變慢。比如10M的網卡和100的網卡就對NET*SQL有非常明顯的影響，還有交換機、集線器等等網絡設備的性能對網絡的影響很明顯，建議在任何網絡中不要試圖用3個集線器來將網段互聯。? OS參數的設置下表給出了OS的參數設置及說明，DBA可以根據實際需要對這些參數進行設置內核參數名說明bufpages對buffer空間不按靜態分配，采用動態分配，使bufpages值隨nbuf一起對buffer空間進行動態分配。create_fastlinks對HFS文件系統允許快速符號鏈接，dbc_max_pct加大最大動態buffer空間所占物理內存的百分比，以滿足應用系統的讀寫命中率的需要。dbc_min_pct設置最小動態buffer空間所占物理內存的百分比desfree提高開始交換操作的最低空閑內存下限，保障系統的穩定性，防止出現不可預見的系統崩潰（Crash）。fs_async允許進行磁盤異步操作，提高CPU和磁盤的利用率lotsfree提高系統解除換頁操作的空閑內存的上限值，保證應用程序有足夠的可用內存空間。maxdsiz針對系統數據量大的特點，加大最大數據段的大小，保證應用的需要。（32位）maxdsiz_64bitmaximum process data segment size for 64_bitMaxssiz加大最大堆棧段的大小。（32_bit）maxssiz_64bit加大最大堆棧段的大小（64_bit）Maxtsiz提高最大代碼段大小，滿足應用要求maxtsiz_64bit原值過大，應調小Minfree提高停止交換操作的自由內存的上限Shmem允許進行內存共享，以提高內存的利用率。Shmmax設置最大共享內存段的大小，完全滿足目前的需要。Timeslice由于系統的瓶頸主要反映在磁盤I/O上，因此　降低時間片的大小，一方面可避免因磁盤I/O不暢造成CPU的等待，從而提高了CPU的綜合利用率。另一方面減少了進程的阻塞量。unlockable_mem提高了不可鎖內存的大小，使可用于換頁和交換的內存空間擴大,用以滿足系統對內存管理的要求。摘自：http://www.zdnet.com.cn/developer/database/story/0,3800066906,39276980,00.htm11:27 AM | Add a comment | Permalink | Blog it | OracleUNIX內存占用基本檢查 1: 使用top指令. top指令是按cpu占用率排序的,如果想一次獲得所有進程的快照,使用命令top -n [最大進程數] -f 輸出到文件,比如top -n 1000 -f topsnapshot.log top指令輸出內存的統計信息包括Memory: 2614368K (2249100K) real, 5838616K (5264696K) virtual, 113028K free Page# 1/1其中沒有括號起來的是總數,括號括起來的部分是活動進程使用的內存數,free則是真實空閑的物理內存數.進程信息的列包括CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND和內存相關的只有SIZE和RESSIZE：任務的代碼加上數據再加上棧空間的大小。 RES：任務使用的物理內存的總數量要檢查進程是否有內存泄露,和實際占用的內存大小,看RES列即可.2:檢查共享內存占用的內存容量使用ipcs -m -b命令,-m表示檢查共享內存,-b表示輸出每個內存的字節數,得到的共享內存信息輸出列包括:T ID KEY MODE OWNER GROUP SEGSZSEGSZ列則是字節數.把每列相加則是共享內存占用的內存總數.3: 調整內核動態高速緩沖區參數HP-UX某些型號的服務器運行的時候需要幾乎1G的內存維持系統運行,比如作為設備緩沖什么的.可以用kmtune命令檢查內核配置參數,動態高速緩沖區參數dbc_min_pct 和 dbc_max_pct參數表示一個高速緩沖區允許的可用內存的最小和最大百分比,dbc_max_pct的缺省值是50,一般設置為10即可.4:在HP-UX上還可以使用glanceglance是個很強的工具，可惜不是免費的....11:16 AM | Add a comment | Permalink | Blog it | HPUXJuly 27Oracle的優化器(Optimizer)本文的目的： 1、說一說Oracle的Optimizer及其相關的一些知識。 2、回答一下為什么有時一個表的某個字段明明有索引,當觀察一些SQL的執行計劃時,發現確不走索引的問題。 3、如果你對 FIRST_ROWS、 ALL_ROWS這兩種模式有疑惑時也可以看一下這篇文章。開始吧:Oracle在執行一個SQL之前,首先要分析一下語句的執行計劃,然后再按執行計劃去執行。分析語句的執行計劃的工作是由優化器(Optimizer)來完成的。不同的情況,一條SQL可能有多種執行計劃,但在某一時點,一定只有一種執行計劃是最優的,花費時間是最少的。相信你一定會用Pl/sql Developer、Toad等工具去看一個語句的執行計劃,不過你可能對Rule、Choose、First rows、All rows這幾項有疑問,因為我當初也是這樣的,那時我也疑惑為什么選了以上的不同的項,執行計劃就變了?1、優化器的優化方式Oracle的優化器共有兩種的優化方式,即基于規則的優化方式(Rule-Based Optimization,簡稱為RBO)和基于代價的優化方式(Cost-Based Optimization,簡稱為CBO)。 A、RBO方式：優化器在分析SQL語句時,所遵循的是Oracle內部預定的一些規則。比如我們常見的,當一個where子句中的一列有索引時去走索引。 B、CBO方式：依詞義可知,它是看語句的代價(Cost)了,這里的代價主要指Cpu和內存。優化器在判斷是否用這種方式時,主要參照的是表及索引的統計信息。統計信息給出表的大小、有少行、每行的長度等信息。這些統計信息起初在庫內是沒有的,是你在做analyze后才出現的,很多的時侯過期統計信息會令優化器做出一個錯誤的執行計劃,因些我們應及時更新這些信息。在Oracle8及以后的版本,Oracle列推薦用CBO的方式。我們要明了,不一定走索引就是優的 ,比如一個表只有兩行數據,一次IO就可以完成全表的檢索,而此時走索引時則需要兩次IO,這時對這個表做全表掃描(full table scan)是最好的。2、優化器的優化模式(Optermizer Mode)優化模式包括Rule,Choose,First rows,All rows這四種方式,也就是我們以上所提及的。如下我解釋一下：Rule:不用多說,即走基于規則的方式。Choolse:這是我們應觀注的,默認的情況下Oracle用的便是這種方式。指的是當一個表或或索引有統計信息,則走CBO的方式,如果表或索引沒統計信息,表又不是特別的小,而且相應的列有索引時,那么就走索引,走RBO的方式。First Rows:它與Choose方式是類似的,所不同的是當一個表有統計信息時,它將是以最快的方式返回查詢的最先的幾行,從總體上減少了響應時間。All Rows:也就是我們所說的Cost的方式,當一個表有統計信息時,它將以最快的方式返回表的所有的行,從總體上提高查詢的吞吐量。沒有統計信息則走基于規則的方式。3、如何設定選用哪種優化模式a、Instance級別我們可以通過在init<SID>.ora文件中設定OPTIMIZER_MODE=RULE、OPTIMIZER_MODE=CHOOSE、OPTIMIZER_MODE=FIRST_ROWS、OPTIMIZER_MODE=ALL_ROWS去選用3所提的四種方式,如果你沒設定OPTIMIZER_MODE參數則默認用的是Choose這種方式。B、Sessions級別通過SQL> ALTER SESSION SET OPTIMIZER_MODE=<Mode>;來設定。C、語句級別這些需要用到Hint,比如: SQL> SELECT /*+ RULE */ a.userid, 2 b.name, 3 b.depart_name 4 FROM tf_f_yhda a, 5 tf_f_depart b 6 WHERE a.userid=b.userid;4、為什么有時一個表的某個字段明明有索引,當觀察一些語的執行計劃確不走索引呢？如何解決呢？A、不走索引大體有以下幾個原因 ♀你在Instance級別所用的是all_rows的方式 ♀你的表的統計信息(最可能的原因) ♀你的表很小,上文提到過的,Oracle的優化器認為不值得走索引。 B、解決方法 ♀可以修改init<SID>.ora中的OPTIMIZER_MODE這個參數,把它改為Rule或Choose,重起數據庫。也可以使用4中所提的Hint. ♀刪除統計信息 SQL>analyze table table_name delete statistics; ♀表小不走索引是對的,不用調的。5、其它相關A、如何看一個表或索引是否是統計信息SQL>SELECT * FROM user_tables 2 WHERE table_name=<table_name> 3 AND num_rows is not null;SQL>SELECT * FROM user_indexes 2 WHERE table_name=<table_name> 3 AND num_rows is not null;b、如果我們先用CBO的方式,我們應及時去更新表和索引的統計信息,以免生形不切合實的執行計劃。SQL> ANALYZE TABLE table_name COMPUTE STATISTICS; SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS;具體的ANALYZE語句請參照Oracle8i/9i 的refrence文檔。12:42 PM | Add a comment | Permalink | Blog it | Oracleoracle大數據量的導入和導出在oracle中批量數據的導出是借助sqlplus的spool來實現的。批量數據的導入是通過sqlload來實現的。大量數據的導出部分如下：/**************************** sql腳本部分 demo.sql begin**************************//*************************** @author meconsea* @date 20050413* @msn meconsea@hotmail.com* @Email meconsea@163.com**************************///##--markup html：html格式輸出，缺省為off//##--autocommit：自動提交insert、update、delete帶來的記錄改變，缺省為off//##--define：識別命令中的變量前綴符，缺省為on，也就是'&'，碰到變量前綴符，后面的字符串作為變量處理.set colsep''; //##--域輸出分隔符set echo off; //##--顯示start啟動的腳本中的每個sql命令，缺省為onset feedback off; //##--回顯本次sql命令處理的記錄條數，缺省為on set heading off; //##--輸出域標題，缺省為onset pagesize 0; //##--輸出每頁行數，缺省為24,為了避免分頁，可設定為0。set linesize 80; //##--輸出一行字符個數，缺省為80set numwidth 12; //##--輸出number類型域長度，缺省為10set termout off; //##--顯示腳本中的命令的執行結果，缺省為onset timing off; //##--顯示每條sql命令的耗時，缺省為offset trimout on; //##--去除標準輸出每行的拖尾空格，缺省為offset trimspool on; //##--去除重定向（spool）輸出每行的拖尾空格，缺省為offspool C:datadmczry.txt; select trim(czry_dm),trim(swjg_dm),trim(czry_mc) from dm_czry;spool off;EOF/************************ demo.sql end***********************/在數據導入的時候采用sqlload來調用，在該部分調用的時候用java來調用sqlload。sqlload包括ctl控制文件。例如：/********************** meconsea ctl********************/load datainfile 'C:datadmczry.txt'replace into table DM_CZRYfields terminated by X'09'(CZRY_DM,SWJG_DM,CZRY_MC)/********************* end* 注釋：里面的replace可以改為append*******************/java程序如下：在java程序用可以根據需求寫成一個bat文件。把數據庫的配置和文件的路徑寫到一個properties文件。/************************** ide properties************************/Dserver=test/test@SJJZsqlldr=D:/oracle/ora92/bin/SQLLDR.EXEctldmczry=C:/data/ctl/dmczry.ctltxtdmczry=C:/data/dmczry.txt寫個PropertyBean.java來操作properties文件。(偷懶不寫了！)用DmCzry.java來把記錄導入db中。部分代碼如下：/***************************** 代碼摘要****************************/..............sqlldr = pb.getSqlldr();txt = pb.getTxtdmczry();ctl = pb.getCtldmczry();Dserver= pb.getDserver();Process processCmd = Runtime.getRuntime().exec(sqlldr+" "+cmdStr);.............12:42 PM | Add a comment | Permalink | Blog it | Oracle在Oracle中查看各個表、表空間占用空間的大小查看當前用戶每個表占用空間的大小：Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name查看每個表空間占用空間的大小：Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name12:36 PM | Add a comment | Permalink | Blog it | OracleJuly 22Oracle 數據類型Oracle 數據類型數據類型描述VARCHAR2(size) NVARCHAR2(size)可變長度的字符串，其最大長度為 size 個字節。size 的最大值是 4000，而最小值是 1。您必須指定一個 VARCHAR2 的 size。可變長度的字符串，依據所選的國家字符集，其最大長度為 size 個字符或字節。size 的最大值取決于存儲每個字符所需要的字節數，其上限為 4000 個字節。您必須為 NVARCHAR2 指定一個 size。NUMBER(p,s)精度為 p 并且數值范圍為 s 的數值。精度 p 的范圍是從 1 到 38。數值范圍 s 的范圍是從 -84 到 127。LONG可變長度的字符數據，其最大長度可達 2G 或 231 –1 個字節。DATE有效日期范圍從公元前 4712 年 1 月 1 日到公元后 4712 年 12 月 31 日。RAW(size)長度為 size 字節的原始二進制數據。size 的最大值為 2000 字節。您必須為 RAW 值指定一個 size。LONG RAW可變長度的原始二進制數據，其最大長度可達 2G 字節。CHAR(size) NCHAR(size) 固定長度的字符數據，其長度為 size 字節。size 的最大值為 2000 字節。默認或最小的 size 是一個字節。固定長度的字符數據，其長度依據國家字符集的選擇為 size 個字符或字節。size 的最大值取決于存儲每個字符所需要的字節數，其上限為 2000 個字節。默認或最小的 size 是一個字符或字節，這取決于字符集。CLOB NCLOB 一個字符大型對象，可容納單字節的字符。不支持寬度不等的字符集。最大大小為 4G 字節。一個字符大型對象，可容納固定寬度的多字節字符。不支持寬度不等的字符集。最大大小為 4G 字節。儲存國家字符集數據。BLOB一個二進制大對象。最大大小為 4G 字節。BFILE包含一個大型二進制文件的定位器，其存儲在數據庫的外面。使得可以以字節流 I/O 訪問存在數據庫服務器上的外部 LOB。最大大小為 4G 字節。 3:57 PM | Add a comment | Permalink | Blog it | OracleJuly 19ORACLE鎖的管理 ORACLE里鎖有以下幾種模式:0：none1：null 空 2：Row-S 行共享(RS)：共享表鎖 3：Row-X 行專用(RX)：用于行的修改4：Share 共享鎖(S)：阻止其他DML操作5：S/Row-X 共享行專用(SRX)：阻止其他事務操作6：exclusive 專用(X)：獨立訪問使用數字越大鎖級別越高, 影響的操作越多。一般的查詢語句如select ... from ... ;是小于2的鎖, 有時會在v$locked_object出現。select ... from ... for update; 是2的鎖。當對話使用for update子串打開一個游標時，所有返回集中的數據行都將處于行級(Row-X)獨占式鎖定，其他對象只能查詢這些數據行，不能進行update、delete或select...for update操作。insert / update / delete ... ; 是3的鎖。沒有commit之前插入同樣的一條記錄會沒有反應, 因為后一個3的鎖會一直等待上一個3的鎖, 我們必須釋放掉上一個才能繼續工作。創建索引的時候也會產生3,4級別的鎖。locked_mode為2,3,4不影響DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作會提示ora-00054錯誤。有主外鍵約束時 update / delete ... ; 可能會產生4,5的鎖。DDL語句時是6的鎖。以DBA角色, 查看當前數據庫里鎖的情況可以用如下SQL語句：select object_id,session_id,locked_mode from v$locked_object;select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;如果有長期出現的一列，可能是沒有釋放的鎖。我們可以用下面SQL語句殺掉長期沒有釋放非正常的鎖：alter system kill session 'sid,serial#';如果出現了鎖的問題, 某個DML操作可能等待很久沒有反應。當你采用的是直接連接數據庫的方式，也不要用OS系統命令 $kill process_num 或者 $kill -9 process_num來終止用戶連接，因為一個用戶進程可能產生一個以上的鎖, 殺OS進程并不能徹底清除鎖的問題。記得在數據庫級別用alter system kill session 'sid,serial#';殺掉不正常的鎖。5:42 PM | Add a comment | Permalink | Blog it | OracleJuly 18利用STATSPAGK調整ORACLE性能一、摘要　　大部分DBA都利用數據緩沖區命中率，latch free wait time等指標來做數據庫性能調整。ORACLE提供的幾個簡單工具如STATSPACK（或以前版本的BSTAT/ESTAT）中包含了DBA 所需要的主要指標。但如何有效地利用這些數據調整性能？從那里開始調整？本文使用YAPP性能優化方法，結合STATSPACK提供的數據，給出了應該采取的調整步驟。二、YAPP性能優化方法　　YAPP提供了另一種數據庫性能調整方法，它不使用命中率等指標來衡量數據庫的性能而是通過響應時間來衡量：　　Response time = service time + wait time　　即用戶面對的響應時間由服務時間和等待時間組成。服務時間是處理你的請求實際使用的CPU時間，等待時間即等待資源可用所花費的時間。例如如果執行一個需要查找索引的SQL語句，CPU時間可能包括buffer cache中的索引數據塊的處理時間，掃描該數據塊找到所需行的時間等，此過程中ORACLE可能需要從盤上讀數據，此時可能出現磁盤等待。　　YAPP方法的主要思路是找出service time，wait time的主要組成部分，然后進行排序并根據順序調整。因此在IO不是引起問題的原因時，你不會做出象‘數據緩沖區命中率太低,最好增加緩沖的結論’,在SQL語句的CPU處理時間為20分鐘時，你也不會做出‘必須將latchwait time減少20秒’的調整決定。另外用YAPP做優化時，你可以通過減少整個時間（如用更快的磁盤）或單位時間（如減少訪問磁盤次數）。因此我們稱YAPP為基于時間的調優方法，基本步驟如下：　　（1）、得到服務時間和等待時間及其組成部分　　（2）、將所有組成部分排序　　（3）、依次優化每個部分　　（4）、對表中的每一項,減少每次執行的代價或執行次數　　STATSPACK或bstat/estat中的數據完全能滿足基于時間而不是基于命中率的優化方法。然而實際上要找出所有耗時的部分有些困難，在分析細節時service和wait本身并不精確，例如當你等待磁盤IO時，實際是從OS的緩沖中讀寫，實際上它是service （即CPU）時間,因此響應時間更好的表達為：　　Response time = time compnent1+….+time componentn　　用戶感知的響應時間由一系列時間成分組成，所謂性能優化就是優化最耗時的成分，依次類推。從ORACLE instance的角度，請求一般含三個部分：client （如SQLPLUS，TUXEDO），前臺進程（如LOCAL = NO的服務進程），后臺進程（如DBWR）。三、ORACLE中的時間記錄　　所有的ORACLE進程（前臺和后臺）都會將所使用的CPU時間（service time）和各種等待事件所費時間記錄下來，這些信息記錄在SGA，用戶可通過V$▁視圖訪問這些數據。這種數據分為session級和system級，用戶可訪問V$SYSTEM▁EVENT和V$SYSSTAT等視圖來獲取這些信息。ORACLE的STATSPACK工具（老版本中的BSTAT/ESTAT）就是查詢這些視圖來收集，計算和生成性能數據。要在ORACLE中產生時間記錄，DBA必須在INIT.ORA中將TIMED▁STATISTICS設置為TRUE或通過ALTERSYSTEM將其定義為TRUE。8i以前的版本中時間單位為1/100秒。9i以后時間單位為1/1,000,000秒(微秒)。本文主要面向system級的數據，但使用的方法適用于session級的數據。　　在基于時間的優化方法中，最重要的視圖是V$SYSTEM▁EVENT，V$SYSSTAT，V$LATCH，V$SQLAREA。V$SYSSTAT記錄使用的CPU時間，V$SYSTEM▁EVENT記錄進程等待時間花費的間，V$SQLAREA能用于找出最耗資源的SQL語句，而V$LATCH則可用于各種LATCH的等待信息。這些視圖的詳細結構和含義見Sewer Reference Mannual四、利用STATSPACK優化性能　　前一節所說的V$▁ 中記錄的數據都是系統啟動后的累加值，從某一個時間點看這些累加值沒有實際意義。只有每隔一段時間對這些累加值取樣，計算出抽樣之間的差別對優化才有價值。ORACLE的STATSPACK就是完成定期取樣的工作，一般可用ORACLE的JOB來自動完成定期取樣。數據收集完成后，DBA可以運行STATSPACK帶的SPREPORT生成某兩個取樣點之間的差別。STATSPACK生成的報告中含有各種數據，包括上述四個視圖中的數據。　　1、從STATSPACK報告中找出晌應時間組成部分　　基于時間的優化方法YAPP就是要找出最值得優化（最耗時）的成分。我們需找出前臺進程使用的sevice time及等待事件花費的時間，service time信息可以從V$SYSSTAT中得到而事件等待花費的時間可從V$SYSTEM▁EVENT中得到。在STATSPACK報告中它們分別在 '''' Instance Activity Stats for DB '''' 和 '''' Wait Eventsfor DB '''' 一節中。尤其要注意三個時間成分：　　CPU used by this session Total CPU time spent. 　　Recursive cpu usage 　　Time spent doing recursive work in the foreground . 　　This includes data dictionary lookup and any PL /SQL work, including time spent by SQL inside 　　PL/SQL parse time cpu 　　CPU time spent parsing SQL statements　　Recursive cpu usage和parse time cpu CPU是CPU used by this session的組成部分，除此之外的CPU時間我們一律定義為other CPU　　下一步需找出wait time的組成部分，最簡單的方法就是找出 '''' Top5Wait Events '''' 下的5個等待事件，另一種方法即在 '''' Wait events for DB '''' 一節中找出最主要的事件。　　下面是根據STATSPACK報告的數據，用基于YAPP方法的優化步驟：　　（1）、找出parse time cpu所花費的時間　　（2）、找出CPU used by this session的值，減去parse time cpu，得出other CPU 　　（3）、找出最耗時的等待事件　　（4）、將1—3的成分倒序排序，從第一項開始優化　　（5）、如果最耗時的等待事件不是latch free，見Tuning possibilities for wait events 　　（6）、如果最耗時的等待事件是latch free，見Tuning possibilities for latches 　　（7）、如果最耗時的成分是與CPU有關的成分,見Tuning possibilities for CPU　　2、Tuning possibilities for CPU　　recursive cpu usage 如果處理大量的PLSQL此成分可能很高，本文不深入討論此問題產生的原因，但你需要找出你所有的PLSQL，包括存儲過程。找出CPU開銷最大的PLSQL過程并對其優化。如果PLSQL中的主要工作是完成過程處理而非執行SQL，高開銷的recursive cpu usage可能是需要優化的成分。　　parse time cpu 分析（parsing）SQL是一個開銷很大的，它可以通過SQL語句的重用來避免。在預編譯程序中，可通過增加MAXOPENCURSORS參數減少這部分開銷。V$SQL的PARSE▁CALLS和EXECUTIONS可用來找出經常parse的語句。　　Other cpu 其它CPU主要用于處理緩沖區中的緩沖。一般而言，SQL語句花費的CPU時間與訪問的緩沖區個數成比例，因此可以從V$SQL中的buffer gets得到SQL所防問的緩沖區個數，在STATSPACK中，可以查看 '''' SQL ordered by Gets for DB ''''。應對清單中的SQL語句優化。在bstat /estat報告中沒有SQL語句，需定期查詢V$SQLAREA，找出buffer gets增加最快的語句。9i中V$SQL中含有CPU▁T|ME字段,記錄語句所花費的時間。　　3、Tuning possibilities for wait events　　db file scattered read 當ORACLE全表掃描時,一次需讀多個數據塊，此時使用這一等待事件。i n i t .o r a中的db▁ file▁mutiblock▁read▁count定義了多數據塊讀取時，一次能讀取的最大塊數。一般此參數定義為4—16，與數據庫大小無關。但值越大DB▁BLOCK▁SlZE應越小。如果db file scattered read所占比例較大,必須減少IO的代價（如使用更快的磁盤,均衡IO分布），或減少全表掃描的次數（優化SQL語句）。參見下面IO優化。　　db file sequential read 表示ORACLE順序讀數據塊，一般出現在讀索引。如果db file sequential read等待很長，必須減少IO的代價（如使用更快的磁盤，均衡IO分布），或增加緩沖區。參見下面IO優化。　　buffer busy waits 多個進程訪問（修改）緩沖區中同一數據塊時出現此等待事件。當表沒有free lists而對表并行插入時，或回滾段個數太少時，會出現此事件，V$WA|TSTAT及 STATSPACK報告可輔助找出原因。　　latch free 見下節。　　Enqueue 一般為應用程序使用的鎖，例如SEELECT ... FOR UPDATE。如果此部分占用的時間較大，需分析應用系統，尤其是長時間占有鎖資源的代碼。要分析每個鎖的等待時間不太可能，雖然V$LOCK記錄了每種所等待的次數。　　log file sync 任何時候一個事物提交時，它將通知LGWR將LOG▁BUFFER寫入日志文件，如果此部分占用時間較長，應減少COMMIT的次數，此外應使用性能更好的IO系統，另一個相關的事件是'''' log buffer parallel write '''' ，也與IO系統或CPU資源太少有關。　　free buffer wait 當一個SESSION需要空閑緩沖區但不能獲取時，出現此等待事件。它將通知DBWR將臟的緩沖區寫入數據文件。需要確定是否需要優化IO系統或者增加DBWR的個數，如果此事件不是由于IO系統性能引起的，可考慮增加緩沖區。　　rdbms ipc message 這些事件為空閑事件，一般應占主要的時間。''''　　pmon timer　　smon timer　　SQL*Net message 　　from client　　Tuning possibilities for latches　　shared pool 在parsing，尤其是hard parsing時。如果應用程序使用常量而不是BIND變量，可能會對此latch大量競爭，8.1.6以后可在init.ora中設置cursor▁sharing為force來減少hard parsing和對此latch的競爭，應用程序應保證只分析一次，執行多次。　　library cache 在soft parsing和hard parsing時都會大量使用此latch，如有可能應修改應用程序，減少競爭。在init.ora中設置cursor▁sharing為force可減少soft parsing和hard parsing需要的library cache。此外定義session▁cached▁cursors也能減少同一session中soft parsing對library cache的競爭。此外還可以定義cursor▁space▁for▁time=true。　　mw cache row cache保護字典信息，如表和列的信息。hard parsing需要row cache 。在init.ora中設置cursor▁sharing為force可減少競爭。　　cache buffer chain 保護緩沖區的hash chain，用于對緩沖區的每次訪問。一般可通過減少訪問緩沖區次數來減少對l|atch的競爭。利用X$BH可以找出某些hash chain是否有許多的緩沖區，經常會有熱塊（如root index block）可能引起競爭。　　cache buffer lru chain 數據緩沖一組LRU塊組成的鏈。每一個由一個cache buffer lru chain 保護通過增加db▁ block▁lru▁latches可減少競爭。　　4、Tuning possibilities for I/O 如果db file scattered/sequential read等直接的事件或file write（DBWR/LGWR）等非直接事件占用的時間比例較大，需要檢查IO的效率。STATSPACK報告中有一節為 ''''Tablespace IO Summary for DB'''' 其中列出了表空間名稱和它們的lO rate。另一節'''' file IO Statistics for DB''''列出了每個數據文件和它們的IO rate。首先應檢查IO rate是否在期望的范圍，其次應檢查IO分布。如果IO rate 在可接受的范圍（帶cache的文件2—10ms或裸設備的每次IO 5—20ms）而且所有的數據文件IO rate相似，那么可以肯定IO系統的性能符合要求。這種情況下減少每次IO代價沒有必要，應該減少IO的次數（增加緩沖區或優化SQL）。然而如果IO rate大大超出合理范圍或分布不合理，你需要重組IO子系統，如使用更多的磁盤驅動器，修改結構（如不使用RAID5），或IO重新分布。五、值得注意的地方　　雖然ORACLE的統計和等待事件可以為你找出系統瓶頸提供了很好的數據，但有些情況這些數據可能會誤導用戶：　　·ORACLE 8i以前的版本時間單位是1/100秒，但在某些特別|的系統中其精度不夠。因而某些發生過的事件可能沒有記錄，而某些發生時間并不很長的事件記錄的時間要比實際的時間長。這個問題在9i中不會出現（計量單位是1/1,000,000秒）。　　·ORACLE前臺進程花費的CPU時間記錄比較粗糙，CPU used by this session遠遠大于實際使用配的時間，唯一能做的估計是所用CPU時間與所訪問的緩沖區個數成比例，但在運行大的PLSQL，復雜的表達式，表連接時這種估計是不精確的。一般而言，這類估計在OLTP類型的應用系統是有效的，對DSS系統這種估計是不精確的。　　·V$SYSSTAT視圖包含前臺和后臺進程時間的總和，然而CPU時間成分中只有前臺進程所用的時間值得注意，某些后臺進程（尤其是DBWR，LGWR）使用了大量的CPU，導致前臺進程統計配的不精確。　　·某些時間沒有計算。如SQL*NET的時間，但它影響響應時間。　　·YAPP中只考慮了ORACLE前臺進程使用的時間，如果ORACLE所用的時間只占響應時間的很小部分，優化ORACLE不會帶來任何性能改進。六、IBSTAT/ESTAT的使用　　STATSPACK只有8.1.6以后的版本才有，如果使用的老的版本只有BSTAT/ESTAT，兩者的主要區別是：　　·BSTAT/ESAT由DBA直接手工運行而不通過ORACLE JOB自動運行，每運行一次只收集一個時間間隔的數據。　　·BSTAT/ESAT沒有SQL語句的信息，如果OTHER CPU是開銷最大的成分，需要查詢V$SQL找出最耗資源的SQL。　　·沒有TOP5 WAIT EVENT,需查找視圖找出最耗時的事件。[關閉窗口]1:41 PM | Add a comment | Permalink | Blog it | OracleOracle性能調優實踐中的幾點心得很多的時侯，做Oracle DBA的我們，當應用管理員向我們通告現在應用很慢、數據庫很慢的時侯，我們到數據庫時做幾個示例的Select也發現同樣的問題時，有些時侯我們會無從下手，因為我們認為數據庫的各種命種率都是滿足Oracle文檔的建議。實際上如今的優化己經向優化等待(waits)轉型了，實際中性能優化最根本的出現點也都集中在IO，這是影響性能最主要的方面，由系統中的等待去發現Oracle庫中的不足、操作系統某些資源利用的不合理是一個比較好的辦法，下面把我的一點實踐經驗與大家分享一下，本文測重于Unix環境。一、通過操作系統的一些工具檢查系統的狀態，比如CPU、內存、交換、磁盤的利用率，根據經驗或與系統正常時的狀態相比對，有時系統表面上看起來看空閑這也可能不是一個正常的狀態，因為cpu可能正等待IO的完成。除此之外我們還應觀注那些占用系統資源(cpu、內存)的進程。1、如何檢查操作系統是否存在IO的問題？使用的工具有sar,這是一個比較通用的工具。 Rp1#Sar -u 2 10 即每隔2秒檢察一次，共執行20次，當然這些都由你決定了。示例返回： HP-UX hpn2 B.11.00 U 9000/800 08/05/03 18:26:32 %usr %sys %wio %idle 18:26:34 80 9 12 0 18:26:36 78 11 11 0 18:26:38 78 9 13 1 18:26:40 81 10 9 1 18:26:42 75 10 14 0 18:26:44 76 8 15 0 18:26:46 80 9 10 1 18:26:48 78 11 11 0 18:26:50 79 10 10 0 18:26:52 81 10 9 0 Average 79 10 11 0 其中的%usr指的是用戶進程使用的cpu資源的百分比，%sys指的是系統資源使用cpu資源的百分比，%wio指的是等待io完成的百分比，這是值得我們觀注的一項，%idle即空閑的百分比。如果wio列的值很大，如在35%以上，說明你的系統的IO存在瓶頸，你的CPU花費了很大的時間去等待IO的完成。Idle很小說明系統CPU很忙。像我的這個示例，可以看到wio平均值為11說明io沒什么特別的問題，而我的idle值為零，說明我的cpu已經滿負荷運行了。當你的系統存在IO的問題，可以從以下幾個方面解決 ♀聯系相應的操作系統的技術支持對這方面進行優化，比如hp-ux在劃定卷組時的條帶化等方面。 ♀查找Oracle中不合理的sql語句，對其進行優化 ♀對Oracle中訪問量頻繁的表除合理建索引外，再就是把這些表分表空間存放以免訪問上產生熱點，再有就是對表合理分區。2、關注一下內存。常用的工具便是vmstat，對于hp-unix來說可以用glance,Aix來說可以用topas,當你發現vmstat中pi列非零，memory中的free列的值很小，glance,topas中內存的利用率多于80%時，這時說明你的內存方面應該調節一下了，方法大體有以下幾項。 ♀劃給Oracle使用的內存不要超過系統內存的1/2,一般保在系統內存的40%為益。 ♀為系統增加內存 ♀如果你的連接特別多，可以使用MTS的方式 ♀打全補丁，防止內存漏洞。3、如何找到點用系用資源特別大的Oracle的session及其執行的語句。Hp-unix可以用glance,topIBM AIX可以用topas些外可以使用ps的命令。通過這些程序我們可以找到點用系統資源特別大的這些進程的進程號，我們就可以通過以下的sql語句發現這個pid正在執行哪個sql，這個sql最好在pl/sql developer,toad等軟件中執行, 把<>中的spid換成你的spid就可以了。SELECT a.username, a.machine, a.program, a.sid, a.serial#, a.status, c.piece, c.sql_text FROM v$session a, v$process b, v$sqltext c WHERE b.spid=<spid> AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece 我們就可以把得到的這個sql分析一下，看一下它的執行計劃是否走索引，對其優化避免全表掃描，以減少IO等待，從而加快語句的執行速度。提示：我在做優化sql時，經常碰到使用in的語句，這時我們一定要用exists把它給換掉，因為Oracle在處理In時是按Or的方式做的，即使使用了索引也會很慢。比如：SELECT col1,col2,col3 FROM table1 a WHERE a.col1 not in (SELECT col1 FROM table2) 可以換成：SELECT col1,col2,col3 FROM table1 a WHERE not exists (SELECT 'x' FROM table2 bWHERE a.col1=b.col1)4、另一個有用的腳本：查找前十條性能差的sql. SELECT * FROM ( SELECT PARSING_USER_ID EXECUTIONS, SORTS, COMMAND_TYPE, DISK_READS, sql_text FROM v$sqlarea ORDER BY disk_reads DESC ) WHERE ROWNUM<10 ;二、迅速發現Oracle Server的性能問題的成因，我們可以求助于v$session_wait這個視圖，看系統的這些session在等什么，使用了多少的IO。以下是我提供的參考腳本：腳本說明：查看占io較大的正在運行的session SELECT se.sid, se.serial#, pr.SPID, se.username, se.status, se.terminal, se.program, se.MODULE, se.sql_address, st.event, st.p1text, si.physical_reads, si.block_changes FROM v$session se, v$session_wait st, v$sess_io si, v$process pr WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC對檢索出的結果的幾點說明：1、我是按每個正在等待的session已經發生的物理讀排的序，因為它與實際的IO相關。2、你可以看一下這些等待的進程都在忙什么，語句是否合理？ Select sql_address from v$session where sid=<sid>; Select * from v$sqltext where address=<sql_address>;執行以上兩個語句便可以得到這個session的語句。你也以用alter system kill session 'sid,serial#';把這個session殺掉。3、應觀注一下event這列，這是我們調優的關鍵一列，下面對常出現的event做以簡要的說明：a、buffer busy waits，free buffer waits這兩個參數所標識是dbwr是否夠用的問題，與IO很大相關的，當v$session_wait中的free buffer wait的條目很小或沒有的時侯，說明你的系統的dbwr進程決對夠用，不用調整；free buffer wait的條目很多，你的系統感覺起來一定很慢，這時說明你的dbwr已經不夠用了，它產生的wio已經成為你的數據庫性能的瓶頸，這時的解決辦法如下：a.1增加寫進程，同時要調整db_block_lru_latches參數示例：修改或添加如下兩個參數 db_writer_processes=4 db_block_lru_latches=8a.2開異步IO，IBM這方面簡單得多，hp則麻煩一些，可以與Hp工程師聯系。b、db file sequential read，指的是順序讀，即全表掃描，這也是我們應該盡量減少的部分，解決方法就是使用索引、sql調優，同時可以增大db_file_multiblock_read_count這個參數。c、db file scattered read,這個參數指的是通過索引來讀取，同樣可以通過增加db_file_multiblock_read_count這個參數來提高性能。d、latch free,與栓相關的了，需要專門調節。e、其他參數可以不特別觀注

上一篇：如何根據一對一，一對多，多對一和多對多的關系創建表

下一篇：sql注入