SQL應用及優(yōu)化

1、 2006 hewlett-packard development company, l.p.the information contained herein is subject to change without notice sql應用及優(yōu)化2009.04目錄一、sql應用1.查詢語句2.函數(shù)3.多表查詢和子查詢4.dml語句應用二、sql優(yōu)化1.sql語句的優(yōu)化器2.調優(yōu)sql語句的相關工具3.影響cbo模式的統(tǒng)計信息4.sql語句優(yōu)化的基本原則5.rbo模式下的規(guī)則和建議6.sql語句中的hint7.寫sql語句的一些提示三、附錄：1.常見oracle hint的用法2.監(jiān)控數(shù)據(jù)庫性

2、能的sqlsql應用一、基本查詢語法：select *|distinct column|expression alias,. from tablewhere condition(s) order by column, expr, alias asc|desc;select column, group_function from table where condition group by group_by_expression having group_condition order by column;where 中可以使用的一些運算 any ，some ，all ， not ，and ，o

3、r（需要注意，建議使用or的地方都加上括號），in， not in ，between ，exists，is null ，is not null，likeorder by asc，desc 排序字段必須在 選擇的列表中 ，對null 要注意，默認是在最后的?？梢酝ㄟ^nulls last 或nulls first來改變。order by、 having 中可以使用子查詢表的集合操作：union 、union all 、minus、 intersectdual 選擇系統(tǒng)變量和計算表達式。 如select sysdate，user from dual；select 中的 group by使用與havi

4、ng 典型實例：1、匯總 2、查重等select file_name,count(*) from log_file_twhere modual = imp and partition_id_month = 3 group by file_name having count(*) 1其他select使用 如：復制表結構 create table t1 as select * from t where 1=2；sql應用二、函數(shù)使用中注意單行函數(shù)和多行函數(shù)case expressioncase expr when comparison_expr1 then return_expr1 when co

5、mparison_expr2 then return_expr2 when comparison_exprn then return_exprn else else_exprenddecode functiondecode(col|expression, search1, result1 , search2, result2,., , default)示例：update serv_biz_code set carry_type = case when service_type = mas then sms when service_type = 400 then voice else carr

6、y_typeend;update serv_biz_code set carry_type = case service_type when mas then sms when 400 then voice else carry_typeend;好處：只使用一次全表掃描。簡單明了。sql應用三、多表查詢和子查詢注意：避免笛卡爾積 建議：對表使用別名，關聯(lián)相關的字段。外連接使用（+）oracle9i開始支持，sql 99標準。select table1.column, table2.columnfromtable1, table2wheretable1.column = table2.colum

7、n(+);sql應用示例對對not in 的改進語句的幾種方法的改進語句的幾種方法1.select s.ec_code from serv_biz_code s where s.ec_code not in (select t.customer_code from bill_item_usage_detail t )2.select s.ec_code from serv_biz_code s where not exists (select 1 from bill_item_usage_detail b where s.ec_code = b.customer_code )3.select

8、s.ec_code from serv_biz_code s , bill_item_usage_detail bwhere s.ec_code = b.customer_code(+) and b.customer_code is null 多列的子查詢（ ，） in （，）select s.ec_code,duct_code from serv_biz_code s where (s.ec_code,duct_code) not in (select t.customer_code,duct_code from bill_item_usage_detail t

9、 )其他dml中的子查詢update /*+ rowid(bb) */ ur_sms_200904_t bb set bb.charge1 = ( select b.charge1 from jxj_rater_ur_20090417 b where bb.rowid = b.id )where exists (select 1 from jxj_rater_ur_20090417 aa where aa.id = bb.rowid) ;注意： aa.id和bb.rowid 的值是物理地址物理地址，如果表或記錄變更過該語句講實效。rowid 和rownum 的使用 rowmum的數(shù)據(jù)具有隨機性

10、，可以通過建立子查詢的視圖，根據(jù)固定的關鍵字進行order by 后再使用，盡量保證數(shù)據(jù)唯一性。sql應用子查詢單行子查詢和多行子查詢，注意使用的條件with as 使用介紹提高查詢的性能，如果一個sql中相同的部分比較多，可以放到一起，執(zhí)行一個with as在實際應用中，可以應用到各個環(huán)節(jié)sql應用示例-數(shù)據(jù)庫為bill數(shù)據(jù)庫，目的為了檢查rater計算結果是否正確，從bill_item_usage_detail表中取出的累計條數(shù)與本身套餐值直接計算，看是否-與bill_item_usage_detail表中累計的錢相同，如果相同，認為rater計算正確，如果不相同，需要查找原因-創(chuàng)建帳單匯

11、總表，找到指定月份指定出賬日前的所有計費的話單條數(shù)累計-drop table zhp_200903_bill_item;create table zhp_200903_bill_item as select customer_code,sum(accu_occurence) bill_count,sum(charge) bill_charge -,sum(charge) /sum(accu_occurence) fee from bill_item_usage_detaillink_imp b where b.acct_month = 200904 and b.charge_item is n

12、ot null and product_code = 010101001 and b.partition_id_day 0 /*如果達不到累計數(shù)量，則bill_count-y=0*/ then b.bill_count-y else 0 end count-y,case when z 0 then case when (b.bill_count-y)*z = to_date(20090401,yyyymmdd) t1 , chg_plan_inst_bridge t2 where t1.chg_plan_inst_id = t2.cp_inst_id /*and t2.svc_opt_code

13、 = comm_fee*/ ),ec2info as (select distinct t1.customer_code ec_code,t2.param_name,t2.param_value from (select customer_code,chg_plan_inst_id from ecgroup2.subscribed_chg_plan a,ecgroup2.cus_product_bridge b where a.sub_id = b.id and duct_code = 010101001 and a.expiry_date = to_date(20090401,yy

14、yymmdd) ) t1 , chg_plan_inst_bridge t2 where t1.chg_plan_inst_id = t2.cp_inst_id /*and t2.svc_opt_code = comm_fee*/ )/*兩個ecgroup中的定購套餐*/select t.ec_code,nvl(t.param_value,0) x,nvl(tt.param_value,0) y,nvl(ttt.param_value,0) z from (select * from ec1info where param_name = x) t, (select * from ec1info

15、 where param_name = y) tt, (select * from ec1info where param_name = z) tttwhere t.ec_code = tt.ec_code(+) and tt.ec_code = ttt.ec_code(+)and (t.param_value 0 or tt.param_value 0)union allselect t.ec_code,nvl(t.param_value,0) x,nvl(tt.param_value,0) y,nvl(ttt.param_value,0) z from (select * from ec2

16、info where param_name = x) t, (select * from ec2info where param_name = y) tt, (select * from ec2info where param_name = z) tttwhere t.ec_code = tt.ec_code(+) and tt.ec_code = ttt.ec_code(+)and (t.param_value 0 or tt.param_value 0) ) aa, zhp_200903_bill_item bwhere aa.ec_code = b.customer_code;sql應用

17、四、dml1、insert nologing 和append2、update 3、delete4、merge事物控制語句commitsavepointrollback生產(chǎn)數(shù)據(jù)一定先備份，然后再操作。同時注意，減少for update的操作。（鎖表，同時操作步驟（sql）、數(shù)據(jù)等沒有備份對以后問題的查找會有一定得影響，盡量使用update語句，對操作現(xiàn)場和數(shù)據(jù)保留）delete -truncate table (ddl) truncate 沒有回滾段，不能undo ,不進行trigger 的觸發(fā)。且自動完成commit。比較危險，慎用! 好處：快。如果數(shù)據(jù)表經(jīng)常的delete 和insert 會

18、產(chǎn)生很多碎片，即使數(shù)據(jù)少任然會很慢，此時可以用truncate，對表進行清理。此時如果有索引最好rubuild一下。sql應用insert append需要注意的一個小問題1. append方式添加記錄對insert into . values語句不起作用。2. 以append方式批量插入的記錄，其存儲位置在hwm 之上，即使hwm之下存在空閑塊也不能使用。3. 以append方式插入記錄后，要執(zhí)行commit，才能對表進行查詢。否則會出現(xiàn)錯誤：ora-12838: 無法在并行模式下修改之后讀/修改對象4. 在歸檔模式下，要把表設置為nologging，然后以append方式批量添加記錄，才會

19、顯著減少redo數(shù)量。在非歸檔模式下，不必設置表的nologging屬性，即可減少redo數(shù)量。如果表上有索引，則append方式批量添加記錄，不會減少索引上產(chǎn)生的redo數(shù)量，索引上的redo數(shù)量可能比表的redo數(shù)量還要大。用insert append可以實現(xiàn)直接路徑加載，速度比常規(guī)加載方式快。但有一點需要注意: insert append時在表上加“6”類型的鎖，會阻塞表上的所有dml語句。因此在有業(yè)務運行的情況下要慎重使用。若同時執(zhí)行多個insert append對同一個表并行加載數(shù)據(jù)，并不一定會提高速度。因為每一時刻只能有一個進程在加載(排它鎖造成)。insert all when

20、cond1 then into table1 when cond2 then into table2 select * from tab；sql應用 示例merge into table_name table_alias using (table|view|sub_query) alias on (join condition) when matched then update set col1 = col_val1, col2 = col2_val when not matched then insert (column_list) values (column_values);merge

21、into copy_emp c using employees e on (c.employee_id = e.employee_id)when matched then update set c.first_name = e.first_name, c.last_name = e.last_name, . c.department_id = e.department_idwhen not matched then insert values(e.employee_id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_da

22、te, e.job_id, e.salary, mission_pct, e.manager_id, e.department_id);delete /*+ rowid(t) */ log_file_t where rowid in (select row_id from ( select file_name,min(rowid) row_id,count(*) from log_file_t where modual = imp and partition_id_month = 3 group by file_name having count(*) 1 )課后：請指出這條語句的問題？課后：

23、請指出這條語句的問題？sql優(yōu)化-基本原則sql語句的優(yōu)化器調優(yōu)sql語句的相關工具影響cbo模式的統(tǒng)計信息sql語句優(yōu)化的基本原則rbo模式下的規(guī)則和建議sql語句中的hint執(zhí)行計劃的穩(wěn)定性執(zhí)行計劃的穩(wěn)定性sql優(yōu)化什么是優(yōu)化器？優(yōu)化器(optimizer) 是一組oracle的內(nèi)部例行程序，該例行程序用以決定sql語句的最有效的訪問路徑。oracle數(shù)據(jù)庫會自動進行查詢優(yōu)化,這些操作對于用戶來說是透明的優(yōu)化器模式通過初始化參數(shù)optimizer_mode設置優(yōu)化模式在oracle9i, 兩種優(yōu)化模式可以被選擇: 基于規(guī)則的 rule-based:使用一個分級系統(tǒng)語法(syntax)驅動和

24、字典(dictionary)驅動的oracle從早期版本提供rbo優(yōu)化器，至oracle10g該優(yōu)化器不再被支持基于代價的 cost-based:選擇最低代價的路徑統(tǒng)計(statistics)驅動從oracle7(1992)-oracle10g主要支持的優(yōu)化器sql優(yōu)化設置優(yōu)化模式在實例級:optimizer_mode = choose|rule|first_rows|first_rows_n| all_rows在會話級:alter session set optimizer_mode = choose|rule|first_rows|first_rows_n| all_rows在語句級: 使

25、用提示(hints)oracle10g?rbo不再被支持缺省的優(yōu)化器模式為all_rowsoptimizer_modechoose/rule 參數(shù)不再支持all_rows/first_rows/first_rows_n 是oralce10g支持的優(yōu)化目標sql優(yōu)化-診斷工具概述statspackexplain plansql trace 和 tkprofsql*plus autotrace 特性oracle sql analyzestatspack和explain planstatspack對數(shù)據(jù)庫進行數(shù)據(jù)的采集，通過分析報告診斷問題。采集的數(shù)據(jù)量比較大在statspack中關于sql的報告s

26、tatspack 收集了下列關于sql的信息:sql ordered by getssql ordered by readssql ordered by executionssql ordered by parse callsexplain plan不需要打開跟蹤就可以使用需要 plan_table 表:創(chuàng)建解釋表:查詢 plan_table 表顯示執(zhí)行計劃:直接查詢 plan_table使用腳本 utlxpls.sql (隱藏了并行查詢的信息)使用腳本 utlxplp.sql (顯示并行查詢信息)sql $oracle_home/rdbms/admin/utlxplansql explain

27、 plan for 2 select last_name from hr.employees;使用 sql trace 和 tkprofsql trace是oracle提供的用于進行sql跟蹤的手段，是強有力的輔助診斷工具。在日常的數(shù)據(jù)庫問題診斷和解決中是非常常用的方法。 一般，一次跟蹤可以分為以下幾步： 1、界定需要跟蹤的目標范圍，并使用適當?shù)拿顔⒂盟韪櫋?2、經(jīng)過一段時間后，停止跟蹤。此時應該產(chǎn)生了一個跟蹤結果文件。 3、找到跟蹤文件，并對其進行格式化，然后閱讀或分析。 為了使用 sql trace 和 tkprof:設置初始化參數(shù).alter session set sql_tra

28、ce = true運行應用.alter session set sql_trace = false使用 tkprof格式化顯示跟蹤文件的內(nèi)容.解釋輸出的結果.啟用或禁用 sql trace在實例級:sql_trace = true|false在會話級: sql alter session set sql_trace = true|false;sql execute dbms_session.set_sql_trace 2 (true|false);sql execute dbms_system.set_sql_trace_in_session 2 (session_id, serial_id,

29、 true|false);使用tkprof格式化跟蹤文件$ tkprof tracefile.trc output.txt optionstkprof 提供的統(tǒng)計信息提供的統(tǒng)計信息count:執(zhí)行調用的次數(shù)cpu:cpu 使用的描秒數(shù)elapsed:總的持續(xù)時間disk:物理讀數(shù)query:一致性讀(consistent read )的邏輯讀數(shù)current:當前模式(current mode )的邏輯讀數(shù)rows: 處理的行數(shù)sql*plus autotrace在sql*plus環(huán)境下使用的執(zhí)行計劃功能創(chuàng)建 plan_table 表創(chuàng)建和授予 plustrace 角色 sql $oracle

30、_home/sqlplus/admin/plustrce.sqlsql grant plustrace to scott;autotrace 語法:set autotrace off | on | traceonly explain | statistics 影響cbo模式的統(tǒng)計信息管理統(tǒng)計信息使用 analyze 命令收集或者刪除統(tǒng)計.也可以使用 dbms_stats 包: gather_table_stats gather_index_stats gather_schema_stats gather_database_stats存放統(tǒng)計信息的數(shù)據(jù)字典下列數(shù)據(jù)字典和特殊視圖在執(zhí)行analyz

31、e命令后可以提供非常有用的統(tǒng)計信息:dba_tables, dba_tab_columnsdba_clustersdba_indexes, index_statsindex_histogram, dba_tab_histograms這些統(tǒng)計信息是靜態(tài)的,將一直保持到再次執(zhí)行analyze命令或者dbms_stats包.影響cbo模式的統(tǒng)計信息表的統(tǒng)計信息表的統(tǒng)計信息行數(shù)塊數(shù)和空塊數(shù)平均可用的空閑空間行鏈和行遷移數(shù)平均行長度最后一次 analyze 時間和樣例值大小數(shù)據(jù)字典: dba_tables索引的統(tǒng)計信息索引的統(tǒng)計信息索引級別 (高度)葉子塊數(shù)和唯一值數(shù)每個關鍵字的平均葉子塊數(shù)每個關鍵字的

32、平均數(shù)據(jù)塊數(shù)索引記錄數(shù)聚簇因子(clustering factor)數(shù)據(jù)字典: dba_indexes列的統(tǒng)計信息列的統(tǒng)計信息唯一值數(shù)最小值, 最大值 (以 raw binary 格式存儲)最后一次 analyze 的時間和樣例值大小數(shù)據(jù)字典: user_tab_col_statisticssql優(yōu)化基本原則：1.減少重分析2.合理使用索引3.使用合理的表連接方式4.減少不必要的排序5.降低邏輯讀sql優(yōu)化sql解析is it in an open cursor（ session_cached_cursors =on）? | no|yes open a cursor| |statement i

33、s hashed and compared ( with the hashed value in the sql area )| is it in sql area? -yes-(soft parse)- - | execute | | | no parse statement (hard parse)- | execute | soft parse 軟解析，通過hash計算查找sql在共享池中存在于是重用執(zhí)行計劃的過程hard parse 硬解析，通過hash計算查找發(fā)現(xiàn)sql在共享池中不存在，于是重新產(chǎn)生執(zhí)行計劃的過程 軟解析和硬解析對資源的消耗差異很大 sql優(yōu)化sql語句的解析執(zhí)行步驟

34、:1 語法分析 分析語句的語法是否符合規(guī)范，衡量語句中各表達式的意義。 2 語義分析 檢查語句中涉及的所有數(shù)據(jù)庫對象是否存在，且用戶有相應權限。 3 視圖轉換 將涉及視圖的查詢語句轉換為相應的對基表查詢語句。4 表達式轉換 將復雜的sql表達式轉換為較簡單的等效連接表達式。 5 選擇優(yōu)化器 不同的優(yōu)化器一般產(chǎn)生不同的“執(zhí)行計劃” 6 選擇連接方式 oracle有三種連接方式，對多表連接oracle可選擇適當?shù)倪B接方式。 7 選擇連接順序 對多表連接oracle選擇哪一對表先連接，選擇這兩表中哪個表做為源數(shù)據(jù)表。 8 選擇數(shù)據(jù)的搜索路徑 根據(jù)以上條件選擇合適的數(shù)據(jù)搜索路徑，如是選用全表搜索還是利

35、用索引或是其他的方式。 9 產(chǎn)生“執(zhí)行計劃” 并執(zhí)行sql優(yōu)化數(shù)據(jù)庫共享池為什么要使用共享池1.sql解析代價比較高2.有限周期內(nèi)系統(tǒng)一般運行大量重復代碼3.系統(tǒng)的sql數(shù)量總是有限的4.合理的使用共享池能夠降低重分析的次數(shù)如何減少重分析的次數(shù)1.配置足夠大的共享池2.采取統(tǒng)一的語句書寫規(guī)則3.使用綁定變量4.在不能使用綁定變量的基礎上使語句實現(xiàn)代碼共享sql優(yōu)化不使用綁定的影響使用更多的共享池來緩存sql和執(zhí)行計劃使用更多的cpu資源將產(chǎn)生更多的數(shù)據(jù)庫內(nèi)部鎖導致共享池管理代價的增加sql優(yōu)化數(shù)據(jù)庫對綁定的補救措施數(shù)據(jù)庫對綁定的補救措施cursor_sharing = force/exact/

36、similar我們?yōu)槭裁催€要強調在程序中綁定 cursor_sharing 存在bug cursor_sharing 影響sql執(zhí)行計劃 cursor_sharing 消耗額外內(nèi)存和cpu 在應用中綁定的代價低，維護的代價很高 由數(shù)據(jù)庫設置cursor_sharing 風險很高sql優(yōu)化表之間三種連接方式表之間三種連接方式sql優(yōu)化rbo下下sql語句的優(yōu)化規(guī)則語句的優(yōu)化規(guī)則在rbo模式下，sql語句的執(zhí)行遵循著規(guī)則級別的優(yōu)先級： single row by rowid single row by cluster join single row by hash cluster key with

37、 unique key single row by unique index cluster join hash cluster key indexed cluster key composite key single-column non-unique index bounded range search on indexed columns unbounded range search on indexed columns sort-merge join max or min of indexed column order by on indexed columns full table-

38、scan寫sql語句的一些提示從i/o的觀點來看，使用索引沒有意義時建議使用全表掃描如果查詢中包含了子查詢，那么注意首先優(yōu)化子查詢注意關聯(lián)子查詢，盡量減少關聯(lián)子查詢的使用，因為它的代價很高，并且非常消耗cpu在sql語句中使用not exists 代替 not in用表連接替換exists 使用帶有前導字段的like來替換substr函數(shù)考慮使用union all代替多個or連接操作如果經(jīng)常執(zhí)行主細表的聯(lián)合查詢，建立外鍵索引考慮使用非唯一索引支持唯一性約束條件主動的確定使用循環(huán)嵌套、合并連接、散列連接，盡可能測試使用一種代價較小的連接方式。如果需要在pl/sql 程序中使用動態(tài)sql，建議使用

39、execute immediate對于非常大的表，考慮使用表和索引的分區(qū)如果需要在創(chuàng)建索引的時候減少所需時間，可以在會話集設置比較大的sort_area_size考慮更多的使用decode函數(shù)，而不是在pl/sql中作判斷一定要周期性的收集信息，及時發(fā)現(xiàn)系統(tǒng)中的潛在問題選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效) 兩個表聯(lián)合查詢，數(shù)據(jù)量小的表放最后；三個以上表聯(lián)合查詢，交叉表放最后。where子句中的連接條件書寫順序 那些可以過濾掉最大數(shù)量記錄的條件必須寫在where子句的末尾 使用truncate替代delete 盡量多使用commit （保證數(shù)據(jù)完整性的基礎上）減少對表的訪問次數(shù)

40、（減少邏輯讀）避免索引列的類型 隱式轉換造成的索引無效oracle hintshints主要用來改變主要用來改變sql執(zhí)行計劃執(zhí)行計劃:1.指定優(yōu)化模式指定優(yōu)化模式 2.指定表數(shù)據(jù)的獲取方式指定表數(shù)據(jù)的獲取方式 3.指定表連接的順序指定表連接的順序 4.指定表連接的方式指定表連接的方式 語法: /*+ hint hint . */ (在plsql中， +與hint的第一個字符之間的空格是很重要的 /*+ all_rows */ 是正確的，但/*+all_rows */ 是錯誤的) 建議少用，使用不好會影響效率。如：并行太多會影響整個數(shù)據(jù)庫的性能。1、與、與optimizer mode優(yōu)化模式相

41、關優(yōu)化模式相關: first_rows, all_rows ： force cbo first rows or all rows rule ： force rule if possible ordered ： access tables in the order of the from clause 2 讀取方式讀取方式: full(tab) ： use fts on tab cache(tab) ： if table within treat as if it had the cache option set. only applies if fts used. nocache(tab)：

42、do not cache table even if it has cache option set. only relevant for fts rowid(tab) ： access tab by rowid directly select /*+ rowid( table ) */ . from tab where rowid between &1 and &2; hash(tab) ： use hash scan to access tab index( tab index ) ： use index to access tab index_asc( tab index ) ： use

43、 index to access tab for range scan. index_desc( tab index ) ： use descending index range scan index_ffs( tab index) ： index fast full scan - rather than fts. 3 連接連接: use_nl(tab) ：use table tab as the driving table in a nested loops join. if the driving row source is a combination of tables name one

44、 of the tables in the inner join and the nl should drive off the entire row-source. does not work unless accompanied by an ordered hint. use_merge(tab.) ：use tab as the driving table in a sort-merge join. does not work unless accompanied by an ordered hint. use_hash(tab1 tab2) ：join each specified t

45、able with another row source with a hash join. tab1 is joined to previous row source using a hash join. (=7.3) 4 并行查詢選項并行查詢選項: parallel ( table, , ) ： use parallel degree / instances as specified parallel_index(table, index, degree ,instances ) ：parallel range scan for partitioned index 5 其他其他:appen

46、d ： only valid for insert . select. allows insert to work like direct load or to perform parallel insert. noappend： do not use insert append functionality rewrite(v1,v2) ： 8.1+ with a view list use eligible materialized view without view list use any eligible mv norewrite ： 8.1+ do not rewrite the q

47、uery 附錄：在sql優(yōu)化過程中常見oracle hint的用法在sql語句優(yōu)化過程中，我們經(jīng)常會用到hint,現(xiàn)總結一下1. /*+all_rows*/表明對語句塊選擇基于開銷的優(yōu)化方法,并獲得最佳吞吐量,使資源消耗最小化.例如:select /*+all+_rows*/ emp_no,emp_nam,dat_in from bsempms where emp_no=scott; 2. /*+first_rows*/表明對語句塊選擇基于開銷的優(yōu)化方法,并獲得最佳響應時間,使資源消耗最小化.例如:select /*+first_rows*/ emp_no,emp_nam,dat_in fro

48、m bsempms where emp_no=scott;3. /*+choose*/表明如果數(shù)據(jù)字典中有訪問表的統(tǒng)計信息,將基于開銷的優(yōu)化方法,并獲得最佳的吞吐量;表明如果數(shù)據(jù)字典中沒有訪問表的統(tǒng)計信息,將基于規(guī)則開銷的優(yōu)化方法;例如:select /*+choose*/ emp_no,emp_nam,dat_in from bsempms where emp_no=scott;4. /*+rule*/表明對語句塊選擇基于規(guī)則的優(yōu)化方法.例如:select /*+ rule */ emp_no,emp_nam,dat_in from bsempms where emp_no=scott; 5

49、. /*+full(table)*/表明對表選擇全局掃描的方法.例如:select /*+full(a)*/ emp_no,emp_nam from bsempms a where emp_no=scott;6. /*+rowid(table)*/提示明確表明對指定表根據(jù)rowid進行訪問.例如:select /*+rowid(bsempms)*/ * from bsempms where rowid=aaaaaaaaaaaaaaand emp_no=scott;7. /*+cluster(table)*/ 提示明確表明對指定表選擇簇掃描的訪問方法,它只對簇對象有效.例如:select /*+

50、cluster */ bsempms.emp_no,dpt_no from bsempms,bsdptmswhere dpt_no=tec304 and bsempms.dpt_no=bsdptms.dpt_no;8. /*+index(table index_name)*/表明對表選擇索引的掃描方法.例如:select /*+index(bsempms sex_index) use sex_index because there are fewmale bsempms */ from bsempms where sex=m;9. /*+index_asc(table index_name)*

51、/表明對表選擇索引升序的掃描方法.例如:select /*+index_asc(bsempms pk_bsempms) */ from bsempms where dpt_no=scott;10. /*+index_combine*/為指定表選擇位圖訪問路經(jīng),如果index_combine中沒有提供作為參數(shù)的索引,將選擇出位圖索引的布爾組合方式.例如:select /*+index_combine(bsempms sal_bmi hiredate_bmi)*/ * from bsempmswhere sal5000000 and hiredatesysdate;11. /*+index_joi

52、n(table index_name)*/提示明確命令優(yōu)化器使用索引作為訪問路徑.例如:select /*+index_join(bsempms sal_hmi hiredate_bmi)*/ sal,hiredatefrom bsempms where salv.avg_sal;20. /*+no_merge(table)*/對于有可合并的視圖不再合并.例如:select /*+no_merge(v) */ a.emp_no,a.emp_nam,b.dpt_no from bsempms a (select dpt_no,avg(sal) as avg_sal from bsempms b

53、group by dpt_no) v where a.dpt_no=v.dpt_no and a.salv.avg_sal;21. /*+ordered*/根據(jù)表出現(xiàn)在from中的順序,ordered使oracle依此順序對其連接.例如:select /*+ordered*/ a.col1,b.col2,c.col3 from table1 a,table2 b,table3 c where a.col1=b.col1 and b.col1=c.col1;22. /*+use_nl(table)*/將指定表與嵌套的連接的行源進行連接,并把指定表作為內(nèi)部表.例如:select /*+ordere

54、d use_nl(bsempms)*/ bsdptms.dpt_no,bsempms.emp_no,bsempms.emp_nam from bsempms,bsdptms where bsempms.dpt_no=bsdptms.dpt_no;23. /*+use_merge(table)*/將指定的表與其他行源通過合并排序連接方式連接起來.例如:select /*+use_merge(bsempms,bsdptms)*/ * from bsempms,bsdptms where bsempms.dpt_no=bsdptms.dpt_no;24. /*+use_hash(table)*/將指

55、定的表與其他行源通過哈希連接方式連接起來.例如:select /*+use_hash(bsempms,bsdptms)*/ * from bsempms,bsdptms where bsempms.dpt_no=bsdptms.dpt_no;25. /*+driving_site(table)*/強制與oracle所選擇的位置不同的表進行查詢執(zhí)行.例如:select /*+driving_site(dept)*/ * from bsempms,deptbsdptms where bsempms.dpt_no=dept.dpt_no;26. /*+leading(table)*/將指定的表作為連

56、接次序中的首表.27. /*+cache(table)*/當進行全表掃描時,cache提示能夠將表的檢索塊放置在緩沖區(qū)緩存中最近最少列表lru的最近使用端例如:select /*+full(bsempms) cahe(bsempms) */ emp_nam from bsempms;28. /*+nocache(table)*/當進行全表掃描時,cache提示能夠將表的檢索塊放置在緩沖區(qū)緩存中最近最少列表lru的最近使用端例如:select /*+full(bsempms) nocahe(bsempms) */ emp_nam from bsempms;29. /*+append*/直接插入到

57、表的最后,可以提高速度.insert /*+append*/ into test1 select * from test4 ;30. /*+noappend*/通過在插入語句生存期內(nèi)停止并行模式來啟動常規(guī)插入.insert /*+noappend*/ into test1 select * from test4 ; 附錄 監(jiān)控數(shù)據(jù)庫性能的sql監(jiān)控數(shù)據(jù)庫性能的sql1. 監(jiān)控事例的等待select event,sum(decode(wait_time,0,0,1) prev, sum(decode(wait_time,0,1,0) curr,count(*) tot from v$sessio

58、n_wait group by event order by 4;2. 回滾段的爭用情況select name, waits, gets, waits/gets ratio from v$rollstat a, v$rollname b where a.usn = b.usn; 3. 監(jiān)控表空間的 i/o 比例select df.tablespace_name name,df.file_name file,f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbwfrom v$filestat f, dba_data_files dfw

59、here f.file# = df.file_idorder by df.tablespace_name;4. 監(jiān)控文件系統(tǒng)的 i/o 比例select substr(a.file#,1,2) #, substr(,1,30) name, a.status, a.bytes, b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file# = b.file#; 5.在某個用戶下找所有的索引select user_indexes.table_name, user_indexes.index_name,uniquene

60、ss, column_namefrom user_ind_columns, user_indexeswhere user_ind_columns.index_name = user_indexes.index_nameand user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name,user_indexes.index_name, column_position;6. 監(jiān)控 sga 的命中率select a.value + b.v