课程第63/64/65次（2017-10-20星期五，2017-10-22星期天上下午）

botang

1. select * from dba_users u
   
2.   order by  username;
   
3.   
   
4. select  * from dba_objects o where o.owner='OUTLN';
   
5. 
   
6. select  * from outln.OL$ ;
   
7. 
   
8. alter user sh identified by sh account unlock;
   
9. 
   
10.     create outline outline1 on   
    
11.     SELECT c.cust_id, SUM(amount_sold)
    
12.       FROM
    
13.       sh.sales s, sh.customers c
    
14.       WHERE s.cust_id = c.cust_id
    
15.       GROUP BY c.cust_id;
    
16.       
    
17.       -------
    
18.       select  * from dba_sql_profiles;
    
19.       
    
20.       
    
21.       select  * from dba_sql_plan_baselines;

复制代码

1. select  * from dba_sql_plan_baselines  order by created;
   
2. 
   
3. declare
   
4.   report clob;
   
5. begin
   
6. report:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'');
   
7. dbms_output.put_line(report);
   
8. end;
   

复制代码

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SYS_SQL_6fe28d438dfc352f
  PLAN_NAME  = SQL_PLAN_6zsnd8f6zsd9g11df68d0
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SQL_PLAN_6zsnd8f6zsd9g11df68d0
------------------------------------
  Plan was verified: Time used 1.83 seconds.
  Plan failed performance criterion: 4.11 times worse than baseline plan.

                            Baseline Plan      Test Plan       Stats Ratio
                            -------------      ---------       -----------
  Execution Status:              COMPLETE       COMPLETE
  Rows Processed:                       0              0
  Elapsed Time(ms):                81.114        332.951               .24
  CPU Time(ms):                    81.098        332.949               .2

FIRST_ROWS:
SQL> @exquery1.sql
SQL> set echo on
SQL> explain plan for
  2  select /*LOAD_AUTO*/ * from sh.sales where quantity_sold > 40 order by prod_id;

Explained.

SQL>
SQL> @?/rdbms/admin/utlxplp.sql
SQL> Rem
SQL> Rem $Header: utlxplp.sql 23-jan-2002.08:55:23 bdagevil Exp $
SQL> Rem
SQL> Rem utlxplp.sql
SQL> Rem
SQL> Rem Copyright (c) 1998, 2002, Oracle Corporation.    All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem     utlxplp.sql - UTiLity eXPLain Parallel plans
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem     script utility to display the explain plan of the last explain plan
SQL> Rem     command. Display also Parallel Query information if the plan happens to
SQL> Rem     run parallel
SQL> Rem
SQL> Rem    NOTES
SQL> Rem     Assume that the table PLAN_TABLE has been created. The script
SQL> Rem     utlxplan.sql should be used to create that table
SQL> Rem
SQL> Rem     With SQL*plus, it is recomended to set linesize and pagesize before
SQL> Rem     running this script. For example:
SQL> Rem     set linesize 130
SQL> Rem     set pagesize 0
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    bdagevil    01/23/02 - rewrite with new dbms_xplan package
SQL> Rem    bdagevil    04/05/01 - include CPU cost
SQL> Rem    bdagevil    02/27/01 - increase Name column
SQL> Rem    jihuang    06/14/00 - change order by to order siblings by.
SQL> Rem    jihuang    05/10/00 - include plan info for recursive SQL in LE row source
SQL> Rem    bdagevil    01/05/00 - make deterministic with order-by
SQL> Rem    bdagevil    05/07/98 - Explain plan script for parallel plans
SQL> Rem    bdagevil    05/07/98 - Created
SQL> Rem
SQL>
SQL> set markup html preformat on
SQL>
SQL> Rem
SQL> Rem Use the display table function from the dbms_xplan package to display the last
SQL> Rem explain plan. Use default mode which will display only relevant information
SQL> Rem
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3803407550

----------------------------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |       1 |      29 |     491   (3)| 00:00:06 |         |         |
|   1 |  SORT ORDER BY         |         |       1 |      29 |     491   (3)| 00:00:06 |         |         |
|   2 |   PARTITION RANGE ALL|         |       1 |      29 |     490   (2)| 00:00:06 |       1 |      28 |
|*  3 |    TABLE ACCESS FULL | SALES |       1 |      29 |     490   (2)| 00:00:06 |       1 |      28 |
----------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("QUANTITY_SOLD">40)

Note
-----
   - SQL plan baseline "SQL_PLAN_6zsnd8f6zsd9g54bc8843" used for this statement

19 rows selected.

1. declare
   
2.   report clob;
   
3. begin
   
4. report:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_6fe28d438dfc352f',
   
5.                                                   plan_name=>'SQL_PLAN_6zsnd8f6zsd9g11df68d0',
   
6.                                                   verify => 'NO');
   
7. dbms_output.put_line(report);
   
8. end;

复制代码

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SYS_SQL_6fe28d438dfc352f
  PLAN_NAME  = SQL_PLAN_6zsnd8f6zsd9g11df68d0
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = NO
  COMMIT     = YES

Plan: SQL_PLAN_6zsnd8f6zsd9g11df68d0
------------------------------------
  Plan was changed to an accepted plan.

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 0
Number of plans accepted: 1

FIRST_ROWS:

1. SQL> @exquery1.sql
   
2. SQL> set echo on
   
3. SQL> explain plan for
   
4.   2  select /*LOAD_AUTO*/ * from sh.sales where quantity_sold > 40 order by prod_id;
   
5. 
   
6. Explained.
   
7. 
   
8. SQL>
   
9. SQL> @?/rdbms/admin/utlxplp.sql
   
10. SQL> Rem
    
11. SQL> Rem $Header: utlxplp.sql 23-jan-2002.08:55:23 bdagevil Exp $
    
12. SQL> Rem
    
13. SQL> Rem utlxplp.sql
    
14. SQL> Rem
    
15. SQL> Rem Copyright (c) 1998, 2002, Oracle Corporation.        All rights reserved.
    
16. SQL> Rem
    
17. SQL> Rem    NAME
    
18. SQL> Rem         utlxplp.sql - UTiLity eXPLain Parallel plans
    
19. SQL> Rem
    
20. SQL> Rem    DESCRIPTION
    
21. SQL> Rem         script utility to display the explain plan of the last explain plan
    
22. SQL> Rem         command. Display also Parallel Query information if the plan happens to
    
23. SQL> Rem         run parallel
    
24. SQL> Rem
    
25. SQL> Rem    NOTES
    
26. SQL> Rem         Assume that the table PLAN_TABLE has been created. The script
    
27. SQL> Rem         utlxplan.sql should be used to create that table
    
28. SQL> Rem
    
29. SQL> Rem         With SQL*plus, it is recomended to set linesize and pagesize before
    
30. SQL> Rem         running this script. For example:
    
31. SQL> Rem         set linesize 130
    
32. SQL> Rem         set pagesize 0
    
33. SQL> Rem
    
34. SQL> Rem    MODIFIED   (MM/DD/YY)
    
35. SQL> Rem    bdagevil        01/23/02 - rewrite with new dbms_xplan package
    
36. SQL> Rem    bdagevil        04/05/01 - include CPU cost
    
37. SQL> Rem    bdagevil        02/27/01 - increase Name column
    
38. SQL> Rem    jihuang        06/14/00 - change order by to order siblings by.
    
39. SQL> Rem    jihuang        05/10/00 - include plan info for recursive SQL in LE row source
    
40. SQL> Rem    bdagevil        01/05/00 - make deterministic with order-by
    
41. SQL> Rem    bdagevil        05/07/98 - Explain plan script for parallel plans
    
42. SQL> Rem    bdagevil        05/07/98 - Created
    
43. SQL> Rem
    
44. SQL>
    
45. SQL> set markup html preformat on
    
46. SQL>
    
47. SQL> Rem
    
48. SQL> Rem Use the display table function from the dbms_xplan package to display the last
    
49. SQL> Rem explain plan. Use default mode which will display only relevant information
    
50. SQL> Rem
    
51. SQL> select * from table(dbms_xplan.display());
    
52. 
    
53. PLAN_TABLE_OUTPUT
    
54. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
55. Plan hash value: 899219946
    
56. 
    
57. -----------------------------------------------------------------------------------------------------------------------
    
58. | Id  | Operation                            | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    
59. -----------------------------------------------------------------------------------------------------------------------
    
60. |   0 | SELECT STATEMENT                    |                      |     1 |    29 |  2883        (1)| 00:00:35 |       |       |
    
61. |   1 |  SORT ORDER BY                            |                      |     1 |    29 |  2883        (1)| 00:00:35 |       |       |
    
62. |   2 |   PARTITION RANGE ALL                    |                      |     1 |    29 |  2882        (1)| 00:00:35 |     1 |    28 |
    
63. |*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES              |     1 |    29 |  2882        (1)| 00:00:35 |     1 |    28 |
    
64. |   4 |     BITMAP CONVERSION TO ROWIDS     |                      |       |       |            |              |       |       |
    
65. |   5 |      BITMAP INDEX FULL SCAN            | SALES_PROMO_BIX |       |       |            |              |     1 |    28 |
    
66. 
    
67. PLAN_TABLE_OUTPUT
    
68. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
69. -----------------------------------------------------------------------------------------------------------------------
    
70. 
    
71. Predicate Information (identified by operation id):
    
72. ---------------------------------------------------
    
73. 
    
74.    3 - filter("QUANTITY_SOLD">40)
    
75. 
    
76. Note
    
77. -----
    
78.    - SQL plan baseline "SQL_PLAN_6zsnd8f6zsd9g11df68d0" used for this statement
    
79. 
    
80. 21 rows selected.
    
81. 
    
82. SQL>
    

复制代码

FIX基线（相当于大纲）：

1. declare
   
2.   v_1  number;
   
3. begin
   
4.    v_1 :=dbms_spm.alter_sql_plan_baseline(sql_handle=>'SYS_SQL_6fe28d438dfc352f',
   
5.                                                   plan_name=>'SQL_PLAN_6zsnd8f6zsd9g11df68d0',
   
6.                                                   attribute_name => 'FIXED',
   
7.                                                   attribute_value => 'YES');
   
8.      dbms_output.put_line(v_1);
   
9.   end;

复制代码

ALL_ROWS：

1. SQL> alter session set optimizer_mode=all_rows;
   
2. 
   
3. Session altered.
   
4. 
   
5. SQL> @exquery1.sql
   
6. SQL> set echo on
   
7. SQL> explain plan for
   
8.   2  select /*LOAD_AUTO*/ * from sh.sales where quantity_sold > 40 order by prod_id;
   
9. 
   
10. Explained.
    
11. 
    
12. SQL>
    
13. SQL> @?/rdbms/admin/utlxplp.sql
    
14. SQL> Rem
    
15. SQL> Rem $Header: utlxplp.sql 23-jan-2002.08:55:23 bdagevil Exp $
    
16. SQL> Rem
    
17. SQL> Rem utlxplp.sql
    
18. SQL> Rem
    
19. SQL> Rem Copyright (c) 1998, 2002, Oracle Corporation.        All rights reserved.
    
20. SQL> Rem
    
21. SQL> Rem    NAME
    
22. SQL> Rem         utlxplp.sql - UTiLity eXPLain Parallel plans
    
23. SQL> Rem
    
24. SQL> Rem    DESCRIPTION
    
25. SQL> Rem         script utility to display the explain plan of the last explain plan
    
26. SQL> Rem         command. Display also Parallel Query information if the plan happens to
    
27. SQL> Rem         run parallel
    
28. SQL> Rem
    
29. SQL> Rem    NOTES
    
30. SQL> Rem         Assume that the table PLAN_TABLE has been created. The script
    
31. SQL> Rem         utlxplan.sql should be used to create that table
    
32. SQL> Rem
    
33. SQL> Rem         With SQL*plus, it is recomended to set linesize and pagesize before
    
34. SQL> Rem         running this script. For example:
    
35. SQL> Rem         set linesize 130
    
36. SQL> Rem         set pagesize 0
    
37. SQL> Rem
    
38. SQL> Rem    MODIFIED   (MM/DD/YY)
    
39. SQL> Rem    bdagevil        01/23/02 - rewrite with new dbms_xplan package
    
40. SQL> Rem    bdagevil        04/05/01 - include CPU cost
    
41. SQL> Rem    bdagevil        02/27/01 - increase Name column
    
42. SQL> Rem    jihuang        06/14/00 - change order by to order siblings by.
    
43. SQL> Rem    jihuang        05/10/00 - include plan info for recursive SQL in LE row source
    
44. SQL> Rem    bdagevil        01/05/00 - make deterministic with order-by
    
45. SQL> Rem    bdagevil        05/07/98 - Explain plan script for parallel plans
    
46. SQL> Rem    bdagevil        05/07/98 - Created
    
47. SQL> Rem
    
48. SQL>
    
49. SQL> set markup html preformat on
    
50. SQL>
    
51. SQL> Rem
    
52. SQL> Rem Use the display table function from the dbms_xplan package to display the last
    
53. SQL> Rem explain plan. Use default mode which will display only relevant information
    
54. SQL> Rem
    
55. SQL> select * from table(dbms_xplan.display());
    
56. 
    
57. PLAN_TABLE_OUTPUT
    
58. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
59. Plan hash value: 899219946
    
60. 
    
61. -----------------------------------------------------------------------------------------------------------------------
    
62. | Id  | Operation                            | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    
63. -----------------------------------------------------------------------------------------------------------------------
    
64. |   0 | SELECT STATEMENT                    |                      |     1 |    29 |  2883        (1)| 00:00:35 |       |       |
    
65. |   1 |  SORT ORDER BY                            |                      |     1 |    29 |  2883        (1)| 00:00:35 |       |       |
    
66. |   2 |   PARTITION RANGE ALL                    |                      |     1 |    29 |  2882        (1)| 00:00:35 |     1 |    28 |
    
67. |*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES              |     1 |    29 |  2882        (1)| 00:00:35 |     1 |    28 |
    
68. |   4 |     BITMAP CONVERSION TO ROWIDS     |                      |       |       |            |              |       |       |
    
69. |   5 |      BITMAP INDEX FULL SCAN            | SALES_PROMO_BIX |       |       |            |              |     1 |    28 |
    
70. 
    
71. PLAN_TABLE_OUTPUT
    
72. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
73. -----------------------------------------------------------------------------------------------------------------------
    
74. 
    
75. Predicate Information (identified by operation id):
    
76. ---------------------------------------------------
    
77. 
    
78.    3 - filter("QUANTITY_SOLD">40)
    
79. 
    
80. Note
    
81. -----
    
82.    - SQL plan baseline "SQL_PLAN_6zsnd8f6zsd9g11df68d0" used for this statement
    
83. 
    
84. 21 rows selected.
    

复制代码

不固定：

1. declare
   
2.   v_1  number;
   
3. begin
   
4.    v_1 :=dbms_spm.alter_sql_plan_baseline(sql_handle=>'SYS_SQL_6fe28d438dfc352f',
   
5.                                                   plan_name=>'SQL_PLAN_6zsnd8f6zsd9g11df68d0',
   
6.                                                   attribute_name => 'FIXED',
   
7.                                                   attribute_value => 'NO');
   
8.      dbms_output.put_line(v_1);
   
9.   end;

复制代码

1. SQL> @exquery1.sql
   
2. SQL> set echo on
   
3. SQL> explain plan for
   
4.   2  select /*LOAD_AUTO*/ * from sh.sales where quantity_sold > 40 order by prod_id;
   
5. 
   
6. Explained.
   
7. 
   
8. SQL>
   
9. SQL> @?/rdbms/admin/utlxplp.sql
   
10. SQL> Rem
    
11. SQL> Rem $Header: utlxplp.sql 23-jan-2002.08:55:23 bdagevil Exp $
    
12. SQL> Rem
    
13. SQL> Rem utlxplp.sql
    
14. SQL> Rem
    
15. SQL> Rem Copyright (c) 1998, 2002, Oracle Corporation.        All rights reserved.
    
16. SQL> Rem
    
17. SQL> Rem    NAME
    
18. SQL> Rem         utlxplp.sql - UTiLity eXPLain Parallel plans
    
19. SQL> Rem
    
20. SQL> Rem    DESCRIPTION
    
21. SQL> Rem         script utility to display the explain plan of the last explain plan
    
22. SQL> Rem         command. Display also Parallel Query information if the plan happens to
    
23. SQL> Rem         run parallel
    
24. SQL> Rem
    
25. SQL> Rem    NOTES
    
26. SQL> Rem         Assume that the table PLAN_TABLE has been created. The script
    
27. SQL> Rem         utlxplan.sql should be used to create that table
    
28. SQL> Rem
    
29. SQL> Rem         With SQL*plus, it is recomended to set linesize and pagesize before
    
30. SQL> Rem         running this script. For example:
    
31. SQL> Rem         set linesize 130
    
32. SQL> Rem         set pagesize 0
    
33. SQL> Rem
    
34. SQL> Rem    MODIFIED   (MM/DD/YY)
    
35. SQL> Rem    bdagevil        01/23/02 - rewrite with new dbms_xplan package
    
36. SQL> Rem    bdagevil        04/05/01 - include CPU cost
    
37. SQL> Rem    bdagevil        02/27/01 - increase Name column
    
38. SQL> Rem    jihuang        06/14/00 - change order by to order siblings by.
    
39. SQL> Rem    jihuang        05/10/00 - include plan info for recursive SQL in LE row source
    
40. SQL> Rem    bdagevil        01/05/00 - make deterministic with order-by
    
41. SQL> Rem    bdagevil        05/07/98 - Explain plan script for parallel plans
    
42. SQL> Rem    bdagevil        05/07/98 - Created
    
43. SQL> Rem
    
44. SQL>
    
45. SQL> set markup html preformat on
    
46. SQL>
    
47. SQL> Rem
    
48. SQL> Rem Use the display table function from the dbms_xplan package to display the last
    
49. SQL> Rem explain plan. Use default mode which will display only relevant information
    
50. SQL> Rem
    
51. SQL> select * from table(dbms_xplan.display());
    
52. 
    
53. PLAN_TABLE_OUTPUT
    
54. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
55. Plan hash value: 3803407550
    
56. 
    
57. ----------------------------------------------------------------------------------------------
    
58. | Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    
59. ----------------------------------------------------------------------------------------------
    
60. |   0 | SELECT STATEMENT     |             |           1 |          29 |         491   (3)| 00:00:06 |             |             |
    
61. |   1 |  SORT ORDER BY             |             |           1 |          29 |         491   (3)| 00:00:06 |             |             |
    
62. |   2 |   PARTITION RANGE ALL|             |           1 |          29 |         490   (2)| 00:00:06 |           1 |          28 |
    
63. |*  3 |    TABLE ACCESS FULL | SALES |           1 |          29 |         490   (2)| 00:00:06 |           1 |          28 |
    
64. ----------------------------------------------------------------------------------------------
    
65. 
    
66. 
    
67. PLAN_TABLE_OUTPUT
    
68. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
69. Predicate Information (identified by operation id):
    
70. ---------------------------------------------------
    
71. 
    
72.    3 - filter("QUANTITY_SOLD">40)
    
73. 
    
74. Note
    
75. -----
    
76.    - SQL plan baseline "SQL_PLAN_6zsnd8f6zsd9g54bc8843" used for this statement
    
77. 
    
78. 19 rows selected.
    
79. 
    
80. SQL>
    

复制代码

---

1. declare
   
2.   v_1  number;
   
3. begin
   
4.    v_1 :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_6fe28d438dfc352f'
   
5.                                                  );
   
6.      dbms_output.put_line(v_1);
   
7.   end;

复制代码

1. select s.SQL_FULLTEXT, s.SQL_ID, s.CHILD_NUMBER from v_$sql s
   
2. where sql_fulltext like '%LOAD_STS%'
   
3.   and sql_fulltext not like '%v$sql%'  and sql_fulltext not like 'explain%';

复制代码

Top SQL产生的隐式SQL调优集：


调整AUTO SQLTUNING在一个维护性窗口里的最长运行时间：

1. DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK', 'TIME_LIMIT', 7200);
   

复制代码

自动接受SQL PROFILE：

1. begin
   
2.    DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK',
   
3.    'ACCEPT_SQL_PROFILES', 'TRUE');
   
4.    end;

复制代码

不等窗口，直接执行SQL AUTO TUNE：

1. declare
   
2. v_1 varchar2(200);
   
3. begin
   
4.    v_1:=dbms_sqltune.execute_tuning_task('SYS_AUTO_SQL_TUNING_TASK');
   
5. end;

复制代码

SQL PROFILE绿色的钩：


IMPLEMENT SQL PROFILE以后执行时就不会理睬/*+ FULL(s)  FULL(c) */







[oracle@station17 AST]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sun Oct 22 15:56:18 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> var a  clob ;
SQL> exec :a := dbms_sqltune.REPORT_TUNING_TASK('SQL_TUNING_1508657565131');

PL/SQL procedure successfully completed.

SQL> set long 1000000
SQL> print :a

A
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------
---------------------------
Tuning Task Name          : SQL_TUNING_15086
57565131
Tuning Task Owner          : SYS
Workload Type              : SQL Tuning Set
Scope                  : COMP
REHENSIVE
Global Time Limit(seconds)      : 1800
Per-SQL Time Limit(seconds)      : 300

A
--------------------------------------------------------------------------------
Completion Status          : COMPLETED
Started at              : 10/22/2
017 15:32:55
Completed at              : 10/22/2017
15:32:57
SQL Tuning Set (STS) Name      : XX1
SQL Tuning Set Owner          : AST
Number of Statements in the STS   : 1

----------------------------------------------------
---------------------------

A
--------------------------------------------------------------------------------
SUMMARY SECTION
--------------------------------------------------------
-----------------------
              Global SQL Tuning Result Statistic
s
----------------------------------------------------------
---------------------
Number of SQLs Analyzed              : 1
Number of SQLs in the Report             : 1
Number of SQLs with Findings
     : 1

A
--------------------------------------------------------------------------------
Number of SQLs with Alternative Plan Findings: 1
Number of SQLs with SQL profiles implement
ed : 1
Number of SQLs with Index Findings         : 1

-------------------------------------------
------------------------------------
    SQLs with Findings Ordered by Maximum (
Profile/Index) Benefit, Object ID
----------------------------------------------
---------------------------------

A
--------------------------------------------------------------------------------
object ID  SQL ID     statistics profile(be
nefit) index(benefit) restructure
---------- ------------- ---------- ----------
------ -------------- -----------
     2 b6xtxjp4dkvdp
98.62%           90.98%

---------------------------------------------
----------------------------------
Tables with New Potential Indices (ordered b
y schema, number of times, table)

A
--------------------------------------------------------------------------------
----------------------------------------------
---------------------------------
Schema Name            Table Name
      Index Name     Nb Time
--------------------------- ------------------
--------- -------------- --------
             SH SALES
      IDX$$_00380001    1

---------------------------------------------
----------------------------------

A
--------------------------------------------------------------------------------
DETAILS SECTION
-------------------------------------------------
------------------------------
Statements with Results Ordered by Maximum (Prof
ile/Index) Benefit, Object ID
--------------------------------------------------
-----------------------------
Object ID  : 2
Schema Name: AST
SQL ID       : b6xtxjp4dkvdp
SQL Text   : /* SQL Analyze(192,0) */ select /*+ US

A
--------------------------------------------------------------------------------
E_NL(s c) FULL(s) FULL(c)
         AST */ c.cust_id, sum(s.quantity_sold) fr
om sh.sales s,
         sh.customers c where s.cust_id =
c.cust_id and c.cust_id < 2
         group by c.cust_id

----------------------------------------------------------
---------------------
FINDINGS SECTION (3 findings)
------------------------------------------------

A
--------------------------------------------------------------------------------
-------------------------------

1- SQL Profile Finding (see explain plans secti
on below)
--------------------------------------------------
------
  A potentially better execution plan was found for t
his statement.

  Recommendation (estimated benefit: 98.62%)


A
--------------------------------------------------------------------------------
  ------------------------------------------
  - A manually-created SQL profile is present on the s
ystem.
    Name:   SYS_SQLPROF_015f42fffff70005
    Status: ENABLED

  Validation results
  ------------------
  The SQL profile was tested by executing both it
s plan and the original plan
  and measuring their respective execution statisti

A
--------------------------------------------------------------------------------
cs. A plan may have been
  only partially executed if the other could be run to
completion in less time.

               Original Plan  With SQL Pro
file  % Improved
               -------------  -
---------------  ----------
  Completion Status:        COMPLETE      CO
MPLETE
  Elapsed Time(us):         102245

A
--------------------------------------------------------------------------------
  63      99.93 %
  CPU Time(us):          102317
          0        100 %
  User I/O Time(us):              0
     0
  Buffer Gets:               2824
   39       98.61 %
  Physical Read Requests:          0
           0
  Physical Write Requests:          0
          0

A
--------------------------------------------------------------------------------
  Physical Read Bytes:              0
         0
  Physical Write Bytes:           0
        0
  Rows Processed:              0
       0
  Fetches:                  0
      0
  Executions:                  1
     1


A
--------------------------------------------------------------------------------
  Notes
  -----
  1. The original plan was first executed to warm t
he buffer cache.
  2. Statistics for original plan were aver
aged over next 8 executions.
  3. The SQL profile plan was first executed to war
m the buffer cache.
  4. Statistics for the SQL profile plan
were averaged over next 9 executions.


A
--------------------------------------------------------------------------------
2- Index Finding (see explain plans sect
ion below)
-------------------------------------------------
-
  The execution plan of this statement can be improved by
creating one or more
  indices.

  Recommendation (estimated benefit: 90.98%)
  ----------------------------------------
--

A
--------------------------------------------------------------------------------
  - Consider running the Access Advisor to improve the ph
ysical schema design
    or creating the recommended index.
    create index SH.IDX$$_00380001 on SH
.SALES("CUST_ID");

  Rationale
  ---------
    Creating the recommended indices significantly impro
ves the execution plan
    of this statement. However, it might be preferable to

A
--------------------------------------------------------------------------------
run "Access Advisor"
    using a representative SQL workload as opposed to a si
ngle statement. This
    will allow to get comprehensive index recommendations w
hich takes into
    account index maintenance overhead and a
dditional space consumption.

3- Alternative Plan Finding
---------------------------
  Some alternative execution plans for this statement

A
--------------------------------------------------------------------------------
were found by searching
  the system's real-time and historical performance data
.

  The following table lists these plans ranked by their a
verage elapsed time.
  See section "ALTERNATIVE PLANS SECTION" for detailed info
rmation on each
  plan.

  id plan hash    last seen         elapsed (s)  origi

A
--------------------------------------------------------------------------------
n       note
  -- ---------- -------------------- ------------ --
------------- ----------------
   1 1361934538  2017-10-22/11:59:46        0.000
Cursor Cache
   2 3070788227  2017-10-22/11:59:46        0.
000 Cursor Cache
   3 4005616876  2017-10-22/11:59:44
0.120 Cursor Cache    original plan

  Recommendation

A
--------------------------------------------------------------------------------
  --------------
  - Consider creating a SQL plan baseline for
the plan with the best average
    elapsed time.
    execute dbms_sqltune.create_sql_plan_baseline(
task_name =>
        'SQL_TUNING_1508657565131', object_
id => 2, owner_name => 'SYS',
        plan_hash_value => 1361934538);

---------------------------------------------

A
--------------------------------------------------------------------------------
----------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------
------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 4005616876

---------------------------------------------------
----------------------------------------

A
--------------------------------------------------------------------------------
--------
| Id  | Operation          | Name      | Rows  |
Bytes | Cost (%CPU)| Time     | Pstart|
Pstop |
---------------------------------------------------
----------------------------------------
--------
|   0 | SELECT STATEMENT      |       |    1 |
    13 |   893     (1)| 00:00:11 |       |
       |
|   1 |  HASH GROUP BY          |       |    1 |

A
--------------------------------------------------------------------------------
    13 |   893     (1)| 00:00:11 |       |
       |
|   2 |   NESTED LOOPS          |       |    1 |
    13 |   892     (1)| 00:00:11 |       |
       |
|*  3 |    TABLE ACCESS FULL  | CUSTOMERS |    1 |
     5 |   405     (1)| 00:00:05 |       |
       |
|   4 |    PARTITION RANGE ALL|       |    1 |
     8 |   488     (2)| 00:00:06 |     1 |
    28 |

A
--------------------------------------------------------------------------------
|*  5 |     TABLE ACCESS FULL | SALES      |    1 |
     8 |   488     (2)| 00:00:06 |     1 |
    28 |
---------------------------------------------------
----------------------------------------
--------

Predicate Information (identified by operation id
):
---------------------------------------------------


A
--------------------------------------------------------------------------------
   3 - filter("C"."CUST_ID"<2)
   5 - filter("S"."CUST_ID"<2 AND "S"."CUST_ID"="C".
"CUST_ID")

2- Using SQL Profile
--------------------
Plan hash value: 3070788227

---------------------------------------------------------
----------------------------------------
----------------------

A
--------------------------------------------------------------------------------
| Id  | Operation                 | Name
     | Rows  | Bytes | Cost (%CPU)| Time
     | Pstart| Pstop |
---------------------------------------------------------
----------------------------------------
----------------------
|   0 | SELECT STATEMENT             |
     |       1 |      13 |      55   (2)| 00:0
0:01 |         |         |
|   1 |  HASH GROUP BY                 |
     |       1 |      13 |      55   (2)| 00:0

A
--------------------------------------------------------------------------------
0:01 |         |         |
|   2 |   NESTED LOOPS                 |
     |       1 |      13 |      54   (0)| 00:0
0:01 |         |         |
|   3 |    PARTITION RANGE ALL             |
     |       1 |       8 |      54   (0)| 00:0
0:01 |       1 |      28 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| SALES
     |       1 |       8 |      54   (0)| 00:0
0:01 |       1 |      28 |
|   5 |      BITMAP CONVERSION TO ROWIDS     |

A
--------------------------------------------------------------------------------
     |         |         |          |
     |         |         |
|*  6 |       BITMAP INDEX RANGE SCAN         | SALES_CUST
_BIX |         |         |          |
     |       1 |      28 |
|*  7 |    INDEX UNIQUE SCAN             | CUSTOMERS_
PK   |       1 |       5 |       0   (0)| 00:0
0:01 |         |         |
---------------------------------------------------------
----------------------------------------
----------------------

A
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
-------------------------------------------
--------

   6 - access("S"."CUST_ID"<2)
       filter("S"."CUST_ID"<2)
   7 - access("S"."CUST_ID"="C"."CUST_ID")
       filter("C"."CUST_ID"<2)

3- Using New Indices

A
--------------------------------------------------------------------------------
--------------------
Plan hash value: 1966027323

-----------------------------------------
----------------------------------------
--------------------------------------
| Id  | Operation
    | Name         | Rows  | Bytes | C
ost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------
----------------------------------------

A
--------------------------------------------------------------------------------
--------------------------------------
|   0 | SELECT STATEMENT
    |             |       1 |      13 |
   5   (0)| 00:00:01 |         |         |
|   1 |  SORT GROUP BY NOSORT
    |             |       1 |      13 |
   5   (0)| 00:00:01 |         |         |
|   2 |   NESTED LOOPS
    |             |         |         |
      |         |         |         |
|   3 |    NESTED LOOPS

A
--------------------------------------------------------------------------------
    |             |       1 |      13 |
   5   (0)| 00:00:01 |         |         |
|*  4 |     INDEX RANGE SCAN
    | CUSTOMERS_PK   |       1 |       5 |
   2   (0)| 00:00:01 |         |         |
|*  5 |     INDEX RANGE SCAN
    | IDX$$_00380001 |       1 |         |
   2   (0)| 00:00:01 |         |         |
|   6 |    TABLE ACCESS BY GLOBAL INDEX R
OWID| SALES         |       1 |       8 |
   3   (0)| 00:00:01 | ROWID | ROWID |

A
--------------------------------------------------------------------------------
-----------------------------------------
----------------------------------------
--------------------------------------

Predicate Information (identified by operation id):
-----------------------------------------------
----

   4 - access("C"."CUST_ID"<2)
   5 - access("S"."CUST_ID"="C"."CUST_ID")


A
--------------------------------------------------------------------------------
       filter("S"."CUST_ID"<2)

-----------------------------------------------
--------------------------------
ALTERNATIVE PLANS SECTION
-----------------------------------------
--------------------------------------

Plan 3
------


A
--------------------------------------------------------------------------------
  Plan Origin              :Cursor Cache

  Plan Hash Value          :4005616876

  Executions              :9

  Elapsed Time              :0.120 sec

  CPU Time              :0.120 sec

  Buffer Gets              :3177

A
--------------------------------------------------------------------------------

  Disk Reads              :0

  Disk Writes              :0


Notes:
  1. Statistics shown are averaged over multiple execution
s.
  2. The plan matches the original plan.


A
--------------------------------------------------------------------------------
------------------------------------------------------
----------------------------------------
-----
| Id  | Operation          | Name      | Rows  | By
tes | Cost (%CPU)| Time     | Pstart| Ps
top |
------------------------------------------------------
----------------------------------------
-----
|   0 | SELECT STATEMENT      |       |    1 |
13 |    893   (1)| 00:00:11 |        |

A
--------------------------------------------------------------------------------
    |
|   1 |  HASH GROUP BY          |       |    1 |
13 |    893   (1)| 00:00:11 |        |
    |
|   2 |   NESTED LOOPS          |       |    1 |
13 |    892   (1)| 00:00:11 |        |
    |
|*  3 |    TABLE ACCESS FULL  | CUSTOMERS |    1 |
  5 |    405   (1)| 00:00:05 |        |
    |
|   4 |    PARTITION RANGE ALL|       |    1 |

A
--------------------------------------------------------------------------------
  8 |    488   (2)| 00:00:06 |      1 |
28 |
|*  5 |     TABLE ACCESS FULL | SALES      |    1 |
  8 |    488   (2)| 00:00:06 |      1 |
28 |
------------------------------------------------------
----------------------------------------
-----

Predicate Information (identified by operation id):
----------------------------------------

A
--------------------------------------------------------------------------------
-----------

   3 - filter("C"."CUST_ID"<2)
   5 - filter("S"."CUST_ID"<2 AND "S"."CUST_ID"="C"."CU
ST_ID")

Plan 1
------

  Plan Origin              :Cursor Cache


A
--------------------------------------------------------------------------------
  Plan Hash Value          :1361934538

  Executions              :10

  Elapsed Time              :0.000 sec

  CPU Time              :0.000 sec

  Buffer Gets              :44

  Disk Reads              :0

A
--------------------------------------------------------------------------------

  Disk Writes              :0


Notes:
  1. Statistics shown are averaged over multiple
executions.

----------------------------------------------
----------------------------------------
---------------------------------

A
--------------------------------------------------------------------------------
| Id  | Operation                 |
Name        | Rows    | Bytes | Cost (
%CPU)| Time    | Pstart| Pstop |
----------------------------------------------
----------------------------------------
---------------------------------
|   0 | SELECT STATEMENT             |
        |     1 |    13 |    56
  (4)| 00:00:01 |    |    |
|   1 |  SORT GROUP BY                 |
        |     1 |    13 |    56

A
--------------------------------------------------------------------------------
  (4)| 00:00:01 |    |    |
|   2 |   NESTED LOOPS                 |
        |     1 |    13 |    55
  (2)| 00:00:01 |    |    |
|   3 |    PARTITION RANGE ALL             |
        |   130 |  1040 |    54
  (0)| 00:00:01 |     1 |    28 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID|
SALES        |   130 |  1040 |    54
  (0)| 00:00:01 |     1 |    28 |
|   5 |      BITMAP CONVERSION TO ROWIDS     |

A
--------------------------------------------------------------------------------
        |    |    |
     |        |    |    |
|*  6 |       BITMAP INDEX RANGE SCAN         |
SALES_CUST_BIX |    |    |
     |        |     1 |    28 |
|*  7 |    INDEX UNIQUE SCAN             |
CUSTOMERS_PK    |     1 |     5 |     0
  (0)| 00:00:01 |    |    |
----------------------------------------------
----------------------------------------
---------------------------------

A
--------------------------------------------------------------------------------

Predicate Information (identified by operati
on id):
---------------------------------------------------

   6 - access("S"."CUST_ID"<2)
       filter("S"."CUST_ID"<2)
   7 - access("S"."CUST_ID"="C"."CUST_ID")
       filter("C"."CUST_ID"<2)

Plan 2

A
--------------------------------------------------------------------------------
------

  Plan Origin              :Cursor Cache

  Plan Hash Value          :3070788227

  Executions              :10

  Elapsed Time              :0.000 sec

  CPU Time              :0.000 sec

A
--------------------------------------------------------------------------------

  Buffer Gets              :44

  Disk Reads              :0

  Disk Writes              :0


Notes:
  1. Statistics shown are averaged over multiple executions
.

A
--------------------------------------------------------------------------------

--------------------------------------------------------
----------------------------------------
-----------------------
| Id  | Operation                 | Name
      | Rows  | Bytes | Cost (%CPU)| Tim
e     | Pstart| Pstop |
--------------------------------------------------------
----------------------------------------
-----------------------
|   0 | SELECT STATEMENT             |

A
--------------------------------------------------------------------------------
      |     1 |    13 |    55    (2)| 00:
00:01 |       |       |
|   1 |  HASH GROUP BY                 |
      |     1 |    13 |    55    (2)| 00:
00:01 |       |       |
|   2 |   NESTED LOOPS                 |
      |     1 |    13 |    54    (0)| 00:
00:01 |       |       |
|   3 |    PARTITION RANGE ALL             |
      |   130 |  1040 |    54    (0)| 00:
00:01 |     1 |    28 |

A
--------------------------------------------------------------------------------
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| SALES
      |   130 |  1040 |    54    (0)| 00:
00:01 |     1 |    28 |
|   5 |      BITMAP CONVERSION TO ROWIDS     |
      |       |       |        |
      |       |       |
|*  6 |       BITMAP INDEX RANGE SCAN         | SALES_CUS
T_BIX |       |       |        |
      |     1 |    28 |
|*  7 |    INDEX UNIQUE SCAN             | CUSTOMERS
_PK   |     1 |     5 |     0    (0)| 00:

A
--------------------------------------------------------------------------------
00:01 |       |       |
--------------------------------------------------------
----------------------------------------
-----------------------

Predicate Information (identified by operation id):
------------------------------------------
---------

   6 - access("S"."CUST_ID"<2)
       filter("S"."CUST_ID"<2)

A
--------------------------------------------------------------------------------
   7 - access("S"."CUST_ID"="C"."CUST_ID")
       filter("C"."CUST_ID"<2)

---------------------------------------------------
----------------------------



SQL>