SQLP
[SQLP] 옵티마이저 연산
songsua
2024. 8. 4. 19:33
옵티마이저 연산
1) Result cache operator
SGA에서 sql 명령문의 결과를 저장해 놓는 공간이다
Result cache operator 에서 정보를 저장하는 방법은 두가지 있다.
- resault_cache_Hint
DBMS_RESAULT_CACHE_HINT 패키지에는 통계정 보들이 포함되어 있다.
V$RESAULT_CACHE_OBJECT VIEW 는 result_cache 를 가지고 있다.
- no resault_cache hint
#RESULT CAHCE 안에 결과값 저장하기
SELECT /*+RESULT_CACHE*/ DEPARTMENT_ID, AVG(DEPARTMENT_ID) FROM DEPARTMENTS GROUP BY DEPARTMENT_ID ORDER BY DEPARTMENT_ID;
##CACHE안에 제대로 들어가 있는지 확인하는 방법
SELECT * FROM V$RESULT_CACHE_OBJECTS;
15 Dependency Published 4065 3576512481 SYS.USER$ 24/08/04
4 Dependency Published 3087 2965806095 SYS.SCHEDULER$_LIGHTWEIGHT_JOB 24/08/04
0 Dependency Published 2867 43875123 SYS.SCHEDULER$_PROGRAM 24/08/04
7 Dependency Published 2661 280889957 SYS.SCHEDULER$_CLASS 24/08/04
16 Dependency Published 2377 2940500297 SYS.WRI$_SQLSET_DEFINITIONS 24/08/04
17 Dependency Published 2356 3233122612 SYS.WRI$_SQLSET_STATEMENTS 24/08/04
27 Dependency Published 1832 1338505000 HR.DEPARTMENTS 24/08/04
24 Dependency Published 1524 2572772852 MDSYS.SDO_FEATURE_USAGE 24/08/04
12 Dependency Published 1523 1724483059 SYS.REG$ 24/08/04
5 Dependency Published 1503 3170112991 SYS.SCHEDULER$_COMB_LW_JOB 24/08/04
13 Dependency Published 1270 2774877430 SYS.DBA_SUBSCR_REGISTRATIONS 24/08/04
2 Dependency Published 1257 4154062057 SYS.SCHEDULER$_JOB 24/08/04
22 Dependency Published 1210 3122439354 SYS.DBA_SQLSET_STATEMENTS 24/08/04
18 Dependency Published 1113 3524568153 SYS.WRI$_SQLSET_PLANS 24/08/04
20 Dependency Published 637 373387901 SYS.WRI$_SQLSET_MASK 24/08/04
21 Dependency Published 437 429576629 SYS.WRH$_SQLTEXT 24/08/04
19 Dependency Published 388 1890652548 SYS.WRI$_SQLSET_STATISTICS 24/08/04
11 Result Published 3774 1577176766 SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) */ "AFFINITY" FROM "SYS"."SCHEDULER$_CLASS" "K" WHERE LOWER("K"."AFFI CDB 24/08/04
25 Result Published 3131 1367239739 select /*+ FIRST_ROWS(1) RESULT_CACHE */ count(*) from mdsys.sdo_feature_usage where used = 'Y' and rownum = 1 SQL 24/08/04
1 Result Published 2536 2544495080 SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) */ "OBJ#","FLAGS" FROM "SYS"."SCHEDULER$_PROGRAM" "K" WHERE 1=1 CDB 24/08/04
8 Result Published 1290 2395256074 SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) */ "AFFINITY" FROM "SYS"."SCHEDULER$_CLASS" "K" WHERE LOWER("K"."AFFI CDB 24/08/04
28 Result Published 808 1388806952 SELECT /*+RESULT_CACHE*/ DEPARTMENT_ID, AVG(DEPARTMENT_ID) FROM DEPARTMENTS GROUP BY DEPARTMENT_ID ORDER BY DEPARTMENT_ID SQL 24/08/04
10 Result Published 572 1574326844 SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) */ "AFFINITY" FROM "SYS"."SCHEDULER$_CLASS" "K" WHERE LOWER("K"."AFFI CDB 24/08/04
9 Result Published 436 1124610484 SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) */ "AFFINITY" FROM "SYS"."SCHEDULER$_CLASS" "K" WHERE LOWER("K"."AFFI CDB 24/08/04
3 Result Invalid 2453 1332935061 SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) */ "OBJ#","PROGRAM_OID","FLAGS","JOB_STATUS","LAST_START_DATE","RETRY CDB 24/08/04
23 Result Invalid 3210 1652657290 SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ "CON_DBID","SQL_ID","PLAN_HASH_VALUE","ELAPSED_TIME", CDB 24/08/04
26 Result Invalid 2453 1332935061 SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) */ "OBJ#","PROGRAM_OID","FLAGS","JOB_STATUS","LAST_START_DATE","RETRY CDB 24/08/04
6 Result Invalid 3995 257417115 SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ "OBJ#","PROGRAM_OID","FLAGS","JOB_STATUS","LAST_START CDB 24/08/04
14 Result Invalid 3835 1630224123 SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ "TIMEOUT" FROM "SYS"."DBA_SUBSCR_REGISTRATIONS" "K" W CDB 24/08/04
2) Inlist 연산 ★★★★
in 연산자를 사용하면 선택도에 따라서 옵티마이저가 다른 sql 명령문으로 판단하고 연산을 진행한다.
- 선택도가 닞은 경우: 인덱스로 각각 값을 찾고 모든 결과값을 union all 한다.
ex) deptno in (10) 인 경우 인덱스를 사용한다.
- 선택도가 높은 경우: full table scan 을 사용하여 결과값을 찾아낸다.
ex) deptno in (10,20,30) 인 경우 한꺼번에 값을 읽기 때문에 full scan
##선택도가 낮은 경우
select * from employees where employee_id in (100,110,146);
100 Steven King SKING 515.123.4567 03/06/17 AD_PRES 24000 90
110 John Chen JCHEN 515.124.4269 05/09/28 FI_ACCOUNT 8200 108 100
146 Karen Partners KPARTNER 011.44.1344.467268 05/01/05 SA_MAN 13500 0.3 100 80
##선택도가 낮은 경우로서, employee_id 값이 100,110,146 인 경우의 결과값을 모두 합쳐서 출력하게 된다.
##선택도가 높은 경우
selct * from employees where employee_id in (select employee_id from employees where mod((employee_id),2)=1);
##employee의 행에서 홀수 값만 출력한다
199 Douglas Grant DGRANT 650.507.9844 08/01/13 SH_CLERK 2600 124 50
201 Michael Hartstein MHARTSTE 515.123.5555 04/02/17 MK_MAN 13000 100 20
203 Susan Mavris SMAVRIS 515.123.7777 02/06/07 HR_REP 6500 101 40
205 Shelley Higgins SHIGGINS 515.123.8080 02/06/07 AC_MGR 12008 101 110
101 Neena Kochhar NKOCHHAR 515.123.4568 05/09/21 AD_VP 17000 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 06/01/03 IT_PROG 9000 102 60
105 David Austin DAUSTIN 590.423.4569 05/06/25 IT_PROG 4800 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 07/02/07 IT_PROG 4200 103 60
109 Daniel Faviet DFAVIET 515.124.4169 02/08/16 FI_ACCOUNT 9000 108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 05/09/30 FI_ACCOUNT 7700 108 100
113 Luis Popp LPOPP 515.124.4567 07/12/07 FI_ACCOUNT 6900 108 100
115 Alexander Khoo AKHOO 515.127.4562 03/05/18 PU_CLERK 3100 114 30
117 Sigal Tobias STOBIAS 515.127.4564 05/07/24 PU_CLERK 2800 114 30
119 Karen Colmenares KCOLMENA 515.127.4566 07/08/10 PU_CLERK 2500 114 30
121 Adam Fripp AFRIPP 650.123.2234 05/04/10 ST_MAN 8200 100 50
3) Count stop 연산
rownum 연산자를 사용하여 출력 행을 제한할 수 있다
fetch 함수를 사용할때 와 rownum 연산을 수행할 때, 어느 것이 성능이 우수한지 문제된다.
4) first row 연산
인덱스 설정된 컬럼은 인덱스에 따라 쉽게 최소 값, 최대 값을 구할 수 있다.
##최소값 구하기
select min(employee_id) from employees where employee_id < 140;
## sort aggregate:
sort aggregate가 나오는 이유는 MIN() 함수가 사용되었기 때문이다.
MIN() 함수는 조건을 만족하는 집합에서 최소 값을 찾기 위해 전체 데이터셋을 확인하기 위함이다.
데이터 필터링: where employee_id < 140 조건에 맞는 데이터를 필터링한다.
정렬 및 집계: MIN(employee_id) 값을 찾기 위해 선택된 데이터셋을 정렬하거나 데이터셋 전체를 검사해야 한다.