【正文】 2 move partition ‘|| 。. 查看表的信息Select * from user_tables。) and = upper(39。)。Rows for each sort：數(shù)據(jù)排序情況公式：sorts (rows) / ( sorts (memory) + sorts (disk) )執(zhí)行：select (+) from v$sysstat a,v$sysstat b,v$sysstat c where =’sorts (rows)’ and =’sorts (memory)’ and =’sorts (disk)’。從V$SYSSTAT獲取負(fù)載間檔(Load Profile)數(shù)據(jù)負(fù)載間檔是監(jiān)控系統(tǒng)吞吐量和負(fù)載變化的重要部分，該部分提供如下每秒和每個(gè)事務(wù)的統(tǒng)計(jì)信息：logons cumulative, parse count (total), parse count (hard), executes, physical reads, physical writes, block changes, and redo size.被格式化的數(shù)據(jù)可檢查’rates’是否過高，或用于對(duì)比其它基線數(shù)據(jù)設(shè)置為識(shí)別system profile在期間如何變化。Parse time CPU to parse time elapsed：通常，該項(xiàng)顯示鎖競(jìng)爭比率。公式：1 (parse count/execute count)執(zhí)行：select 1() from v$sysstat a,v$sysstat b where =’parse count (total)’ and =’execute count’。Inmemory sort ratio：該項(xiàng)顯示內(nèi)存中完成的排序所占比例。公式：1 ( parse count (hard) / parse count (total) ) 執(zhí)行：select 1() from v$sysstat a,v$sysstat b Where =39。Soft parse ratio：這項(xiàng)將顯示系統(tǒng)是否有太多硬解析。db block gets39。公式：1((physical readsphysical reads directphysical reads direct (lob)) / session logical reads)執(zhí)行：select 1(()/) from v$sysstat a,v$sysstat b,v$sysstat c,v$sysstat d where =’physical reads’ and =’physical reads direct’ and =’physical reads direct (lob)’ and =’session logical reads’。. 查找Fast Full index 掃描的Sql語句可以這樣: Select sql_text from v$sqltext t, v$sql_plan p Where = And =’INDEX’ And =’FULL SCAN’ Order by , 。NAME VALUE table scans (short tables) 18602table scans (long tables) 111SQL select count(target),target from v$session_longops where opname = 39。table scans (short tables)39。 ORDER BY username。 AND NOT LIKE 39。因此，做好日常的檢查與管理工作尤其重要，主要工作包括：l 數(shù)據(jù)庫的性能監(jiān)控檢查l 數(shù)據(jù)庫狀態(tài)巡檢檢查l 數(shù)據(jù)庫備份情況與可恢復(fù)性檢查. 數(shù)據(jù)庫性能監(jiān)控管理通過對(duì)數(shù)據(jù)庫性能監(jiān)控的管理工作，可以有效的防范風(fēng)險(xiǎn)事件的發(fā)生，并能從長時(shí)間的運(yùn)行數(shù)據(jù)中，總結(jié)出數(shù)據(jù)庫性能的狀態(tài)，為系統(tǒng)性能優(yōu)化、隱患事件排查提供更好的依據(jù)。. 數(shù)據(jù)庫管理Oracle數(shù)據(jù)庫的管理主要是要做好日常的檢查管理工作，并檢查好數(shù)據(jù)庫的備份情況，在發(fā)生緊急情況時(shí)能及時(shí)不僅切換到備用系統(tǒng)上進(jìn)行工作，并要能在數(shù)據(jù)庫數(shù)據(jù)出現(xiàn)異常的時(shí)候，能及時(shí)恢復(fù)數(shù)據(jù)。SQL*N%39。enqueue39。. 查詢?nèi)頀呙璧谋鞸QL col name for a30SQL select name,value from v$sysstat where name in (39。)。COUNT(TARGET) TARGET 84 159 9 2 . 查出全表掃描的表Select sql_text from v$sqltext t, v$sql_plan p Where = And =’TABLE ACCESS’ And =’FULL’ Order by , 。NAME VALUE parse count (total) 16103parse count (hard) 343parse count (failures) 5該項(xiàng)顯示buffer cache大小是否合適。,value,0)))/(sum(decode(name,39。,value,0))) buffer hit ratio from v$sysstat。不過，如果總解析量(parse count total)偏低，這項(xiàng)值可以被忽略。parse count (total)39。Parse to execute ratio：在生產(chǎn)環(huán)境，最理想狀態(tài)是一條sql語句一次解析多數(shù)運(yùn)行。公式：1 (parse time cpu / CPU used by this session)執(zhí)行：select 1() from v$sysstat a,v$sysstat b where =’parse time cpu’ and =’CPU used by this session’。公式：parse time cpu / parse time elapsed執(zhí)行：select $sysstat a,v$sysstat b where =’parse time cpu’ and =’parse time elapsed’。它將指出是否系統(tǒng)主要用于只讀訪問或是主要進(jìn)行諸多數(shù)據(jù)操作(如：inserts/updates/deletes)公式：db block changes / session logical reads執(zhí)行：select $sysstat a,v$sysstat b where =’db block changes’ and =’session logical reads’ 。table_name39。table_owner39。) and = and = order by 。TABLESPACE_NAME TS_SIZE CWMLITE 20DRSYS 20ODM 20PERFSTAT 99SYSTEM 400. 查看回滾段名稱及大小select segment_name, tablespace_name, , (initial_extent/1024) InitialExtent, (next_extent/1024) NextExtent, max_extents, CurExtent From dba_rollback_segs r, v$rollstat v Where = (+) order by segment_name 。/. 查看LOCKSELECT /*+ ORDERED USE_HASH(H,R) */ HOLD_SID, WAIT_SID, decode(, ”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”, ) type, decode(, 0, ”None”, 1, ”Null”, 2, ”RowS (SS)”, 3, ”RowX (SX)”, 4, ”Share”, 5, ”S/RowX (SSX)”, 6, ”Exclusive”, to_char()) hold, decode(, 0, ”None”, 1, ”Null”, 2, ”RowS (SS)”, 3, ”RowX (SX)”, 4, ”Share”, 5, ”S/RowX (SSX)”,6, ”Exclusive”, to_char()) request, ,FROM VLOCK H,VLOCK R WHERE = 1 AND =0 and ”MR” AND ”MR” AND = AND = . 找ORACLE字符集select * from $ where name=39。 /. 查看空間詳細(xì)使用情況CREATE OR REPLACE PROCEDURE show_space ( p_segname IN VARCHAR2, p_owner IN VARCHAR2 DEFAULT USER, p_type IN VARCHAR2 DEFAULT 39。 l_unused_blocks NUMBER。 l_last_used_block NUMBER。 END。, l_total_blocks)。 p (39。Unused Bytes39。, l_lastusedextfileid)。 p (39。/. 顯示緩沖區(qū)的相關(guān)SQLSELECT tch, file, dbablk, CASE WHEN obj = 4294967295 THEN 39。|| object_type|| 39。|| object_name) || DECODE (COUNT (*), 1, 39。) FROM dba_objects WHERE data_object_id = ) END whatFROM (SELECT tch,file,dbablk,obj FROM x$bh WHERE state 0 ORDER BY tch DESC) xWHERE ROWNUM = 5。_ora_39。sessionid39。在v$session_longops視圖中，sofar字段表示已經(jīng)掃描的塊數(shù)，totalwork表示總得需要掃描的塊數(shù)，所以我們即可以對(duì)正在運(yùn)行的長查詢進(jìn)行監(jiān)控，比如在索引創(chuàng)建時(shí)，查看索引創(chuàng)建的進(jìn)度，也可以查看系統(tǒng)中以往的長查詢。set lines 121set