㈠ 如何查找Oracle中存在全表掃描的sql語句
1． 對返回的行無任何限定條件，即沒有where 子句
2． 未對數據表與任何索引主列相對應的行限定條件
例如：在City-State-Zip列創建了三列復合索引，那麼僅對State列限定條件不能使用這個索引，因為State不是索引的主列。
3． 對索引的主列有限定條件，但是在條件表達式里使用以下表達式則會使索引失效，造成全表掃描：
（1）where子句中對欄位進行函數、表達式操作，這將導致引擎放棄使用索引而進行全表掃描,
Demo:
where upper(city)='TokYo' 或 City || 'X' like 'TOKYO%',
select id from t where num/2=100 應改為: select id from t where num=100*2
select * from emp where to_char(hire_date,'yyyymmdd')='20080411' (不使用)
select * from emp where hire_date = to_char('20080411','yyyymmdd') (使用)
（2）查詢欄位is null時索引失效，引起全表掃描。
where City is null 或 ,where City is not null,
解決方法：SQL語法中使用NULL會有很多麻煩，最好索引列都是NOT NULL的；對於is null，可以建立組合索引，nvl(欄位,0),對表和索引analyse後，is null查詢時可以重新啟用索引查找,但是效率還不是值得肯定；is not null 時永遠不會使用索引。一般數據量大的表不要用is null查詢。
select id from t where num is null
可以在num上設置默認值0，確保表中num列沒有null值，然後這樣查詢：
select id from t where num=0
(3)查詢條件中使用了不等於操作符（<>、!=）會限制索引、引起全表掃描
Where city!='TOKYO'.
解決方法：通過把不等於操作符改成or，可以使用索引，避免全表掃描。例如，把column<>』aaa』，改成column<』aaa』 or column>』aaa』，就可以使用索引了。
(4)對索引的主列有限定條件，但是條件使用like操作以及值以『%』開始或者值是一個賦值變數。例如：
where City like '%YOK%'
where City like: City_bind_Variable xl_rao
select * from emp where name like '%A' (不使用索引)
select * from emp where name like 'A%' (使用索引)
解決辦法：首先盡量避免模糊查詢，如果因為業務需要一定要使用模糊查詢，則至少保證不要使用全模糊查詢，對於右模糊查詢，即like 『…%』，是會使用索引的；左模糊like 『%...』無法直接使用索引，但可以利用reverse + function index 的形式，變化成 like 『…%』；全模糊是無法優化的，一定要的話考慮用搜索引擎。出於降低資料庫伺服器的負載考慮，盡可能地減少資料庫模糊查詢。
4． or語句使用不當會引起全表掃描
原因：where子句中比較的兩個條件，一個有索引，一個沒索引，使用or則會引起全表掃描。例如：where A=：1 or B=：2，A上有索引，B上沒索引，則比較B=：2時會重新開始全表掃描
5．模糊查詢效率很低：
原因：like本身效率就比較低，應該盡量避免查詢條件使用like；對於like『%...%』（全模糊）這樣的條件，是無法使用索引的，全表掃描自然效率很低；另外，由於匹配演算法的關系，模糊查詢的欄位長度越大，模糊查詢效率越低。
解決辦法：首先盡量避免模糊查詢，如果因為業務需要一定要使用模糊查詢，則至少保證不要使用全模糊查詢，對於右模糊查詢，即like『…%』，是會使用索引的；左模糊like
『%...』無法直接使用索引，但可以利用reverse + function index的形式，變化成like『…%』；全模糊是無法優化的，一定要的話考慮用搜索引擎。出於降低資料庫伺服器的負載考慮，盡可能地減少資料庫模糊查詢。
6．查詢條件中含有is null的select語句執行慢
原因：Oracle 中，查詢欄位is null時單索引失效，引起全表掃描。
解決方法：SQL語法中使用NULL會有很多麻煩，最好索引列都是NOT NULL的；對於is null，可以建立組合索引，nvl(欄位,0),對表和索引analyse後，is null查詢時可以重新啟用索引查找,但是效率還不是值得肯定；is not null時永遠不會使用索引。一般數據量大的表不要用is null查詢。
7.查詢條件中使用了不等於操作符（<>、!=）的select語句執行慢
原因：SQL中，不等於操作符會限制索引，引起全表掃描，即使比較的欄位上有索引
解決方法：通過把不等於操作符改成or，可以使用索引，避免全表掃描。例如，把column<>』aaa』，改成column<』aaa』or column>』aaa』，就可以使用索引了。
8.使用組合索引，如果查詢條件中沒有前導列，那麼索引不起作用，會引起全表掃描；但是從Oracle9i開始，引入了索引跳躍式掃描的特性，可以允許優化器使用組合索引，即便索引的前導列沒有出現在WHERE子句中。例如：create index skip1 on emp5(job,empno); 全索引掃描select count(*) from emp5 where empno=7900; 索引跳躍式掃描select /*+ index(emp5 skip1)*/ count(*) from emp5 where empno=7900;前一種是全表掃描，後一種則會使用組合索引。
9. or語句使用不當會引起全表掃描
原因：where子句中比較的兩個條件，一個有索引，一個沒索引，使用or則會引起全表掃描。例如：where A=：1 or B=：2，A上有索引，B上沒索引，則比較B=：2時會重新開始全表掃描。
10.組合索引，排序時應按照組合索引中各列的順序進行排序，即使索引中只有一個列是要排序的，否則排序性能會比較差。例如：create index skip1 on emp5(job,empno，date); select job，empno from emp5 where job=』manager』and empno=』10』order by job,empno,date desc;實際上只是查詢出符合job=』manager』and empno=』10』條件的記錄並按date降序排列，但是寫成order by date desc性能較差。
11.Update語句，如果只更改1、2個欄位，不要Update全部欄位，否則頻繁調用會引起明顯的性能消耗，同時帶來大量日誌。
12.對於多張大數據量的表JOIN，要先分頁再JOIN，否則邏輯讀會很高，性能很差。
13.select count(*) from table；這樣不帶任何條件的count會引起全表掃描，並且沒有任何業務意義，是一定要杜絕的。
14.sql的where條件要綁定變數，比如where column=：1，不要寫成where column=『aaa』，這樣會導致每次執行時都會重新分析，浪費CPU和內存資源。
15.不要使用in操作符，這樣資料庫會進行全表掃描，
推薦方案：在業務密集的SQL當中盡量不採用IN操作符
16.not in 使用not in也不會走索引
推薦方案：用not exists或者（外聯結+判斷為空）來代替
17.> 及 < 操作符（大於或小於操作符）
大於或小於操作符一般情況下是不用調整的，因為它有索引就會採用索引查找，但有的情況下可以對它進行優化，如一個表有100萬記錄，一個數值型欄位 A，30萬記錄的A=0，30萬記錄的A=1，39萬記錄的A=2，1萬記錄的A=3。那麼執行A>2與A>=3的效果就有很大的區別了，因為A>2時ORACLE會先找出為2的記錄索引再進行比較，而A>=3時ORACLE則直接找到=3的記錄索引。
18.UNION操作符
UNION在進行表鏈接後會篩選掉重復的記錄，所以在表鏈接後會對所產生的結果集進行排序運算，刪除重復的記錄再返回結果。實際大部分應用中是不會產生重復的記錄，最常見的是過程表與歷史表UNION。如：
select * from gc_dfys
union
select * from ls_jg_dfys
這個SQL在運行時先取出兩個表的結果，再用排序空間進行排序刪除重復的記錄，最後返回結果集，如果表數據量大的話可能會導致用磁碟進行排序。
推薦方案：採用UNION ALL操作符替代UNION，因為UNION ALL操作只是簡單的將兩個結果合並後就返回。
19.WHERE後面的條件順序影響
WHERE子句後面的條件順序對大數據量表的查詢會產生直接的影響，如
Select * from zl_yhjbqk where dy_dj = '1K以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1K以下'
以上兩個SQL中dy_dj及xh_bz兩個欄位都沒進行索引，所以執行的時候都是全表掃描，第一條SQL的dy_dj = '1KV以下'條件在記錄集內比率為99%，而xh_bz=1的比率只為0.5%，在進行第一條SQL的時候99%條記錄都進行dy_dj及xh_bz的比較，而在進行第二條SQL的時候0.5%條記錄都進行dy_dj及xh_bz的比較，以此可以得出第二條SQL的CPU佔用率明顯比第一條低。
20.查詢表順序的影響
在FROM後面的表中的列表順序會對SQL執行性能影響，在沒有索引及ORACLE沒有對表進行統計分析的情況下ORACLE會按表出現的順序進行鏈接，由此因為表的順序不對會產生十分耗伺服器資源的數據交叉。（註：如果對表進行了統計分析，ORACLE會自動先進小表的鏈接，再進行大表的鏈接）
㈡ 不藉助第三方工具 怎樣查看sql的執行計劃
I) 使用Explain Plan,查詢PLAN_TABLE;
EXPLAIN PLAN
SET STATEMENT_ID='QUERY1'
FOR
SELECT *
FROM a
WHERE aa=1;
SELECT operation, options, object_name, object_type, ID, parent_id
FROM plan_table
WHERE STATEMENT_ID = 'QUERY1'
ORDER BY ID;
II)SQLPLUS中的SET TRACE 即可看到Execution Plan Statistics
SET AUTOTRACE ON;
㈢ Oracle有沒有查詢計劃緩存
在PL/SQL中把相應SQL語句粘貼到SQL窗口，按F5就能查看相應執行計劃。
這個是查詢緩存中的執行計劃。
select * from v$sql_plan
㈣ 失敗原因:執行SQL失敗,失敗原因:批處理中出現錯誤: ORA-00911: invalid character
應該是錯誤輸入了全形字元，輸入半形字元就行了。
錯誤信息ORA-00911: invalid character說明了在執行的SQL語句中出現了無效字元，所在SQL語句無法通過語法分析過程導致了錯誤結果。
可能出現的原因有：錯誤地輸入了全形字元，比如輸入了全形逗號。
如果使用C++、Java、C#等編程時，總會習慣在語句最後加分號，而這個符號在SQL中是無效字元。
實際語句和列類型不匹配，比如將數值列賦值為字元串。
與display函數不同，display_cursor顯示的為真實的執行計劃。
對於format參數，使用與display函數的各個值，同樣適用於display_cursor函數。
當statistics_level為all或使用gather_plan_statistics提示可以獲得執行時的統計信息。
根據真實與預估的統計信息可以初步判斷SQL效率低下的原因，如統計信息的准確性、主要的開銷位於那些步驟等。
看你的sql語句最後是否有分號，有的話去掉就行 看你Oracle伺服器端字元集是否和Oracle客戶端字元集匹配。
查詢oracle server端的字元集：SQL>select userenv(『language』) from al。
查詢oracle client端的字元集。在windows平台下，就是注冊表裡面相應OracleHome的NLS_LANG。
還可以在dos窗口裡面自己設置，比如: set nls_lang=AMERICAN_AMERICA.ZHS16GBK。
㈤ 怎麼使用plsql查看執行計劃
一.在線查看執行計劃表
如果PLAN_TABLE表不存在，執行$ORACLE_HOME/rdbms/admin/utlxplan.sql創建plan_table表。
1.explain plan
for
select * from ......
2.select * from table(DBMS_XPLAN.Display);
二.使用oracle第三方工具：
plsql developer(F5)
Toad (Ctrl+E)
三.使用SQL*PLUS：
如果PLAN_TABLE表不存在，執行$ORACLE_HOME/rdbms/admin/utlxplan.sql創建plan_table表。
如果PLUSTRACE角色不存在，執行
$ORACLE_HOME/sqlplus/admin/plustrce.sql
1.sqlplus / as sysdba
set autotrace on;
關於Autotrace幾個常用選項的說明:
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 報告，這是預設模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只顯示優化器執行路徑報告
SET AUTOTRACE ON STATISTICS -- 只顯示執行統計信息
SET AUTOTRACE ON ----------------- 包含執行計劃和統計信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不顯示查詢
2.執行sql
四.sql trace
1.alter session set sql_trace=true;
2.執行sql
3.alter session set sql_trace=false;
4.查看相應的sql trace文件。
五.診斷事件(10046)
1.alter session set events '10046 trace name context forever,level 12';
2.執行sql
3.alter session set events '10046 trace name context off';
3.查看相應的sql trace文件。
可利用TKPROF工具查看跟蹤文件
TKPROF是一個用於分析oracle跟蹤文件並且產生一個更加清晰合理的輸出結果的可執行工具。如果一個系統的執行效率比較低，一個比較好的方法是跟蹤用戶的會話並且使用TKPROF工具的排序功能格式化輸出，從而找出有問題的SQL語句。
TKPROF命令後面的選項及輸出文件各個列的含義在這里不做詳細的介紹。google一下就會有很多資料。
下面簡單描述一下TKPROF工具的使用步驟：
1、在session級別設置sql_trace=true
sys@ORCL>alter session set sql_trace=true;
Session altered.
如果要在pl/sql中對session級別設置true，可以使用dbms_system這個包：
sys@ORCL> exec dbms_system.set_sql_trace_in_session(sid,serial#,true);
2、指定一下生成的trace文件的名字，便於查找：
sys@ORCL>alter session set trace file_identifier='yourname';
3、執行SQL語句。
4、利用TKPROF工具格式化輸出的trace 文件：
[oracle@q1test01~] $tkprof/oracle/admin/orcl/ump/orcl_ora_10266_yourname.trc/oracle/yourname.txtexplain=user/pwdaggregate=yessys=nowaits=yessort=fchela
5、查看生成的文件再設置sql_trace=false：
sys@ORCL>alter session set sql_trace=false;
㈥ 獲取SQL執行計劃的常見幾種方法
1. 預估執行計劃 - Explain Plan
Explain plan以SQL語句作為輸入，得到這條SQL語句的執行計劃，並將執行計劃輸出存儲到計劃表中。
首先，在你要執行的SQL語句前加explain plan for，此時將生成的執行計劃存儲到計劃表中，語句如下：
explain plan for SQL語句
然後，在計劃表中查詢剛剛生成的執行計劃，語句如下：
select * from table(dbms_xplan.display);
注意：Explain plan只生成執行計劃，並不會真正執行SQL語句，因此產生的執行計劃有可能不準，因為：
1）當前的環境可能和執行計劃生成時的環境不同；
2）不會考慮綁定變數的數據類型；
3）不進行變數窺視。
2. 查詢內存中緩存的執行計劃 （dbms_xplan.display_cursor）
如果你想獲取正在執行的或剛執行結束的SQL語句真實的執行計劃（即獲取library cache中的執行計劃），可以到動態性能視圖里查詢。方法如下：
1）獲取SQL語句的游標
游標分為父游標和子游標，父游標由sql_id(或聯合address和hash_value)欄位表示，子游標由child_number欄位表示。
如果SQL語句正在運行，可以從v$session中獲得它的游標信息，如：
select status, sql_id, sql_child_number from v$session where status='ACTIVE' and ....
如果知道SQL語句包含某些關鍵字，可以從v$sql視圖中獲得它的游標信息，如：
select sql_id, child_number, sql_text from v$sql where sql_text like '%關鍵字%『
2）獲取庫緩存中的執行計劃
為了獲取緩存庫中的執行計劃，可以直接查詢動態性能視圖v$sql_plan和v$sql_plan_statistics_all等，但更方便的方法是以sql_id和子游標為參數，執行如下語句：
select * from table(dbms_xplan.display_cursor('sql_id',child_number));
3）獲取前一次的執行計劃：
set serveroutput off
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
3. 查詢歷史執行計劃（dbms_xplan.display_awr）
AWR會定時把動態性能視圖中的執行計劃保存到dba_hist_sql_plan視圖中，如果你想要查看歷史執行計劃，可以採用如下方法查詢：
select * from table(dbms_xplan.display_awr('sql_id');
4. 在用sqlplus做SQL開發是（Autotrace）
set autotrace是sqlplus工具的一個功能，只能在通過sqlplus連接的session中使用，它非常適合在開發時測試SQL語句的性能，有以下幾種參數可供選擇：
SET AUTOTRACE OFF ---------------- 不顯示執行計劃和統計信息，這是預設模式
SET AUTOTRACE ON EXPLAIN ------ 只顯示優化器執行計劃
SET AUTOTRACE ON STATISTICS -- 只顯示統計信息
SET AUTOTRACE ON ----------------- 執行計劃和統計信息同時顯示
SET AUTOTRACE TRACEONLY ------ 不真正執行，只顯示預期的執行計劃，同explain plan
5. 生成Trace文件查詢詳細的執行計劃 （SQL_Trace, 10046）
SQL_TRACE作為初始化參數可以在實例級別啟用，也可以只在會話級別啟用，在實例級別啟用SQL_TRACE會導致所有進程的活動被跟蹤，包括後台進程及所有用戶進程，這通常會導致比較嚴重的性能問題，所以在一般情況下，我們使用sql_trace跟蹤當前進程，方法如下：
SQL>alter session set sql_trace=true;
...被跟蹤的SQL語句...
SQL>alter session set sql_trace=false;
如果要跟蹤其它進程，可以通過Oracle提供的系統包DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION來實現，例如：
SQL> exec dbms_system.set_sql_trace_in_session(sid,serial#,true) --開始跟蹤
SQL> exec dbms_system.set_sql_trace_in_session(sid,serial#,false) --結束跟蹤
生成trace文件後，再用tkprof 工具將sql trace 生成的跟蹤文件轉換成易讀的格式，語法如下：
tkprof inputfile outputfile
10046事件是SQL_TRACE的一個升級版，它也是追蹤會話，生成Trace文件，只是它裡面的內容更詳細，
㈦ 如何在執行sql後看到execution plan 的結果
在SQL Server中，我們通常將腳本載入到 Management Studio 查詢編輯器後，通過單擊查詢編輯器工具欄上的「顯示估計的執行計劃」或「包括實際的執行計劃」按鈕，可以選擇是顯示估計的執行計劃還是顯示實際的執行計劃。如果單擊「顯示估計的執行計劃」，則將分析該腳本並生成估計的執行計劃。如果單擊「包括實際的執行計劃」，則必須在生成執行計劃之前執行該腳本。分析或執行腳本之後，請單擊「執行計劃」選項卡以查看執行計劃輸出的圖形表示形式。你可以參考微軟官方文檔：https://technet.microsoft.com/zh-cn/library/ms178071(v=sql.105).aspx
㈧ 這句帶有大於，小於號的查詢條件的SQL是全表查詢嗎怎麼提高它的查詢效率
帶where條件了，不是全表查詢，把後面的問號替換成具體的條件，另外，將查詢*替換為具體要查的欄位，如果表有索引，並且條件欄位也含有索引欄位，最好走索引
暈，不知道你表的索引、主鍵情況，也不知道你where條件後面怎麼寫的，怎麼可能知道你的執行計劃呢？
看sql語句的執行效率，可以在plsql的執行計劃窗口（explain plan window）查看，根據執行計劃再來調整你的sql語句