How to query the execution plan of an SQL statement in storage execution.
The method is simple: first find the SQL statement in V $ sqlarea, then obtain SQL _id, and go to V $ SQL _plan to see the plan it executes.
Example:
First query SQL _id Based on the SQL statement
Select *
From v $ sqlarea SR
Where Sr. SQL _text like 'insert into t_billinvoicedetail %'
Order by Sr. first_load_time DESC;
-- Query the execution plan based on SQL _id
Select * from V $ SQL _plan pa where pa. SQL _id = '6c8y8nuysajcf ';
Or the following is more intuitive:
Select '--------------------------------------------------------------------------------'
From dual
Union all
Select '| operation | PHV/Object Name | rows | bytes | cost |' as "optimizer plan :"
From dual
Union all
Select '--------------------------------------------------------------------------------'
From dual
Union all
Select *
From (select rpad ('| substr (lpad ('', 1 * (depth-1) | operation |
Decode (options, null, '','' | options ),
1,
62 ),
63,
'') | '|
Rpad (decode (ID,
0,
'-----' | To_char (hash_value) | '-----',
Substr (decode (substr (object_name, 1, 7 ),
'Sys _ Le _',
Null,
Object_name) | '',
1,
20 )),
21,
'') | '|
Lpad (decode (cardinality,
Null,
'',
Decode (sign (Cardinality-10000 ),
-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,
'') | 'As" explain Plan"
From v $ SQL _plan
Where SQL _id = '6c8y8nuysajcf ')
Union all
Select '--------------------------------------------------------------------------------'
From dual;
As for how to force changes to the implementation plan, continue to explore!