獲取oracle正在處於等待狀態的sql語句的執行計劃的語句
col operation format a50
col cost format 999999
col kbytes format 999999
col object format a20
select hash_value,child_number,
lpad('',2*depth)
||operation
||''
||options
||decode(id,0,substr(optimizer,1,6)||'Cost='||to_char(cost))operation,
object_name,object_type,cost,round(bytes/1024) kbytes
from v$sql_plan where hash_value in(
select a.sql_hash_value
from v$session a,v$session_wait b
where a.sid=b.sid
and b.event='db file sequential read')
order by hash_value,child_number,ID;
SQL> col operation format a55
SQL> col cost format 99999
SQL> col kbytes format 999999
SQL> col object format a25
SQL> select hash_value,child_number,
2 lpad('',2*depth)
3 ||operation
4 ||''
5 ||options
6 ||decode(id,0,substr(optimizer,1,6)||'Cost='||to_char(cost))operation,
7 object_name,object_type,cost,round(bytes/1024) kbytes
8 from v$sql_plan where hash_value in(
9 select a.sql_hash_value
10 from v$session a,v$session_wait b
11 where a.sid=b.sid
12 and b.event='db file sequential read')
13 order by hash_value,child_number,ID;
HASH_VALUE CHILD_NUMBER OPERATION OBJECT_NAME OBJECT_TYPE COST KBYTES
---------- ------------ ------------------------------------------------------- ------------------------------ ---------------------------------------- ----- ------
1722201563 0 SELECT STATEMENTFIRST_Cost=203255 20325
5
1722201563 0 SORTORDER BY 20325 5910
5
1722201563 0 HASHGROUP BY 20325 5910
5
1722201563 0 HASH JOIN 20193 5910
1
1722201563 0 TABLE ACCESSFULL BS_CORP TABLE 85 41
1722201563 0 NESTED LOOPSSEMI 20184 4561
5
1722201563 0 HASH JOIN 20184 4540
3
1722201563 0 HASH JOIN 19970 2515
4
1722201563 0 HASH JOIN 19529 1813
2
1722201563 0 HASH JOINRIGHT SEMI 19442 489
1
1722201563 0 VIEW VW_SQ_1 VIEW 19402 36
8
1722201563 0 FILTER
1722201563 0 NESTED LOOPS 19402 241
8
1722201563 0 NESTED LOOPS 19402 157
6
1722201563 0 MERGE JOINCARTESIAN 28436 264415
1722201563 0 INLIST ITERATOR
1722201563 0 INDEXRANGE SCAN PK_BS_CORP INDEX (UNIQUE) 4 0
1722201563 0 BUFFERSORT 28432 5509
1722201563 0 TABLE ACCESSFULL LV_BUSI_RECORD TABLE 862 5509
1722201563 0 TABLE ACCESSBY INDEX ROWID BS_FAMILY TABLE 19402 0
6
HASH_VALUE CHILD_NUMBER OPERATION OBJECT_NAME OBJECT_TYPE COST KBYTES
---------- ------------ ------------------------------------------------------- ------------------------------ ---------------------------------------- ----- ------
1722201563 0 BITMAP CONVERSIONTO ROWIDS
1722201563 0 BITMAP AND
1722201563 0 BITMAP CONVERSIONFROM ROWIDS
1722201563 0 INDEXRANGE SCAN PK_BS_FAMILY INDEX (UNIQUE) 1
1722201563 0 BITMAP CONVERSIONFROM ROWIDS
1722201563 0 INDEXRANGE SCAN IDX_BS_FAMILY_CORP_ID INDEX 1
1722201563 0 INDEXRANGE SCAN INDEX_FAMILY_ID INDEX 1 0
1722201563 0 TABLE ACCESSFULL LV_BUSI_BILL TABLE 393 847
1722201563 0 TABLE ACCESSFULL LV_BUSI_RECORD TABLE 867 15524
1722201563 0 TABLE ACCESSFULL BS_FAMILY TABLE 4407 23768
1722201563 0 TABLE ACCESSFULL LV_BUSI_ASSIGN TABLE 913 12421
1722201563 0 INDEXRANGE SCAN INDEX_FAMILY_ID INDEX 1 3236
1722201563 1 SELECT STATEMENTFIRST_Cost=197255 19725
5
1722201563 1 SORTORDER BY 19725 907
5
1722201563 1 HASHGROUP BY 19725 907
5
1722201563 1 HASH JOIN 19704 907
8
1722201563 1 HASH JOIN 19613 516
0
1722201563 1 TABLE ACCESSFULL BS_CORP TABLE 85 41
1722201563 1 NESTED LOOPSSEMI 19604 389
4
1722201563 1 NESTED LOOPS 19604 386
3
1722201563 1 NESTED LOOPS 19488 278
0
HASH_VALUE CHILD_NUMBER OPERATION OBJECT_NAME OBJECT_TYPE COST KBYTES
---------- ------------ ------------------------------------------------------- ------------------------------ ---------------------------------------- ----- ------
1722201563 1 HASH JOINSEMI 19442 75
1
1722201563 1 TABLE ACCESSFULL LV_BUSI_BILL TABLE 392 70
1722201563 1 VIEW VW_SQ_1 VIEW 19402 36
8
1722201563 1 FILTER
1722201563 1 NESTED LOOPS 19402 241
8
1722201563 1 NESTED LOOPS 19402 157
6
1722201563 1 MERGE JOINCARTESIAN 28436 264415
1722201563 1 INLIST ITERATOR
1722201563 1 INDEXRANGE SCAN PK_BS_CORP INDEX (UNIQUE) 4 0
1722201563 1 BUFFERSORT 28432 5509
1722201563 1 TABLE ACCESSFULL LV_BUSI_RECORD TABLE 862 5509
1722201563 1 TABLE ACCESSBY INDEX ROWID BS_FAMILY TABLE 19402 0
6
1722201563 1 BITMAP CONVERSIONTO ROWIDS
1722201563 1 BITMAP AND
1722201563 1 BITMAP CONVERSIONFROM ROWIDS
1722201563 1 INDEXRANGE SCAN PK_BS_FAMILY INDEX (UNIQUE) 1
1722201563 1 BITMAP CONVERSIONFROM ROWIDS
1722201563 1 INDEXRANGE SCAN IDX_BS_FAMILY_CORP_ID INDEX 1
1722201563 1 INDEXRANGE SCAN INDEX_FAMILY_ID INDEX 1 0
1722201563 1 TABLE ACCESSBY INDEX ROWID LV_BUSI_RECORD TABLE 1 0
1722201563 1 INDEXRANGE SCAN IDX_BUSI_BILL_BILLSN INDEX 1
HASH_VALUE CHILD_NUMBER OPERATION OBJECT_NAME OBJECT_TYPE COST KBYTES
---------- ------------ ------------------------------------------------------- ------------------------------ ---------------------------------------- ----- ------
1722201563 1 TABLE ACCESSBY INDEX ROWID BS_FAMILY TABLE 1 0
1722201563 1 INDEXUNIQUE SCAN PK_BS_FAMILY INDEX (UNIQUE) 1
1722201563 1 INDEXRANGE SCAN INDEX_FAMILY_ID INDEX 1 3236
1722201563 1 TABLE ACCESSFULL LV_BUSI_ASSIGN TABLE 913 12421
66 rows selected
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-752560/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 獲取oracle sql語句詳細些執行計劃OracleSQL
- Laravel 獲取執行的sql語句LaravelSQL
- 查詢Oracle正在執行的sql語句及執行該語句的使用者OracleSQL
- sql語句如何執行的SQL
- 後臺執行SQL語句(oracle)SQLOracle
- Mybatis 動態執行SQL語句MyBatisSQL
- mysql的sql語句執行流程MySql
- SQL 語句的執行順序SQL
- java連線oracle執行sql語句JavaOracleSQL
- python關於pymysql 執行sql語句in的用法PythonMySql
- Oracle中獲取TABLE的DDL語句的方法Oracle
- Oracle執行語句跟蹤 使用sql trace實現語句追蹤OracleSQL
- spark sql語句效能最佳化及執行計劃SparkSQL
- 分析執行計劃優化SQLSQL語句處理的過程(轉)優化SQL
- SQL語句各子句的執行順序SQL
- Laravel 框架查詢執行的 SQL 語句Laravel框架SQL
- 一條sql語句的執行過程SQL
- Oracle資料庫SQL語句執行過程Oracle資料庫SQL
- SQL語句執行順序SQL
- Oracle基本SQL語句OracleSQL
- SQL Server 查詢歷史執行的SQL語句SQLServer
- Oracle獲取資料庫中的物件建立語句Oracle資料庫物件
- 一條更新的SQL語句是如何執行的？SQL
- SQL 查詢語句的執行順序解析SQL
- 一條update SQL語句是如何執行的SQL
- 一條SQL更新語句是如何執行的SQL
- Hive SQL語句的正確執行順序HiveSQL
- 一條SQL更新語句是如何執行的？SQL
- oracle常用後臺程序及sql語句執行流程OracleSQL
- SQL語句IN的用法SQL
- sql語句執行緩慢分析SQL
- mysql執行sql語句過程MySql
- Oracle行轉列、列轉行的Sql語句總結OracleSQL
- SQL查詢語句 (Oracle)SQLOracle
- [20190125]簡單快速檢視那些sql語句正在執行.txtSQL
- 輸入的查詢 SQL 語句，是如何執行的？SQL
- 一條SQL語句在MySQL中如何執行的MySql
- 一條 SQL 查詢語句是如何執行的？SQL
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle