一、oracle執行計劃

1.原理：Explain可以用來迅速方便地查出對于給定SQL語句中的查詢數據是如何得到的即搜索路徑(我們通常稱為Access Path)。從而使我們選擇最優的查詢方式達到最大的優化效果。

2.使用：explain PLAN [ SET STATEMENT_ID [=] < string literal > ] [ INTO < table_name > ] FOR < sql_statement >

　　STATEMENT_ID：是一個唯一的字符串，把當前執行計劃與存儲在同一PLAN中的其它執行計劃區別開來。
　　TABLE_NAME：是plan表名，它結構如前所示，你可以任意設定這個名稱。
　　SQL_STATEMENT：是真正的SQL語句。

3.查看：select * from table(dbms_xplan.display);

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

　　1、查看總COST，獲得資源耗費的總體印象

　　　　執行計劃第一行所對應的COST，反應了運行這段SQL的總體估計成本，通常COST低的執行計劃要好一些。

　　2、 按照從左至右，從上至下的方法，了解執行計劃的執行步驟

　　　　縮進最多的那一步，最先執行；結果集（對應ROWS/基數）；來分析表的訪問方式，連接順序以及連接方式是否合理。

　　3.分析表的訪問方式

　　　　表的訪問方式主要是兩種：全表掃描（TABLE ACCESS FULL）和索引掃描(INDEX SCAN)

　　4、 分析表的連接方式和連接順序

　　　　表的連接順序：就是以哪張表作為驅動表來連接其他表的先后訪問順序。?

??????? 　　表的連接方式：簡單來講，就是兩個表獲得滿足條件的數據時的連接過程。主要有三種表連接方式，嵌套循環（NESTED LOOPS）、哈希連接（HASH JOIN）和排序-合并連接（SORT MERGE JOIN）。

?

???????　　?我們常見得是嵌套循環和哈希連接。?

?

????????　　嵌套循環：最適用也是最簡單的連接方式。類似于用兩層循環處理兩個游標，外層游標稱作驅動表，Oracle檢索驅動表的數據，一條一條的代入內層游標，查找滿足WHERE條件的所有數據，因此內層游標表中可用索引的選擇性越好，嵌套循環連接的性能就越高。?

?

????????　　哈希連接：先將驅動表的數據按照條件字段以散列的方式放入內存，然后在內存中匹配滿足條件的行。哈希連接需要有合適的內存，而且必須在CBO優化模式下，連接兩表的WHERE條件有等號的情況下才可以使用。哈希連接在表的數據量較大，表中沒有合適的索引可用時比嵌套循環的效率要高。

　　5、謂詞說明

?

　　 access("A"."EMPNO"="B"."MGR")

????? ? filter("A"."EMPNO"="B"."MGR")

?

?? ? ?? filter("B"."MGR" IS NOT NULL)

?

?　　 Access:?表示這個謂詞條件的值將會影響數據的訪問路勁（表還是索引）。

?

????? ?? Filter：表示謂詞條件的值不會影響數據的訪問路勁，只起過濾的作用。

?

?　　在謂詞中主要注意access，要考慮謂詞的條件，使用的訪問路徑是否正確。

二、oracle索引

　　1、建立索引：CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name…])

　　　　????????????? 唯一索引、普通索引 ? ? ??索引名稱　　　表名　　　　列名 ? 單列索引、組合索引

　　2、索引失效：如果條件中有or，即使其中有條件帶索引也不會使用(這也是為什么盡量少用or的原因)

　　　　　　　　對于多列索引，不是使用的首位索引，則不會使用索引

　　　　　　　　like查詢是以%_開頭（%號在前或在后都不一定走索引，和數據量有關，走不走索引說白了還是看實際情況的成本多少。）

　　　　　　　　對索引列進行運算.需要建立函數索引.

　　　　　　　如果列類型是字符串，那一定要在條件中將數據使用引號引用起來,否則不使用索引

　　　　　　　not in ,not exist.

　　　　　　　B-tree索引 is null不會走,is not null會走,位圖索引 is null,is not null?? 都會走

三、MYSQL執行計劃

　　explain select...

　　explain extended select...? 將執行計劃“反編譯”成select語句 運行show warnings可以得到被mysql優化后的查詢語句

　　explain partitions select...用于分區表

　　每列含義：

　　1.id 執行順序 越大越早執行

　　2.select_type simple、primary、subquery..有沒有包括子查詢，查詢復雜度

　　3.type ALL全表、index遍歷索引樹、range索引范圍掃描、ref非唯一索引、eq_ref唯一索引、const\system查詢被編譯為常量、N

　　4、possinle_keys 列出可能被用的索引

　　5、key 實際使用的索引 TIPS覆蓋索引 只用索引二沒有查表

　　6、key_len 索引的字節數，索引的最大可能索引

　　7、ref 表連接匹配條件

　　8、rows 估算要查找的記錄條數

　　9、Extra 附錄 Using index覆蓋索引、Using where用where過濾結果集、Using temporary使用臨時表、Using filesort使用文件排序

?四、MYSQL查詢線程

　　show processlist;前一百條

　　show full processlist;全部

　　id:線程id

　　user：單列用戶

　　host：哪個ip發起的

　　db列：現在連接哪個數據庫

　　command列，顯示當前連接的執行的命令，一般就是休眠（sleep），查詢（query），連接（connect）

　　time列，此這個狀態持續的時間，單位是秒

　　state列，顯示使用當前連接的sql語句的狀態

　　info列，顯示這個sql語句

　　最重要的額是state列，有

　　

這個命令中最關鍵的就是state列，mysql列出的狀態主要有以下幾種：

Checking table?
正在檢查數據表（這是自動的）。?
Closing tables?
正在將表中修改的數據刷新到磁盤中，同時正在關閉已經用完的表。這是一個很快的操作，如果不是這樣的話，就應該確認磁盤空間是否已經滿了或者磁盤是否正處于重負中。?
Connect Out?
復制從服務器正在連接主服務器。?
Copying to tmp table on disk?
由于臨時結果集大于 tmp_table_size，正在將臨時表從內存存儲轉為磁盤存儲以此節省內存。?
Creating tmp table?
正在創建臨時表以存放部分查詢結果。?
deleting from main table?
服務器正在執行多表刪除中的第一部分，剛刪除第一個表。?
deleting from reference tables?
服務器正在執行多表刪除中的第二部分，正在刪除其他表的記錄。?
Flushing tables?
正在執行 FLUSH TABLES，等待其他線程關閉數據表。?
Killed?
發送了一個kill請求給某線程，那么這個線程將會檢查kill標志位，同時會放棄下一個kill請求。MySQL會在每次的主循環中檢查kill標志位，不過有些情況下該線程可能會過一小段才能死掉。如果該線程程被其他線程鎖住了，那么kill請求會在鎖釋放時馬上生效。?
Locked?
被其他查詢鎖住了。?
Sending data?
正在處理 SELECT 查詢的記錄，同時正在把結果發送給客戶端。?
Sorting for group?
正在為 GROUP BY 做排序。?
Sorting for order?
正在為 ORDER BY 做排序。?
Opening tables?
這個過程應該會很快，除非受到其他因素的干擾。例如，在執 ALTER TABLE 或 LOCK TABLE 語句行完以前，數據表無法被其他線程打開。 正嘗試打開一個表。?
Removing duplicates?
正在執行一個 SELECT DISTINCT 方式的查詢，但是MySQL無法在前一個階段優化掉那些重復的記錄。因此，MySQL需要再次去掉重復的記錄，然后再把結果發送給客戶端。?
Reopen table?
獲得了對一個表的鎖，但是必須在表結構修改之后才能獲得這個鎖。已經釋放鎖，關閉數據表，正嘗試重新打開數據表。?
Repair by sorting?
修復指令正在排序以創建索引。?
Repair with keycache?
修復指令正在利用索引緩存一個一個地創建新索引。它會比 Repair by sorting 慢些。?
Searching rows for update?
正在講符合條件的記錄找出來以備更新。它必須在 UPDATE 要修改相關的記錄之前就完成了。?
Sleeping?
正在等待客戶端發送新請求.?
System lock?
正在等待取得一個外部的系統鎖。如果當前沒有運行多個 mysqld 服務器同時請求同一個表，那么可以通過增加 --skip-external-locking參數來禁止外部系統鎖。?
Upgrading lock?
INSERT DELAYED 正在嘗試取得一個鎖表以插入新記錄。?
Updating?
正在搜索匹配的記錄，并且修改它們。?
User Lock?
正在等待 GET_LOCK()。?
Waiting for tables?
該線程得到通知，數據表結構已經被修改了，需要重新打開數據表以取得新的結構。然后，為了能的重新打開數據表，必須等到所有其他線程關閉這個表。以下幾種情況下會產生這個通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, 或 OPTIMIZE TABLE。?
waiting for handler insert?
INSERT DELAYED 已經處理完了所有待處理的插入操作，正在等待新的請求

　　

?

轉載于:https://www.cnblogs.com/bigmonkeys/p/7886428.html

總結

以上是生活随笔為你收集整理的oracle数据库操作的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。