sua-tech
블로그 이미지

Home

Write

Setting

About Me
Dark
DBMS/Oracle

[Oracle] Tunning Advisor 사용해보기 (DBMS_SQLTUNE 패키지 사용하기 )

sua-tech
|2024. 7. 26. 15:44

기본 쿼리 

SELECT 
    d.DEPARTMENT_NAME AS "Department Name",
    l.CITY AS "City",
    l.COUNTRY_ID AS "Country",
    COUNT(e.EMPLOYEE_ID) AS "Employee Count",
    AVG(e.SALARY) AS "Average Salary"
FROM 
    EMPLOYEES e
JOIN 
    DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
JOIN 
    LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID
GROUP BY 
    d.DEPARTMENT_NAME, l.CITY, l.COUNTRY_ID
ORDER BY 
    d.DEPARTMENT_NAME;

 

각 부서(department)별로, 부서에서 일하는 직원(employee)의 평균 급여(average salary)와 직원 수(employee count)를 구하고, 부서의 위치(location) 정보도 포함시키는 쿼리.

 

 

통계수집

    
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER, TABNAME => 'EMPLOYEES', CASCADE => TRUE, ESTIMATE_PERCENT =>'10', DEGREE => '1', granularity => 'ALL');
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER, TABNAME => 'DEPARTMENTS', CASCADE => TRUE, ESTIMATE_PERCENT =>'10', DEGREE => '1', granularity => 'ALL');
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER, TABNAME => 'LOCATIONS', CASCADE => TRUE, ESTIMATE_PERCENT =>'10', DEGREE => '1', granularity => 'ALL');

 

 

SQL ID 찾기 

1. plan에서 확인 

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL ,'ALLSTATS LAST'));
    


SQL_ID  du3j7dqqa7gs3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */      d.DEPARTMENT_NAME AS 
"Department Name",     l.CITY AS "City",     l.COUNTRY_ID AS "Country", 
    COUNT(e.EMPLOYEE_ID) AS "Employee Count",     AVG(e.SALARY) AS 
"Average Salary" FROM      EMPLOYEES e JOIN      DEPARTMENTS d ON 
e.DEPARTMENT_ID = d.DEPARTMENT_ID JOIN      LOCATIONS l ON 
d.LOCATION_ID = l.LOCATION_ID GROUP BY      d.DEPARTMENT_NAME, l.CITY, 
l.COUNTRY_ID ORDER BY      d.DEPARTMENT_NAME
 
Plan hash value: 2049930105
 
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |     11 |00:00:00.01 |      14 |       |       |          |
|   1 |  SORT GROUP BY                 |             |      1 |    106 |     11 |00:00:00.01 |      14 |  2048 |  2048 | 2048  (0)|
|*  2 |   HASH JOIN                    |             |      1 |    106 |    106 |00:00:00.01 |      14 |  1265K|  1265K| 1442K (0)|
|   3 |    MERGE JOIN                  |             |      1 |     27 |     27 |00:00:00.01 |       8 |       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| LOCATIONS   |      1 |     23 |     19 |00:00:00.01 |       2 |       |       |          |
|   5 |      INDEX FULL SCAN           | LOC_ID_PK   |      1 |     23 |     19 |00:00:00.01 |       1 |       |       |          |
|*  6 |     SORT JOIN                  |             |     19 |     27 |     27 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   7 |      TABLE ACCESS FULL         | DEPARTMENTS |      1 |     27 |     27 |00:00:00.01 |       6 |       |       |          |
|   8 |    TABLE ACCESS FULL           | EMPLOYEES   |      1 |    107 |    107 |00:00:00.01 |       6 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   6 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
       filter("D"."LOCATION_ID"="L"."LOCATION_ID")

 

 

2. V$SQLAREA 에서 찾기 

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
col  SQL_FULLTEXT FORMAT a350
SET PAGESIZE 24



selecT SQL_ID , SQL_TEXT, SQL_FULLTEXT from V$SQLAREA WHERE SQL_FULLTEXT LIKE '%DEPARTMENT%' and  SQL_FULLTEXT like '%LOCATIONS%';
05:55:53 SUA@ORCL> selecT SQL_ID , SQL_TEXT, SQL_FULLTEXT from V$SQLAREA WHERE SQL_FULLTEXT LIKE '%DEPARTMENT%' and  SQL_FULLTEXT like '%LOCATIONS%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
cqk18gksp4zqa
SELECT	    d.DEPARTMENT_NAME AS "Department Name",	l.CITY AS "City",     l.COUNTRY_ID AS "Country",     COUNT(e.EMPLOYEE_ID) AS "Employee Count",	   AVG(e.SALARY) AS "Average Salary" FROM      E
MPLOYEES e JOIN      DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID JOIN	  LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID GROUP BY      d.DEPARTMENT_NAME, l.CITY, l.COUNTRY_ID ORDER BY	   d.DEP
ARTMENT_NAME
SELECT
    d.DEPARTMENT_NAME AS "Department Name",
    l.CITY AS "City",
    l.COUNTRY_ID AS "Country",
    COUNT(e.EMPLOYEE_ID) AS "Employee Count",
    AVG(e.SALARY) AS "Average Salary"
FROM
    EMPLOYEES e
JOIN
    DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
JOIN
    LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID
GROUP BY

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    d.DEPARTMENT_NAME, l.CITY, l.COUNTRY_ID
ORDER BY
    d.DEPARTMENT_NAME

du3j7dqqa7gs3
SELECT /*+ GATHER_PLAN_STATISTICS */	  d.DEPARTMENT_NAME AS "Department Name",     l.CITY AS "City",     l.COUNTRY_ID AS "Country",	   COUNT(e.EMPLOYEE_ID) AS "Employee Count",	 AVG(e.SALARY) A
S "Average Salary" FROM      EMPLOYEES e JOIN	   DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID JOIN	LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID GROUP BY	   d.DEPARTMENT_NAME, l.CITY, l.
COUNTRY_ID ORDER BY	 d.DEPARTMENT_NAME
SELECT /*+ GATHER_PLAN_STATISTICS */
    d.DEPARTMENT_NAME AS "Department Name",
    l.CITY AS "City",
    l.COUNTRY_ID AS "Country",
    COUNT(e.EMPLOYEE_ID) AS "Employee Count",
    AVG(e.SALARY) AS "Average Salary"
FROM
    EMPLOYEES e
JOIN

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
JOIN
    LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID
GROUP BY
    d.DEPARTMENT_NAME, l.CITY, l.COUNTRY_ID
ORDER BY
    d.DEPARTMENT_NAME

05gjnpwkt3zzt
selecT SQL_ID , SQL_TEXT, SQL_FULLTEXT from V$SQLAREA WHERE SQL_FULLTEXT LIKE '%DEPARTMENT%' and  SQL_FULLTEXT like '%LOCATIONS%'
selecT SQL_ID , SQL_TEXT, SQL_FULLTEXT from V$SQLAREA WHERE SQL_FULLTEXT LIKE '%DEPARTMENT%' and  SQL_FULLTEXT like '%LOCATIONS%'

 

 

 

Advisor 실행하기 

 

advisor log에 아무것도 없다.

SELECT OWNER, TASK_NAME, EXECUTION_START, EXECUTION_END, STATUS  FROM DBA_ADVISOR_LOG WHERE OWNER = 'SUA';



05:59:55 SUA@ORCL> SELECT OWNER, TASK_NAME, EXECUTION_START, EXECUTION_END, STATUS  FROM DBA_ADVISOR_LOG WHERE OWNER = 'SUA';

no rows selected

Elapsed: 00:00:00.00

 

 

Create Tuning Task 

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK (
                          sql_id      => 'du3j7dqqa7gs3',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 1800,
                          task_name   => 'du3j7dqqa7gs3_tuning_task',
                          description => 'Tuning task for statement du3j7dqqa7gs3.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

 

확인한 SQL ID 를 넣어준다

 

advisor log 에 남게 된다

06:03:14 SUA@ORCL> SELECT OWNER, TASK_NAME, EXECUTION_START, EXECUTION_END, STATUS  FROM DBA_ADVISOR_LOG WHERE OWNER = 'SUA';

OWNER
--------------------------------------------------------------------------------------------------------------------------------
TASK_NAME															 EXECUTION EXECUTION STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------- --------- -----------
SUA
du3j7dqqa7gs3_tuning_task													 NULL	   NULL      INITIAL

 

 

 Execute Tuning Task 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'du3j7dqqa7gs3_tuning_task');

06:04:41 SUA@ORCL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'du3j7dqqa7gs3_tuning_task');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.88

 

 

 

Tuning advisor Report 

06:05:29 SUA@ORCL> SELECT DBMS_SQLTUNE.report_tuning_task('du3j7dqqa7gs3_tuning_task') AS RECOMMENT FROM dual;

RECOMMENT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : du3j7dqqa7gs3_tuning_task
Tuning Task Owner  : SUA
Workload Type	   : Single SQL Statement
Scope		   : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at	   : 07/26/2024 06:04:42
Completed at	   : 07/26/2024 06:04:43

-------------------------------------------------------------------------------
Schema Name: SUA
SQL ID	   : du3j7dqqa7gs3
SQL Text   : SELECT /*+ GATHER_PLAN_STATISTICS */
		 d.DEPARTMENT_NAME AS "Department Name",
		 l.CITY AS "City",
		 l.COUNTRY_ID AS "Country",
		 COUNT(e.EMPLOYEE_ID) AS "Employee Count",
		 AVG(e.SALARY) AS "Average Salary"
	     FROM

RECOMMENT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
		 EMPLOYEES e
	     JOIN
		 DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
	     JOIN
		 LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID
	     GROUP BY
		 d.DEPARTMENT_NAME, l.CITY, l.COUNTRY_ID
	     ORDER BY
		 d.DEPARTMENT_NAME

-------------------------------------------------------------------------------
There are no recommendations to improve the statement.

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

추천 사항이 없다고 한다... 

 

 

advisor log 삭제하기

## 삭제 하기 
BEGIN
  DBMS_SQLTUNE.DROP_TUNING_TASK (task_name => 'du3j7dqqa7gs3_tuning_task');
END;
/

 

 

 

 

다시해본다 

 

기본쿼리

SELECT /*+ FULL(e) USE_NL(e d l) INDEX(e EMP_EMP_ID_PK) */
    d.DEPARTMENT_NAME AS "Department Name",
    l.CITY AS "City",
    l.COUNTRY_ID AS "Country",
    COUNT(e.EMPLOYEE_ID) AS "Employee Count",
    AVG(e.SALARY) AS "Average Salary"
FROM
    EMPLOYEES e
JOIN
    DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
JOIN
    LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID
WHERE
    e.SALARY > 0
GROUP BY
    d.DEPARTMENT_NAME, l.CITY, l.COUNTRY_ID
ORDER BY
    d.DEPARTMENT_NAME;

각 부서에 대한 직원 수와 평균 급여를 계산하여 반환

 

Department Name 	       City			      Co Employee Count Average Salary
------------------------------ ------------------------------ -- -------------- --------------
Accounting		       Seattle			      US	      2 	 10150
Administration		       Seattle			      US	      1 	  4400
Executive		       Seattle			      US	      3     19333.3333
Finance 		       Seattle			      US	      6 	  8600
Human Resources 	       London			      UK	      1 	  6500
IT			       Southlake		      US	      5 	  5760
Marketing		       Toronto			      CA	      2 	  9500
Public Relations	       Munich			      DE	      1 	 10000
Purchasing		       Seattle			      US	      6 	  4150
Sales			       Oxford			      UK	     34     8955.88235
Shipping		       South San Francisco	      US	     45     3475.55556

11 rows selected.

Elapsed: 00:00:00.01

optimizer 가 선택한 plan

 
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |     11 |00:00:00.01 |      14 |       |       |          |
|   1 |  SORT GROUP BY                 |             |      1 |    106 |     11 |00:00:00.01 |      14 |  2048 |  2048 | 2048  (0)|
|*  2 |   HASH JOIN                    |             |      1 |    106 |    106 |00:00:00.01 |      14 |  1265K|  1265K| 1302K (0)|
|   3 |    MERGE JOIN                  |             |      1 |     27 |     27 |00:00:00.01 |       8 |       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| LOCATIONS   |      1 |     23 |     19 |00:00:00.01 |       2 |       |       |          |
|   5 |      INDEX FULL SCAN           | LOC_ID_PK   |      1 |     23 |     19 |00:00:00.01 |       1 |       |       |          |
|*  6 |     SORT JOIN                  |             |     19 |     27 |     27 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   7 |      TABLE ACCESS FULL         | DEPARTMENTS |      1 |     27 |     27 |00:00:00.01 |       6 |       |       |          |
|*  8 |    TABLE ACCESS FULL           | EMPLOYEES   |      1 |    107 |    107 |00:00:00.01 |       6 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   6 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
       filter("D"."LOCATION_ID"="L"."LOCATION_ID")
   8 - filter("E"."SALARY">0)

억지로 hint를 주어 plan 변경

 
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |     11 |00:00:00.01 |      14 |       |       |          |
|   1 |  SORT GROUP BY                 |             |      1 |    106 |     11 |00:00:00.01 |      14 |  2048 |  2048 | 2048  (0)|
|*  2 |   HASH JOIN                    |             |      1 |    106 |    106 |00:00:00.01 |      14 |  1265K|  1265K| 1498K (0)|
|   3 |    MERGE JOIN                  |             |      1 |     27 |     27 |00:00:00.01 |       8 |       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| LOCATIONS   |      1 |     23 |     19 |00:00:00.01 |       2 |       |       |          |
|   5 |      INDEX FULL SCAN           | LOC_ID_PK   |      1 |     23 |     19 |00:00:00.01 |       1 |       |       |          |
|*  6 |     SORT JOIN                  |             |     19 |     27 |     27 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   7 |      TABLE ACCESS FULL         | DEPARTMENTS |      1 |     27 |     27 |00:00:00.01 |       6 |       |       |          |
|*  8 |    TABLE ACCESS FULL           | EMPLOYEES   |      1 |    107 |    107 |00:00:00.01 |       6 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   6 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
       filter("D"."LOCATION_ID"="L"."LOCATION_ID")
   8 - filter("E"."SALARY">0)

 

SQL_ID 찾기

selecT SQL_ID , SQL_TEXT, SQL_FULLTEXT from V$SQLAREA WHERE SQL_FULLTEXT LIKE '%FULL(e)%'; 



06:14:55 SUA@ORCL> selecT SQL_ID , SQL_TEXT, SQL_FULLTEXT from V$SQLAREA WHERE SQL_FULLTEXT LIKE '%FULL(e)%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0pggxvxszcdsh
selecT SQL_ID , SQL_TEXT, SQL_FULLTEXT from V$SQLAREA WHERE SQL_FULLTEXT LIKE '%FULL(e)%'
selecT SQL_ID , SQL_TEXT, SQL_FULLTEXT from V$SQLAREA WHERE SQL_FULLTEXT LIKE '%FULL(e)%'

c44t2qwp5j3vt
SELECT /*+ FULL(e) USE_NL(e d l) INDEX(e EMP_EMP_ID_PK) */     d.DEPARTMENT_NAME AS "Department Name",	   l.CITY AS "City",	 l.COUNTRY_ID AS "Country",	COUNT(e.EMPLOYEE_ID) AS "Employee Count"
,     AVG(e.SALARY) AS "Average Salary" FROM	 EMPLOYEES e JOIN     DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID JOIN	  LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID WHERE	 e.SALARY > 0 GR
OUP BY	   d.DEPARTMENT_NAME, l.CITY, l.COUNTRY_ID ORDER BY	d.DEPARTMENT_NAME
SELECT /*+ FULL(e) USE_NL(e d l) INDEX(e EMP_EMP_ID_PK) */
    d.DEPARTMENT_NAME AS "Department Name",
    l.CITY AS "City",
    l.COUNTRY_ID AS "Country",
    COUNT(e.EMPLOYEE_ID) AS "Employee Count",
    AVG(e.SALARY) AS "Average Salary"
FROM
    EMPLOYEES e
JOIN

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
JOIN
    LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID
WHERE
    e.SALARY > 0
GROUP BY
    d.DEPARTMENT_NAME, l.CITY, l.COUNTRY_ID
ORDER BY
    d.DEPARTMENT_NAME


Elapsed: 00:00:00.06

 

Tunning  작업 생성

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK (
                          sql_id      => 'c44t2qwp5j3vt',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 1800,
                          task_name   => 'c44t2qwp5j3vt_tuning_task',
                          description => 'Tuning task for statement c44t2qwp5j3vt.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

 

 Tunning 실행

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'c44t2qwp5j3vt_tuning_task');

 

Tunning  Report 확인

SELECT DBMS_SQLTUNE.report_tuning_task('c44t2qwp5j3vt_tuning_task') AS RECOMMENT FROM dual;
06:21:11 SUA@ORCL> SELECT DBMS_SQLTUNE.report_tuning_task('c44t2qwp5j3vt_tuning_task') AS RECOMMENT FROM dual;

RECOMMENT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : c44t2qwp5j3vt_tuning_task
Tuning Task Owner  : SUA
Workload Type	   : Single SQL Statement
Scope		   : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at	   : 07/26/2024 06:21:16
Completed at	   : 07/26/2024 06:21:16

-------------------------------------------------------------------------------
Schema Name: SUA
SQL ID	   : c44t2qwp5j3vt
SQL Text   : SELECT /*+ FULL(e) USE_NL(e d l) INDEX(e EMP_EMP_ID_PK) */
		 d.DEPARTMENT_NAME AS "Department Name",
		 l.CITY AS "City",
		 l.COUNTRY_ID AS "Country",
		 COUNT(e.EMPLOYEE_ID) AS "Employee Count",
		 AVG(e.SALARY) AS "Average Salary"
	     FROM

RECOMMENT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
		 EMPLOYEES e
	     JOIN
		 DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
	     JOIN
		 LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID
	     WHERE
		 e.SALARY > 0
	     GROUP BY
		 d.DEPARTMENT_NAME, l.CITY, l.COUNTRY_ID
	     ORDER BY
		 d.DEPARTMENT_NAME

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 12.49%)

RECOMMENT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
	    'c44t2qwp5j3vt_tuning_task', task_owner => 'SUA', replace =>
	    TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

			   Original Plan  With SQL Profile  % Improved
			   -------------  ----------------  ----------
  Completion Status:		COMPLETE	  COMPLETE
  Elapsed Time (s):		  .0002 	  .000158	  21 %
  CPU Time (s): 		.000176 	  .000158      10.22 %
  User I/O Time (s):		      0 		0
  Buffer Gets:			     16 	       14	12.5 %
  Physical Read Requests:	      0 		0
  Physical Write Requests:	      0 		0

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

--> 바꾸면 21% 빨라진다 

  Notes
  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.

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

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

----------------------------------------------------------------------------------------------------
| Id  | Operation			| Name		   | Rows  | Bytes | Cost (%CPU)| Time	   |

RECOMMENT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		|		   |   106 |  4664 |	14   (8)| 00:00:01 |
|   1 |  SORT GROUP BY			|		   |   106 |  4664 |	14   (8)| 00:00:01 |
|*  2 |   HASH JOIN			|		   |   106 |  4664 |	13   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL		| EMPLOYEES	   |   107 |   749 |	 3   (0)| 00:00:01 |
|   4 |    VIEW 			| VW_GBF_10	   |	27 |   999 |	10   (0)| 00:00:01 |
|   5 |     NESTED LOOPS		|		   |	27 |   918 |	10   (0)| 00:00:01 |
|   6 |      NESTED LOOPS		|		   |	27 |   918 |	10   (0)| 00:00:01 |
|   7 |       TABLE ACCESS FULL 	| LOCATIONS	   |	23 |   345 |	 3   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN		| DEPT_LOCATION_IX |	 1 |	   |	 0   (0)| 00:00:01 |
|   9 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENTS	   |	 1 |	19 |	 1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - access("E"."DEPARTMENT_ID"="ITEM_1")
   3 - filter("E"."SALARY">0)
   8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

Hint Report (identified by operation id / Query Block Name / Object Alias):

RECOMMENT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total hints for statement: 8 (U - Unused (8))
--> 8개의 힌트가 사용되지 않음 
---------------------------------------------------------------------------

   0 -	STATEMENT
	 U -  IGNORE_OPTIM_EMBEDDED_HINTS / hint overridden by another in parent query block
	 U -  OPTIMIZER_FEATURES_ENABLE(default) / hint overridden by another in parent query block

   3 -	SEL$01AC8E89 / E@SEL$1
	 U -  FULL(e) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
	 U -  INDEX(e EMP_EMP_ID_PK) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
	 U -  USE_NL(e d l) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS

   5 -	SEL$7A437C77
	 U -  ELIM_GROUPBY(@"SEL$A458B75D") / duplicate hint

   7 -	SEL$7A437C77 / L@SEL$2
	 U -  USE_NL(e d l) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS

   8 -	SEL$7A437C77 / D@SEL$1
	 U -  USE_NL(e d l) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS


RECOMMENT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2- Using SQL Profile
--------------------
Plan hash value: 2049930105

----------------------------------------------------------------------------------------------
| Id  | Operation		       | Name	     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	     |	 106 |	4346 |	  10  (20)| 00:00:01 |
|   1 |  SORT GROUP BY		       |	     |	 106 |	4346 |	  10  (20)| 00:00:01 |
|*  2 |   HASH JOIN		       |	     |	 106 |	4346 |	   9  (12)| 00:00:01 |
|   3 |    MERGE JOIN		       |	     |	  27 |	 918 |	   6  (17)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| LOCATIONS   |	  23 |	 345 |	   2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN	       | LOC_ID_PK   |	  23 |	     |	   1   (0)| 00:00:01 |
|*  6 |     SORT JOIN		       |	     |	  27 |	 513 |	   4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL	       | DEPARTMENTS |	  27 |	 513 |	   3   (0)| 00:00:01 |
|*  8 |    TABLE ACCESS FULL	       | EMPLOYEES   |	 107 |	 749 |	   3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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


RECOMMENT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   6 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
       filter("D"."LOCATION_ID"="L"."LOCATION_ID")
   8 - filter("E"."SALARY">0)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 5 (U - Unused (5))
---------------------------------------------------------------------------

   4 -	SEL$9E43CB6E / L@SEL$2
	 U -  USE_NL(e d l) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS

   7 -	SEL$9E43CB6E / D@SEL$1
	 U -  USE_NL(e d l) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS

   8 -	SEL$9E43CB6E / E@SEL$1
	 U -  FULL(e) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
	 U -  INDEX(e EMP_EMP_ID_PK) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
	 U -  USE_NL(e d l) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS

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

RECOMMENT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

추천해준 쿼리로 실행

execute dbms_sqltune.accept_sql_profile(task_name => 'c44t2qwp5j3vt_tuning_task',task_owner => 'SUA', replace => TRUE);

'DBMS > Oracle' 카테고리의 다른 글

[Oracle] Optimizer Mode 란?  (0) 2024.08.16
[Oracle] sample schema 및 insert 하는 법 (HR , OE , PM ,SH ERD 확인 및 샘플 파일 다운 / 기본 제공 스크립트 적용)  (0) 2024.07.24
[Oracle] ORA-01950: no privileges on tablespace 'USERS' -> quota 권한으로 해결  (0) 2024.07.19
[Oracle] dba_hist_osstat view 보는 법  (0) 2024.07.10
[Oracle] AWR Report 보는법 - Advisory Statistics 이 뭔지 알아본다  (0) 2024.06.09

티스토리툴바