Shulou(Shulou.com)06/01 Report--

View v$sql_plan is useful when analyzing the performance of SQL statements. Here are a few examples.

The execution plan of the SQL that has been run in the library cache can be queried through the following script

Col "Explain Plan" for A100

Select''as "No.",'| Operation | Object Name | Rows | Bytes | Cost |'

As "Explain Plan" from dual

Union all

Select to_char (id, '999'), rpad (' |'| | substr (lpad ('', 1* (depth-1)) | | operation | |

Decode (options, null,''| | substr (optimizer, 1,7),''| | options), 1,35), 36,'') | |'|

Rpad (decode (id, 0,'')

Substr (decode (substr (object_name, 1,7), 'SYS_LE_', null, object_name)

|', 1,30), 31,') | |'|'| | lpad (decode (cardinality, null,'') |

Decode (sign (cardinality-1000),-1, cardinality | |'

Decode (sign (cardinality-1000000),-1, trunc (cardinality / 1000) | |'K'

Decode (sign (cardinality-1000000000),-1, trunc (cardinality / 1000000) | |'M'

Trunc (cardinality / 1000000000) | |'G'), 7,') | |'|

Lpad (decode (bytes, null,''

Decode (sign (bytes-1024),-1, bytes | |'

Decode (sign (bytes-1048576),-1, trunc (bytes / 1024) | |'K'

Decode (sign (bytes-1073741824),-1, trunc (bytes / 1048576) | |'M'

Trunc (bytes / 1073741824) | |'G'), 6,') | |'|

Lpad (decode (cost, null,'', decode (sign (cost-10000000),-1, cost | |''

Decode (sign (cost-1000000000),-1, trunc (cost / 1000000) | |'M'

Trunc (cost / 1000000000) | |'G')), 8,') | |'|'

From v$sql_plan sp

Where sp.sql_id ='& sql_id'

Enter the value of sql_id: a08vqym1n4k5n

No. Explain Plan

-

| | Operation | Object Name | Rows | Bytes | Cost | |

0 | SELECT STATEMENT ALL_ROW | 2 |

1 | NESTED LOOPS | | 1 | 821 | 2 |

2 | NESTED LOOPS | | 1 | 808 | 2 |

3 | HASH JOIN | | 1 | 782 | 2 |

4 | NESTED LOOPS | | 1 | 736 | 1 |

5 | NESTED LOOPS | | 1 | 718 | 0 |

6 | FIXED TABLE FULL | X$KSUSE | 1 | 182 | 0 |

7 | FIXED TABLE FIXED INDEX | X$KGLCURSOR_CHILD_SQLID (ind:2 | 1 | 536 | 0 |

8 | INDEX RANGE SCAN | I_AUDIT_ACTIONS | 1 | 18 | 1 |

9 | FIXED TABLE FULL | X$KSUPR | 1 | 46 | 0 |

10 | FIXED TABLE FIXED INDEX | X$KSLWT (ind:1) | 1 | 26 | 0 |

11 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 13 | 0 |

Combined with v$sqltext, you can quickly find the SQL statements in the library cache that perform full table scan or full index scan.

The following finds the SQL statement for the full table scan

Select t.address

T.hash_value

T.sql_id

P.child_number

T.piece

Sql_text

P.object_name

From v$sqltext t, v$sql_plan p

Where t.hash_value = p.hash_value

And p.operation = 'TABLE ACCESS'

And p.options = 'FULL'

And p.object_owner = 'RMES'

Order by t.address, t.hash_value, t.sql_id, p.child_number, t.piece

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PIECE SQL_TEXT OBJECT_NAME

000007FF5567C198 580882336 cwmfwqcj9z3x0 00 DELETE RMES.R_WIP_AGV_T T WHERE T.LINE_ID =: B1 AND T.WIP_AGV_ID R_WIP_STATION_T

000007FF5567C198 580882336 cwmfwqcj9z3x0 0 1 NOT IN (SELECT T1.WIP_AGV_ID FROM RMES.R_WIP_STATION_T T1 WHERE R_WIP_STATION_T

000007FF5567C198 580882336 cwmfwqcj9z3x0 0 2 T1.WIP_AGV_ID IS NOT NULL AND T1.WIP_AGV_ID 0 AND T1.LINE_ID R_WIP_STATION_T

000007FF5567C198 580882336 cwmfwqcj9z3x0 0 3 =: B1) R_WIP_STATION_T

000007FF5590F710 2960126118 dtsrgu6s6zw56 20 UPDATE RMES.R_WIP_STATION_T SET GROUP_ID =: B7, STATION_ID =: B R_WIP_STATION_T

000007FF5590F710 2960126118 dtsrgu6s6zw56 2 16, WIP_EMP_ID =: B5, TEAM_ID =: B4, SHIFT_ID =: B3, ERROR_FL R_WIP_STATION_T

000007FF5590F710 2960126118 dtsrgu6s6zw56 2 2 AG = '0000, IN_LINE_TIME =: B2, OUT_LINE_TIME =: B2, IN_STATION_ R_WIP_STATION_T

000007FF5590F710 2960126118 dtsrgu6s6zw56 2 3 TIME = SYSDATE, OUTLINE_FLAG = '1mm, HAD_OUTLINED =' 1' WHERE WIP R_WIP_STATION_T

000007FF5590F710 2960126118 dtsrgu6s6zw56 2 4 _ TRACKING_ID =: B1 R_WIP_STATION_T

The following finds the SQL statement for the full index scan

Select t.address

T.hash_value

T.sql_id

P.child_number

T.piece

Sql_text

P.object_name

From v$sqltext t, v$sql_plan p

Where t.hash_value = p.hash_value

And p.operation = 'INDEX'

And p.options = 'FULL SCAN'

And p.object_owner = 'RMES'

Order by t.address, t.hash_value, t.sql_id, p.child_number, t.piece

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PIECE SQL_TEXT OBJECT_NAME

000007FF55FC55E8 1305393110 b4ysx616wxdyq 00 select pd.force,pd.position from rmes.r_press_t p,rmes.r_press_d IDX_PK_PRESS_DETAIL_ID

000007FF55FC55E8 1305393110 b4ysx616wxdyq 0 1 etail_t pd where p.press_id=pd.press_id and p.pressroomid=-1 ORDE IDX_PK_PRESS_DETAIL_ID

000007FF55FC55E8 1305393110 b4ysx616wxdyq 0 2 R BY PD.PRESS_DETAIL_ID ASC IDX_PK_PRESS_DETAIL_ID

The following query executes the plan by waiting for the event

Col operation for a50

Select hash_value

Child_number

Lpad (', 2 * depth) | | operation | |''| | options | |

Decode (id, 0, substr (optimizer, 1,6) | | 'Cost =' | | to_char (cost)) operation

Object_name

Cost

Round (bytes / 1024) kbytes

From v$sql_plan

Where hash_value in

(select sql_hash_value from v$session where event ='& waitevent')

Order by hash_value, child_number, id

Enter the value of waitevent: enq: TX-row lock contention

HASH_VALUE CHILD_NUMBER OPERATION OBJECT_NAME COST KBYTES

2186674966 0 SELECT STATEMENT ALL_RO Cost = 3 3

2186674966 0 FOR UPDATE

2186674966 0 BUFFER SORT

2186674966 0 TABLE ACCESS FULL C_LINE_T 3 1

The current wait events for all sessions can be queried through v$session_wait

Col username for a10

Col event for a60

Select sw.sid, s. Serializations, s.username, sw.event, sw.wait_time, sw.state from v$session s, v$session_wait sw where sw.event not like 'rdbms%' and sw.sid = s.sid

Finally, you can find the corresponding SQL statement by waiting for the event

Select sql_text

From v$sql

Where sql_id =

(select sql_id

From v$session

Where sid =

(select sid from v$session_wait where event ='& waitwvent')

Enter the value of waitwvent: enq: TX-row lock contention

SQL_TEXT

-

Select * from cmes.c_line_t for update

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.