SQL HINT 를 이용하여 SQL Tunning 을 하는 방법

―  cost-based approach를 선택한 경우에는 best throughput와 best reponse time사이에 하나를 선택

―  SQL문이 optimization approach 와 goal을 기술한 한개의 힌트를 가진다면, optimizer는 statistics의 존재여부와 ALTER SESSSION명령에 있는 OPTIMIZER_GOAL와 OPIMIZER_MODE 초기 파라메타의 값과는 상관없이 기술된 approach를 사용.

―  INDEX 힌트의문법은

table : scan될 index와관련있는 table의이름이나 alias를기술

index : index scan이수행될 index를기술

―  힌트는하나이상의 indexes들을기술
· 힌트가하나의사용가능한 index를기술합니다면, optimizer는 index에서한개의 scan을수행.
· optimizer는 full table scan이나 table에있는다른 index에대한 scan은수행하지않음.
· 힌트가사용가능한 index의리스트를기술합니다면, optimizer는리스트에있는각각의 index에대한 scan을하는데드는비용을고려한후에가장적은비용이드는 index scan을이행
· 이 access path가최저의비용을가진다면 optimizer는이리스트로부터여러 index를 scan하고그결과들을 merge.
· optimizer는 full table scan이나힌트에있지않은 index scan은고려하지않음.
· 힌트가어떤 index도기술하지않았다면, optimizer는 table에있는사용가능한 index를각각 scan한비용을고려한후에 lowest cost를가진 index scan을수행.
· 이 access path가최저비용을가진다면 optimizer는 muliple index를 scan하고그결과값을 merge.

SELECT name, height, weightFROM patientsWHERE sex=’M’·  sex의열은 index되어있고, 이칼럼은 ‘M’과 ‘F’의값을가짐.·  병원에남자의수와여자의수가동일하다면, 이질의는연관된 table의행의최다퍼센트를반환하고, full table scan이 index scan보다는더빠르게됩니다.·  병원의환자중남자의비율이매우적다면, 질의는관련된 table의행에대해적은비율을반환하고, 이경우에는 index scan이 full table scan보다더빠릅니다.
―  각 disinct column value의발생수는 optimizer에게별로유용하지않다(도움이되지않는다.)― cost-based approach는각각의값들이각각의행에서나타나는빈도수가동일하다고가정을합니다.
―  한칼럼이단 2개의다른값들(distinct values)을가진다면 optimizer는그두값들이각각 row의 50%로정도나타난다고가정합니다그래서 cost-based approcah는 index scan보다는 full table scan을선택하곤합니다.
―  WHERE절에있는값이모든 row에대해매우적은퍼센트를가진다면, 힌트에 index scan을사용하여 optimizer가강제로 index scan을사용하게할수있습니다.
―  아래문장에서 INDEX 힌트는 SEX_INDEX에대해 index scan을선택합니다.·

SELECT /*+ INDEX(patients sex_index) Use SEX_INDEX, since there are few male patients */name, height, weightFROM patientsWHERE sex = ‘M’;

SELECT /*+ index(emp job_index) */ empno, ename, salfrom empwhere job = ‘SALESMAN’;

·  OPTIMIZER_GOAL 은FIRST_ROWS로
―  예제 2analyze table emp estimate statistics;analyze table dept estimate statistics;alter session set sql_trace = true;alter session set optimizer_goal = first_rows;SELECT empno, ename, salfrom empwhere job = ‘SALESMAN’;

SELECT /*+ full(emp) */ empno, ename, salfrom empwhere job = ‘SALESMAN’;

SELECT /*+ index(emp job_index) */ empno, ename, salfrom empwhere job = ‘SALESMAN’;

―  INDEX_ASC 힌트의문법은

―  각파라메타는 INDEX 힌트에서와같은목적을의미.

―  range scan에대해서오라클의 default behavior는 index된값에대해오름차순으로정렬하고그 index entry들을 scan하는것이므로이힌트는일반적으로 index 힌트보다더나은점은없습니다.

―  만약 SQL 문이 index range scan을사용합니다면, 오라클은 index된값들을내림차순으로정렬한 index entry들을 scan.

―  INDEX_desc 힌트의문법은

―  각파라메타는 INDEX 힌트에서와같은목적을의미.
―  이힌트는 table보다 INDEX를더많이접근하므로 SQL문에영향을주지않음.
―  아래의 SQL문은 index된값에대해서항상오름차순으로정렬된 index range scan을수행

CREATE TABLE tank_readings (time DATE CONSTAINT un_time UNIQUE,temperature NUMBER);

―  table의각행들은한시점에서시간과온도를저장.
―  TIME칼럼에대해 UNIQUE 제약을주면 table이동일한시점에서한번만내용을읽도록합니다.
―  오라클은 TIME 칼럼에강제로 index를수행.
―  특별한 T시간에대해서읽은가장최근의온도를 SELECT 하는 complex query를생각해보자.
―  Subquery는온도를읽는시점 T나 T이전의가장최근시간의값을반환.
―  Parent query는그시간에대한온도를찾는다.

SELECT temperatureFROM tank_readingsWHERE time = (SELECT MAX(time)

              FROM tank_readingsWEHRE time <= TO_DATE(:t) );

―  위 SQL문에대한 execution plan은아래그림과같습니다.

―  위 SQL문장을실행시키면, 오라클은아래와같은 operations를실행.

· step 4와 3은 subquery를실행.

– step 4는시간 T와같거나더적은모든 TIME 값들을반환할 UN_TIME index의 range scan을수행.

– step 3는 step 4로부터최대 TIME값을선택하고그값을반환. ·step 2 와 1은 parent query를실행.

– step 2는 step 3에의해반환된 TIME 값에맞는 UN_TIME index의 unique scan을수행하고관련된 ROWID를반환.

– step 1은 step 2에의해반환된 ROWID를사용하여 TANK_READING table을접근하고 TEMPERATURE 값을반환.

― Step 4에서오라클은오름차순으로정렬된 index에있는 TIME 값을 scan.
―  오라클은첫번째 TIME값이 T보다더큰경우에 scaning을중지하고그후에 step 3에서 T값과같거나더적은모든값을반환.
―  INDEX_DESC 힌트를사용하면 index로부터단하나의 TIME값을읽어오는질의사용가능.

SELECT /*+ INDEX_DESC(tank_readings un_time) */ temperature FROM tank_readings WHERE time <= TO_DATE(:t) AND ROWNUM = 1;ORDER BY time DESC;

―  이것의 execution plan을아래그림과같습니다.

· step 3은 T와동일하거나더적은 TIME값을찾기위해 UN_TIME index를 range scan하고그와관련된 ROWID를반환.
· step 2는 step 3에의해반환된 ROWID값들로서 TANK_READING table에접근.
· step 1은 step 2로부터단하나의행을요구함으로 ROWNUM=1이라는조건을수행.

―  INDEX_DESC 힌트때문에 step 3은 T값부터시작하는내림차순으로정렬된 index에서 TIME값을 scan.
―  scan된첫번째 TIME값은 T이거나 T값보다는적은최대 TIME값입니다.
―  step 1은단하나의행을요구한후부터는 , step 3은첫번째 TIME값이후의 index entry에대해더이상 scan하지않는다.
―  default 행동이오름차순 index scan이므로 INDEX_DESC 힌트없이이질의를수행하면오라클은 table에서 T와같거나그보다적은최대시간을처음 scaning하는것보다최초의시간을 scaning함으로서시작하게됨. step1은최초의시간에대한온도를반환하게됩니다.
―  위질의에서좀더빨리이복합질의에서요구하는온도를반환하려면 INDEX_DESC 힌트를사용해야만합니다.

table : merge할 index와연관된 table의이름이나 alias를기술.

index : index scan을수행하는 index를기술― 최소 2개이상최대 5개이하의 index를기술해야합니다.

―  SQL문에서 ORDERED 힌트를생략하고 join를수행하면, optimizer가 table을 join할순서를선택

―  각 table에서 SELECT 해오는행의수에대해알고있다면 join 순서를기술하는 ORDERED 힌트를사용하는것이좋다.

―  사용자가 inner 와 outer table을선택하는것이 optimizer가할수있는것보다나을수도있습니다.

● SQL문이 table의 alias를사용합니다면힌트에서도 table의이름보다는 alias를사용해야만합니다.

● table의이름이나 alias는 local database에 table의 synonym이나 table을가지고있어야만합니다.

● USE_NL과 USE_MERGE 힌트는 ORDERED 힌트를사용해야만합니다.

● 오라클은참조된 table이조인에서강제로 innertable이될때이힌트를사용해야만합니다.

● 참조된 table이 outer table이라면이힌트들을무시합니다.

table : nested loops join의 inner table로서사용될 table의이름과 alias입니다.

―  예를들어서, ACCOUNTS와 CUSTOMERS table을조인하는 SQL문이있다고생각해보자. 이들 table들은 cluster에함께저장되지않았다고가정합니다.

SELECT accounts.balance, customers.last_name, customers.first_nameFROM accounts, customersWHERE accounts.custno = customers.custo;

―  cost-based approach의 default 목적은 best throughput이므로이 optimizer는좀더빨리질의에의해 SELECT 된모든행들을반환하기위해 nested loops operation이나 sort-merge operation중하나를선택합니다.
―  그러나질의에의해선택된첫번째행만반환할때필요시간이매우적어야할경우에는 best throughput보다 best response time으로 SQL문을최적화하는것이더낫다.
―  그렇게하려면 USE_NL 힌트를사용함으로서 optimizer가 nested loops join을강제로선택하게할수있습니다.
―  SQL문에서 USE_NL 힌트는 CUSTOMERStable을 inner table로가지는 nested loop를선택

SELECT /*+ ORDERED USE_NL(customers) Use N-L to get first row faster */accounts.balance, customers.last_name, customers.first_name FROM accounts, customers  WHERE accounts.custno = customers.custno;

―  많은경우에 nested loops join은 sort-merge join보다는더빨리첫번째행을반환합니다.
―  Nested loop join은한 table로부터첫번빼 SELECT 한행을읽은후에첫번째행을반환할수있고, 다른 table에서첫번째로일치하는행을찾고그것들을결합합니다.
―  반면에 sort-merge join은양 table에서 SELECT 한모든 row들을읽고정렬한후각각의저장된 row source의첫번째행들을결합할때까지첫번째행을반환할수없습니다.

SELECT dname, deptnoFROM deptWHERE deptno NOT IN(

SELECT deptno FROM emp);

― Execution Plan with Two Full Table Scans

― step3을통해오라클은 DEPTNO 칼럼에 index가있음에도불구하고 EMPtable을 full table scan함으로서위 SQL문을실행합니다는것을알수있습니다.

― full table scan은time-consuming operation을할수있습니다.

― EMPtable을찾는subquery에 index사용이가능한 WHERE절이없기때문에오라클은 index를사용할수없습니다.

―  그러나, 아래의 SQL문은 index에접근함으로서동일한행을 SELECT 합니다.

SELECT dname, deptnoFROM deptWHERE NOT EXISTS(

SELECT deptnoFROM empWHERE dept.deptno = emp.deptno);

― Execution Plan with a Full Table Scan and an Index Scan

― subquery의 WHERE절이 EMPtable의 DEPTNO칼럼을사용하므로 DEPTNO_INDEX를사용할수있습니다.

― index의사용은 execution plan의 step3에서하게됩니다

―  DEPTNO_INDEX의 index range scan은첫번째문장에서 EMP table의 full scan하는것보다시간이더적게걸린다.
―  첫번째 query는 DEPT table에서모든 DEPTNO를가져오기위해 EMP table을한번 full scan합니다.

― 이런이유로두번째 SQL 문은첫번째보다는더빠릅니다.

― Application에 NOT IN operator를사용하는 SQL문를가진다면 NOT EXISTS operator를사용해서그것들을다시써라. 이것은 index가있다면그 index를사용할수있게해줄것입니다.

●  비록요구된지식이같을지라도프로세스는다르다.

●  튜닝하기위한 application에서특별한문장을다음과같이함으로써분리해야합니다.

· application에익숙하라.
· SQL trace를이용하여특별한문제문장을분리하라.

● 이전절에서논의했던새로운 SQL 문장을기록하고, index, 클러스터, 해싱, 힌트를 하기 위한지침을 이용하여문장을 튜닝 할 수 있습니다.

· application을이용하는 SQL 문은무엇인가?

· application이처리하는데이타는무엇인가?

· 데이타의특징과 distribution은무엇인가?

· applicatin의무슨 operation이 data를실행하는가?

―  application 사용자들과성능에대해이야기해보라.
―  application의어떤부분을식별하기위해서그들이성능향상의필요를느끼는지를물어보라.
―  가능하면개인적인 SQL 문장을위해서이부분들이적어질때까지좁혀라.

· SQL 문장이파싱, 실행, 페치된횟수
· 각 SQL 문장을처리하는데필요한시간
· 각 SQL 문장과관련된메모리와디스크 access
· 각 SQL 문장을처리하는 row의수
―  SQL trace facility는또한 EXPLAIN PLAN 명령을이용하여 execution plan을생성할수있습니다.