oracle index rebuild를 판단하는 기준이나 rebuild가 필요한지 자체에 대한 많은 말들이 있긴한데.. 

다음의 글은 index_stats view의 del_lf_rows를 이용한 index rebuild 방안에 대한 구조적인 한계에 대한 설명입니다.
뭐 이러한 방안을 대량 delete 작업 후 바로 적용하면 될 거 같은데,
아래와 같은 내용을 알아둬야 .. 수치가 좀 이상하게 나올 경우 당황하지 않겠네요~ 

But, as the Oracle myth busters like Richard Foote have been saying for years,  that's not how Oracle's B-tree indexes work. When you delete an index entry, Oracle marks it as deleted but leaves it in place. When you commit your transaction Oracle does nothing to the index entry – but other processes now know that the entry can be wiped from the block allowing the space to be re-used.      (원문 : Index Rebuilds « Oracle Scratchpad )




crs resource의 상태를 crs_stat 명령으로 확인해 보면 가끔 resource의 상태가 UNKNOW인 경우가 가끔 발생한다.
CRS resource가 정상적으로 가동 되지 않은 경우도 있고 또 정상적으로 가동된 것 같은데 UNKNOW인 경우도 가끔 있다.  

oracle CRS resource가 UNKNOWN 상태로 변경되는 이유는 일반적으로 CRS resource start/stop/check 시의 action script 수행이 실패할 때 status가 변경되게 된다. 다음은 CRS resource의 state가 UNKNOWN으로 변경되는 일반적인 원인이다.
    

1.  The permission of the resource trace file is incorrect.
2.  The permission of the action script and other racg script is incorrect.
3.  The server load is very heavy and the action script times out.
4.  The look up to NIS hangs or takes very long time and causes the action script to time out.


CRS resource의 action script는 다음의 명령으로 확인할 수 있다. 
crs_stat -p <resource name such as ora.node1.vip> | grep ACTION_SCRIPT

CRS resource 이름은 다음의 명령으로 확인할 수 있다.
crs_stat | grep -i name

다음은 위에 기술한 CRS resource의 UNKNOWN state의 일반적인 원인에 대해 점검해 볼 항목이다.

1.  The permission of the resource trace file if it is incorrect. 
The resource trace file in in the HOME/log/<node name>/racg directory where HOME is the the HOME directory of action script for the resource.

2.  The permission of racg scripts in the HOME/bin directory if it they are incorrect. 
HOME is the the HOME directory of action script for the resource. Please issue "ls -l HOME/bin/racg*" to get the permission of the racg script. Please issue "ls -l HOME/bin/racg*" as user oracle or a user who normally starts up failing resources.
If any of the racg script is a soft link to another file, then check the permission of the file to which the racg script is soft linked.

3.  Check crsd.log and see if the resource action script timed out. 
If it did, then check if the server load was heavy (95% used or higher) for a minute or longer at the time of the failure. Setting up OSWatcher or IPD/OS can help troubleshooting this if the timeout occurs intermittently. Also, check if the NIC was having a problem at the time of the failure. 


참고 : Common Causes for CRS Resources in UNKNOWN State (Doc ID 860441.1) 
네이버 지식인에 DBMS_JOB으로 만들어진 JOB의 interval을 1초로 지정해도 
5초 정도의 interval을 갖는다는 질문이 있어 간단히 테스트 해 봤습니다. 

DBMS_JOB package로 job을 생성합니다 
SQL> variable jobno number;
SQL> begin
SQL> dbms_job.submit(:jobno,'insert into test values (sysdate);',
SQL> sysdate,'sysdate+1/60/24/50',FALSE);
SQL> end;
SQL> /

PL/SQL procedure successfully completed.

SQL> print jobno
     JOBNO
----------
        21

SQL> exec dbms_job.run(21);
PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.

SQL> select * from user_jobs;

       JOB LOG_USER                       PRIV_USER                      SCHEMA_USER                    LAST_DATE LAST_SEC         THIS_DATE THIS_SEC         NEXT_DATE NEXT_SEC         TOTAL_TIME B
---------- ------------------------------ ------------------------------ ------------------------------ --------- ---------------- --------- ---------------- --------- ---------------- ---------- -
INTERVAL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  FAILURES
----------
WHAT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_ENV
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MISC_ENV                                                           INSTANCE
---------------------------------------------------------------- ----------
        21 SYS                            SYS                            SYS                            12-OCT-10 16:50:28                                    12-OCT-10 16:50:29                  0 N
sysdate+1/60/24/50
         0
insert into test values (sysdate);
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
0102000202000000                                                          0

JOB 내역을 보면 정상적으로 NEXT_DATE는 LAST_DATE + 1초로 나옵니다 

SQL> alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
Session altered.

SQL> select * from test;
COMMIT_DATE
-------------------
2010/10/12 16:50:04
2010/10/12 16:50:08
2010/10/12 16:50:13
2010/10/12 16:50:18
2010/10/12 16:50:23
2010/10/12 16:50:28
2010/10/12 16:50:33
2010/10/12 16:50:38
2010/10/12 16:50:43
2010/10/12 16:50:48
2010/10/12 16:50:53
2010/10/12 16:50:58
2010/10/12 16:51:03
2010/10/12 16:51:08

14 rows selected.

그러나 table에 들어간 시간을 보니 4초 차이가 발생하는 군요...

SQL> exec dbms_job.broken(21,TRUE);
PL/SQL procedure successfully completed.

SQL> exec dbms_job.remove(21);
PL/SQL procedure successfully completed.

SQL> delete from test;
25 rows deleted.

SQL> commit;
Commit complete.

이번엔 DBMS_SCHEDULER로 테스트 해봤습니다. 

SQL> begin
  2  dbms_scheduler.create_schedule(
schedule_name  => 'INTERVAL_EVERY_1_SECONDS',
  start_date    => trunc(sysdate),
  repeat_interval => 'freq=SECONDLY;interval=1',
  comments     => 'Runtime: Every day all 1 SECONDS');  3    4    5    6  
  7  end;
  8  /

PL/SQL procedure successfully completed.

1초에 한번씩 수행할 Schedule을 생성합니다 

SQL> begin
dbms_scheduler.create_program
(program_name=> 'TEST_PROC01',
 program_type=> 'STORED_PROCEDURE',
 program_action=> 'SYS.TEST_PROC',
 enabled=>true,
 comments=>'job interval test'
 );
end;
PL/SQL procedure successfully completed.

수행할 Program을 생성합니다. 
TEST_PROC는 test table에 sysdate를 입력하는 procedure 입니다.

SQL> begin
dbms_scheduler.create_job
 (job_name => 'JOB_TEST',
  program_name=> 'TEST_PROC01',
  schedule_name=>'INTERVAL_EVERY_1_SECONDS',
  enabled=>true,
  auto_drop=>false,
  comments=>'job test');
end;
/  
PL/SQL procedure successfully completed.

이제 먼저 생성한 schedule과 program을 이용해 JOB을 생성합니다 

SQL> begin
dbms_scheduler.run_job('JOB_TEST',TRUE);
end; 
/
PL/SQL procedure successfully completed.



SQL> select * from test;

COMMIT_DATE
-------------------
2010/10/12 17:12:38
2010/10/12 17:12:39
2010/10/12 17:12:40
2010/10/12 17:12:41
2010/10/12 17:12:42
2010/10/12 17:12:43
2010/10/12 17:12:44
2010/10/12 17:12:45
2010/10/12 17:12:46
2010/10/12 17:12:47
2010/10/12 17:12:48
2010/10/12 17:12:49
2010/10/12 17:12:50
2010/10/12 17:12:51
2010/10/12 17:12:52
2010/10/12 17:12:53
2010/10/12 17:12:54
2010/10/12 17:12:55
2010/10/12 17:12:55
2010/10/12 17:12:56
2010/10/12 17:12:57
2010/10/12 17:12:58
2010/10/12 17:12:59
2010/10/12 17:13:00
2010/10/12 17:13:01
...

table에 들어간 데이터를 보니 1초에 한번씩 수행하고 있군요.. 

DBMS_JOB을 이용한 job은 interval에 제한이 있는 걸까요? 그런 자료는 못 본것 같은데..

예전 oracle 8에서 job_queue_interval parameter가 있을때
수행 시간이 job_queue_interval 주기때문에 job interval이 정확히 계산되지 않았던 것 같은데... 
혹시 job_queue_process가 wake-up 하는 시간때문일까요?  






이 자료는 O'Reilly에서 나온 " Mastering Oracle SQL, 2nd Edition"을 공부하면서 몰랐던 부분을 정리하고 있습니다. 
"Mastering Oracle SQL, 2nd Edition"의 저자는 Alan Beaulieu, Sanjay Mishra 이며 2004년에 나온 책이네요. 
알라딘에서 구해 볼 수 있습니다. 

HAVING 절은 항상 쓰면서 따로 메뉴얼을 읽어본적은 없다.
그래서 아마 group 함수의 결과에 대해 조건을 주는거? 이렇게 생각하고 있었는데, 
역시 메뉴얼을 읽어야해 ~ 

HAVING 절은 GROUP BY 절에 의해 나오는 나올수 있는 결과에 대해 filtering 해주는 역활을 해준다. 
즉 내가 지금까지 생각하던 거와는 달리 
group 함수의 결과 컬럼에 대해서만 조건을 주는 게 아니라 
결과 SET group by 절에 의해 나올 수 있는 결과 set 에 대해 조건을 줄 수 있다. 

다음의 예를 보면 .. 
부서별 인원수를 구하는 SQL이다.
SQL> select deptno,count(*) from emp group by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        30          6
        20          5
        10          3

이 중인 인원수가 4이상인 부서를 출력한다. 즉 group 함수에 의해 계산된 값에 대한 조건이다. 
SQL> select deptno,count(*) from emp group by deptno having count(*) > 4;

    DEPTNO   COUNT(*)
---------- ----------
        30          6
        20          5

아래의 SQL은 group by 에 의해 만들어진 결과에서 deptno가 20 이상인 부서만 출력한다. 
SQL> select deptno,count(*) from emp group by deptno having deptno > 20;

    DEPTNO   COUNT(*)
---------- ----------
        30          6

그러면 group by 절에 의한 결과에 대한 having과 select 절의 where 절은 바꿔 쓸 수 있을까?
아래의 결과를 보면 having deptno > 20 과 where deptno > 20의 결과는 같다. (당연한가?)

SQL> select deptno,count(*) from emp where deptno > 20 group by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        30          6

그러나 having 절에 의한 조건은 filter 처리로 계산된 결과에서 특정 조건만을 걸러내고 있으나, 
where 절의 경우 access 처리로 scan의 범위를 줄이고 있음을 알 수 있다. 

SQL> select deptno,count(*) from emp group by deptno having deptno > 20;

------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     2 |     6 |     3  (34)| 00:00:01 |
|*  1 |  FILTER                |           |       |       |            |          |
|   2 |   HASH GROUP BY        |           |     2 |     6 |     3  (34)| 00:00:01 |
|   3 |    INDEX FAST FULL SCAN| EMP_IDX04 |   449 |  1347 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPTNO">20)

SQL> select deptno,count(*) from emp where deptno > 20 group by deptno;

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |     2 |     6 |     1   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|           |     2 |     6 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN   | EMP_IDX03 |   193 |   579 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTNO">20)

queyr의 결과는 동일하나 access 하는 범위 자체가 틀리니 이에 대한 주의가 필요하다. 
당연히 access 범위를 줄일 수 있는 조건은 where 절에 기술하는 것이 유리할 듯 !!

근데, 테스트 하다보니 아래처럼 수행해도 결과가 잘 나오더군요. 
select는 count를 했으나 having에는 sum으로 조건을 주어도 결과가 잘 나오네요. 

SQL> select deptno,count(*) from emp group by deptno having sum(sal) > 10000;

    DEPTNO   COUNT(*)
---------- ----------
        20          5

Execution Plan
----------------------------------------------------------
Plan hash value: 3110987654

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     4 |    28 |    13   (8)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   HASH GROUP BY     |      |     4 |    28 |    13   (8)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |   449 |  3143 |    12   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SUM("SAL")>10000)

그럼 group by에 의한 수행 결과에 대해 조건을 주는 것이 아니라 
group by에 의해 나올 수 있는 결과에 대해 조건을 줄 수 있다는 거군요..

count(*)와 sum(sal)은 operation 자체가 다른데, 이걸 다 계산하고 있다는 건가? 
아니면 having에 있는 sum()이 query 수행시 참조되는 건가? 

"The HAVING clause is closely associated with the GROUP BY clause. The HAVING clause is used to put a filter on the groups created by the GROUP BY clause. If a query has a HAVING clause along with a GROUP BY clause, the result set will include only the groups that satisfy the condition specified in the HAVING clause."

네, having 절에 있는 group operation도 참조되는 게 맞습니다.. ^^

SQL> select deptno from emp group by deptno having sum(sal) > 10000;

    DEPTNO
----------
        20



* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!



이 자료는 O'Reilly에서 나온 " Mastering Oracle SQL, 2nd Edition"을 공부하면서 몰랐던 부분만을 정리하고 있습니다.
"Mastering Oracle SQL, 2nd Edition"의 저자는 Alan Beaulieu, Sanjay Mishra 이며 2004년에 나온 책이네요. 
알라딘에서 구해 볼 수 있습니다. 

Oracle GROUP 함수는 다중 사용을 지원한다. 몰랐다...
간단히 말하면 GROUP 함수 안에 GROUP 함수를 사용할 수 있다. 

예를 들자면 ..

부서별 연봉 합계이다. 
SQL> select deptno,sum(sal) from emp group by deptno;

    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20      10875
        10       8750

이 연봉 합계가 가장 높은 연봉 합은 다음처럼 구할 수 있다..

SQL> select max(salary) from (
SQL> select deptno,sum(sal) salary from emp group by deptno;

MAX(SALARY)
-----------
      10875

이걸 중첩 GROUP operation을 쓰면 다음과 같이 쓰일 수 있다. 

SQL> select max(sum(sal)) from emp group by deptno;

MAX(SUM(SAL))
-------------
        10875

SQL> select max(sum(sal)),min(sum(sal)),avg(sum(sal)) from emp group by deptno;

MAX(SUM(SAL)) MIN(SUM(SAL)) AVG(SUM(SAL))
------------- ------------- -------------
        10875          8750          9675

그럼 중첩 GROUP operation은 몇개까지 중첩될 수 있을까? 
아래 SQL이 어떤 의미인지는 모르겠지만..

일단 3개 중첩!!

SQL> select max(sum(count(sal))) from emp group by deptno;
select max(sum(count(sal))) from emp group by deptno
               *
ERROR at line 1:
ORA-00935: group function is nested too deeply

SQL> !oerr ora 935
00935, 00000, "group function is nested too deeply"
// *Cause:
// *Action:

GROUP function에 dept의 제한이 있는게 확인 된다. 
그럼 SUBQUERY를 사용하게 되면.. 

SQL> select max(a) from (
SQL>   select sum(b) a from  (
SQL>    select count(sal) b from emp group by deptno ));

    MAX(A)
----------
        14

잘 되는 군요.

다들 아시는 내용이겠지만, 저는 몰랐기 때문에 정리합니다 
-,.-



* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!



보통, Test 시스템에서 가동 시스템으로 데이타를 이관하는 경우, 데이타 뿐만 아니라 통계정보도 이관하여, Test계와 가동계를 동일하게 유지하고자 하는 경우가 있읍니다. 이러한 경우,데이타는 쉽게 옮겨도 통계치는 딱히 옮기는 방법이 없습니다. 이때 아래의 방법을 사용하시면 쉽게 올길수 있읍니다.

dbms_stats.export_table_stats(
ownname   VARCHAR2,                -- schema name
tabname   VARCHAR2,                -- table name
partname  VARCHAR2 DEFAULT NULL,   -- partition name
stattab   VARCHAR2,                -- stat table name
statid    VARCHAR2 DEFAULT NULL,   -- optional identifier
cascade   BOOLEAN  DEFAULT TRUE,   -- TRUE = indexes too
statown   VARCHAR2  DEFAULT NULL); -- stat table schema

exec dbms_stats.export_table_stats(USER, 'servers', NULL, 'STAT_TAB', NULL, TRUE);

dbms_stats.import_table_stats(
ownname       VARCHAR2,
tabname       VARCHAR2,
partname      VARCHAR2 DEFAULT NULL,
stattab       VARCHAR2,
statid        VARCHAR2 DEFAULT NULL,
cascade       BOOLEAN  DEFAULT TRUE,
statown       VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN  DEFAULT to_no_invalidate_type(get_param('NO_INVALIDATE')),
force         BOOLEAN DEFAULT FALSE);

exec dbms_stats.import_table_stats(USER, 'servers', stattab=>'STAT_TAB');

예 : SCOTT의 EMP  통계를 SCOTT2의 EMP2로 이관

1. 통계정보를 임시로 저장할 Table생성.
SQL> exec dbms_stats.create_stat_table('SCOTT','STATS');

2. 원하는 table의 통계정보를 임시 table로 이관.
SQL> exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','emp stats',TRUE);

* 참고 (updated: 2010/08/03)
"emp stats"로 중간에 space가 들어가니 에러가 나는 군요.. 이래서 직접 테스트를해봐야 한다니까...

SQL> exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','EMP_STATS',TRUE,'SCOTT');

BEGIN dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','EMP STATS',TRUE,'SCOTT'); END;
*
ERROR at line 1:
ORA-20001: EMP STATS is an invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 8240
ORA-06512: at "SYS.DBMS_STATS", line 9188
ORA-06512: at line 1

* 주의 (updated: 2010/08/03)
STATS table의 데이터를 qery 해보면 EMP table의 원래 소유자인 SCOTT이 C5 column에 명시되어 있습니다. 만약 다른 계정으로, 즉 SCOTT2 계정으로 통계정보를 옮기려면 STATS table의 SCOTT값을 SCOTT2로 update 해주어야 합니다. 
두개의 DB의 계정이 동일하다면 변경해 줄 필요는 없겠죠~


3. 임시 table을 export, 그리고 target db로 import.
 $exp scott/tiger tables=STATS file=expstat.dmp
 $imp scott/tiger file=expstat.dmp full=y log=implog.txt
(만약 동일 DB라면 이 과정은 생략 가능합니다.)

4. 임시 Table로 부터 통계치를 원하는 table에 넣는다.
SQL> exec dbms_stats.import_table_stats('SCOTT2','EMP2',NULL,'STATS');





CROSS JOIN/ Cartesian Products

 - Cartesian product 값을 얻을때 사용 합니다. 
 - 즉 join을 통해 생성가능한 모든 row를 return 합니다. 

SQL>SELECT ename FROM emp CROSS JOIN dept;
SQL>SELECT ename FROM emp, dept;

참고: CROSS가 생략되어 INNER join으로 수행되는 경우(join condition이 없는 경우) 나 CROSS join에서 join condition이 기술된 경우 에러발생합니다. 

SQL>SELECT e.lname, d.name FROM employee e JOIN department d;
ORA-00905: missing keyword

SQL>SELECT e.lname, d.name FROM employee e CROSS JOIN department d ON e.dept_id = d.dept_id;
ORA-00933: SQL command not properly ended


INNER JOIN 

 - 일반 조인시 Table간의 ','를 생략하고 'INNER JOIN'을 추가하고 WHERE절대신 ON절을 사용해야 합니다.
 - INNER는 생략 가능합니다.

SQL>SELECT e.empno, e.ename FROM dept d INNER JOIN emp e ON d.deptno=e.deptno;
SQL>SELECT e.empno, e.ename FROM dept d , emp e WHERE d.deptno=e.deptno;

EQUI-JOIN/ NON-EQUI-JOIN 
 - Join condition에 equal operation(=)을 사용하는 JOIN 
 - Join condition에 equal operation 외의 다른 operation을 사용하는 JOIN

SQL> SELECT s.name supplier_name, p.name part_name FROM supplier s JOIN part p ON s.supplier_id = p.supplier_id;
SQL> SELECT p.name part_name, c.inv_class inv_class FROM part p JOIN inventory_class c ON p.unit_cost BETWEEN c.low_cost AND c.high_cost;

SELF JOIN
 - 동일 TABLE에 대한 JOIN

SQL> SELECT e.lname employee, m.lname manager FROM employee e JOIN employee m ON e.manager_emp_id = m.emp_id;

OUTER JOIN 

 - Join시 특정 table의 join 조건에 일치하지 않은 row를 추가할때 사용해야 합니다.
 - { LEFT | RIGHT | FULL } [OUTER]

SQL>SELECT e.empno, e.ename FROM dept d LEFT OUTER JOIN emp e ON d.deptno=e.deptno;
SQL>SELECT e.empno, e.ename FROM dept d , emp e WHERE d.deptno=e.deptno(+);

SQL>SELECT e.empno, e.ename FROM dept d RIGHT OUTER JOIN emp e ON d.deptno=e.deptno;
SQL>SELECT e.empno, e.ename FROM dept d , emp e WHERE d.deptno(+)=e.deptno;

SQL>SELECT e.empno, e.ename FROM dept d FULL OUTER JOIN emp e ON d.deptno=e.deptno;

참고: full outer join의 비밀 [Science of DataBase]


PARTITION OUTER JOIN 

 - PARTITION OUTER JOIN을 사용하여 분석 대상이 되는 디멘션의 densification을 수행할 수 있으며, 
 - 파티션이나 테이블 내부에서 OUTER JOIN을 적용할 수 있습니다.

select hiredate,d.dname,  nvl(sum_sal,0) sum_sal
from dept d
left outer join (select deptno,to_char(hiredate,'YYYY') hiredate, sum(sal) sum_sal from emp group by deptno,to_char(hiredate,'YYYY')) e
partition by (e.hiredate)
on d.deptno = e.deptno
order by 1,2
/

HIREDATE     DNAME                                         SUM_SAL
------------ ------------------------------------------ ----------
1980         ACCOUNTING                                          0
1980         OPERATIONS                                          0
1980         RESEARCH                                          800
1980         SALES                                               0
1981         ACCOUNTING                                       7450
1981         OPERATIONS                                          0
1981         RESEARCH                                         5975
1981         SALES                                            9400
1982         ACCOUNTING                                       1300
1982         OPERATIONS                                          0
1982         RESEARCH                                         3000
1982         SALES                                               0
1983         ACCOUNTING                                          0
1983         OPERATIONS                                          0
1983         RESEARCH                                         1100
1983         SALES                                               0



NATURAL JOIN 

 - Equijoin과 동일하다고 보시면 됩니다.
 - 두 테이블의 동일한 이름을 가지는 칼럼은 모두 조인이 됩니다. (oracle forum : HR유저에서 NATURAL JOIN 결과가 다릅니다 )
 - 동일한 컬럼을 내부적으로 찾게 되므로 테이블 Alias를 주면 오류가 발생 합니다. 
    (ORA-25155: column used in NATURAL join cannot have qualifier)
 - 동일한 컬럼이 두개 이상일 경우 JOIN~USING문장으로 조인되는 컬럼을 제어 할 수 있습니다.
 
SQL>SELECT empno, ename, deptno FROM emp NATURAL JOIN dept 
SQL>SELECT e.empno, e.ename, d.deptno FROM emp e, dept d WHERE e.deptno=d.deptno


JOIN ~ USING 

 - NATURAL JOIN의 단점은 동일한 이름을 가지는 칼럼은 모두 조인이 되었는데 USING 문을 사용하면 컬럼을 선택해서 조인을 할 수가 있습니다.
 - USING절 안에 포함되는 컬럼에 Alias를 지정하면 오류가 발생 합니다.
  
SQL>SELECT e.empno, e.ename, deptno FROM emp e JOIN dept d USING(deptno)


ON 구문

 - 조인 조건을 지정 할 수 있습니다. 
 - 모든 논리 연산 및 서브쿼리를 지정할 수 있습니다.

SQL>SELECT e.empno, e.ename, e.sal
       FROM emp e JOIN dept    d  ON (e.deptno=d.deptno)
                           JOIN bonus  b ON (b.ename = e.ename)
       WHERE e.sal IS NOT NULL




* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!



10g 부터는 Oracle에서 자동으로 optimizer statistic 통계정보를 수집하게 됩니다.
11g에는 scheduler job에서 automated task로 변경된 거 같은데,
모두 마찬가지로 10% 이상의 변경을 대상으로 수집합니다. (11g 두? .. 음)

다음의 SQL은 dba_tab_modifications table에서 DML에 의해 변경된 건수와 dba_tables의 num_rows를 비교해
10% 이상 변경된 table을 조회하는 SQL 문입니다.

select TABLE_OWNER,m.TABLE_NAME,num_rows,round(num_rows/10,0) "NUM_ROWS/10",inserts+updates+deletes "tot_changed",
INSERTS,UPDATES,DELETES,TIMESTAMP,LAST_ANALYZED,round(LAST_ANALYZED-TIMESTAMP,0) GAP
from dba_tab_modifications  m, dba_tables t
where table_owner not in ('SYS')
and t.table_name = m.table_name
and t.owner = m.table_owner
and round(num_rows/10,0) - (inserts+updates+deletes) < 0
order by LAST_ANALYZED-TIMESTAMP
/

UPDATE (2010/07/05)

아래 블로그는 제가 구독하는 Oracle 관련 blog 입니다.
dba_tab_modifications에 대해 자세한 설명과 test 결과를 post 해 놓았네요. dba_tab_modifications의 데이터가 즉각적으로 보이지 않던데, 
flush를 해줘야 하는 군요.. 움~

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO








데이터 파일의 저장공간을 넉넉히 갖고 운영할 수 있으면 좋겠지만, 대부분의 사이트 들은 한정된 disk 공간으로 운영을 합니다.  Disk의 가격도 만만치 않거니와 향후 발생할 데이터 양을 너무 빡빡하게 계산해 놓으면 이 한정된 disk를 알뜰하게 DBA들이 사용해야 하죠. 

Disk의 공간을 줄이기 위해 compress table이나 table re-org 작업, tablespace shrink 작업들을 하게되는데 오늘은 Bulletin 10165의 내용을 정리합니다. 

tablespace shrink 작업은 extent가 할당되지 않은 공간 중 마지막 extent 부분 부터 줄일 수 있습니다. 즉, tablespace가 처음 할당 되고 얼마 되지 않은 table 들이 생성됬을 경우 꽤 짭짭할 공간을 줄일 수 있겠지만, 이미 사용한지 좀 된 tablespace의 경우에는 중간중간 extent가 할당되어 얼마 못 줄이게 됩니다. 이런 경우에는 table을 drop하고 재 생성하는 등의 re-org 작업이 선행 되거나 tablespace를 이리저리 건너 뛰면서 줄여야 됩니다. 

그럼 tablespace를 resize 할 수 있는 max size를 어떻게 구할 수 있을까요? 

dba_extents라는 view를 보면 datafile 별로 extent가 할당된 정보가 나옵니다. 이 할당된 extent의 마지막 위치를 파악해서 바로 그 이후로 size를 지정하면 됩니다.  다음의 공식은 bulletin 10165에 나와 있는 공식입니다. 

1) 줄이고자하는 file id를 확인합니다.
SQLPLUS> select file_id, file_name from dba_data_files;

2) 얼마를 사용했는지 확인합니다.
SQLPLUS> select block_id, blocks from dba_extents where file_id='FILE_ID' order by block_id;

FILE_ID 대신에 줄이려는 file id를 기술합니다.

3)현재 사용중이 db block size를 확인합니다.
SQLPLUS> show parameter db_block_size

2)에서 조회된 마지막의 block_id, blocks 값을 가지고 계산합니다. 
block_id * db_block_size + blocks * db_block_size 의 결과에 한 block더한 값만큼만 줄이는 것이 가능합니다. 

만일 마지막으로 조회된 값이 block_id가 578261, blocks가 515, db_block_size가 2048 일 경우  
578261*2048 + 515*2048 =1185333248로 최소한 1.2GB이상은 써야 합니다. 

4) 실제 datafile을 줄입니다.
SQLPLUS>alter database datafile '/oracle/dbs/toolsORA.dbf' resize 1200M;



* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!



Faster Startup


The days when dinosaurs ruled the earth and 2GB memory was considered large are gone. Now, it's not uncommon to see large buffer caches to the tune of 100GB. When the instance is started, it might take several minutes, or even hours, to initialize a buffer cache of this size.

If you look deeper into the situation, you will notice that the entire buffer cache need not be up when the database instance starts. After the instance starts, the buffer cache is empty, which gradually fills up when users select data from tables. So, there is no need to initialize the entire buffer cache when the instance starts.

In Oracle Database 10g Release 2, this behavior is accounted for in the startup logic. When you start the instance, only 10% of the buffer cache is initialized; the rest is initialized after the database is opened by the checkpoint process. This new approach reduces instance startup time significantly.

Bear in mind, however, that until the entire buffer cache is initialized, automatic buffer cache sizing is not available.

아시다 시피 SGA memory 영역은 Database가 nomount 단계에서 할당됩니다. 
이 new feature는 nomount 단계에서는 10%의 buffer pool만 할당하고 나머지는 instance가 open 된 후에 할당한다는 의미입니다.


Drop Empty Datafiles


Imagine that you just added a datafile to the wrong directory or tablespace—a fairly common error. All is not lost; the datafile doesn't contain any data yet, so you can easily drop it, right?

Unfortunately, you can't. Prior to Oracle Database 10g Release 2, your only clean option for removing a datafile is to drop the entire tablespace and then rebuild it without that particular file. If the tablespace contains data, you have to go through the time-consuming and laborious process of storing the data on a separate location and reinstating it. In addition to its inconvenience, this process makes the tablespace unavailable.

Thankfully, in Oracle Database 10g Release 2 the process has been simplified: You can just drop the datafile. For example, the following command will remove the indicated datafile from the tablespace as well as from the server.
alter tablespace users drop datafile '/tmp/users01.dbf'
 /
There are a couple restrictions, however: The datafile must be empty to be dropped. You can't drop the last datafile in a tablespace; the tablespace itself must be dropped. And the tablespace must be online and in read-write status.

10g 이전 버전에서는 데이터파일을 잘 못 붙일 경우 이거 띄어 내려면 tablespace 전체를 재구성해야 하죠. 
예전 OPS 쓸때 raw device가 아닌 filesystem에 datafile을 추가하는 등의 황당한 경우도 있었습니다.. ㅋ
drop empty datafiles new feature는 이런 유저 실수로 인한 막대한 작업을 더이상 안해도 되게 해주겠군요. 


Catch the Error and Move On: Error Logging Clause


Suppose you are trying to insert the records of the table ACCOUNTS_NY to the table ACCOUNTS. The table ACCOUNTS has a primary key on ACC_NO column. It's possible that some rows in ACCOUNTS_NY may violate that primary key. Try using a conventional insert statement:
SQL> insert into accounts
  2  select * from accounts_ny;
insert into accounts
*
ERROR at line 1:
ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) violated
None of the records from the table ACCOUNTS_NY has been loaded. Now, try the same with error logging turned on. First, you need to create a table to hold the records rejected by the DML statement. Call that table ERR_ACCOUNTS.
exec dbms_errlog.CREATE_ERROR_LOG ('ACCOUNTS','ERR_ACCOUNTS')
Next, execute the earlier statement with the error-logging clause.
SQL> insert into accounts
  2  select * from accounts_ny
  3  log errors into err_accounts
  4  reject limit 200
  5  /

6 rows created.
Note that the table ACCOUNTS_NY contains 10 rows yet only six rows were inserted; the other four rows were rejected due to some error. To find out what it was, query the ERR_ACCOUNTS table.
SQL> select ORA_ERR_NUMBER$, ORA_ERR_MESG$, ACC_NO
 2  from err_accounts;

ORA_ERR_NUMBER$   ORA_ERR_MESG$                                       ACC_NO
 ---------------  --------------------------------------------------  ------
               1  ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) vi  9997
               olated
               1  ORA-00001: unique constraint (ARUP.PK_ACCOUNTS)vi  9998
               olated
               1  ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) vi 9999
               olated
               1  ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) vi 10000
               olated
Note the columns ORA_ERR_NUMBER$, which show the Oracle error number encountered during the DML statement execution, and the ORA_ERR_MESG$, which shows the error message. In this case you can see that four records were rejected because they violated the primary key constraint PK_ACCOUNTS. The table also captures all the column of table ACCOUNTS, including the column ACC_NO. Looking at the rejected records, note that these account numbers already exist in the table; hence the records were rejected with ORA-00001 error. Without the error-logging clause, the whole statement would have failed, with no records rejected. Through this clause, only the invalid records were rejected; all others were successful.

이 기능은 sql*loader의 bad file 같은 기능입니다. 
특정 에러가 발생해도 이를 logging만 하고 나머지 데이터는 처리를 해주는 군요.  
PK constraint violation 발생하면 이거 처리하는 것도 좀 귀찮은 작업인데, 요 기능 쓰면 약간 편해지겠습니다. ^^

그나 저나 아직도 10g new feature를 모르고 있다니... 좌절이다~



* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!



널리 알려진 기능이지만 사용하는 방법 sample 입니다.
PC 내에 있는 scrpt 정리하다 삭제 전에 한번 올려 봅니다.
혹시 누군가에겐 필요할까 싶어서 ㅎㅎ
(절대 몇일 동안 oracle post가 없어 올리는 건 아닙니다. --;)

ora102@TEST4:/app/oracle/product/db/rdbms/log> sqlplus scott/tiger

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Aug 3 14:57:17 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$U4gkooULB1PgQLMKTWwM7A==$0 TABLE
BIN$U4iK63kw1zTgQLMKTWx98g==$0 TABLE
EMP                            TABLE
AAA                            TABLE

SQL>
SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP              BIN$U4iK63kw1zTgQLMKTWx98g==$0 TABLE        2008-08-03:14:32:03
TEST             BIN$U4gkooULB1PgQLMKTWwM7A==$0 TABLE        2008-08-03:14:03:27

SQL>  FLASHBACK TABLE TEST TO BEFORE DROP;

Flashback complete.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$U4iK63kw1zTgQLMKTWx98g==$0 TABLE
TEST                           TABLE
EMP                            TABLE
AAA                            TABLE

SQL> purge recyclebin;

Recyclebin purged.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE
EMP                            TABLE
AAA                            TABLE


SQL> drop table test;

Table dropped.

SQL> select * from user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION
------------------------------ -------------------------------- ---------
TYPE                      TS_NAME                        CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME               DROPSCN PARTITION_NAME                   CAN CAN
------------------- ---------- -------------------------------- --- ---
   RELATED BASE_OBJECT PURGE_OBJECT      SPACE
---------- ----------- ------------ ----------
BIN$U4juzy+wgKngQLMKTWwgxQ==$0 TEST                             DROP
TABLE                     USERS                          2008-08-03:13:57:59
2008-08-03:14:59:59   15636802                                  YES YES
     56903       56903        56903   

2010/03/30
oracle flashback 기능을 그림과 함꼐 잘 설명해 놓은 post가 있어 링크 겁니다

행복만땅 개발자 :: [오라클 10g] 플래시백의 설정/관리





oracle RAC(OPS도 마찬가지지만)를 사용할때, 각각의 instance는 자신의 SGA와 buffer cache를 갖는다.

RAC는 성능 향상과 노드간의 데이터 정합성을 위해 이들 각 instance내의 buffer내의 block의 변경을 자알 관리해야 한다. cache resource라 불리는 이들 buffer에 대한 각각의 copy 본은 cluster의 한 노드에는 반드시 master를 갖고 있어야 한다.

10g 이전 버전에서는 한 instance에서 해당 cache resource에 대해 master로 설정되면 instance startup이나 shutdown, node eviction 등으로 인한 reconfiguration 이전에는 그 설정이 지속되게 된다.  따라서 만약 노드 B가 cache resource에 대해 master로 설정되면 reconfiguration이 발생되기 전까지는 이 설정이 지속된다.

2009년 10월 12일 Update:


첨부된 문서는 RAC 간의 데이터 전송인 "cache fusion"에 대한 설명이다. 해당 설명의 그림을 보면 buffer access를 위해 계속 resource master에 해당 block에 대해 어떤 instance에서 master owner ship을 갖고 있는지 확인하고 있다.
이 문서는 2003년 봄 oracle magazine에 실린 "cache fusion"에 대한 설명이다.


10g에서 소개된 DRM은 자주 access 되는 node로의 buffer에 대한 master가 변경되는 re-mastering 개념이 소개되었다. 따라서 이전 버전처럼 reconfiguration 없이도 master의 재 설정이 가능해 졌다.

이러한 re-mastering operation은 LMD trace에서 확인할 수 있다.
Begin DRM(202) - transfer pkey 4294951314 to 0 oscan 1.1
*** 2006-08-01 17:34:54.645
Begin DRM(202) - transfer pkey 4294951315 to 0 oscan 1.1
*** 2006-08-01 17:34:54.646
Begin DRM(202) - transfer pkey 4294951316 to 0 oscan 1.1
*** 2006-08-01 17:34:54.646
Begin DRM(202) - transfer pkey 4294951317 to 0 oscan 1.1
RAC에서는 buffer내의 block에 대한 access시 반드시 해당 block의 master와 통신을 하게 되어 있다. 따라서 특정 노드에서 자주 참조되는 block은 해당 노드에서 master를 갖는것이 성능상의 잇점을 얻을 수 있을 것이다.

하지만 oracle10g RAC에 대해서 이전 버전처럼 업무를 노드 별로 나누는 것을 강조하고 있지 않다. 더구나 sql*net의 load balance 기능을 사용하고 있다면, remastering에 의한 부담이 더 크지 않을까나..

참고 : Note 390483.1 DRM - Dynamic Resource management

2010/03/25 Updated: 
Oracle database internals by Riyaj라는 유명한 오라클 블로그에서 DRM에 대해 post 하나를 올렸군요. 
오 ..역쉬, force가 다르군요. ㅜㅡ

내용을 좀 읽어보니 아예 DRM을 disable 시키지 말고 값을 크게 해서 remastering을 최소화하는 것을 권하는 군요. 
DRM 기능은 _gc_affinity_time을 0으로 설정해 disable 시킬 수 있습니다. 그러나 _gc_affinity_limit와 _gc_affinity_minimum의 값 조정으로 DRM 기능을 최소화 할 수 도 있군요. 이렇게 최소화 시켜놓으면 사용자게 manual 하게 rematering 하는 게 여전히 가능하답니다. 
manual 하게 수정은 "oradebug lkdebug -m pkey" 명령으로 가능합니다. 

자세한 내용은 아래 링크에서...

http://orainternals.wordpress.com/2010/03/25/rac-object-remastering-dynamic-remastering/






오라클의 전체적인 사용현황과 그에 따른 AWR, ADDM을 수행하기 좋은 툴하나 소개합니다. 
OraScope.Net이라는 툴인데, FreeWare 입니다. 

Session, Lock, Database 등 모니터링 기능은 없는 듯하나, 
Database 전체를 파악하기 위한 resource 사용현황들은 거의 다 있는 것 같습니다. 

Download는 아래사이트에 방문하시면 됩니다..

OraScope.Net LITE 는 FREEWARE 입니다.

 

즉, 무료 오라클 성능 모니터링 툴입니다.

개인, 기업, 기관등 사용자와 장소에 구애받지 않고, 마음대로 사용하실수 있습니다.

다만, OraScope.Net LITE 를 저작자의 동의없이 상업적 용도로는 사용할수 없으며,

본 싸이트에서 제공하는 배포화일 형태 그대로, 자유로이 배포하실수 있습니다.(배포화일의 재 PACKAGING은 불허)

 

OraScope.Net LITE 의 모든 저작권은 저작자( orascope@orascope.net ) 에게 있습니다.

 

OraScope.Net LITE 를 사용하여 일어나는 문제나 기타 제반 사항에 대해, 저작자는 책임이 없음을 알려드립니다.

(기타 후원해 주실 분이나 업체는 orascope@orascope.net 로 연락주시면 됩니다.Laughing)









오라클에는 현재 수행 중인 세션을 정리하는 방법이 두가지가 있습니다.

하나는 익히 알고 있는 ALTER SYSTEM KILL SESSION 명령이고 다른 하나는 ALTER SYSTEM DISCONNECT SESSION 명령입니다. 이 두개의 명령은 "ALTER SYSTEM" 명령의 "end_session_clauses"에 속하는 명령입니다.




일반적으로 비정상적인 세션을 정리할 때 alter system kill session 명령을 수행하곤 하는데, kill session 명령은 현재 수행중인 transaction을 바로 정리해 버리죠. 그런데, disconnect session  명령은 현재까지 수행된 transaction은 처리하고 session을 정리할 수 있는 방법입니다.

ALTER SYSTEM KILL SESSION '13, 8' IMMEDIATE;
ALTER SYSTEM DISCONNECT SESSION '13, 8' POST_TRANSACTION;


DISCONNECT SESSION은 dedicated server를 강제로 정리해 현 세션을 disconnect 하게 된다. 세션의 지정은 KILL SESSION 과 마찬가지로 'sid, serial#' 으로 지정한다.

DISCONNECT SESSION 문에는 "POST_TRANSACTION" 과 "IMMEDIATE" option을 지정할 수 있다.

POST_TRANSACTION은 현재 transaction이 수행 중이라면 이 transaction이 끝난 후에 세션을 정리하게 되며, 만약 현재 수행 중인 transaction이 없다면 "KILL SESSION"과 동일한 효과를 갖는다. (근데, "KILL SESSION"절에 IMMEDIATE option은 언제 생긴거람.. ?)









오라클의 통계정보는 다른 DB나 계정으로 옮겨질 수 있습니다.
개발장비에서 수집된 통계정보를 옮긴다거나, 기존의 운영장비의 통계정보를 migration 할 대상으로 옮겨 동일한 SQL 수행을 유도할 수 있습니다.

다음은 현재 SCOTT 계정의 통계정보를 다른 Database의 SCOTT 계정으로 옮기는 예제입니다.

* statistic 정보 수집 table 생성
SQL> exec dbms_stats.create_stat_table('SCOTT','STATS');

* scott.emp table의 통계정보를 statistic 정보수집 테이블인 STATS로 export
exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS',NULL,TRUE);

* STATS table export
%exp scott/tiger tables=STATS file=expstat.dmp

* STATS table import
%imp scott/tiger file=expstat.dmp full=y log=implog.txt 

* STATS table의 통계정보를 SCOTT.EMP에 import
exec dbms_stats.import_table_stats('SCOTT','EMP',NULL,'STATS',NULL,TRUE);

기존의 통계정보를 다른 Database의 동일 계정으로 옮기는 작업에 대한 내용들은 많이 기술 되어 있는데,
이름이 다른 계정은 어떻게 옮길까요?

다른 계정으로 통계정보를 옮기는 작업은 다른 데이터베이스내의 동일 계정으로 통계정보를 옮기는 작업처럼 procedure로만은 불가능합니다.
물론 작업 자체가 불가능하다는 이야기는 아닙니다.

You may not export stats from one schema name and import into a different schema name (Bug 1077535). The schema names much match exactly. If the target database schema name (import database) is different from the source database schema name (export database), then you may update the table you exported the statistics into and set the C5 column to the target schema name.

통계정보를 export 받기 전에 생성하는 statistic 정보를 저장하는 stat table에는 통계정보가 만들어진 계정의 이름이 지정되어 있어,
이를 다른 계정으로 import하려고 하면 import는 성공했다고 나오나 통계정보는 입력 되지 않습니다.

따라서 수집된 통계정보가 있는 table의 owner column의 owner를 바꿔줘야 합니다.

i.e.
"update table sd_stat set c5 = '<target schemaname>'
where c5 = '<Source Schema name>'
  and statid = '<Stat Id used while exporting these stats>;"

update sd_stat set c5 = 'JBARLOW';
where c5 = 'SCOTT'
  and statid = 'a';
commit;

정리해 보자면 SCOTT 계정의 SD table의 통계정보를 JBAARLOW 계정으로 옮긴다면 아래와 같이 수행해야 합니다.

* statistic 정보 수집 table 생성
SQL> exec dbms_stats.create_stat_table('SCOTT','SD_STAT');

* scott.sd table의 통계정보를 statistic 정보수집 테이블인 SD_STATS로 export
exec dbms_stats.export_table_stats('SCOTT','SD',NULL,'SD_STAT',NULL,TRUE);

*  SD_STAT table의 계정정보 수정
update sd_stat set c5 = 'JBARLOW';
where c5 = 'SCOTT';
commit;

* STATS table의 통계정보를 JBARLOW.SD table에 import
exec dbms_stats.import_table_stats('JBARLOW','SD',NULL,'SD_STAT',NULL,TRUE,'SCOTT');


참고 : Note 117203.1 How to Use DBMS_STATS to Move Statistics to a Different Database





* CRS process 확인
1. ps -ef | grep d.bin

* Resource Status 확인
crs_stat -t
crs_stat -p ==> CRS PROFILE을 확인

* Node_name 확인
as oracle> $ORA_CRS_HOME/crs/bin/olsnodes -n

* CRS 실행
1. as root> $ORA_CRS_HOME/crs/bin/crsctl start crs

* ASM 사용시 Start/Stop 방법
as oracle> srvctl start/stop asm -n node_name

* Listener Start/Stop 방법
as oracle> srvctl start/stop listener -n node_name [-l listenername]

* Instance Startup/Stop

srvctl start instance -d oraDB -i oraDB1
srvctl stop instance -d oraDB -i oraDB1
srvctl status database -d oraDB
Instance oraDB1 is not running on node ds04e
Instance oraDB2 is not running on node ds05e

* Database Down 방법

1. sqlplus "/as sysdba"
   SQL> shutdown immediate
   또는
   srvctl stop database -d db_name

2. srvctl stop nodeapps -n <node#1_name>
3. srvctl stop nodeapps -n <node#2_name>
4. as root> /fsoracle/app/oracle/crs/bin/crsctl stop crs
5. OS Cluster Down                     -- 꼭 내릴 필요는 없음

* Database startup 방법

1. OS Cluster startup
2. as root> /fsoracle/app/oracle/crs/bin/crsctl start crs
3. srvctl start nodeapps -n <node#1_name>        -- Resource Start (Optional)
4. srvctl start nodeapps -n <node#2_name>        -- Resource Start (Optional)
5. sqlplus "/as sysdba"
   SQL> startup
   또는 srvctl start database -d db_name

* CRS setup 방법

1. Database Auto Restart Disable 방법
   crs_stat -p ora.DSCTMA.db > /tmp/ora.DSCTMA.db.cap
   crs_profile -update ora.DSCTMA.db -dir /tmp -o as=2,ra=0
   crs_register ora.DSCTMA.db -dir /tmp -u
   crs_stat -p  ora.DSCTMA.db | grep -E "REQUIRED_RESOURCES|RESTART_ATTEMPTS|AUTO_START"


2.Instance Auto Restart  Disable 방법
  crs_stat -p ora.DSCTMA.DSCTMA01.inst > /tmp/ora.DSCTMA.DSCTMA01.inst.cap
  crs_profile -update ora.DSCTMA.DSCTMA01.inst -dir /tmp -o as=2,ra=0        -- Resource Option 수정
  crs_register ora.DSCTMA.DSCTMA01.inst -dir /tmp -u
  crs_stat -p  ora.DSCTMA.DSCTMA01.inst | grep -E "REQUIRED_RESOURCES|RESTART_ATTEMPTS|AUTO_START"

  crs_stat -p ora.DSCTMA.DSCTMA02.inst > /tmp/ora.DSCTMA.DSCTMA02.inst.cap
  crs_profile -update ora.DSCTMA.DSCTMA02.inst -dir /tmp -o as=2,ra=0        -- Resource Option 수정
  crs_register ora.DSCTMA.DSCTMA02.inst -dir /tmp -u
  crs_stat -p  ora.DSCTMA.DSCTMA02.inst | grep -E "REQUIRED_RESOURCES|RESTART_ATTEMPTS|AUTO_START"

3.crsctl disable crs ( Automatic Startup Disable )

4. Network Down 시 Database shutdown 방지 방법
crs_stat -p ora.DSCTMA.DSCTMA01.lsnr > /tmp/ora.DSCTMA.DSCTMA01.lsnr.cap
crs_profile -update ora.DSCTMA.DSCTMA01.lsnr -dir /tmp -o as=0,ra=0
crs_register ora.DSCTMA.DSCTMA01.lsnr -dir /tmp -u
crs_stat -p  ora.DSCTMA.DSCTMA01.lsnr | grep -E "REQUIRED_RESOURCES|RESTART_ATTEMPTS|AUTO_START"


5. VIP auto restart 기능 OFF
crs_stat -p ora.<node#1_name>.vip > /tmp/ora.<node#1_name>.vip.cap
crs_profile -update ora.<node#1_name>.vip -dir /tmp -o as=0  # auto_start=0
crs_register ora.<node#1_name>.vip -dir /tmp -u
crs_stat -p  ora.<node#1_name>.vip | grep -E "REQUIRED_RESOURCES|RESTART_ATTEMPTS|AUTO_START"






SQL> alter table p_emp rename partition emp_p1 to emp_p01;
SQL> alter table p_emp rename partition emp_p2 to emp_p02;
SQL> alter table p_emp rename partition emp_p3 to emp_p03;

SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name = 'P_EMP';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
P_EMP                          EMP_MAX                        MAXVALUE
P_EMP                          EMP_P01                        2000
P_EMP                          EMP_P02                        3000
P_EMP                          EMP_P03                        4000

partition table의 partition 이름 변경시 index의 partition 이름은 같이 변경되지 않는다. 따라서 index partition이름도 같이 변경해 주어야 한다.

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P1                         2000       USABLE
P_EMP_I              EMP_P2                         3000       USABLE
P_EMP_I              EMP_P3                         4000       USABLE

SQL> ALTER INDEX p_emp_i RENAME PARTITION emp_p1 TO emp_p01;
SQL> ALTER INDEX p_emp_i RENAME PARTITION emp_p2 TO emp_p02;
SQL> ALTER INDEX p_emp_i RENAME PARTITION emp_p3 TO emp_p03;

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P01                        2000       USABLE
P_EMP_I              EMP_P02                        3000       USABLE
P_EMP_I              EMP_P03                        4000       USABLE










1. partition table & local index 생성.

CREATE TABLE p_emp (sal NUMBER(7,2))
 PARTITION BY RANGE(sal)
 (partition emp_p1 VALUES LESS THAN (2000),
  partition emp_p2 VALUES LESS THAN (4000),
  partition emp_max VALUES LESS THAN (MAXVALUE));

 create index p_emp_i on p_emp (sal) local;

insert into p_emp values (1000);
insert into p_emp values (2000);
insert into p_emp values (3000);
commit;

SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name = 'P_EMP';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
P_EMP                          EMP_MAX                        MAXVALUE
P_EMP                          EMP_P1                         2000
P_EMP                          EMP_P2                         4000

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions  where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P1                         2000       USABLE
P_EMP_I              EMP_P2                         4000       USABLE

2. split partition table

전체 partition range의 중간에 partition을 삽입히기 위해서는 split partition 명령을 사용해야 한다. MAXVALUE가 없는 상태에서 제일 끝에 추가할 경우는 add partition 명령을 사용한다.

SQL> alter table p_emp split partition emp_p2 at (3000) into (partition emp_p2, partition emp_p3);

SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name = 'P_EMP';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
P_EMP                          EMP_MAX                        MAXVALUE
P_EMP                          EMP_P1                         2000
P_EMP                          EMP_P2                         3000
P_EMP                          EMP_P3                         4000


SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions  where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P1                         2000       USABLE
P_EMP_I              EMP_P2                         3000       UNUSABLE
P_EMP_I              EMP_P3                         4000       UNUSABLE

partition table의 local partition index도 같이 split 되나 데이터가 있는 상태라면 "UNUSABLE" 상태로 되므로 이에 대한 rebuild 작업이 반드시 필요하다.

If the partition you are splitting contains data, the ALTER TABLE...SPLIT PARTITION statement marks UNUSABLE the new partitions (there are two) in each local index, all global index partitions, and any
global nonpartitioned index. You must rebuild such affected indexes or index partitions.

(Metalink Note 165599.1 Top Partitioned Tables Issues 중.)


SQL> alter index p_emp_i rebuild partition emp_p2;
SQL> alter index p_emp_i rebuild partition emp_p3;

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P1                         2000       USABLE
P_EMP_I              EMP_P2                         3000       USABLE
P_EMP_I              EMP_P3                         4000       USABLE







oracle 9i rac 이전 버전인 ops에서는 양 노드간의 data sync를 맞추기 위해 file ping 방식을 사용했다. 즉, 한 노드에서 변경된 내용을 다른 노드에서 읽고자 했을 때 변경된 데이터를 file에 write하고 이를 다시 다른 노드에서 읽는 방식을 사용했다.

이 방식은 노드 간에 동일한 데이터를 읽게 되는 경우 file writing이 발생되므로 성능에 심각한 영향을 끼칠 수 있다.
따라서 OPS에서는 양 노드의 업무 분할이 필요했고, 공통으로 사용되는 code성 데이터 사용을 가능한한 최소화 해야 되었다.

oracle 9i RAC에서는 memory간의 통신으로 데이터를 전달하는 cache fusion 방식이 사용되면서 노드간의 데이터 sync 방식의 성능은 크게 향상되었다. 이제 노드간의 데이터 sync는 이전 버전에서 처럼 더이상 심각한 문제가 문제를 유발하지 않으며, 노드간의 업무 분할은 더이상 필요가 없어 졌다.

cache fusion으로 인한 pinging 현상의 성능이 좋아져서 업무 분할이 필요없어짐으로 해서 비로서 load balance 기능 구현이 가능해 졌으며, 이 을 이용해 각 노드의 resource를 보다 효율적으로 사용할 수 있다.

load balance는 client-side load balance와 server-side load balance로 나눌 수있다.

client-side load balance는 tnsnames.ora내의 tns alias 설정시 load balnace 설정에 의해 가능하며 기술된 listener list에 대해 random 하게 connection을 이루게 된다. 따라서 connection의 개수는서버의 resource 사용량이나 기존의 connection 갯수와는 상관없이 random 하게 이루어 진다.

2009년 10월 13일 update :

net_service_name=
 (DESCRIPTION=
  (LOAD_BALANCE=on)
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME=sales.us.acme.com))


server-side load balnace는 listener로 들어오는 client connection을 oracle background process인 pmon의 dynamic service registration에 의해 수집된 profile statistic 정보를 이용해 덜 바쁜 노드, 덜 바쁜 instance, 덜 바쁜 dispatcher 순으로 connection을 전이 시켜 준다.

server-side load balance는 init file내의 service_name, local_listener와 remote_listener 등록이 필요하다.

2009년 10월 13일 update :

다음의 그림은 server-side load balancing 구성에 대한 그림이다. instance A는 listener A를 local listener로 갖고 있으며, listener B를 remote listener로 갖는다. 마찬가지로 instance B는 listener B를 local listener로 listener A를 remote listener로 갖는다.

각각의 instance의 PMON process는 주기적으로 해당 node에 대한 profile statistic을 listener에게 알려주어 listener로 들어오는 client connection request는 load가 적은 노드로 connection request를 redirect 하기도 한다.







BFT (BigFileTablespace)는 oracle 10g에서 사용가능한 tablespace type 이다. Bigfile Tablespace는 하나의 매우 큰 datafile을 갖는 tablespace를 말한다. Bigfile tablespace의 개념이 만들어 지면서 기존의 일반적인 tablespace는 자동으로 smallfile tablespace로 불린다.

SMALLFILE tablespace와 BIGFILE tablespace는 database내에 공존할 수 있다.

      SQL> select TABLESPACE_NAME, BIGFILE from DBA_TABLESPACES;

      TABLESPACE_NAME                BIG
      ------------------------------ ---
      SYSTEM                         NO
      UNDOTBS1                       NO
      SYSAUX                         NO
      TEMP                           NO
      USERS                          NO
      TEST_BIG                       YES

BIGFILE tablespace는 하나의 datafile만을 사용하게 된다. 이는 datafile에 대한 관리가 간편해 질 수 있다는 것을 의미하며, 일반적인 SMALLFILE tablespace에서 사용하던 다음과 같은 option도 역시 사용가능하다.

      SQL> ALTER TABLESPACE test_big AUTOEXTEND OFF;
      Tablespace altered.

      SQL>  ALTER TABLESPACE test_big AUTOEXTEND ON;
      Tablespace altered.

      SQL> ALTER TABLESPACE test_big RESIZE 2M;
      Tablespace altered.

BIGFILE tablespace는 다음과 같이 사용될 수 있다.

      --> ASM (Automatic Storage Management)
      --> a logical volume manager supporting striping/RAID
      --> dynamically extensible logical volumes
      --> Oracle Managed Files (OMF)

만약 default로 BIGFILE tablespace를 생성하고 싶다면 다음과 같이 tablespace 생성 option을 바꿀 수 있다.

    SQL> select * from database_properties
          where property_name='DEFAULT_TBS_TYPE';

     PROPERTY_NAME        PROPERTY_VALUE  DESCRIPTION
     -------------------- --------------- ------------------------
     DEFAULT_TBS_TYPE     SMALLFILE       Default tablespace type
   
     SQL> ALTER DATABASE SET DEFAULT bigfile TABLESPACE;
     Database altered.

     SQL> CREATE TABLESPACE big_test2
          DATAFILE '/ORACLE10/ORCL/testbig2.dbf' size 1M;

     Tablespace created.

     SQL> select TABLESPACE_NAME, BIGFILE from DBA_TABLESPACES;

     TABLESPACE_NAME                BIG
     ------------------------------ ---------
     ...
     USERS                          NO
     TEST_BIG                       YES
     TEST_BIG2                      YES

앞서 말한 것 처럼 BIGFILE tablespace에는 datafile 추가는 불가능하다.

      SQL> ALTER TABLESPACE test_big
           ADD DATAFILE '/ORACLE10/ORCL/testbig2.dbf' size 500G;

      ALTER TABLESPACE test_big
      *
      ERROR at line 1:
      ORA-32771: cannot add file to bigfile tablespace


여러 datafile을 사용해 큰 공간을 확보하는 방법에 비해 하나의 큰 파일로 tablespace를 구성하는 이 방법은 관리상의 이점이 있으나, backup이나 recovery 단계에서는 큰 file에 대한 backup과 file 손상이 발생할 경우 전체를 restore해야 하기 때문에 오히려 부담이 될 수 도 있다. 따라서 각각의 tablespace type에 대한 이점과 단점을 구분하고 적용해야할 것이다.

참고 문서 :
Note 262472.1  10g: BIGFILE Type Tablespaces Versus SMALLFILE Type
Note 243245.1  10G New Storage Features and Enhancements
Note 1057891.6 HOW TO USE DBMS_ROWID PROCEDURE AND FUNCTIONS








오라클의 SQL 언어를 수행할 수 있는 tool인 sqlplus에서는 변수를 지원한다.

변수는 다들 잘 알고 있듯이 특정 변경되는 값을 변수값으로 입력 받아 이를 해당 변수와 치환해주게 된다. SQLPLUS에서 지원하는 변수는 2가지로 변수값이 지속되지 않는 변수 temporary variable과 그 변수 값이 지속되어 해당 값을 삭제, 재정의 또는 sqlplus를 빠져나갈때 까지 유지하는 defined variable이 있다.

Temporary variable의 경우 SQL구문의 수행 시 해당 변수값을 입력 받아 사용되며 동일한 SQL이 재수행 되더라도 해당 변수값을 다시 묻게 된다.

SQL> SELECT product_id, name, price
  2  FROM products
  3  WHERE product_id = &product_id_var;

Enter value for product_id_var: 2
old   3: WHERE product_id = &product_id_var
new   3: WHERE product_id = 2

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         2 Chemistry                              30

SQL> /
Enter value for product_id_var: 3
old   3: WHERE product_id = &product_id_var
new   3: WHERE product_id = 3

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         3 Supernova                           25.99

위의 예를 보면 product_id_var 변수의 값이 3으로 치환되고 이에 대한 결과를 보여준다. 이러한 old/ new 값에 대한 output은 VERIFY option을 이용해 출력을 제어할 수 있다.

SQL> SET VERIFY OFF
SQL> /

Enter value for product_id_var: 4

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         4 Tank War                            13.95

다시 변수에 대한 old/ new value를 출력하려면 VERIFY option을 on으로 설정한다.

SQL> SET VERIFY ON

또 "SET DEFINE" 명령을 이용해 변수 앞의 ampersand (&)도 바꿔 줄 수 있다. UNIX 상에서 해당 character가 다른 의미로 사용되고 있는 경우를 제외하면 별로 사용할 일은 없을 듯 하지만..

SQL> SET DEFINE '#'
SQL> SELECT product_id, name, price
  2  FROM products
  3  WHERE product_id = #product_id_var;

Enter value for product_id_var: 5
old   3: WHERE product_id = #product_id_var
new   3: WHERE product_id = 5

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         5 Z Files                             49.99

다시 원래의 ampersand (&)로 바꾸려면 다음과 같이 수행해 준다.

SQL> SET DEFINE '&'

가끔 동일한 변수를 같은 SQL문에서 사용하게 되는데 이때 동일한 변수라도 사용한 개수만큼 다시 값을 묻게 된다. 이러한 현상을 막기 위해서는 ampersand를 두개로 지정해 준다.  (&&)

SQL> SELECT name, &col_var
  2  FROM &table_var
  3  WHERE &col_var = &col_val;

Enter value for col_var: product_type_id
old   1: SELECT name, &col_var
new   1: SELECT name, product_type_id

Enter value for table_var: products
old   2: FROM &table_var
new   2: FROM products

Enter value for col_var: product_type_id
Enter value for col_val: 1
old   3: WHERE &col_var = &col_val
new   3: WHERE product_type_id = 1

NAME                           PRODUCT_TYPE_ID
------------------------------ ---------------
Modern Science                               1
Chemistry                                    1

You can avoid having to repeatedly enter a variable by using &&. For example:

SELECT name, &&col_var
FROM &table_name
WHERE &&col_var = &col_val;


Defined Variable은 선언된 변수를 여러번 사용이 가능하다. 이 값은 재정의 되거나, 변수값이 삭제되거나, SQLPLUS를 빠져나가기 전까지 사용가능하다. Defined variable은 "DEFINE" 명령을 통해 변수값이 설정, 조회하며 "UNDEFINE"명령에 의해 변수값이 삭제된다. 또 "ACCEPT" 명령을 통해 해당 변수값을 유저로 부터 받아들일 수 있다.

SQL> DEFINE product_id_var = 7
SQL> DEFINE product_id_var

DEFINE PRODUCT_ID_VAR          = "7" (CHAR)

SQL> SELECT product_id, name, price
      2  FROM products
      3  WHERE product_id = &product_id_var;

old   3: WHERE product_id = &product_id_var
new   3: WHERE product_id = 7

ACCEPT 명령은 해당 변수의 type과 입력 받을 format, prompt 형태 등의 다양한 지정이 가능하다. HIDE option의 경우 9i 이전엔 asterisk characters (*)로 보여주지만 10g 부터는 아무런 문자를 출력하지 않는다.

ACCEPT variable_name [type] [FORMAT format] [PROMPT prompt] [HIDE]

SQL> ACCEPT customer_id_var NUMBER FORMAT 99 PROMPT 'Customer id: '
Customer id: 5

SQL> ACCEPT date_var DATE FORMAT 'DD-MON-YYYY' PROMPT 'Date: '
Date: 12-DEC-2006

SQL> ACCEPT password_var CHAR PROMPT 'Password: ' HIDE
Password: *******

현재 설정되어 있는 변수값을 clear 하기 위해서는 UNDEFINE 명령이 사용된다. 물론 재정의나 SQLPLUS를 logout 하면 해당 값은 clear 되지만 다른 script를 계속 수행한다면 예상치 못한 상황을 만나지 않기 위해 깨끗이 정리할 필요가 있다.

SQL> UNDEFINE customer_id_var
SQL> UNDEFINE date_var
SQL> UNDEFINE password_var





AWR table map을 찾던 중 "A Tour of the AWR Tables"라는 NOCOUG Summer Conference에서 소개된 자료를 확인할 수 있었습니다. 제목을 보니 AWR관련 table에 대한 여러 힌트를 얻을 수 있을 듯해서 자료를 보니 "load spike"를 찾기 위한 좀 다른 방법을 설명하고 있네요.

이전 " oracle awr 설정 시 고려해야 할 점" post에서 강호동, 이건희 회장까지 등장시키며 AWR 자료 수집 주기를 15 ~20분으로 줄여야한다고 글을 썼는데, 이 글의 저자는 "In my experience, the hourly interval is appropriate." 라고 하더군요. 저자는 DBA_HIST_SYSSTAT의 DB time, elapsed time 등의 load profile 자료를 사용하지 않고 1초마다 수집되는 ASH의 자료를 이용해 "load spike"를 찾는 방법을 제시하고 있습니다.

오옷 .. 그런 방법이.. 근데, 그 외의 성능관련 정보는 어떻게... --;
추가적인 AWR 관련 table을 이용한 문제가 될만한 SQL 찾는 방법 등도 같이 소개합니다.

Average Active Session이 튀는 시간 찾기.
column sample_hour format a16

select
   to_char(round(sub1.sample_time, 'HH24'), 'YYYY-MM-DD HH24:MI') as sample_hour,
   round(avg(sub1.on_cpu),1) as cpu_avg,
   round(avg(sub1.waiting),1) as wait_avg,
   round(avg(sub1.active_sessions),1) as act_avg,
   round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
   ( -- sub1: one row per second, the resolution of SAMPLE_TIME
     select
        sample_id,
        sample_time,
        sum(decode(session_state, 'ON CPU', 1, 0))  as on_cpu,
        sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
        count(*) as active_sessions
     from
        dba_hist_active_sess_history
     where
        sample_time > sysdate - (&hours/24)
     group by
        sample_id,
        sample_time
   ) sub1
group by
   round(sub1.sample_time, 'HH24')
order by
   round(sub1.sample_time, 'HH24')
;

SAMPLE_HOUR          CPU_AVG   WAIT_AVG    ACT_AVG ACT_VAR_MEAN
----------------- ---------- ---------- ---------- ------------
2008-04-16 07:00         1.4         .4        1.8           .6
2008-04-16 08:00         1.8         .5        2.3            1
2008-04-16 09:00         2.3         .5        2.8          1.3
2008-04-16 10:00         2.6         .6        3.2          2.3
2008-04-16 11:00         3.5         .6        4.1          2.3
2008-04-16 12:00         2.4         .6          3          1.1
2008-04-16 13:00         2.3         .6        2.9            1
2008-04-16 14:00         3.7        2.7        6.4         95.4   <== spike in variance
2008-04-16 15:00         3.1         .7        3.8          1.9
2008-04-16 16:00         2.9         .7        3.6          1.6
2008-04-16 17:00         2.3         .4        2.7           .9
2008-04-16 18:00         2.1         .6        2.7          2.6

Average Active Session이 튀는 시간대의 특정 분단위 찾기.
column sample_minute format a16

select
   to_char(round(sub1.sample_time, 'MI'), 'YYYY-MM-DD HH24:MI') as sample_minute,
   round(avg(sub1.on_cpu),1) as cpu_avg,
   round(avg(sub1.waiting),1) as wait_avg,
   round(avg(sub1.active_sessions),1) as act_avg,
   round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
   ( -- sub1: one row per second, the resolution of SAMPLE_TIME
     select
        sample_id,
        sample_time,
        sum(decode(session_state, 'ON CPU', 1, 0))  as on_cpu,
        sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
        count(*) as active_sessions
     from
        v$active_session_history
     where
        sample_time > sysdate - (&minutes/1440)
     group by
        sample_id,
        sample_time
   ) sub1
group by
   round(sub1.sample_time, 'MI')
order by
   round(sub1.sample_time, 'MI')
;

SAMPLE_MINUTE           CPU_AVG   WAIT_AVG    ACT_AVG ACT_VAR_MEAN
-------------------- ---------- ---------- ---------- ------------
2008-04-16 13:54:00           3          1          4            0
2008-04-16 13:55:00         3.2         .3        3.5           .7
2008-04-16 13:56:00         4.2         .3        4.5          3.4
2008-04-16 13:57:00         3.8         .8        4.7           .7
2008-04-16 13:58:00         6.3          1        7.3          1.6
2008-04-16 13:59:00         3.4         .4        3.8           .2
2008-04-16 14:00:00         8.3         .5        8.8          1.8
2008-04-16 14:01:00        10.7        2.2       12.8           .5
2008-04-16 14:02:00         3.5         .7        4.2           .5
2008-04-16 14:03:00         2.6        1.2        3.8          1.5
2008-04-16 14:04:00         3.3        1.2        4.5          1.3
2008-04-16 14:05:00         8.2         .7        8.8          2.1
2008-04-16 14:06:00         6.7        1.3          8          1.1
2008-04-16 14:07:00         4.7        3.2        7.8          3.7
2008-04-16 14:08:00        20.5      109.8      130.3          170  <== spike in AAS
2008-04-16 14:09:00           6        1.3        7.3         10.3
2008-04-16 14:10:00         2.6         .4          3           .8
2008-04-16 14:11:00           4         .3        4.3          1.1
2008-04-16 14:12:00         5.7         .8        6.5          1.6
2008-04-16 14:13:00           3         .3        3.3           .7
2008-04-16 14:14:00         1.8         .7        2.5           .6
2008-04-16 14:15:00         3.3          2        5.3          2.2


 


다음의 그림은 AWR 내의 wait event관련 view의 primary/reference key 들입니다.
필요한 view를 각각의색에 맞게 join하여 사용할 수 있습니다.


다음은 Oracle 10g R2 Reference 메뉴얼 링크입니다.
해당 view가 어떤 정보들을 가지고 있는지 확인할 수 있습니다.

DBA_HIST_SERVICE_WAIT_CLASS
DBA_HIST_SESS_TIME_STATS
DBA_HIST_SYSTEM_EVENT
DBA_HIST_ACTIVE_SESS_HISTORY
DBA_HIST_BASELINE
DBA_HIST_BG_EVENT_SUMMARY
DBA_HIST_WAITCLASSMET_HISTORY
DBA_HIST_WAITSTAT
DBA_HIST_EVENT_NAME
DBA_HIST_SNAPSHOT







Oracle AWR 관련 table 중 SQL에 관련된 table 들과 reference key를 그린 그림입니다.
다음의 table join으로 과거의 SQL 수행 정보 등을 찾을 수 있습니다. 


아래의 SQL은 Oracle Note에 나온 AWR 수집 데이터에서 SQL 관련 정보를 추출하는 SQL 입니다.

SQL ordered by CPU time:

column sqltext format a65
set pages 60
set lines 132
set trims on

select x.*, dbms_lob.substr(d.sql_text) sqltext
from (
select distinct sql.dbid, sql.sql_id, sum(sql.cpu_time_total)
cpu_total
from dba_hist_sqlstat sql
where sql.snap_id between &begin_id and &end_id
group by
sql.dbid, sql.sql_id
order by
sum(sql.cpu_time_total) desc
) x,
dba_hist_sqltext d
where d.sql_id = x.sql_id
and d.dbid = x.dbid
and rownum <= 10;

SQL ordered by Executions:

column sqltext format a30
select distinct sql.sql_id, dbms_lob.substr(d.sql_text) sqltext, sum(sql.executions_total) from
dba_hist_sqlstat sql,
dba_hist_snapshot s,
dba_hist_sqltext d
where
s.snap_id between &begin_id and &end_id
and rownum <= 10
group by sql.sql_id, dbms_lob.substr(d.sql_text)

SQL ordered by Disk Reads:

column sqltext format a30
select distinct sql.sql_id, dbms_lob.substr(d.sql_text) sqltext, sum(sql.
disk_reads_total) from
dba_hist_sqlstat sql,
dba_hist_snapshot s,
dba_hist_sqltext d
where
s.snap_id between &begin_id and &end_id
and rownum <= 10
group by sql.sql_id, dbms_lob.substr(d.sql_text)


참고 : Note 833323.1 Customized queries from AWR

 
100명의 사람들이 있습니다.
이 사람들의 어떤 평균 수치값이 주어져 있습니다.

이 그룹에 주어진 평균 수치가 만약 몸무게나 키, 허리 둘레 등에 대한 평균이라고 한다면,
이 그룹 중 강호동처럼 (몸무게가 어떤지는 잘은 모릅니다^^;) 몸무게가 다른 사람들 보다 많이 나가는 사람을 찾을 수 있을가요? 

물론 평균 수치가 재산, 보유주택 가격 등에 대한 평균이고 이 중 이건희 회장 같은 사람이 있다면 당연히 그 평균값은 거의 한 사람의 값과 같겠죠. 따라서 일반적이지 않은 사람이 섞여있다고 판단할 수 있습니다.

(이 내용은 [블랙스완]이란 책에서 경제학자들의 일반화 오류에 대한 예입니다. 물론 거긴 강호동이나 이건희 회장은 나오지 않습니다만...ㅋㅋ)

database system 내에서 수행되는 많은 AP들이 있습니다.
AWR은 현황에 대한 분석 뿐 아니라 많은 경우 비 정상적으로 문제를 발생하는 AP 등을 찾는데도 자주 사용됩니다.
대부분의 비정상적인 AP는 일반 AP에 비해 2~3배 정도의 resource를 더 많이 사용하죠.

위의 예처럼 대부분의 수치를 차지하는 이건희 회장 같은 AP가 있다면, 이건 계발에 대한 검증이나 운영장비로의 적용 단계에 문제가 있다고 판단해야 합니다 !! 또 AWR을 보지 않아도 찾아내기 쉽죠.
(개인적으로 이건희 회장을 좋아하진 않지만, 뭐 다른 의도가 있는건 아닙니다. 얼마전 뉴스에서 제일 돈이 많다고해서 부럽기도 하고.. 뭐)

서론이 너무 길었지만,
특정 시간대에 문제의 소지가 있는 AP가 있는지에 대한 판단은 그 표본 범위가 작을 수록 더 쉬워지기 마련입니다.
즉 강호동이 10000명 중 하나일 경우와 10명 중 하나일 경우 전체 평균에 기여하는 부분이 달라지죠.

oracle 10g 부터 AWR이 default로 수행됩니다. 1주일의 보관 주기와 1시간의 수집 주기를 가지고 있죠.
하지만 대부분의 제가 본 AWR 주기는 (얼마 보진 않았지만.. --;) default로 설정되어 사용하고 있습니다.

만약 특정 시간동안 hang이 발생했거나 resource contention이 발생했다면 이에 대한 증거들인 database statistic 값들이 1시간이란 시간 안에서 "나 여기 있어요" 하고 말할 수 있을까요?

AWR의 데이터를 한달에 한번씩 수집해서 "이번달엔 이 정도 일했습니다."라고 말할 용도가 아니라면
적어도 15분 ~ 30분 정도의 수집 주기와 AP 수행 주기의 2배 만큼은 보관해야 합니다.
 
또 한가지 AWR에는 base-line을 기록할 수 있습니다.
base-line은 말 그대로 기준선이 됩니다. 이 base-line을 사용하면 "막연히 이번달엔 지난달 보다 좀 느려진것 같아요" 대신 "이번달은 지난달 보다 load가 1.5배 정도 더 발생해 전체적인 속도가 늦어졌어요" 라고 말할 수 있겠죠...

다 쓰고 보니 별 내용이 없군요..
하지만 위의 예제는 꼭 써먹구 싶었습니다... ㅋㅋㅋ

* 현재 AWR 설정 값 확인

select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL       RETENTION
------------------- -------------------
+00000 01:00:00.0   +00007 00:00:00.0

* AWR 설정값 변경

begin
   dbms_workload_repository.modify_snapshot_settings (
      interval => 20,
      retention => 2*24*60
   );
end;

* Base-line 설정

exec dbms_workload_repository.create_baseline (56,59,'apply_interest_1'):

* Base-line 확인

select * from dba_hist_baseline;

      DBID BASELINE_ID BASELINE_NAME        START_SNAP_ID END_SNAP_ID
---------- ----------- -------------------- ------------- -----------
4133493568           1 apply_interest_1                56          59





dbms_stat로 table/index에 대한 통계 정보가 새로 생성하면
이후 새로 생성되는 cursor의 경우 새로운 통계정보에 의한 sql plan이 만들어 지지만,
기존의 library cache에 저장된 sql은 새로운 sql plan을 얻기 위해 invalidation 되어
해당 sql은 다시 hard pare 단계를 거쳐 새로운 통계정보에 의한 sql plan을 획득하게 됩니다.

이때 shared pool내의 많은 sql 들이 hard parse 되면서
latch contention과 CPU 자원을 많이 소모하게 되는데,
이러한 현상은 "hard-parse spike"라고 합니다.

이러한 현상은 운영자들에게는 많은 부담이 될텐데요..

이러한 현상을 막기 위해 NO_INVALIDATE option을 이용해 기존의 sql에 대한 invalidation을 제어합니다. 그러나 이는 새로운 통계정보에 의한 sql plan을 당장 사용하지 못하니 이 또한 그리 좋은 방법은 아니겠죠..

10g 부터는 auto invalidate 기능이 추가 되어 "hard parse spike"를 많이 감소시켰다고 하네요..

다음은 metalink의 rolling cursor invalidation에 대한 설명입니다...


문서 557661.1   
Rolling Cursor Invalidations with DBMS_STATS in Oracle10g


Purpose

Starting with Oracle10g, the DBMS_STATS package offers the AUTO_INVALIDATE option for the NO_INVALIDATE parameter of its GATHER_xxx_STATS and DELETE_xxx_STATS procedures. This parameter allows the user to specify when to invalidate dependent cursors i.e. cursors cached in the library cache area of the shared pool which reference a table, index, column or fixed object whose statistics are modified by the procedure call.
According to the documentation the values NO_INVALIDATE can take are:
  • TRUE: does not invalidate the dependent cursors
  • FALSE: invalidates the dependent cursors immediately
  • AUTO_INVALIDATE (default): have Oracle decide when to invalidate dependent cursors
This article describes details of how AUTO_INVALIDATE works.

Scope and Application

This article may be of interest to DBAs interested in the behaviour of DBMS_STATS with respect to cursor invalidations.

Rolling Cursor Invalidations with DBMS_STATS in Oracle10g

When statistics are modified by DBMS_STATS, new cursors not yet cached in the shared pool will use them to get execution plans. Existing cursors cached in the shared pool cannot update their execution plans however. Instead, such cursors are invalidated and new versions (children cursors) are created which get execution plans based on the updated statistics. This involves a hard-parse operation which is more expensive in resource consumption than a soft-parse which simply reuses a cached cursor.
An important question to consider is: when does this invalidation occur in time after the statistics have been modified ?

Cursor Invalidations on Gathering Statistics prior to Oracle10g

In releases prior to Oracle10g gathering statistics using DBMS_STATS resulted in immediate invalidations of dependent cached cursors, unless NO_INVALIDATE was set to TRUE. An invalidation of a cached cursor means it has to be hard-parsed the next time it is executed. If large numbers of such cursors had to be invalidated and immediately re-executed e.g. due to DBMS_STATS being used on a popular object during a time period of heavy user workload, this would result in a hard-parse spike which could have serious effects on performance including high CPU usage, heavy library cache and shared pool latch contention with subsequent slowdown in application user response times.

Setting NO_INVALIDATE to TRUE would prevent such spikes but this meant that cursors would not notice the updated object statistics and would continue using existing execution plans until hard-parsed. Such a hard parse could happen on a cursor reload (i.e. on the next execution following the cursor being automatically flushed out of the shared pool due to inactivity and heavy usage of other cursors) or after a manual flushing of the shared pool (which could itself also result in hard-parse spikes as most of the flushed-out cursors would need to do a hard parse on their next execution.)

Cursor Invalidations with Oracle10g and AUTO_INVALIDATE

With the AUTO_INVALIDATE option the goal is to spread out the cursor invalidations over a time period long enough for hard-parses not to cause noticeable spikes.

In this way a cached cursor depending on an object whose statistics have been modified by DBMS_STATS will be invalidated as follows:
  • when DBMS_STATS modifies statistics for an object, all current cached cursors depending on this object are marked for rolling invalidation. Let's call this time T0.
  • the next time a session parses a cursor marked for rolling invalidation, it sets a timestamp. This timestamp can take a random value up to _optimizer_invalidation_period sec from the time of this parse. The default for this parameter is 18000 sec i.e. 5 hours. Let's call the time of this parse T1 and the timestamp value Tmax. On this (first) parse we reuse the existing cursor i.e. we do not hard-parse and do not use the modified statistics to generate a new plan (it is a soft parse.)
  • on every subsequent parse of this cursor (which is now marked for rolling invalidation and timestamped) we check whether the current time T2 exceeds the timestamp Tmax. If not, we reuse the existing cursor again, as happened on the first (soft) parse at time T1. If Tmax has been exceeded, we invalidate the cached cursor and create a new version of it (a new child cursor) which uses the new statistics of the object to generate its execution plan. The new child is marked ROLL_INVALID_MISMATCH in V$SQL_SHARED_CURSOR to explain why we could not share the previous child.
From the above descriptions, it follows that:
  • a cursor which is never parsed again after being marked for rolling invalidation will not be invalidated and may eventually be flushed out of the shared pool if memory becomes scarce
  • a cursor which is only parsed once after being marked for rolling invalidation will not be invalidated (it will only be timestamped) and again may be eventually flushed out if memory in the shared pool becomes scarce
  • cursors which are regularly reused will become invalidated on the next parse that happens after the timestamp Tmax has been exceeded
It should be clear that the above method is efficient in that it incurs the overhead of invalidations only for frequently reused cursors.

Exception: parallel SQL are immediately invalidated in order to ensure consistency between execution plans of slaves and Query Coordinator across multiple RAC instances. This is not a problem as parallel SQL are usually heavy and therefore hard-parse resources are insignificant to their total resource usage.





Linux, iSCSI 환경에서 Oracle RAC 10g Release 2 클러스터 설치하기


저자 - Jeffrey Hunter

(2,500달러 이하의) 저렴한비용으로 Oracle RAC 10g Release 2 클러스터 개발환경을 셋업하고 설정하는 방법을 배워 보십시오.


이 정도 가격이면 테스트 장비 용으로 구축해 볼만할 거 같긴하네요...




오라클 10g documents library 입니다.

* oracle 10g release 2 (10.2) documents library 
* oracle 10g release 1 (10.1) documents library





오라클에서는 oracle의 성능을 측정하기 위한 몇몇 script를 제공해 왔습니다.

기본적으로 제공되던 script 들은 oracle version에 따라 utlbstat/utlestat, statspack, awr report 등의 형태로 제공되고 있습니다.

utlbstat/utlestat은 시작시점과 끝시점에 수행하여 구간의 데이터를 OS상의 text file로 출력하고,
oracle 8i 부터 제공되었던 statspack은 job이나 cron에 등록하여 구간별 데이터를 얻을 수 있게 되었습니다. awr report는 oracle 10g부터 제공되었으며, sql 수행정보등 level에 따라 좀더 다양한 성능관련 정보들을 보여 줍니다.

awr report는 기본적으로 60분 간격으로 7일간의 데이터를 수집, 보관합니다. 이 데이터는 SYSAUX tablespace에 보관되며, 다음의 performance view에 데이터를 저장하게 됩니다.

V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
V$METRIC - Displays metric information.
V$METRICNAME - Displays the metrics associated with each metric group.
V$METRIC_HISTORY - Displays historical metrics.
V$METRICGROUP - Displays all metrics groups.

DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
DBA_HIST_BASELINE - Displays baseline information.
DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
DBA_HIST_SNAPSHOT - Displays snapshot information.
DBA_HIST_SQL_PLAN - Displays SQL execution plans.
DBA_HIST_WR_CONTROL - Displays AWR settings.

AWR report를 생성하기 위해서는 $ORACLE_HOME/rdbms/admin 밑에 있는 awr로 시작하는 몇몇 script를 수행하여 성능 보고서를 얻게 됩니다. 주로 awrrpt.sql이나 awrsqrpt.sql로 특정 구간의 성능데이터나 특정 구간에서의 SQL 수행정보를 얻곤 하죠.



다음은 awr관련 script 들입니다.

1)The awrrpt.sql SQL script generates an HTML or text report that displays
statistics for a range of snapshot Ids.

2)The awrrpti.sql SQL script generates an HTML or text report that displays
statistics for a range of snapshot Ids on a specified database and instance.

3) The awrsqrpt.sql SQL script generates an HTML or text report that displays
statistics of a particular SQL statement for a range of snapshot Ids. Run this report
to inspect or debug the performance of a SQL statement.

4) The awrsqrpi.sql SQL script generates an HTML or text report that displays
statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.

5) The awrddrpt.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods.

6) The awrddrpi.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods on a specific database and instance.

awr을 control 하기 위한 몇몇 procedure가 제공되는데,
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_setting 은 interval이나 보관기간에 대한 설정을 위해 사용되며, DBMS_WORKLOAD_REPOSITORY.create_baseline는 성능 판단의 기준이 되는 baseline을 만들때 사용됩니다.

자세한 procedure 사용법은 다음과 같습니다.

How to Modify the AWR SNAP SHOT SETTINGS:
=====================================

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
    interval  => 30);          -- Minutes. Current value retained if NULL.
END;
/

Creating the Baseline:
======================

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => 10,
    end_snap_id   => 100,
    baseline_name => 'AWR First baseline');
END;
/

Dropping the AWR baseline:
==========================

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(
(row_snap_id=>40,
High_snap_id=>80);
END;
/

Creating SNAPSHOT Manually:
===========================

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_snapshot();
END;
/

awr report는 수행 주기와 보관주기를 잘 선택해 놓으면, 정작 중요할 때 긴히 써먹을 수 있으니, SYSAUX tablespace 공간 아까워하지 말고, 좀 넉넉히 셋팅하는 것이 좋습니다..

위의 내용은 metalink.oracle.com의 아래 문서를 참조했습니다.

note 748642.1 What is AWR( Automatic workload repository ) and How to generate the AWR report?




+ Recent posts