【文章內(nèi)容簡(jiǎn)介】 27五月09 11 sd 27十月095 rows selected. 3 將用戶名相同的記錄插入samename_users表INSERT INTO samename_users(ID, NAME) SELECT ID, NAME FROM reg_users WHERE NAME IN (SELECT NAME FROM reg_users GROUP BY NAME HAVING COUNT (NAME) 1)。SELECT * FROM samename_users。 ID NAME 13 lisi 10 lisi 22 lisi 12 sd 11 sd 5 rows selected.收集oracle統(tǒng)計(jì)信息優(yōu)化器統(tǒng)計(jì)范圍：表統(tǒng)計(jì)； 行數(shù)，塊數(shù)，行平均長(zhǎng)度；all_tables：NUM_ROWS，BLOCKS，AVG_ROW_LEN；列統(tǒng)計(jì)； 列中唯一值的數(shù)量（NDV），NULL值的數(shù)量，數(shù)據(jù)分布； DBA_TAB_COLUMNS：NUM_DISTINCT，NUM_NULLS，HISTOGRAM；索引統(tǒng)計(jì)；葉塊數(shù)量，等級(jí)，聚簇因子； DBA_INDEXES：LEAF_BLOCKS，CLUSTERING_FACTOR，BLEVEL；系統(tǒng)統(tǒng)計(jì)；I/O性能與使用率； CPU性能與使用率； 存儲(chǔ)在aux_stats$中，需要使用dbms_stats收集，I/O統(tǒng)計(jì)在X$KCFIO中；analyze需要使用ANALYZE統(tǒng)計(jì)的統(tǒng)計(jì)：使用LIST CHAINED ROWS和VALIDATE子句；收集空閑列表塊的統(tǒng)計(jì)；Analyze table tablename pute statistics。Analyze index|cluster indexname estimate statistics。ANALYZE TABLE tablename COMPUTE STATISTICSFOR TABLEFOR ALL [LOCAL] INDEXESFOR ALL [INDEXED] COLUMNS。ANALYZE TABLE tablename DELETE STATISTICSANALYZE TABLE tablename VALIDATE REF UPDATEANALYZE TABLE tablename VALIDATE STRUCTURE [CASCADE]|[INTO TableName]ANALYZE TABLE tablename LIST CHAINED ROWS [INTO TableName]ANALYZE 不適合做分區(qū)表的分析dbms_statsdbms_stats能良好地估計(jì)統(tǒng)計(jì)數(shù)據(jù)（尤其是針對(duì)較大的分區(qū)表），并能獲得更好的統(tǒng)計(jì)結(jié)果，最終制定出速度更快的SQL執(zhí)行計(jì)劃。這個(gè)包的下面四個(gè)存儲(chǔ)過(guò)程分別收集index、table、schema、database的統(tǒng)計(jì)信息: 收集表、列和索引的統(tǒng)計(jì)信息； 收集SCHEMA下所有對(duì)象的統(tǒng)計(jì)信息； 收集索引的統(tǒng)計(jì)信息； 收集系統(tǒng)統(tǒng)計(jì)信息： 所有字典對(duì)象的統(tǒng)計(jì)； 其收集所有系統(tǒng)模式的統(tǒng)計(jì) 刪除表的統(tǒng)計(jì)信息 刪除索引的統(tǒng)計(jì)信息 輸出表的統(tǒng)計(jì)信息 設(shè)置表的統(tǒng)計(jì)統(tǒng)計(jì)收集的權(quán)限==========================必須授予普通用戶權(quán)限sys@ORADB grant execute_catalog_role to hr。sys@ORADB grant connect,resource,analyze any to hr。統(tǒng)計(jì)收集的時(shí)間考慮==========================當(dāng)參數(shù)STATISTICS_LEVEL設(shè)置為TYPICAL或者ALL，系統(tǒng)會(huì)在夜間自動(dòng)收集統(tǒng)計(jì)信息。查看系統(tǒng)自動(dòng)收集統(tǒng)計(jì)信息的job：SELECT * FROM dba_scheduler_jobs WHERE job_name = 39。GATHER_STATS_JOB39。也可以disable自動(dòng)收集統(tǒng)計(jì)信息：BEGIN(39。GATHER_STATS_JOB39。)。END。使用手工統(tǒng)計(jì)對(duì)所有更改活動(dòng)中等的對(duì)象自動(dòng)統(tǒng)計(jì)應(yīng)該足夠充分，由于自動(dòng)統(tǒng)計(jì)收集在夜間進(jìn)行，因此對(duì)于一些更新頻繁的對(duì)象其統(tǒng)計(jì)可能已經(jīng)過(guò)期。兩種典型的對(duì)象：高度變化的表在白天的活動(dòng)期間被TRUNCATE/DROP并重建；塊加載超過(guò)本身總大小10%的對(duì)象；對(duì)于第一種對(duì)象可以使用以下兩種方法：1 將這些表上的統(tǒng)計(jì)設(shè)置為NULL，當(dāng)Oracle遇到?jīng)]有統(tǒng)計(jì)的表時(shí)，將動(dòng)態(tài)收集必要的統(tǒng)計(jì)作為查詢優(yōu)化的一部分；動(dòng)態(tài)收集特征由OPTIMIZER_DYNAMIC_SAMPLING控制，這個(gè)參數(shù)應(yīng)該設(shè)置為大于等于2，默認(rèn)為2?？梢酝ㄟ^(guò)刪除并鎖住統(tǒng)計(jì)將統(tǒng)計(jì)設(shè)置為NULL：(39。SCHEMA39。,39。TABLE39。)。(39。SCHEMA39。,39。TABLE39。)。2 將這些表上的統(tǒng)計(jì)設(shè)置為代表表典型狀態(tài)的值。在表具有某個(gè)有代表性的值時(shí)收集統(tǒng)計(jì)，然后鎖住統(tǒng)計(jì)；由于夜間收集的統(tǒng)計(jì)未必適合于白天的負(fù)載，因此這些情況下使用手工收集比GATHER_STATS_JOB更有效。對(duì)于塊加載，統(tǒng)計(jì)應(yīng)該在加載后立刻收集，通常合并在加載語(yǔ)句的后面防止遺忘。對(duì)于外部表，統(tǒng)計(jì)不能通過(guò)GATHER_DATABASE_STATS，GATHER_SCHEMA_STATS以及自動(dòng)統(tǒng)計(jì)收集收集。因此需要使用GATHER_TABLE_STATS在單個(gè)表上收集統(tǒng)計(jì)，并且在外部表上不支持取樣，ESTIMATE_PERCENT應(yīng)該被顯示設(shè)置為NULL。如果STATISTICS_LEVEL設(shè)置為BASIC禁用了監(jiān)控特征，自動(dòng)統(tǒng)計(jì)收集將不會(huì)檢測(cè)過(guò)期的統(tǒng)計(jì)，此時(shí)需要手工收集。3 需要手工收集的另一個(gè)地方是系統(tǒng)統(tǒng)計(jì)，其不會(huì)自動(dòng)收集。對(duì)于固定表，如動(dòng)態(tài)性能表，需要使用GATHER_FIXED_OBJECTS_STATS收集，這些表上的統(tǒng)計(jì)應(yīng)該在數(shù)據(jù)庫(kù)具有有代表性的活動(dòng)后收集。統(tǒng)計(jì)收集考慮==========================1 統(tǒng)計(jì)收集使用取樣不使用抽樣的統(tǒng)計(jì)收集需要全表掃描并且排序整個(gè)表，抽樣最小化收集統(tǒng)計(jì)的必要資源。2 并行統(tǒng)計(jì)收集，該參數(shù)允許Oracle根據(jù)對(duì)象的大小和并行性初始化參數(shù)的設(shè)置選擇恰當(dāng)?shù)牟⑿卸?。聚簇索引，域索引，位圖連接索引不能并行收集。3 分區(qū)對(duì)象的統(tǒng)計(jì)收集對(duì)于分區(qū)表和索引，DBMS_STATS可以收集單獨(dú)分區(qū)的統(tǒng)計(jì)和全局分區(qū)，對(duì)于組合分區(qū)，可以收集子分區(qū)，分區(qū)，表/索引上的統(tǒng)計(jì)，分區(qū)統(tǒng)計(jì)的收集可以通過(guò)聲明參數(shù)GRANULARITY。根據(jù)將優(yōu)化的SQL語(yǔ)句，優(yōu)化器可以選擇使用分區(qū)統(tǒng)計(jì)或全局統(tǒng)計(jì)，對(duì)于大多數(shù)系統(tǒng)這兩種統(tǒng)計(jì)都是很重要的，Oracle推薦將GRANULARITY設(shè)置為AUTO同時(shí)收集全部信息。4 列統(tǒng)計(jì)和直方圖當(dāng)在表上收集統(tǒng)計(jì)時(shí)，DBMS_STATS收集表中列的數(shù)據(jù)分布的信息，數(shù)據(jù)分布最基本的信息是最大值和最小值，但是如果數(shù)據(jù)分布是傾斜的，這種級(jí)別的統(tǒng)計(jì)對(duì)于優(yōu)化器來(lái)說(shuō)不夠的，對(duì)于傾斜的數(shù)據(jù)分布，直方圖通常用來(lái)作為列統(tǒng)計(jì)的一部分。直方圖通過(guò)METHOD_OPT參數(shù)聲明，Oracle推薦設(shè)置METHOD_OPT為FOR ALL COLUMNS SIZE AUTO，使用該值時(shí)Oracle自動(dòng)決定需要直方圖的列以及每個(gè)直方圖的桶數(shù)。也可以手工設(shè)置需要直方圖的列以及桶數(shù)。如果在使用DBMS_STATS的時(shí)候需要?jiǎng)h除表中的所有行，需要使用TRUNCATE代替drop/create，否則自動(dòng)統(tǒng)計(jì)收集特征使用的負(fù)載信息以及RESTORE_*_STATS使用的保存的統(tǒng)計(jì)歷史將丟失。這些特征將無(wú)法正常發(fā)揮作用。5 確定過(guò)期的統(tǒng)計(jì)對(duì)于那些隨著時(shí)間更改的對(duì)象必須周期性收集統(tǒng)計(jì)，為了確定過(guò)期的統(tǒng)計(jì)，Oracle提供了一個(gè)表監(jiān)控這些更改，這些監(jiān)控默認(rèn)情況下在STATISTICS_LEVEL為TYPICAL/ALL時(shí)啟用，該表為USER_TAB_MODIFICATIONS。 _MONITORING_INFO可以立刻反映內(nèi)存中超過(guò)監(jiān)控的信息。在OPTIONS參數(shù)設(shè)置為GATHER STALE or GATHER AUTO時(shí)，DBMS_STATS收集過(guò)期統(tǒng)計(jì)的對(duì)象的統(tǒng)計(jì)。6 用戶定義統(tǒng)計(jì)在創(chuàng)建了基于索引的統(tǒng)計(jì)后，應(yīng)該在表上收集新的列統(tǒng)計(jì)，這可以通過(guò)調(diào)用過(guò)程設(shè)置METHOD_OPT的FOR ALL HIDDEN COLUMNS。7 何時(shí)收集統(tǒng)計(jì)對(duì)于增量更改的表，可能每個(gè)月/每周只需要收集一次，而對(duì)于加載后表，通常在加載腳本中增加收集統(tǒng)計(jì)的腳本。對(duì)于分區(qū)表，如果僅僅是一個(gè)分區(qū)有了較大改動(dòng)，只需要收集一個(gè)分區(qū)的統(tǒng)計(jì)，但是收集整個(gè)表的分區(qū)也是必要的。系統(tǒng)統(tǒng)計(jì)==========================系統(tǒng)統(tǒng)計(jì)描述系統(tǒng)硬件的特征，包括I/O和CPU。在選擇執(zhí)行計(jì)劃時(shí)，優(yōu)化器考慮查詢所需的CPU和I/O代價(jià)。系統(tǒng)統(tǒng)計(jì)允許優(yōu)化器更加精確的評(píng)價(jià)CPU和IO代價(jià)，選擇更好的查詢計(jì)劃。，Oracle推薦收集系統(tǒng)統(tǒng)計(jì)。收集系統(tǒng)統(tǒng)計(jì)需要DBA權(quán)限。收集的優(yōu)化器系統(tǒng)統(tǒng)計(jì)包括：cpuspeedNW：代表無(wú)負(fù)載CPU速度，CPU速度為每秒鐘CPU周期數(shù)；通過(guò)設(shè)置gathering_mode = NOWORKLOAD或手工設(shè)置統(tǒng)計(jì)；單位Millions/sec。ioseektim：I/O查找時(shí)間=查找時(shí)間+延遲時(shí)間+OS負(fù)載時(shí)間；通過(guò)設(shè)置gathering_mode = NOWORKLOAD或手工設(shè)置統(tǒng)計(jì)；單位為ms。Iotfrspeed：I/O傳輸速度；通過(guò)設(shè)置gathering_mode = NOWORKLOAD或手工設(shè)置統(tǒng)計(jì)；單位為Bytes/ms.Cpuspeed：代表有負(fù)載CPU速度，CPU速度為每秒鐘CPU周期數(shù)；通過(guò)設(shè)置gathering_mode =NOWORKLOAD,INTERVAL, START|STOP或手工設(shè)置統(tǒng)計(jì)；單位Millions/sec。Maxthr：最大I/O吞吐量；通過(guò)設(shè)置gathering_mode =NOWORKLOAD,INTERVAL, START|STOP或手工設(shè)置統(tǒng)計(jì)；單位Bytes/sec.Slavethr：服務(wù)I/O吞吐量是平均并行服務(wù)I/O吞吐量；通過(guò)設(shè)置gathering_mode = INTERVAL，START|STOP或手工設(shè)置統(tǒng)計(jì)；Bytes/sec.Sreadtim：隨機(jī)讀取單塊的平均時(shí)間；通過(guò)設(shè)置gathering_mode =INTERVAL，START|STOP或手工設(shè)置統(tǒng)計(jì)；單位為ms。Mreadtim：順序讀取多塊的平均時(shí)間，通過(guò)設(shè)置通過(guò)設(shè)置gathering_mode = INTERVAL，START|STOP或手工設(shè)置統(tǒng)計(jì)；單位為ms。Mbrc: 多塊讀平均每次讀取的塊數(shù)量；通過(guò)設(shè)置通過(guò)設(shè)置gathering_mode = INTERVAL，START|STOP或手工設(shè)置統(tǒng)計(jì)；單位為blocks。系統(tǒng)統(tǒng)計(jì)的重新收集不會(huì)導(dǎo)致當(dāng)前的SQL無(wú)效，只是所有的新SQL語(yǔ)句使用新的統(tǒng)計(jì)。Oracle提供兩個(gè)選項(xiàng)收集統(tǒng)計(jì)：負(fù)載統(tǒng)計(jì)；非負(fù)載統(tǒng)計(jì)。負(fù)載統(tǒng)計(jì)==========================(’start’)，(’stop’)結(jié)束負(fù)載窗口。(’interval’, interval=N)，N表示N分鐘后系統(tǒng)統(tǒng)計(jì)收集結(jié)束。()刪除負(fù)載統(tǒng)計(jì)。非負(fù)載統(tǒng)計(jì)==========================()收集非負(fù)載統(tǒng)計(jì)，運(yùn)行非負(fù)載統(tǒng)計(jì)時(shí)會(huì)有一定的I/O負(fù)載。在某些情況下，非負(fù)載統(tǒng)計(jì)的值可能會(huì)保持默認(rèn)。管理統(tǒng)計(jì)==========================轉(zhuǎn)儲(chǔ)先前版本的統(tǒng)計(jì)使用RESTORE過(guò)程轉(zhuǎn)儲(chǔ)先前版本的統(tǒng)計(jì)，這些過(guò)程使用一個(gè)時(shí)間戳作為參數(shù)，包含統(tǒng)計(jì)時(shí)間的視圖包括：1 DBA_OPTSTAT_OPERATIONS：其中包含了使用DBMS_STATS在模式/系統(tǒng)級(jí)別執(zhí)行的統(tǒng)計(jì)操作；2 *_TAB_STATS_HISTORY：包含了表統(tǒng)計(jì)更改的歷史。舊的統(tǒng)計(jì)定期刷新，根據(jù)DBMS_STATS的ALTER_STATS_HISTORY_RETENTION過(guò)程設(shè)置而定，默認(rèn)為31天。默認(rèn)情況下，如果STATISTICS_LEVEL為TYPICAL/ALL，自動(dòng)刷新啟用；否則需要使用PURGE_STAT手工刷新。其他轉(zhuǎn)儲(chǔ)與刷新相關(guān)的信息包括：PURGE_STATS： 手工刷新超過(guò)某個(gè)時(shí)間戳的舊統(tǒng)計(jì)；GET_STATS_HISTORY_RENTENTION： 得到當(dāng)前歷史統(tǒng)計(jì)保留值；GET_STATS_HISTORY_AVAILABILTY： 得到可用的最舊的統(tǒng)計(jì)的時(shí)間戳。轉(zhuǎn)儲(chǔ)的限制：1 不能轉(zhuǎn)儲(chǔ)用戶定義統(tǒng)計(jì)；2 如果使用了ANALYZE收集，舊的統(tǒng)計(jì)將無(wú)法轉(zhuǎn)儲(chǔ)。導(dǎo)入/導(dǎo)出統(tǒng)計(jì)==========================，*_STATS導(dǎo)出統(tǒng)計(jì)到自定義表，*_STATS重新導(dǎo)入。也可以使用IMP/EXP導(dǎo)到其他數(shù)據(jù)庫(kù)。轉(zhuǎn)儲(chǔ)統(tǒng)計(jì)與導(dǎo)入導(dǎo)出統(tǒng)計(jì)使用轉(zhuǎn)儲(chǔ)的情況：1 恢復(fù)舊版本的統(tǒng)計(jì)；2 希望數(shù)據(jù)庫(kù)管理統(tǒng)計(jì)歷史的保留和刷新；使用EXPORT/IMPORT_*_STATS的情況：1 實(shí)驗(yàn)各種值的不同情況；2 移動(dòng)統(tǒng)計(jì)到不同數(shù)據(jù)庫(kù)；3 保留統(tǒng)計(jì)數(shù)據(jù)更長(zhǎng)的時(shí)間。鎖住表和模式的統(tǒng)計(jì)==========================一旦統(tǒng)計(jì)被鎖住，將無(wú)法在更改這些統(tǒng)計(jì)直到被解鎖。DBMS_STAT提供兩個(gè)過(guò)程用于解鎖，兩個(gè)用于加鎖：1 LOCK_SCHEMA_STATS；161。164。LOCK_TABLE_STATS；2 UNLOCK_SCHEMA_STATS；161。164。UNLOCK_TABLE_STATS；設(shè)置統(tǒng)計(jì)==========================可以使用SET_*_STATISTICS設(shè)置表，索引，列，系統(tǒng)統(tǒng)計(jì)。使用動(dòng)態(tài)取樣評(píng)價(jià)統(tǒng)計(jì)==========================動(dòng)態(tài)取樣的目的是通過(guò)為謂詞選擇性和表/索引統(tǒng)計(jì)確定更加精確的估計(jì)提高服務(wù)器性能，估計(jì)越精確產(chǎn)生的性能更好。可以使用動(dòng)態(tài)取樣的情況：1 在收集的統(tǒng)計(jì)不能使用或會(huì)導(dǎo)致嚴(yán)重的估計(jì)錯(cuò)誤時(shí)估計(jì)單表的謂詞選擇性；2 估計(jì)沒(méi)有統(tǒng)計(jì)的表/索引的統(tǒng)計(jì)；3 估計(jì)統(tǒng)計(jì)過(guò)期的表和索引的統(tǒng)計(jì)；動(dòng)態(tài)取樣特征由參數(shù)OPTIMIZER_DYNAMIC_SAMPLING控制，默認(rèn)級(jí)別為2。動(dòng)態(tài)取樣的工作機(jī)制主要的性能特征是編譯時(shí)，Oracle在編譯時(shí)決定一個(gè)查詢是否能通過(guò)取樣獲益，如果可以，將用遞歸SQL隨機(jī)掃描一小部分表塊，然后應(yīng)用相關(guān)的單表謂詞評(píng)價(jià)謂詞選擇性。使用動(dòng)態(tài)取樣的時(shí)間使用動(dòng)態(tài)取樣將獲益的情況：1 可以發(fā)現(xiàn)更好的執(zhí)行計(jì)劃；2 取樣時(shí)間僅占總時(shí)間的一小部分；3 查詢將執(zhí)行多次；取樣級(jí)別==========================范圍從1..10缺失統(tǒng)計(jì)處理==================