위의 decode function은 미리 지정된 value와 search_value가 같을 경우 result를, 그렇지 않은 경우 default_value를 출력한다. 이와 같이 decode()는 SQL에서 PL/SQL없이 IF-THEN-ELSE logic을 구현하고 있다.
다음은 decode()의 간단한 동작 예이다.
SELECT DECODE(1, 1, 2, 3)
FROM dual;
DECODE(1,1,2,3)
---------------
2
SELECT DECODE(1, 2, 1, 3)
FROM dual;
DECODE(1,2,1,3)
---------------
3
다음의 예는 more_products table의 available column의 값을 비교해 원하는 string을 출력한다. available column의 값이 'Y'일 경우 "Product is available'을 출력하고, 'Y'가 아닐 경우 'Product is not available"을 출력한다.
SELECT prd_id, available,
DECODE(available, 'Y', 'Product is available',
'Product is not available')
FROM more_products;
PRD_ID A DECODE(AVAILABLE,'Y','PR
---------- - ------------------------
1 Y Product is available
2 Y Product is available
3 N Product is not available
4 N Product is not available
5 Y Product is available
다음은 product table의 product_type_id column의 값들에 따라 원하는 string을 출력한다. 즉 product_type_id가 1일 경우 'Book', 2일 경우 'Video', 3일 경우 'DVD', 4일 경우 'CD', 기타 다른 값일 경우 'Magazine'을 출력한다.
PRODUCT_ID PRODUCT_TYPE_ID DECODE(P
---------- --------------- --------
1 1 Book
2 1 Book
3 2 Video
4 2 Video
5 2 Video
6 2 Video
7 3 DVD
8 3 DVD
9 4 CD
10 4 CD
11 4 CD
12 Magazine
Decode()은 CASE문에 비해 depth가 깊어지면 성능이 떨어지는 것으로 알려져 있다. depth가 깊은 SQL의 경우 CASE()와의 성능 비교가 필요할 수 있다.
NAME DEST_ID THREAD# SEQUENCE# STANDBY_D ARCHIVED APPLIED STATUS COMPLETION_TIM ------------------------------------------------------------ ---------- ---------- ---------- --------- --------- --------------------------- ------------- /ARC2/2_21989_697581426.arc 1 2 21989 NO YES YES A 03/23 14:39:46 /ARC3/3_21296_697581426.arc 1 3 21296 NO YES YES A 03/23 15:06:36 /ARC1/1_47740_697581426.arc 1 1 47740 NO YES IN-MEMORY A 03/23 16:05:58
NAME VALUE UNIT TIME_COMPUTED ------------------------------ -------------------------------------------------- ------------------------------ ------------------------------ apply finish time +00 00:00:03.5 day(2) to second(1) interval 23-MAR-2010 16:24:19 apply lag +00 00:00:00 day(2) to second(0) interval 23-MAR-2010 16:24:19 estimated startup time 32 second 23-MAR-2010 16:24:19 standby has been open N 23-MAR-2010 16:24:19 transport lag +00 00:00:00 day(2) to second(0) interval 23-MAR-2010 16:24:19
서점에서 몇번 오라클 관련 서적을 샀었는데, 많이 실망한 적이 많이 있었습니다.
그때는 오라클에 대해 잘 모를 때였던 거 같은데,
나중에 보니 tablespace를 작업공간이라고 해석해 놓았던 책도 있더군요.. --;
다음의 리스트는 오라클 전문가가 되기 위해 읽을만한 추천책입니다.
물론 이제 막 십년 밖에 안된 야매인 제가 추천해드리는 책은 아니고, http://oracledoug.com 블로그를 운영하는 "Douglas Ian Burns"라는 사람이 추천한 책입니다.
이분은 자그마치 18년의 경력을 갖고 있다시는 군요..
저도 18년 정도 하면 이분 정도의 공력을 갖을 수 있을까..
걱정입니다.. --;
1. Chris Date's Database In Depth from O'Reilly
2. Oracle Concepts manual
3. Expert One-on One: Oracle Written by Tom Kyte
4. Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions
5. Practical Oracle 8i
6. Cost Based Oracle: Fundamentals
7. Optimizing Oracle Performance
8. Oracle Wait Interface: A Practical Guide to Performance Diagnostics and Tuning
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>
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
Oracle EM grid control에서 특정 event가 발생하면 해당 내용을 Critical, Warning으로 구분해서 alert 발생시킵니다.
이 alert 내용을 mail로 delivery 할수 있고 user PL/SQL을 이용해 다른 여러 응용도 가능합니다.
다음의 SQL script는 EM에서 발생한 alert message와 alert의 상태, 그리고 이에 대한 delivery 수행이 잘 되었는지 확인하는 방법입니다.
* Oracle 11g Release 1에 Oracle Grid conrol 10.2.0.5 환경에서 테스트 되었습니다.
SQL> select TARGET_NAME||' /'||METRIC_NAME||' /'||KEY_VALUE METRIC_NAME,TIMESTAMP,DELIVERED,substr(b.MESSAGE,1,100) message,ALERT_STATE,substr(b.DELIVERY_MESSAGE,1,50) DELIVERY_MESSAGE
from sysman.mgmt_notification_log a, SYSMAN.MGMT$ALERT_NOTIF_LOG b
where a.SOURCE_OBJ_GUID(+) =b.SOURCE_OBJ_GUID
and to_char(TIMESTAMP,'MMDD') = '0311'
-- and target_name = 'LISTENER_PMGT_FLPEME01'
-- and DELIVERED ='Y'
order by TIMESTAMP
/
METRIC_NAME TIMESTAMP DEL MESSAGE ALERT_STAT DELIVERY_MESSAGE
--------------------------------------- -------------- --- -------------------------------------------------- ---------- --------------------------------------------------
TEST /problemTbsp /TS_TEST 03/11 02:07:51 Y Tablespace [TS_TEST] is [97 percent ] full Critical Called PL/SQL procedure SYSMAN.LOGGING_ALERT
TEST /problemTbsp /TS_TEST 03/11 02:09:22 Y Tablespace [TS_TEST] is [97 percent ] full Critical Called PL/SQL procedure SYSMAN.LOGGING_ALERT
TEST /problemTbsp /UNDOTBS1 03/11 02:57:45 Y Tablespace [UNDOTBS1] is [97 percent ] full Critical Called PL/SQL procedure SYSMAN.LOGGING_ALERT
TEST /problemTbsp /UNDOTBS1 03/11 02:59:16 Y Tablespace [UNDOTBS1] is [97 percent ] full Critical Called PL/SQL procedure SYSMAN.LOGGING_ALERT
하나는 익히 알고 있는 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은 언제 생긴거람.. ?)
v$ view는 memory structure data를 보여주며, dba_ table(?)은 system tablespace
상의 data dictoinary data를 보여줍니다. 10g에서 awr 관련 view들이 만들어져서 dba_hist view
등에서 memory structure statistic data를 system tablespace에 저장해서 보여주기도 하죠.
아래의 v$fixed_view_definition view는 오라클의 fixed view의 description을 보여주는 view 입니다.
아래의 내용은 얼마전 bind 변수 값을 찾을 방법이 없을까 .. 해서 한번 뒤져보느라 query 해본 내용입니다.
역시 그런 방법은 없는 것 같더군요.. --;
SQL> select view_definition from v$fixed_view_definition where view_name = 'GV$SQL_BIND_CAPTURE';
기존의 통계정보를 다른 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');
Oracle INVISIBLE index는 Oracle 11g new feature 입니다.
말 그대로 보이지 않는 index 입니다. 여기서 보는 주체는 oracle optimizer가 됩니다.
즉 index는 존재하지만 optimizer는 이를 기준으로 plan을 생성하지 않습니다.
그러나 해당 index의 table에 대한 DML 변경 내역은 모두 index에 적용되게 됩니다.
이는 index 생성에 따른 혹은 index 삭제에 따른 전체 성능, 일부 성능 측정에 도움이 될 만한 feature가 아닌가 싶습니다.
Invisible index 생성
SQL> Create index invisible_index on table(column) invisible;
Invitible index 사용 정의
SQL> alter session set optimizer_use_invisible_indexes=true;
Invisible index를 visible로 변경
SQL> alter index invisible_index visible;
visible index를 invisible로 변경
SQL> alter index invisible_index invisible;
Oracle과 다른 DB와의 차이점을 보면 굉장히 손이 많이 갑니다.
oracle 10g, 11g가 나오면서 운영의 자동화와 간소화 기능이 많이 추가 되긴 했지만요..
그런데, 이말을 거꾸로 말해보자면, 사용자의 역량에 따라 tuning 할 수 있는 포인트가 많다는 이야기도 되죠.
또 이런 특성때문에 곳곳에 숨어 있는 고수들도 많이 있습니다.
아래의 SQL은 숨어 있는 이러한 기능들을 control 할 수 있는 parameter를 확인 할 수 있는 SQL 입니다.
물론 show parameter나 v$parameter에서 parameter의 값을 확인 할 수 있지만,
이 두개의 방법으로 찾을 수 있는 parameter는 일반 parameter와 변경된 hidden parameter만을 볼 수 있습니다.
아래의 SQL은 일반 parameter와 hidden parameter 모두 확인 가능한 parameter 입니다.
col value format a20
col description format a70
select a.inst_id "inst_id", ksppinm "name", ksppstvl "value", ksppdesc "description"
from x$ksppi a,x$ksppsv b where a.indx = b.indx and ksppinm like '%¶meter%'
/
SQL만 덩그러니 post하긴 좀 그러니..
각 버전 별로 전체 parameter 갯수와 hidden parameter 개수를 비교해 봤습니다.
10g R2는 전체 1385개의 parameter 중 hidden parameter는 1127 개이고,
11g R1 은 1920개 중 1631개,
11g R2는 2399개 중 2057 개 입니다.
(제 VMware linux 서버 기준입니당)
버전이 높아 질수록 점점 숨겨지는 parameter도 늘어가네요.
또 전체 parameter 중에서 감춰진 parameter가 대부분이군요..
약 70% 정도 되는 거 같네요..
2. 간단한 Makefile
2.1 Makefile 의 내부 구조
2.2 Makefile 예제
2.3 매크로의 사용
2.4 레이블의 사용
3. 매크로(Macro) 와 확장자(Suffix) 규칙
3.1 매크로란 무엇인가? (What is Macro)
3.2 미리 정해져 있는 매크로 (Pre-defined macro)
3.3 확장자 규칙 (Suffix rule)
3.4 내부 매크로 (Internal macro)
4. Makefile를 작성할 때 알면 좋은 것들
4.1 긴 명령어를 여러 라인으로 표시하기
4.2 확장자 규칙의 이용 (Use suffix rule !!)
4.3 매크로 치환 (Macro substitution)
4.4 자동 의존 관계 생성 (Automatic dependency)
4.5 다중 타겟 (Multiple target)
4.6 순환 make (Recursive MAKE)
4.7 불필요한 재컴파일 막기
5. make 중요 옵션 정리
6. Makefile 작성의 가이드라인
7. Makefile의 실제 예제
7.1 프로그램 제작에 쓰일 수 있는 Makefile
7.2 라이브러리와의 링크가 필요한 필요한 Makefile
7.3 LaTeX에서 쓰일 수 있는 Makefile
대부분의 오라클 운영자는 '/as sysdba' 나 system 계정으로 작업을 합니다.
모니터링 중 특정 user에서 대해 수행되는 sql의 이상 등을 발견하는 경우 가끔 해당 sql을 직접 수행해 보는 경우가 있는데, v$sqltext 등에서 잡아온 sql을 그대로 수행하면 에러가 당연히 발생합니다.
해당 sql의 table list가 몇개 안될 경우 table 명 앞에 username을 지정하면, 수행은 가능하지만 귀찮기도 하고 어찌되었던 sql이 변경되 버리고 말죠.
또 다른 sql에서 권한이 있는 table을 수행할 경우에도 table의 각각의 username을 설정해야 합니다.
그게 아니라면 synonym을 따로 만들어 줘야 합니다.
이러한 경우 간단히 CURRENT_SCHEMA의 설정으로 이 모든 귀찮은 것을 해결할 수 있습니다.
CURRENT_SCHEMA는 현재 수행되는 SCHEMA환경을 지정한 SCHEMA로 변경해 줍니다.
SQL> CONNECT SCOTT/TIGER;
SQL> GRANT SELECT ON EMP TO JAMES;
SQL> CONNECT JAMES/TIGER;
SQL> SELECT COUNT(*) FROM SCOTT.EMP;
SQL> SELECT COUNT(*) FROM EMP;
SELECT COUNT(*) FROM EMP
*
ERROR at line 1:
ORA-00942: table or view does not exist
위의 경우 당연히 에러가 발생하게 됩니다.
그러면 synonym이나 username을 추가 지정없이 수행하려면 다음의 CURRENT_SCHEMA를 SCOTT으로 지정해 줍니다.
SQL> CONNECT JAMES;
SQL> ALTER SESSION SET CURRENT_SCHEMA=SCOTT;
Session altered.
이 기능은 테이블을 특정 partition set으로 나누어 관리 및 성능 상의 이점을 얻는다.
다음의 내용은 이러한 partition table의 종류와 간단한 정의이다.
Range Partitioning Method
column 값의 범위에 따라 partition을 나누는 가장 일반적인 방법이다. 이 방식은 대개 날짜와 같은 범위로 많이 나뉘게 되는데, AP의 성격에 따라 데이터가 특정 partition에 몰리는 경우 hash partition 방식이나, list partition 방식 등 다른 partition 방식을 통해 sub-partition을 나누기도 한다.
Create table test164874 ( ord_day NUMBER(2), ord_month NUMBER(2), ord_year NUMBER(4), ord_id NUMBER(10) ) storage (initial 12k next 12k pctincrease 0 minextents 1) PARTITION BY RANGE (ord_year,ord_month,ord_day) ( PARTITION P1 VALUES LESS THAN (2001,3,31) TABLESPACE PART1, PARTITION P2 VALUES LESS THAN (2001,6,30) TABLESPACE part2, PARTITION P3 VALUES LESS THAN (2001,9,30) TABLESPACE part3, PARTITION P4 VALUES LESS THAN (2001,12,32) TABLESPACE part4 )
/
Hash Partitioning Method
partition key의 hash 값에 의해 partition으로 나눠진다. 일반적으로 균등한 분포를 가지며 성능상의 이유로 hash partition을 사용하기도 한다.
CREATE TABLE tabpart1( ord_id NUMBER(5), ord_date DATE ) PARTITION BY HASH(ord_id) PARTITIONS 16 STORE IN (tbs1,tbs2,tbs3,tbs4) /
Composite Partitioning Method
Oracle 10g 이전엔 Range-List, Range-Hash composite partition 만 지원했으나, Oracle 11에서는 List-List, List-Hash,
List-Range and Range-Range composite partition이 지원된다. 또 interval partition 방식에 대해서 Interval-Range,
Interval-List and Interval-Hash가 가능하다.
CREATE TABLE TAB1( ord_id NUMBER(10), ord_day NUMBER(2), ord_month NUMBER(2), ord_year NUMBER(4) ) PARTITION BY RANGE (ord_year,ord_month,ord_day) SUBPARTITION BY HASH(ord_id) SUBPARTITIONS 8 STORE IN (TBS1,TBS2,TBS3,TBS4,TBS5,TBS6,TBS7,TBS8) ( PARTITION P1 VALUES LESS THAN (2001,3,31), PARTITION P2 VALUES LESS THAN (2001,6,30), PARTITION P3 VALUES LESS THAN (2001,9,30), PARTITION P4 VALUES LESS THAN (2001,12,31) ) /
List Partitioning Method
Oracle 9i에 소개된 partition 방식으로 partition key의 값 자체에 의해 분리된 partition으로 데이터가 저장된다. 값에 의해 데이터가 partition 되므로 전혀 관계없는 데이터 값을 하나의 partition에 저장할 수 있다.
Interval partition은 range partition 기능의 확장으로 개별 범위를 명시적으로 정의하지 않아도 해당 interval에 속하는 데이터가 입력 될 때 partition이 자동으로 생성된다.
CREATE TABLE interval_sales (
prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold
NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) INTERVAL(numtodsinterval(7,'day')) ( PARTITION
p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')) );
Reference Partitioning Method
Oracle 11g에서 소개된 partition 방식으로 “모-자” 관계의 table 간에 모 table의 partition key column의 복제 없이 모 table의 partition 구조를 따라가게 됩니다.
SQL> CREATE TABLE ref_part_parent 2 (pcol1 NUMBER PRIMARY KEY, 3 pcol2 VARCHAR2(10)) 4 PARTITION BY RANGE (pcol1) 5 (PARTITION p1 VALUES LESS THAN (100), 6 PARTITION p2 VALUES LESS THAN (200), 7 PARTITION p3 VALUES LESS THAN (300), 8* PARTITION p4 VALUES LESS THAN (MAXVALUE))
Table created.
SQL> CREATE TABLE ref_part_child 2 (ccol1 NUMBER NOT NULL, 3 CONSTRAINT ccol1_fk FOREIGN KEY(ccol1) REFERENCES ref_part_parent(pcol1)) 4 PARTITION BY REFERENCE(ccol1_fk);
Table created.
System Partitioning Method
oracle 11g에서 소개된 partition 방식으로 다른 partition 방식과의 가장 큰 차이는 partition key를 정의하지 않는데 있다. 즉, system이 알아서 partition을 해준다.
virtual column partition 방식은 partition key의 값이 물리적으로 존재하지 않은 경우에도 virtual column 값에 의해 partition을 할 수 있게 한다.
CREATE TABLE employees (employee_id number(6) not null, first_name varchar2(30), last_name varchar2(40) not null, email varchar2(25), phone_number varchar2(20), hire_date date not null, job_id varchar2(10) not null, salary number(8,2), commission_pct number(2,2), manager_id number(6), department_id number(4), total_compensation as (salary *( 1+commission_pct)) ) PARTITION BY RANGE (total_compensation) ( PARTITION p1 VALUES LESS THAN (50000), PARTITION p2 VALUES LESS THAN (100000), PARTITION p3 VALUES LESS THAN (150000), PARTITION p4 VALUES LESS THAN (MAXVALUE) );
오라클은 DB link와 synonym을 통해서 remote oracle database에 있는 table을 local
database 서버에 있는 것처럼 query가 가능합니다. 하지만 내부적으로는 remote database로 관련 query를
수행해 return 되는 데이터를 이용해 결과 값을 출력해줍니다. (당연하죠..ㅎㅎ) 그러나 remote database의
table에 대한 정보가 없기 때문에 일반적인 role을 이용해 query를 생성, 수행 요청하게 됩니다.
(아무 상관없는 그림이지만 내용이 작아 붙여 봅니다. ^^;)
아래 box 안의 role은 remote database로 query를 수행하는 일반적인 규칙입니다. 참조한 문서가 오래전꺼긴 한데, 큰 차이는 없으리라.. 생각되는 군요.
간단히 보자면 ..
MAX() 같은 group 함수는 전달 되지 않으니, 테이블의 범위에 따라서는 엄청난 데이터가 local로 전송된 후 MAX() 값 등을 구할 수도 있겠군요.
또 상수 조건이 있는 table은 remote로 해당 조건이 전달 되겠지만, 그렇지 않은 경우는 FULL TABLE SCAN을 할 수 도 있겠고요..
ORDER BY도 local에서 수행된다니, 대상이 많으면 이것도 성능에 영향을 줄 수 있겠네요.
Aggregate functions like MAX(), COUNT() are NOT passed across the net but rather are performed on the local node.
Expressions in the SELECT list are decomposed into columns and evaluated when the data is retrieved.
Only a relevant subset of columns are fetched across the net.
An expression in a WHERE clause is passed across to the remote database
if it compares the remote column to a literal (eg ename = 'KING').
Expressions in a WHERE clause are passed to the remote database if all
columns are in the expression are located in the same remote table the
remote database (eg emp.sal * 0.20 > emp.bonus)
Datatype conversion functions like TO_CHAR() in a WHERE clause are subject to the conditions in #4 and #5.
The optimizer only has knowledge of local indexes when making decisions about how to execute a query.
Remote indexes can be still be used to resolve the decomposed query sent to the remote database.
GROUP BY is not sent to the remote database.
ORDER BY is not sent to the remote database.
이 내용은 oracle metalink "Note 1004553.6 DISTRIBUTED QUERY ANALYSIS"를 참조했습니다.
써놓고 보니 DB link를 이용하지 말라는 이야기가 된 것 같아 몇줄 더 씁니다.
물론 group 함수, order by 등의 처리가 local database에서 처리가 되지만 이러한 것들을 피해가는 방법들은 다 있기 마련이죠. view를 만들어 remote에서 group 함수 처리를 한다든지, join의 경우 아예 한 곳에서 처리하게 한다든지, temporary table을 만들어 처리하거나.. 뭐 이런
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';
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';
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';
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';
CASE 문은 PL/SQL 없이 SQL문 안에서 if-then-else logic을 구현하는 데 사용합니다.
CASE 문은 "Simple case expressions" 와 "Searched case expressions"로 나눌 수 있는데, "Simple case expressions"은 expression을, "Searched case expressions"은 condition을 사용해 return 값이 결정된다.
다음은 simple CASE 구문의 사용방법이다.
CASE search_expression
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
ELSE default_result
END
위의 예제는 search_expression이 expression1,2,3.. 일 경우 result1,2,3.. 이 출력된다.
SELECT product_id, product_type_id,
CASE product_type_id
WHEN 1 THEN 'Book'
WHEN 2 THEN 'Video'
WHEN 3 THEN 'DVD'
WHEN 4 THEN 'CD'
ELSE 'Magazine'
END
FROM products;
PRODUCT_ID PRODUCT_TYPE_ID CASEPROD
---------- --------------- --------
1 1 Book
2 1 Book
3 2 Video
4 2 Video
5 2 Video
6 2 Video
7 3 DVD
8 3 DVD
9 4 CD
10 4 CD
11 4 CD
12 Magazine
다음은 searched CASE 구문의 사용방법이다.
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN resultN
ELSE default_result
END
위의 예는 condition1,2 가 true 일 경우 result1,2,.. 출력한다.
SELECT product_id, product_type_id,
CASE
WHEN product_type_id = 1 THEN 'Book'
WHEN product_type_id = 2 THEN 'Video'
WHEN product_type_id = 3 THEN 'DVD'
WHEN product_type_id = 4 THEN 'CD'
ELSE 'Magazine'
END
FROM products;
PRODUCT_ID PRODUCT_TYPE_ID CASEPROD
---------- --------------- --------
1 1 Book
2 1 Book
3 2 Video
4 2 Video
5 2 Video
6 2 Video
7 3 DVD
8 3 DVD
9 4 CD
10 4 CD
11 4 CD
12 Magazine
SELECT product_id, price,
CASE
WHEN price > 15 THEN 'Expensive'
ELSE 'Cheap'
END
FROM products;
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.
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
BFT에 대해 좀 더 자세히 이야기 해보자면..
오라클에서 사용할 수 있는 file size는 oracle 에서 block내의 row을 표현하는 ROW_ID 값의 표현 한계와 O/S에서의 file size limit (오라클과 마찬가지 개념이겠지만)에 의해 제한된다.
oracle의 ROW_ID는 SMALLFILE tablespace에서 3byte의 relative file#와 6byte의
block#를 갖고 있었으나 한개의 datafile만을 갖는 BIGFILE tablespace에서는 relative
file#은 의미가 없기 때문에 9byte를 모두 block#을 표현하는데 사용된다. 따라서 2K의 blocksize를 갖는
database에서는 8TB, 32K blocksize를 사용한다면 128TB까지 사용이 가능하게 된다.
이전 버전에서 dbms_rowid를 이용해 file#, rfile#, block# 등의 정보를 확인할 수 있었는데,
BIGFILE tablespace는 row_id format이 다르기 때문에 BIGIFILE tablespace를 사용할 경우
BIGFILE임을 option으로 명시해야 한다.
SQL> select dbms_rowid.rowid_block_number(rowid) from test_small;
SQL> select dbms_rowid.rowid_block_number(rowid,'BIGFILE') from test_big;
기존에 사용하던 즉, SMALLFILE tablespace를 BIGFILE tablespace로 바꾸려면 아래의 방법들 처럼
데이터를 물리적으로 넘기는 작업을 해주어야 한다. row_id format이 다르기 때문에 이에 대해 자동으로
conversion하는 기능은 아직 없다.
A. ALTER TABLE ... MOVE TABLESPACE
B. CREATE TABLE ... AS SELECT
C. Data Pump export and import
- Import the table using the REMAP_TABLESPACE parameter of the Datapump
일반적으로 IN operation은 특정 table(view) data의 row 값에 따른 다른 table의 데이터를 추출해내고자 할 때 자주 사용되는데, 가끔 IN operation을 row가 있는지 check하는 용도로 사용하기도 한다. 그러나 row가 존재하는지에 대해서는 EXISTS라는 근사한 operation을 따로 제공하고 있다.
주의해야 할 점은 EXISTS와 IN은 다른 점이 존재하므로 이에 대해 유의해야 한다. EXISTS는 단지 해당 row가 존재하는지만 check하고 더이상 수행되지 않으나 IN은 실제 존재하는 데이터들의 모든 값까지 확인한다. 따라서 일반적으로 EXISTS operation이 더 좋은 성능을 보이므로 가능하면 EXISTS를 사용하는 것이 바람직해 보인다.
또한가지 EXISTS와 IN 사용시 주의해야 할 점은 join 되는 column에 NULL을 갖는 row가 존재한다면, NOT EXISTS는 true값을, NOT IN은 false 가 return 된다. 즉, NOT IN을 사용하면 조건에 맞는 데이터가 있다고 하더라도 NULL이 존재하면 "no rows selected"라고 나오게 된다. 따라서 NVL을 이용한 NULL 처리가 꼭 필요하다.
다음은 NOT EXISTS operation을 이용한 방법이다.
예제의 products table의 product_type_id column 데이터 중 일부가 NULL로 입력되어 있다.
SELECT product_type_id, name
FROM product_types outer
WHERE NOT EXISTS
(SELECT 1
FROM products inner
WHERE inner.product_type_id = outer.product_type_id);
PRODUCT_TYPE_ID NAME
--------------- ----------
5 Magazine
다음은 동일한 데이터에 대해 NOT IN을 사용했을 경우다. NULL data에 의해 조건 자체가 false가 되어 "no rows selected"라는 결과가 발생한다.
SELECT product_type_id, name
FROM product_types
WHERE product_type_id NOT IN
(SELECT product_type_id
FROM products);
no rows selected
다음은 NVL()을 이용해 NULL값을 처리한 후의 결과이다.
SELECT product_type_id, name
FROM product_types
WHERE product_type_id NOT IN
(SELECT NVL(product_type_id, 0)
FROM products);
PRODUCT_TYPE_ID NAME
--------------- ----------
5 Magazine
NOT IN operation의 경우 위와 같은 사실을 미리 인지하고 있지 않다면 나중에 이러한 경우를 찾기는 매우 어려울 수 있다. 따라서 NULL에 대한 operation이나 table의 default column 값등의 지정 등의 세심한 주의가 필요하다.
변수는 다들 잘 알고 있듯이 특정 변경되는 값을 변수값으로 입력 받아 이를 해당 변수와 치환해주게 된다. 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" 명령을 통해 해당 변수값을 유저로 부터 받아들일 수 있다.
Oracle AWR 데이터는 정해진 interval 마다 oracle statistic 값을 retention 기간만큼 보관하게 됩니다.
따라서 해당 시점에 발생된 statistic 값을 보려면 관련 table에 대해 self join을 해야하는데,
이때 편하게 쓸 수 있는 oracle 함수가 LAG 합수 입니다.
LAG is an analytic function. It provides access to more
than one row of a table at the same time without a self join. Given a
series of rows returned from a query and a position of the cursor, LAG provides access to a row at a given physical offset prior to that position.
If you do not specify offset, then its default is 1. The optional default value is returned if the offset goes beyond the scope of the window. If you do not specify default, then its default is null.
You cannot nest analytic functions by using LAG or any other analytic function for value_expr. However, you can use other built-in function expressions for value_expr.
[참고 ] Oracle 8.1.7 SQL reference [ LAG 함수 ]
간단히 예를 들면..
다음 처럼 DB_TIME 값의 변경 내역을 알고 싶다면 동일한 table에 대한 self join이 필요한데,
이 lag over 함수를 사용하면 join이 필요없어 집니다.
select a.snap_id,a.stat_name,a.value, b.value , a.value - b.value
from
DBA_HIST_SYS_TIME_MODEL a,
DBA_HIST_SYS_TIME_MODEL b
where a.snap_id = b.snap_id + 1
and a.stat_id = 3649082374
and a.stat_id = b.stat_id
/
select snap_id,stat_name,value, lag(value) over (order by snap_id),
value - nvl(lag(value) over (order by snap_id),0)
from DBA_HIST_SYS_TIME_MODEL
where stat_id = 3649082374
/
오라클에서 수행되는 cursor의 수행 정보와 이와 관련된 각종 statistic 정보를 확인할 수 있는 view가 몇개 있습니다.
다음은 해당 view에 대한 비교와 column 정보들, 그리고 해당 view를 이용한 몇개의 sql script 들입니다.
V$SQL_PLAN_STATISTICS:
각 operation에 대한 execution statistics 정보를 제공한다.
1)EXECUTIONS:
Child cursor의 수행된 횟수 이다. 따라서 동일한 SQL이더라도 optimizer 환경이나 bind variable size 등의 변경에 의해 다른 child cursor를 가질 수 있으므로 이에 대한 고려도 필요하다.
두개의 세션에서 동일한 SQL을 2회 수행하는 경우나, 같은 세션에서 동일한 SQL을 2회 수행한 경우 동일하게 2의 값을 갖는다.
2)LAST_OUTPUT_ROWS and OUTPUT_ROWS:
LAST_OUTPUT_ROWS는 해당 operation에 의해 마지막에 처리된 실제 row 수 이며, OUTPUT_ROWS는 누적 합이다.
3)LAST_DISK_READS and DISK_READS:
LAST_DISK_READS는 해당 operation이 disk를 읽은 횟수를 의미하며, DISK_READS는 누적 합이다.
V$SQL_PLAN_STATISTICS_ALL:
다음의 관련 view의 데이터에 대한 전체적인 정보를 갖는다.
V$SQL_PLAN (all operations, objects, cost, cardinality etc)
V$SQL_PLAN_STATISTICS (execution statistics as above)
V$SQL_WORKAREA (memory usage)
1) estimated_optimal_size
해당 operation을 메모리상에서 처리하기 위해 측정된 memory size (KB)
2) last_memory_used
cursor의 마지막 execution 동안 사용된 memory (KB)
3) active_time
Average active time (in centi-seconds)
다음의 SQL script는 특정 SQL cursor에 대한 plan과 관련 statistic 정보를 보여줍니다.
set pagesize 600
set tab off
set linesize 140
set echo off
set long 4000
col TQID format A4
col "SLAVE SQL" format A95 WORD_WRAP
col address format A12
col sql_hash format A15
col exec format 9999
col sql_text format A75 WORD_WRAP
repfooter off;
set timing off veri off space 1 flush on pause off termout on
numwidth 10;
alter session set "_complex_view_merging"=false;
select hash_value||decode(child_number, 0, '', '/'||child_number)
sql_hash,
sql_text
from v$sql
where child_number= 0 and hash_value= &hashvalue;
select '| Operation | Name | Starts | E-Rows | A-Rows | Buffers |
Reads | Writes | E-Time |' as "Plan Table" from dual
union all /* QWEKLOIPYRTJHH7 */
select
'------------------------------------------------------------------------------------------------------------------------'
from dual
union all
select rpad('| '||substr(lpad(' ',1*(depth))||operation||
decode(options, null,'',' '||options), 1, 33), 34, ' ')||'|'||
rpad(substr(object_name||' ',1, 19), 20, ' ')||'|'||
lpad(decode(starts,null,' ',
decode(sign(starts-1000), -1, starts||' ',
decode(sign(starts-1000000), -1, round(starts/1000)||'K',
decode(sign(starts-1000000000), -1, round(starts/1000000)||'M',
round(starts/1000000000)||'G')))), 8, ' ') || '|' ||
lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, round(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, round(cardinality/1000000)||'M',
round(cardinality/1000000000)||'G')))), 8, ' ') || '|' ||
lpad(decode(outrows,null,' ',
decode(sign(outrows-1000), -1, outrows||' ',
decode(sign(outrows-1000000), -1, round(outrows/1000)||'K',
decode(sign(outrows-1000000000), -1, round(outrows/1000000)||'M',
round(outrows/1000000000)||'G')))), 8, ' ') || '|' ||
lpad(decode(crgets,null,' ',
decode(sign(crgets-10000000), -1, crgets||' ',
decode(sign(crgets-1000000000), -1, round(crgets/1000000)||'M',
round(crgets/1000000000)||'G'))), 9, ' ') || '|' ||
lpad(decode(reads,null,' ',
decode(sign(reads-10000000), -1, reads||' ',
decode(sign(reads-1000000000), -1, round(reads/1000000)||'M',
round(reads/1000000000)||'G'))), 8, ' ') || '|' ||
lpad(decode(writes,null,' ',
decode(sign(writes-10000000), -1, writes||' ',
decode(sign(writes-1000000000), -1, round(writes/1000000)||'M',
round(writes/1000000000)||'G'))), 8, ' ') || '|' ||
lpad(decode(etime,null,' ',
decode(sign(etime-10000000), -1, etime||' ',
decode(sign(etime-1000000000), -1, round(etime/1000000)||'M',
round(etime/1000000000)||'G'))), 8, ' ') || '|' as "Explain
plan"
from
(select /*+ no_merge */
p.HASH_VALUE, p.ID, p.DEPTH, p.POSITION, p.OPERATION,
p.OPTIONS, p.COST COST, p.CARDINALITY CARDINALITY,
p.BYTES BYTES, p.OBJECT_NODE, p.OBJECT_OWNER,
p.OBJECT_NAME, p.OTHER_TAG, p.PARTITION_START,
p.PARTITION_STOP, p.DISTRIBUTION, pa.starts,
pa.OUTPUT_ROWS outrows, pa.CR_BUFFER_GETS crgets,
pa.DISK_READS reads, pa.DISK_WRITES writes,
pa.ELAPSED_TIME etime
from
v$sql_plan_statistics_all pa,
V$sql_plan p
where p.hash_value =
&hashvalue
and
p.CHILD_NUMBER= 0
and
p.hash_value = pa.hash_value(+)
and
pa.child_number(+) = 0 )
union all
select
'------------------------------------------------------------------------------------------------------------------------'
from dual;
REM
REM Print slave sql
REM
select /* QWEKLOIPYRTJHH7 */
decode(object_node,null,'', substr(object_node,length(object_node)-3,1)
|| ',' ||
substr(object_node,length(object_node)-1,2)) TQID,
other
"SLAVE SQL"
from v$sql_plan vp
where other is not NULL
and hash_value = &hash_value
and CHILD_NUMBER= 0;
qeps.sql과 qep.sql을 생성해 수행하면 SQL의 성능을 평가하는 8개의 항목에 대해 지정된 갯수의 SQL과 그에 따른 statistic 정보를 보여줍니다. init parameter인 statistics_level를 ALL로 설정해야 정상적인 결과를 보여줍니다.
--
-- Script: qeps.sql
--
-- Script to report the explain plan for the most expensive N SQL statements
-- based on user specified criteria:
--
-- buffer_gets - 1
-- CPU time - 2
-- disk_reads - 3
-- rows_processed - 4
-- executions - 5
-- parse calls - 6
-- Buffers/Exec - 7
-- Cost per row - 8
--
-- Usage: start qeps.sql
--
-- This scripts requires qep.sql in order to function.
-- See Oracle Metalink Note: 550578.1 for more detail.
--
SET ECHO OFF
PROMPT
PROMPT Starting QEPS.SQL
PROMPT
PROMPT NOTES:
PROMPT
PROMPT The database parameter statistics_level should be set to ALL
PROMPT to obtain full statistics in the plan output.
PROMPT
PROMPT Script only works with Oracle 9.2 and above
PROMPT
PROMPT Script must be run from a database account with access to:
PROMPT
PROMPT . gv$sql_plan
PROMPT . gv$sqltext_with_newlines
PROMPT . gv$sql_plan_statistics_all
PROMPT
PROMPT Requires the partner script qep.sql
PROMPT
SET HEAD OFF
SET SERVEROUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET TIMING OFF
SET PAUSE OFF
SET PAGESIZE 0
prompt Available Expense Criteria:
prompt
prompt buffer_gets - 1
prompt CPU time - 2
prompt disk_reads - 3
prompt rows_processed - 4
prompt executions - 5
prompt parse calls - 6
prompt Buffers/Exec - 7
prompt Cost per row - 8
prompt
accept sec_opt prompt "Please select required expense criteria [7]: "
accept top_n prompt 'Please enter the number of SQL Statements to report [5]: '
SET TERMOUT OFF
SPOOL gen_plans.sql
SELECT /* QWEKLOIPYRTJHH7 */
'define instid=' ||'''' || inst_id ||'''' || CHR(10) ||
'define hash_value=' ||'''' || hash_value ||'''' || CHR(10) ||
'define address=' ||'''' || address ||'''' || CHR(10) ||
'define child_number='||'''' || child_number ||'''' || CHR(10) ||
'@qep'
FROM
(SELECT inst_id, hash_value, child_number, address, buffer_gets
FROM gv$sql
WHERE sql_text NOT LIKE '%QWEKLOIPYRTJHH7%'
AND (UPPER(sql_text) like 'SELECT%'
OR UPPER(sql_text) like 'UPDATE%'
OR UPPER(sql_text) like 'DELETE%'
OR UPPER(sql_text) like 'INSERT%')
ORDER BY
DECODE('&sec_opt',
NULL,
buffer_gets / decode(greatest(rows_processed,executions),0,1,
greatest(rows_processed,executions)),
1, buffer_gets,
2, cpu_time,
3, disk_reads,
4, rows_processed,
5, executions,
6, parse_calls,
7, buffer_gets / decode(executions,0,1, executions),
8, buffer_gets / decode(greatest(rows_processed,executions),0,1,
greatest(rows_processed,executions))) DESC )
WHERE rownum < DECODE(TO_NUMBER('&&top_n'),NULL, 6, &&top_n + 1)
/
SPOOL OFF
SET HEAD ON PAGESIZE 66
--
-- Setup Sort Opt descr for main report
--
BREAK ON SORTOPT
COLUMN SORTOPT NEW_VALUE SORTOPT_VAR
SELECT /* QWEKLOIPYRTJHH7 */
decode(NVL('&sec_opt',7)
,1,'Buffer Gets',
2,'CPU time',
3,'Disk Reads',
4,'Rows Processed',
5,'Executions',
6,'Parse Calls',
7,'Buffers/Exec',
8,'Cost per Row') SORTOPT
FROM DUAL;
CLEAR BREAKS
COLUMN instance_name NEW_VALUE instance
SELECT instance_name
FROM v$instance;
SPOOL qeps_&instance._&file_date_var
TTITLE
left today skip 2 center 'Top &&top_n Performing SQL Statements
for Database Instance &&instance by &&sortopt_var '
skip 2
SET TERMOUT ON
@gen_plans
spool
spool off
--
-- Script:
qep.sql
--
-- This script is called via the qeps.sql script. Please refer to Oracle
-- Metalink Note: 550578.1 for more detail.
--
set pagesize 600
set tab off
set linesize 133
set echo off
set long 4000
col Inst_ID format 999 heading "Inst ID"
col TQID format A4
col "SLAVE SQL" format A95 WORD_WRAP
col address format A12 heading "SQL Address"
col sql_hash format A15 heading "SQL Hash"
col buffer_gets heading "Buffer|Gets "
col exec format 9999
col disk_reads heading "Disk |Reads"
col sorts heading "Sorts"
col rows_processed heading "Rows |Processed"
col parse_calls heading "Parse|Calls"
col cpu_time format 9999999.99 heading "CPU Time|(Secs)"
col executions format 999,999,999 heading "Execs"
col sql_text format A80 heading "SQL Text"
repfooter off;
SET TIMING OFF VERI OFF SPACE 1 FLUSH ON PAUSE OFF TERMOUT ON NUMWIDTH 10;
ALTER SESSION SET "_complex_view_merging"=FALSE;
SELECT /* QWEKLOIPYRTJHH7 */ inst_id, address,
hash_value||DECODE(child_number, 0,
'', '/'||child_number) sql_hash,
executions,
parse_calls,
buffer_gets,
disk_reads,
rows_processed,
cpu_time/1000000 as cpu_time,
sorts
FROM gv$sql
WHERE inst_id = '&instid'
AND hash_value = '&hash_value'
AND child_number = '&child_number';
set head off
TTITLE OFF
SELECT /* QWEKLOIPYRTJHH7 */ REPLACE(sql_text,CHR(10),' ') as sql_text
FROM gv$sqltext_with_newlines
WHERE inst_id = '&instid'
AND address = '&address'
ORDER BY piece;
set head on
PROMPT
PROMPT C-Rows => Cardinality O-Rows => Output Rows E-Time => Elapsed Time (Secs)
SELECT RPAD('| Operation',45) || RPAD('| Name',25) || ' |C-Rows|O-Rows|IN-OUT| Buffers |'
|| RPAD(' Reads',9) || RPAD('| Writes',10) || RPAD('| E-Time',10) || '|'
AS "Plan Table"
FROM dual
UNION ALL /* QWEKLOIPYRTJHH7 */
SELECT
'-------------------------------------------------------------------------------------------------------------------------------------'
FROM dual
UNION ALL
SELECT RPAD('| '||substr(LPAD(' ',1*(depth))||operation|| DECODE(options, null,'',' '||options), 1, 44), 45, ' ')||'|'||
RPAD(substr(object_name||' ',1, 25), 25, ' ')||'|'||
LPAD(DECODE(cardinality,null,' ',
DECODE(sign(cardinality-1000), -1, cardinality||' ',
DECODE(sign(cardinality-1000000), -1, round(cardinality/1000)||'K',
DECODE(sign(cardinality-1000000000), -1, round(cardinality/1000000)||'M',
round(cardinality/1000000000)||'G')))), 6, ' ') || '|' ||
LPAD(DECODE(outrows,null,' ',
DECODE(sign(outrows-1000), -1, outrows||' ',
DECODE(sign(outrows-1000000), -1, round(outrows/1000)||'K',
DECODE(sign(outrows-1000000000), -1, round(outrows/1000000)||'M',
round(outrows/1000000000)||'G')))), 6, ' ') || '|' ||
RPAD(DECODE(other_tag,
'PARALLEL_TO_SERIAL', ' P->S',
'PARALLEL_FROM_SERIAL', ' P<-S',
'PARALLEL_FROM_REMOTE', ' P<-R',
'PARALLEL_TO_PARALLEL', ' P->P',
'PARALLEL_COMBINED_WITH_PARENT',' PCWP',' '),6,' ') || '|' ||
LPAD(DECODE(crgets,null,' ',
DECODE(sign(crgets-10000000), -1, crgets||' ',
DECODE(sign(crgets-1000000000), -1, round(crgets/1000000)||'M',
round(crgets/1000000000)||'G'))), 9, ' ') || '|' ||
LPAD(DECODE(reads,null,' ',
DECODE(sign(reads-10000000), -1, reads||' ',
DECODE(sign(reads-1000000000), -1, round(reads/1000000)||'M',
round(reads/1000000000)||'G'))), 9, ' ') || '|' ||
LPAD(DECODE(writes,null,' ',
DECODE(sign(writes-10000000), -1, writes||' ',
DECODE(sign(writes-1000000000), -1, round(writes/1000000)||'M',
round(writes/1000000000)||'G'))), 9, ' ') || '|' ||
REPLACE(TO_CHAR(NVL(etime/1000000,0), '99990.99'),' 0.00',' ') ||
'|' AS "Explain plan"
FROM
(SELECT /*+ no_merge */ /* QWEKLOIPYRTJHH7 */
p.HASH_VALUE, p.ID, p.DEPTH, p.POSITION, p.OPERATION,
p.OPTIONS, p.COST COST, p.CARDINALITY CARDINALITY,
p.BYTES BYTES, p.OBJECT_NODE, p.OBJECT_OWNER,
p.OBJECT_NAME, p.OTHER_TAG, p.PARTITION_START,
p.PARTITION_STOP, p.DISTRIBUTION, pa.starts,
pa.OUTPUT_ROWS outrows, pa.CR_BUFFER_GETS crgets,
pa.DISK_READS reads, pa.DISK_WRITES writes,
pa.ELAPSED_TIME etime
FROM gv$sql_plan_statistics_all pa,
gv$sql_plan p
WHERE p.inst_id = '&instid'
AND pa.inst_id = '&instid'
AND p.hash_value = '&hash_value'
AND p.child_number = '&child_number'
AND pa.child_number(+) = '&child_number'
AND p.id = pa.id(+)
AND p.address = pa.address(+)
ORDER BY p.id)
UNION ALL
SELECT
'-------------------------------------------------------------------------------------------------------------------------------------'
FROM dual;
REM
REM Print slave sql
REM
SELECT /* QWEKLOIPYRTJHH7 */
DECODE(object_node,null,'', substr(object_node,length(object_node)-3,1) || ',' ||
substr(object_node,length(object_node)-1,2)) TQID,
other "Slave SQL"
FROM gv$sql_plan vp
WHERE vp.inst_id = '&instid'
AND vp.other IS NOT NULL
AND vp.hash_value = '&hash_value'
AND vp.child_number = '&child_number';
PROMPT
************************************************************************************************************************************
PROMPT
01/25/2008
Top
Performing SQL Statements for Database Instance V102 by
Buffers/Exec
Parse
Buffer Disk Rows CPU Time
Inst
ID SQL Address SQL Hash Execs Calls Gets
Reads Processed (Secs) Sorts
-------
------------ --------------- ------------ ---------- ----------
---------- ---------- ----------- ----------
1
31ADC51C 2327026800 1 1 3835
778 0 .58 0
select
o.owner#,o.obj#,decode(o.linkname,null,
decode(u.name,nul
l,'SYS',u.name),o.remoteowner),
o.name,o.linkname,o.namespace,o.
subname
from user$ u, obj$ o where u.user#(+)=o.owner# and
o.typ
e#=:1
and not exists (select p_obj# from dependency$ where
p_obj
#
= o.obj#) order by o.obj# for
update
Parse
Buffer Disk Rows CPU Time
Inst
ID SQL Address SQL Hash Execs Calls Gets
Reads Processed (Secs) Sorts
-------
------------ --------------- ------------ ---------- ----------
---------- ---------- ----------- ----------
1
319B23DC 1187151836 1 1 920
32 1 .09 0
select
ee.ectx#, o.owner#, u.name, o.name, ee.num_rules,
ee.num_
boxes,
ee.ee_flags from rule_set_ee$ ee, obj$ o, user$ u
where
ee.rs_obj#
= :1 and ee.ectx# = o.obj# and o.owner# =
u.user#
Parse
Buffer Disk Rows CPU Time
Inst
ID SQL Address SQL Hash Execs Calls Gets
Reads Processed (Secs) Sorts
-------
------------ --------------- ------------ ---------- ----------
---------- ---------- ----------- ----------
1
31A63F14 2372567631 1 1 867
803 0 .21 0
select
o.obj#, u.name, o.name, t.spare1,
DECODE(bitand(t
.flags,
268435456), 268435456, t.initrans, t.pctfree$) from
s
ys.obj$
o, sys.user$ u, sys.tab$ t where
(bitand(t.trigflag,
1048576)
= 1048576) and o.obj#=t.obj# and o.owner#
=
u.user#
Parse
Buffer Disk Rows CPU Time
Inst
ID SQL Address SQL Hash Execs Calls Gets
Reads Processed (Secs) Sorts
-------
------------ --------------- ------------ ---------- ----------
---------- ---------- ----------- ----------
1
31A9A254 162926978 1 1 764
113 0 .12 0
select
table_objno, primary_instance, secondary_instance,
owner_
instance
from sys.aq$_queue_table_affinities a where
a.owner_i
nstance
<> :1 and
dbms_aqadm_syscalls.get_owner_instance(a.prima
ry_instance,
a.secondary_instance,
a.owner_instance)
=
:2 order by table_objno for update of a.owner_instance
skip
locked
Plan
Table
-------------------------------------------------------------------------------------------------------------------------------------
|
Operation | Name
|C-Rows|O-Rows|IN-OUT| Buffers | Reads | Writes | E-Time |
-------------------------------------------------------------------------------------------------------------------------------------
|
FOR UPDATE |
| | 0 | | 371 | 19 | 0 | 0.06|
|
TABLE ACCESS BY INDEX ROWID
|AQ$_QUEUE_TABLE_AFFINITIE| | 0 | | 371 | 19
| 0 | 0.06|
| INDEX FULL SCAN
|AQ$_QTABLE_AFFINITIES_PK | | 14 | | 1 | 1
| 0 | |
-------------------------------------------------------------------------------------------------------------------------------------
************************************************************************************************************************************
currently spooling to qeps_V102_20080125_213802.txt
참고 :
Note 186548.1 9i Release 2 Cached cursors information in the row source level
Note 260942.1 Display Execution plans from Statement's in V$SQL_PLAN
Note 550578.1 How to Obtain the most Resource Intensive SQL Execution Plans using the Libr...
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')
;
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')
;
