Analysis and optimization of SQL statements

Install and view Oracle execution plans
The set of steps Oracle uses when executing SQL statements is called the execution plan

Previous conditions:
In the directory: execution utlxplan.sql $ORACLE _home/rdbms/admin directory

To view the execution plan:
Explan PLAN for <sql statement >

Credit @ORCL >explain Plan for SELECT * from CreditCard;

explained.

Look at the SQL execution plan information
Credit @ORCL >select a.operation,options,object_name,object_type,id,parent_id from plan_table a order by ID;

More intuitive:
Credit @ORCL >select Lpad (", (LEVEL-1)) | | Operation | | "| | Options | | "| | object_name | | "| | Decode (id,0, ' cost= ' | | Position) "Query Plan" from Plan_table connect by prior id=parent_id;

Query Plan
--------------------------------------------------------------------------------------------------------------- ---------
TABLE Accessfullcreditcard
TABLE Accessfullcreditcard
SELECT statementcost=3
TABLE Accessfullcreditcard
TABLE Accessfullcreditcard
SELECT statementcost=3
TABLE Accessfullcreditcard
TABLE Accessfullcreditcard
This can also be queried:
Credit @ORCL >select * from table (dbms_xplan.display);

Plan_table_output
--------------------------------------------------------------------------------------------------------------- ---------
Plan Hash value:2658862924

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------
| 0 |           SELECT STATEMENT |     |  9 |     1332 | 3 (0) | 00:00:01 |
|  1 | TABLE ACCESS full|     CREDITCARD |  9 |     1332 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
-Dynamic sampling used for this statement (level=2)

Turn on auto-tracking feature:
Set Autotrace on

To access the execution plan for a table through ROWID:
SYS as [email protected]>explain plan for
2 Select * from hr.departments where rowid= ' aaar5qaafaaaacvaaa ';

explained.

elapsed:00:00:00.05
SYS as [email protected]>select * FROM table (dbms_xplan.display);

Plan_table_output
--------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------
Plan Hash value:313428322

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------
| 0 |         SELECT STATEMENT |     |    1 |     21 | 1 (0) | 00:00:01 |
|  1 | TABLE ACCESS by USER rowid|     Departments |    1 |     21 | 1 (0) | 00:00:01 |
------------------------------------------------------------------------------------------

8 rows selected.


Execution plan for the connection query:


Optimization Case Study:
Increase the efficiency of the GROUP BY statement:
Select Cardno,sum (amount) from consume group by Cardno have cardno= ' 9555xxxx3 ' or cardno= ' 9555xxxx8 ';
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------
| 0 |          SELECT STATEMENT |   |  114k|   4475k| 175 (3) | 00:00:03 |
|* 1 |          FILTER |       |       |        |          | |
|   2 |          HASH GROUP by |   |  114k|   4475k| 175 (3) | 00:00:03 |
|    3 | TABLE ACCESS full|   Consume |  114k|   4475k| 171 (1) | 00:00:03 |
-------------------------------------------------------------------------------
1. Perform a full table scan, table ACCESS
2. Execution of Group statistics hash Group by
3. Perform filtering operations filter
Analysis: Filtering operations after grouping statistics, the amount of data processed by all group statistics is relatively large
Post-Optimization statements:
Select Cardno,sum (amount) from consume where "cardno" = ' 9555xxxx3 ' OR ' cardno ' = ' 9555xxxx8 ' GROUP by Cardno;



Use exists instead of in keyword







++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Method 1: Run the following script to generate the Plan_table table

Sql> @/u01/app/oracle/product/10.2/db_1/rdbms/admin/utlxplan.sql

Table created.


Sql> explain plan for
2 Select Deptno from Scott.dept Group by Deptno;

explained.

Sql> select Id,operation,options,object_name,position from plan_table;

ID Operation OPTIONS object_name POSITION
---- -------------------- --------------- ------------------------- ----------
0 SELECT STATEMENT 1
1 SORT GROUP by Nosort 1
2 INDEX full SCAN pk_dept 1

Method 2:oracle provides V$sql_plan to

Sql> Select Id,options,operation,object_name,cost
2 from V$sql_plan
3 where object_owner= ' SCOTT ';

No rows selected--There is no data because the explain plan for command only generates an execution plan, rather than actually executing the statement

Sql> Select Deptno from Scott.dept Group by Deptno;

DEPTNO
----------
10
20
30
40



Sql> select Id,operation,options,object_name,position from plan_table;

ID Operation OPTIONS object_name POSITION
---- -------------------- -------------------- -------------------- ----------
0 SELECT STATEMENT 1
1 SORT GROUP by Nosort 1
2 INDEX full SCAN pk_dept 1

Analysis and optimization of SQL statements