Oracle面試題之SQL tunting

1:列舉幾種表連接方式

答：一共有三種連接方式（SQL優(yōu)化）捧挺，嵌套循環(huán)（Nested Loops（NL））萝挤，哈希連接喳瓣，也叫散列連接（Hash Join（HJ））欣福，排序合并連接，也叫歸并連接（Sort Merge Join（SMJ））语泽。

注：

Oracle一次只能連接兩個(gè)表贸典。不管查詢(xún)中有多少個(gè)表，Oracle在連接中一次僅能操作兩張表湿弦。當(dāng)執(zhí)行多個(gè)表的連接時(shí)瓤漏，優(yōu)化器從一個(gè)表開(kāi)始腾夯，將它與另一個(gè)表連接颊埃；然后將中間結(jié)果與下一個(gè)表連接，以此類(lèi)推蝶俱，直到處理完所有表為止班利。

2:不借助第三方工具，怎樣查看sql的執(zhí)行計(jì)劃

答：設(shè)置autotrace榨呆÷薇辏或者使用SQL語(yǔ)句。（答題時(shí)請(qǐng)回答詳細(xì)，加上解析）

解析：

如果要分析某條SQL的性能問(wèn)題闯割，通常我們要先看SQL的執(zhí)行計(jì)劃彻消，看看SQL的每一步執(zhí)行是否存在問(wèn)題。如果一條SQL平時(shí)執(zhí)行的好好的宙拉，卻有一天突然性能很差宾尚，如果排除了系統(tǒng)資源和阻塞的原因，那么基本可以斷定是執(zhí)行計(jì)劃出了問(wèn)題谢澈。

一.查看執(zhí)行計(jì)劃的三種方法

1.1設(shè)置autotrace

1

SET?AUTOTRACE?OFF

此為默認(rèn)值煌贴，即關(guān)閉Autotrace

2

SET?AUTOTRACE?ON?EXPLAIN

只顯示執(zhí)行計(jì)劃

3

SET?AUTOTRACE?ON?STATISTICS

只顯示執(zhí)行的統(tǒng)計(jì)信息

4

SET?AUTOTRACE?ON

包含2,3兩項(xiàng)內(nèi)容

5

SET?AUTOTRACE?TRACEONLY

與ON相似，但不顯示語(yǔ)句的執(zhí)行結(jié)果

1.2使用SQL

SQL>EXPLAIN PLAN FOR sql語(yǔ)句;

SQL>SELECT plan_table_output FROMTABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

例如：

SQL> EXPLAIN PLAN FOR SELECT * FROM dba_users;

已解釋锥忿。

SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

1.3使用Toad,PL/SQL

Developer工具

3:如何使用CBO,CBO與RULE的區(qū)別

答：

在optimizer_mode=choose時(shí),如果表有統(tǒng)計(jì)信息(分區(qū)表外),優(yōu)化器將選擇CBO,否則選RBO牛郑。RBO遵循簡(jiǎn)單的分級(jí)方法學(xué),使用15種級(jí)別要點(diǎn)，當(dāng)接收到查詢(xún)敬鬓，優(yōu)化器將評(píng)估使用到的要點(diǎn)數(shù)目,然后選擇最佳級(jí)別(最少的數(shù)量)的執(zhí)行路徑來(lái)運(yùn)行查詢(xún)淹朋。

CBO嘗試找到最低成本的訪(fǎng)問(wèn)數(shù)據(jù)的方法,為了最大的吞吐量或最快的初始響應(yīng)時(shí)間,計(jì)算使用不同的執(zhí)行計(jì)劃的成本，并選擇成本最低的一個(gè),關(guān)于表的數(shù)據(jù)內(nèi)容的統(tǒng)計(jì)被用于確定執(zhí)行計(jì)劃钉答。

解析：

Oracle的優(yōu)化器有兩種優(yōu)化方式,即基于規(guī)則的優(yōu)化方式(Rule-Based

Optimization瑞你，簡(jiǎn)稱(chēng)為RBO)和基于代價(jià)的優(yōu)化方式(Cost-Based Optimization，簡(jiǎn)稱(chēng)為CBO)希痴，在Oracle8及以后的版本,Oracle強(qiáng)列推薦用CBO的方式者甲。

RBO方式：優(yōu)化器在分析SQL語(yǔ)句時(shí),所遵循的是Oracle內(nèi)部預(yù)定的一些規(guī)則。比如我們常見(jiàn)的砌创，當(dāng)一個(gè)where子句中的一列有索引時(shí)去走索引虏缸。

CBO方式：它是看語(yǔ)句的代價(jià)(Cost),這里的代價(jià)主要指Cpu和內(nèi)存。優(yōu)化器在判斷是否用這種方式時(shí),主要參照的是表及索引的統(tǒng)計(jì)信息嫩实。統(tǒng)計(jì)信息給出表的大小刽辙、有多少行、每行的長(zhǎng)度等信息甲献。這些統(tǒng)計(jì)信息起初在庫(kù)內(nèi)是沒(méi)有的宰缤，是做analyze后才出現(xiàn)的，很多的時(shí)侯過(guò)期統(tǒng)計(jì)信息會(huì)令優(yōu)化器做出一個(gè)錯(cuò)誤的執(zhí)行計(jì)劃,因此應(yīng)及時(shí)更新這些信息晃洒。

注：

走索引不一定就是優(yōu)的慨灭，比如一個(gè)表只有兩行數(shù)據(jù)，一次IO就可以完成全表的檢索,而此時(shí)走索引時(shí)則需要兩次IO,這時(shí)全表掃描(full table scan)是最好球及。

優(yōu)化模式包括Rule氧骤、Choose、First rows吃引、All rows四種方式：

Rule：基于規(guī)則的方式筹陵。

Choolse：默認(rèn)的情況下Oracle用的便是這種方式刽锤。指的是當(dāng)一個(gè)表或或索引有統(tǒng)計(jì)信息，則走CBO的方式朦佩，如果表或索引沒(méi)統(tǒng)計(jì)信息并思，表又不是特別的小，而且相應(yīng)的列有索引時(shí)语稠，那么就走索引纺荧，走RBO的方式。

First Rows：它與Choose方式是類(lèi)似的颅筋，所不同的是當(dāng)一個(gè)表有統(tǒng)計(jì)信息時(shí)宙暇，它將是以最快的方式返回查詢(xún)的最先的幾行，從總體上減少了響應(yīng)時(shí)間议泵。

All Rows:也就是我們所說(shuō)的Cost的方式占贫，當(dāng)一個(gè)表有統(tǒng)計(jì)信息時(shí)，它將以最快的方式返回表的所有的行先口，從總體上提高查詢(xún)的吞吐量型奥。沒(méi)有統(tǒng)計(jì)信息則走RBO的方式。

關(guān)于如何設(shè)定選擇哪種優(yōu)化器模式：

A碉京、instant級(jí)別

修改初始化參數(shù),我們可以通過(guò)在init.ora文件中設(shè)定OPTIMIZER_MODE=RULE厢汹、OPTIMIZER_MODE=CHOOSE、OPTIMIZER_MODE=FIRST_ROWS谐宙、OPTIMIZER_MODE=ALL_ROWS去選用3所提的四種方式,如果你沒(méi)設(shè)定OPTIMIZER_MODE參數(shù)則默認(rèn)用的是Choose這種方式烫葬。

B、session級(jí)別

當(dāng)前會(huì)話(huà)輸入altersession set optimizer_mode = xxx scope = both;

C凡蜻、語(yǔ)句級(jí)別

通過(guò)HINT提示來(lái)選擇

select /*+ rule*/e.ename,d.deptno from emp e ,dept d

where e.deptno = d.deptno

強(qiáng)調(diào)一點(diǎn)：你設(shè)置的hint所包含的執(zhí)行計(jì)劃一定要有否則這個(gè)hint就相當(dāng)于comment

4：如何定位重要(消耗資源多)的SQL

答：

A搭综、查看值得懷疑的SQL

select? substr(to_char(s.pct,'99.00'),2)||'%'load,?s.executions executes,

p.sql_text? from(select address,disk_reads,executions,pct,

rank()over(order? by disk_reads desc) ranking

from(select? address,

disk_reads,

executions,

100*ratio_to_report(disk_reads)over()? pct

from? sys.v_$sql

where? command_type!=47)

where? disk_reads>50*executions) s,

sys.v_$sqltext? p

where? s.ranking<=5

and? p.address=s.address

order by 1,? s.address, p.piece;

B、查看消耗內(nèi)存多的sql

select? b.username,

a.? buffer_gets,

a.executions,

a.disk_reads? / decode(a.executions, 0, 1, a.executions),

a.sql_text? SQL

from? v$sqlarea a, dba_users b

where? a.parsing_user_id = b.user_id

and? a.disk_reads > 10000

order by? disk_reads desc;

C划栓、查看邏輯讀多的SQL

select*

from(select? buffer_gets, sql_text

from? v$sqlarea

where? buffer_gets>500000

order? by buffer_gets desc)

where? rownum<=30;

D兑巾、查看執(zhí)行次數(shù)多的SQL

select? sql_text, executions

from? (select sql_text, executions from v$sqlarea order by executions desc)

where? rownum < 81;

E、查看讀硬盤(pán)多的SQL

select? sql_text, disk_reads

from(select? sql_text, disk_reads from v$sqlarea order by disk_reads desc)

where? rownum<21;

F忠荞、查看排序多的SQL

select? sql_text, sorts

from(select? sql_text, sorts from v$sqlarea order by sorts desc)

where? rownum<21;

G蒋歌、分析的次數(shù)太多，執(zhí)行的次數(shù)太少委煤，要用綁變量的方法來(lái)寫(xiě)sql

select? substr(sql_text, 1, 80) "sql", count(*), sum(executions)? "totexecs"

from? v$sqlarea

where? executions < 5

group by? substr(sql_text, 1, 80)

having? count(*) > 30

order by? 2;

5:如何跟蹤某個(gè)session的SQL

答：

Oracle自帶的sql trace程序可以跟蹤本地session

sys: alter

system set sql_trace = true;對(duì)所有會(huì)話(huà)跟蹤

schema:

alter session set sql_trace = true堂油；對(duì)某個(gè)session會(huì)話(huà)跟蹤sql語(yǔ)句

用tkprof來(lái)格式化跟蹤文件輸出

tkprof輸出內(nèi)容包括1 sql語(yǔ)句2統(tǒng)計(jì)信息3explain

table執(zhí)行計(jì)劃

6:SQL調(diào)整最關(guān)注的是什么

答：

調(diào)整的目的就是為了消耗最小的資源來(lái)完成功能咆霜，通過(guò)查看執(zhí)行計(jì)劃和各種統(tǒng)計(jì)信息來(lái)分辨調(diào)整后的sql對(duì)資源的耗費(fèi)情況，來(lái)找出一個(gè)成本最小的sql語(yǔ)句

檢查系統(tǒng)的I/O問(wèn)題

vmstate能檢查整個(gè)系統(tǒng)的iostat（IOstatistics

查看該SQL的responsetime(db block gets/consistent gets/physical reads/sorts (disk))

7:說(shuō)說(shuō)你對(duì)索引的認(rèn)識(shí)(索引的結(jié)構(gòu)伟端、對(duì)dml影響夸政、為什么提高查詢(xún)性能)

答：

第一講、索引并非總是最佳選擇

1.表未做statistics,或者statistics陳舊蛔糯，導(dǎo)致Oracle判斷失誤叠荠。

2.根據(jù)該表?yè)碛械挠涗洈?shù)和數(shù)據(jù)塊數(shù)盹沈，實(shí)際上全表掃描要比索引掃描更快计维。

第二講袜香、索引也有好壞

1.索引不是越多越好。特別是大量從來(lái)或者幾乎不用的索引鲫惶，對(duì)系統(tǒng)只有損害蜈首。OLTP系統(tǒng)每表超過(guò)5個(gè)索引即會(huì)降低性能，而且在一個(gè)sql中欠母，Oracle從不能使用超過(guò)5個(gè)索引欢策。

2.很多時(shí)候，單列索引不如復(fù)合索引有效率赏淌。

3.用于多表連結(jié)的字段踩寇，加上索引會(huì)很有作用。

第三講六水、索引再好俺孙，不用也是白搭

1. where子句中的這個(gè)字段，必須是復(fù)合索引的第一個(gè)字段掷贾。

2. where子句中的這個(gè)字段睛榄，不應(yīng)該參與任何形式的計(jì)算。

具體來(lái)講想帅，假設(shè)一個(gè)索引是按f1, f2,

f3的次序建立的场靴，現(xiàn)在有一個(gè)sql語(yǔ)句, where子句是f2 = : var2,則因?yàn)閒2不是索引的第1個(gè)字段，無(wú)法使用該索引港准。

索引通常能提高select/update/delete的性能,會(huì)降低insert的速度憎乙。

8:使用索引查詢(xún)一定能提高查詢(xún)的性能嗎?為什么?

答：

索引就是為了提高查詢(xún)性能而存在的,如果在查詢(xún)中索引沒(méi)有提高性能,只能說(shuō)是用錯(cuò)了索引,或者講是場(chǎng)合不同。

9:綁定變量是什么?綁定變量有什么優(yōu)缺點(diǎn)?

答：

綁定變量是相對(duì)文本變量來(lái)講的,所謂文本變量是指在SQL直接書(shū)寫(xiě)查詢(xún)條件叉趣，這樣的SQL在不同條件下需要反復(fù)解析,綁定變量是指使用變量來(lái)代替直接書(shū)寫(xiě)條件泞边，查詢(xún)bind value在運(yùn)行時(shí)傳遞，然后綁定執(zhí)行疗杉。優(yōu)點(diǎn)是減少硬解析,降低CPU的爭(zhēng)用,節(jié)省shared_pool ;缺點(diǎn)是不能使用histogram,sql優(yōu)化比較困難阵谚。

10:如何穩(wěn)定(固定)執(zhí)行計(jì)劃

答：

程序代碼query_rewrite_enabled= true

star_transformation_enabled= true

optimizer_features_enable= 9.2.0

創(chuàng)建并使用stored outline

oracle canautomatically create outlines for all SQL statements, or you can create themfor specific SQL statements. In either case, the outlines derive their inputfrom the optimizer.

oracle creates stored outlinesautomatically when you set the initialization parameter Create_STORED_OUTLINESto true. When activated, oracle creates outlines for all compiled SQLstatements. You can create stored outlines for specific statements using theCreate OUTLINE statement.

Creating

Outlines：

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/outlines.htm

11:和排序相關(guān)的內(nèi)存在8i和9i分別怎樣調(diào)整，臨時(shí)表空間的作用是什么

答：

Oracle 8i中sort_area_size/sort_area_retained_size決定了排序所需要的內(nèi)存烟具，如果排序操作不能在sort_area_size中完成,就會(huì)用到temp表空間

Oracle 9i中如果workarea_size_policy=auto時(shí),排序在pga內(nèi)進(jìn)行,通常pga_aggregate_target的1/20可以用來(lái)進(jìn)行disk

sort;如果workarea_size_policy=manual時(shí),排序需要的內(nèi)存由sort_area_size決定

在執(zhí)行order by/group

by/distinct/union/create index/index rebuild/minus等操作時(shí),如果在pga或sort_area_size中不能完成,排序?qū)⒃谂R時(shí)表空間進(jìn)行(disk

sort),臨時(shí)表空間主要作用就是完成系統(tǒng)中的disk sort.

12:存在表T(a,b,c,d),要根據(jù)字段c排序后取第21—30條記錄顯示梢什，請(qǐng)給出sql

答：

程序代碼

create table t(a number(,b number(,cnumber(,d number();

/

Begin

for i in 1 .. 300 loop

insert into t values(mod(i,2),i/2,dbms_random.value(1,300),i/4);

end loop;

end;

/

select * from (select c.*,rownum as rn from(select * from t order by c desc) c) where rn between 21 and 30;

/

select * from (select * from test order byc desc) x where rownum < 30

minus

select * from (select * from test order byc desc) y where rownum < 20 order by 3 desc

相比之minus性能較差