Replace SQL execution Plan

Switching-Different SQL Plan with SQL profiles in Oracle ...

When SQL is generated dynamically by a business system, or is generated by a third-party system, it may be difficult to modify the business program to improve execution plan and performance at the database level, but you can find the problematic SQL at the database level, adjust and transform the SQL, Then apply the execution plan to the original SQL statement, in the following steps:

• Find SQL-related information on performance issues at the database level;
• refactoring and optimizing SQL;
• Compare the old and optimized SQL performance information;
• The optimal SQL execution plan is applied to the original SQL statement;

With this approach, SQL performance is optimized at the database level. Take a look at the following example;

SQL> @iUSERNAME             INST_NAME            HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR-------------------- -------------------- ------------------------- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------OPS$SYWU             sydb                 sywu.com                  154   23601    11.2.0.4.0 20160421 23407      25    4144:3660       0000000071E1CC20 0000000072134028

The

has the following 2 Sql,sql 1;

Select E.first_name,e.email,e.salary,d.department_name,j.job_titlefrom Employees e,departments d,jobs Jwhere E.D epartment_id=d.department_id and e.job_id=j.job_id and E.salary>8500order by J.job_title;------------------------ --------------------------------------------------------------------------------------------------------------- ----------------------------| Id | Operation | Name | Starts | E-rows | e-bytes| Cost (%CPU) | E-time |   A-rows | A-time |  Buffers |  Omem | 1Mem | Used-mem |------------------------------------------------------------------------------------------------------   -------------------------------------------------------------| 0 |             SELECT STATEMENT |      |        1 |       |    |          40 (100) |     |      29 |00:00:00.01 |       44 |       |          |   ||  1 |             SORT ORDER by |      |     1 |  28 |    2072 | 40 (5) |     00:00:01 |  29 |00:00:00.01 |    44 |  4096 | 4096 | 4096 (0) | |   * 2 |             HASH JOIN |      |     1 |  28 |    2072 | 39 (3) |     00:00:01 |      29 |00:00:00.01 |   44 |   600k|  600k|   802K (0) | |    3 |             MERGE JOIN |      |     1 |  28 |    1624 | 21 (5) |     00:00:01 |      29 |00:00:00.01 |       23 |       |          |   ||     4 | TABLE ACCESS by INDEX rowid|      JOBS |     1 |   19 |     513 | 2 (0) |     00:00:01 |       17 |00:00:00.01 |       2 |       |          |   ||      5 | INDEX Full SCAN |      JOB_ID_PK |     1 |       19 |     | 1 (0) |     00:00:01 |       17 |00:00:00.01 |       1 |       |          | ||     * 6 |             SORT JOIN |     |     17 |   28 |    868 | 19 (6) |     00:00:01 |      29 |00:00:00.01 |  21 |  2048 | 2048 | 2048 (0) | |      * 7 | TABLE ACCESS Full |      EMPLOYEES |     1 |   28 |    868 | 18 (0) |     00:00:01 |      29 |00:00:00.01 |       21 |      | |   ||    8 | TABLE ACCESS Full |      Departments |     1 |   27 |    432 | 18 (0) |     00:00:01 |      27 |00:00:00.01 |       21 |       |          | |-------------------------------------------------------------------------------------------------------------- -----------------------------------------------------

SQL 1 is the original SQL; The following is the adjusted SQL 2;

Select/*+ ordered index (d dept_id_pk) index (J job_id_pk) */e.first_name,e.email,e.salary,d.department_name,j.job_ Titlefrom Employees e,departments d,jobs jwhere e.department_id=d.department_id and e.job_id=j.job_id and E.SALARY&G T;8500order by J.job_title;------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-rows | e-bytes| Cost (%CPU) | E-time |   A-rows | A-time | Buffers |  Reads |  Omem | 1Mem | Used-mem |------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------------------| 0 |                   SELECT STATEMENT |      |        1 |       |     |          8 (100) |     |       29 |00:00:00.02 |      6 |       2 |       |          |   ||  1 | SORT ORDER by |      |     1 |  28 |     2072 | 8 (25) |     00:00:01 |       29 |00:00:00.02 |      6 |  2 |  4096 | 4096 | 4096 (0) | |   * 2 |                   HASH JOIN |      |     1 |  28 |     2072 | 7 (15) |     00:00:01 |       29 |00:00:00.02 |      6 |   2 |   639k|  639k|   881K (0) | |    3 |                   MERGE JOIN |      |     1 |  28 |     1316 | 5 (20) |     00:00:01 |       29 |00:00:00.02 |      4 |       2 |       |          | ||     * 4 | TABLE ACCESS by INDEX ROWID |      EMPLOYEES |     1 |   28 |     868 | 2 (0) |     00:00:01 |       29 |00:00:00.01 |      2 |       1 |       |          |   ||      5 | INDEX Full SCAN |      Emp_department_ix |    1 |       106 |     | 1 (0) |    00:00:01 |       106 |00:00:00.01 |      1 |       1 |       |          | ||     * 6 |                   SORT JOIN |     |     29 |   27 |     432 | 3 (34) |     00:00:01 | 29 |0:00:00.01 |      2 |  1 |  2048 | 2048 |   2048 (0) | |      7 | TABLE ACCESS by INDEX rowid|      Departments |     1 |   27 |     432 | 2 (0) |     00:00:01 |       27 |00:00:00.01 |      2 |       1 |       |          |   ||       8 | INDEX Full SCAN |      DEPT_ID_PK |     1 |       27 |     | 1 (0) |     00:00:01 |       27 |00:00:00.01 |      1 |       1 |       |          |   ||    9 | TABLE ACCESS by INDEX ROWID |      JOBS |     1 |   19 |     513 | 2 (0) |     00:00:01 |       19 |00:00:00.01 |      2 |       0 |       |          |  ||     10 | INDEX Full SCAN |      JOB_ID_PK |     1 |       19 |     | 1 (0) |     00:00:01 |       19 |00:00:00.01 |      1 |       0 |       |          | |-------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------

What needs to be done is to make the original SQL 1 use the second adjusted (SQL 2) execution plan without tweaking and modifying the system code, so we do this by creating a SQL profile;

def v_sqlid=‘01h5fh3dhccyf‘declare    l_sql clob; begin    select t.SQL_FULLTEXT into l_sql from v$sql t where sql_id=‘&v_sqlid‘;    dbms_sqltune.import_sql_profile(sql_text => l_sql,name => ‘pro_&v_sqlid‘,profile => sqlprof_attr(‘IGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE(‘‘11.2.0.4‘‘)DB_VERSION(‘‘11.2.0.4‘‘)ALL_ROWSOUTLINE_LEAF(@"SEL$1")INDEX(@"SEL$1" "E"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID"))INDEX(@"SEL$1" "D"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID"))INDEX(@"SEL$1" "J"@"SEL$1" ("JOBS"."JOB_ID"))LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1" "J"@"SEL$1")USE_MERGE(@"SEL$1" "D"@"SEL$1")USE_HASH(@"SEL$1" "J"@"SEL$1")‘    ));    dbms_output.put_line(‘SQL Profile:pro_&v_sqlid imported...‘);end;/SQL Profile:pro_01h5fh3dhccyf imported...PL/SQL procedure successfully completed.

When the SQL is reloaded, the execution plan changes and SQL profile is used;

Select E.first_name,e.email,e.salary,d.department_name,j.job_titlefrom Employees e,departments d,jobs Jwhere E.D epartment_id=d.department_id and e.job_id=j.job_id and E.salary>8500order by J.job_title;------------------------ --------------------------------------------------------------------------------------------------------------- --------------------------------------------| Id | Operation | Name | Starts | E-rows | e-bytes| Cost (%CPU) | E-time |   A-rows | A-time | Buffers |  Reads |  Omem | 1Mem | Used-mem |------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------------------| 0 |                   SELECT STATEMENT |      |        1 |       |     |          8 (100) |     |       29 |00:00:00.02 |      6 |       2 |       |          |   ||  1 |                   SORT ORDER by |   |   1 |  28 |     2072 | 8 (25) |     00:00:01 |       29 |00:00:00.02 |      6 |  2 |  4096 | 4096 | 4096 (0) | |   * 2 |                   HASH JOIN |      |     1 |  28 |     2072 | 7 (15) |     00:00:01 |       29 |00:00:00.02 |      6 |   2 |   639k|  639k|   869K (0) | |    3 |                   MERGE JOIN |      |     1 |  28 |     1316 | 5 (20) |     00:00:01 |       29 |00:00:00.01 |      4 |       2 |       |          | ||     * 4 | TABLE ACCESS by INDEX ROWID |      EMPLOYEES |     1 |   28 |     868 | 2 (0) |     00:00:01 |       29 |00:00:00.01 |      2 |       1 |       |          |   ||      5 | INDEX Full SCAN |      Emp_department_ix |    1 |       106 |     | 1 (0) |    00:00:01 |       106 |00:00:00.01 |      1 |       1 |       |          | ||     * 6 |                   SORT JOIN |     |     29 |   27 |     432 | 3 (34) |     00:00:01 |       29 |00:00:00.01 |      2 |  1 |  2048 | 2048 |2048 (0) | |      7 | TABLE ACCESS by INDEX rowid|      Departments |     1 |   27 |     432 | 2 (0) |     00:00:01 |       27 |00:00:00.01 |      2 |       1 |       |          |   ||       8 | INDEX Full SCAN |      DEPT_ID_PK |     1 |       27 |     | 1 (0) |     00:00:01 |       27 |00:00:00.01 |      1 |       1 |       |          |   ||    9 | TABLE ACCESS by INDEX ROWID |      JOBS |     1 |   19 |     513 | 2 (0) |     00:00:01 |       19 |00:00:00.01 |      2 |       0 |       |          |  ||     10 | INDEX Full SCAN |      JOB_ID_PK |     1 |       19 |     | 1 (0) |     00:00:01 |       19 |00:00:00.01 |      1 |       0 |       |          | |-------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------Note------SQL profile PRO_01H5FH3DHCCYF used For this statement

The original SQL uses the adjusted execution plan.

Replace SQL execution Plan