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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26015009/viewspace-752560/