에너쓰오라클

oracle 10g에서 소개된 Automatic Optimizer Statistics Collection 기능에 대해 간단히 얘기해보겠습니다. 대부분 이 기능을 끄고 안쓰는 걸로 알고 있는데 요놈 때문에 optimizer가 plan을 바꿔 곤란한 경우가 가끔 생기기도 합니다. 하지만 어찌 보면 굉장히 매력적인 기능이 아닐 수 없습니다. 대부분 데이터 베이스가 엄청 커져서 analyze 하는 시간도 오래 걸릴 뿐 아니라 더이상 오라클에서는 RULE base는 지원안한다고 하니..

그래서 지금은 안쓰지만 언젠가 쓰게될 요 기능에 대해 좀 알아보죠..

그러면 언제 automatic optimizer statistics collection이 시작 되며, 얼마나 수행될까요?
dba_scheduler_jobs를 보면 gather_stats_job이라는 job으로 등록되어 있습니다. 요 넘은 기본으로 저녁 10시 부터 8시간 동안, 또 토요일 00시 부터 2일 간 수행되게 되어 있죠.

만약 평일 8시간, 주말 2일간 수행을 다 못하면 어찌 될까요?
만약 수행 중 다 끝나지 않은 table의 statistic 정보는 원복 하게 됩니다.  그럼 다음날 에 다시 하겠죠? 아마 ~

그럼 얘는 뭘 보고 대상을 선정할 까요?
10g는 기본적으로 monitoring 기능이 enable 되어 있습니다. dba_tables 등의 view를 보면 monitoring이라는 column에 YES로 기본으로 박혀있죠.. dba_tab_modifications에 해당 table의 변경 내역을 저장하고 변경 내역이 10% 이상이 되면 STEAL 상태가 되어 Automatic Optimizer Statistics Collection의 대상이 됩니다.. 그리고 job이 수행되면 dba_tab_modifications의 데이터는 cleanup 됩니다.

RMAN 을 벌려놓긴 했는데, 테스트 할 여유가 요즘 별로 없네요.. 일단.. 미루고.. ^^;


오늘 oracle 11g new feature 인 OLTP table compression을 테스트 할 기회가 있어 간단히 정리해 보겠습니다. oracle 9i R2 에서 oracle 은 table compression 기능을 추가했죠. 이미 다른 database에서는 일반화 되어 있었었다죠?  oracle 9i에서는 bulk loading, insert에 대해서만 compression을 지원했습니다. 즉, parallel insert나 sql loader를 이용한 direct loading, 또 Create Table As Select (CTAS)에 대해서만 지원했습니다.

Table compression은 많은 row를 저장하고 있는 대량의 데이터를 위한 기능인데, 대부분의 이러한 테이블은 bulk loading 보다는 batch 작업을 통해 생성되거나 history성 table들이라 이러한 table들은 "move compress"로 다시 compress하는 과정이 추가로 필요하게 되죠..

Oracle 11g에서는 일반적인 DML에 대해서도 compress를 할수 있게 되었습니다. 단지 compress option을 줄때 "for all operations" option을 추가 해 주면 되죠. 물론 이전의 기능으로 사용하려면 "for direct_load operations" option을 주면 됩니다.

create table compress_for_dire compress for direct_load operations as ...
create table compress_for_dire compress for all operations as ...

ps1)
오늘 테스트를 했더니 두 option 다 block의 row가 꽉 차기 전까지는 일반 block과 구조가 동일한 것 같더군요. block 내 row가 꽉차는 시점에 symbol table이 생성되며 compress가 이루어 지는 듯 합니다..

ps2)
"direct_load operations" option을 준 table에 insert  select ( /*+ append */ 빼구) 주니까 당연히 compress는 안됩니다.... 만 일반 table 보다 row가 더 많이 들어가더군요..

왜그럴까?
아시는 분?

RMA을 사용하기에 앞서 몇몇 고려할 사항들이 있습니다.

- Recovery Catalog 사용여부

Recovery Catalog는 RMAN에 의해서 사용되어지고 유지관리 되는 저장소입니다.
RMAN은 recovery catalog에 저장되어 있는 정보를 사용해서 요청되어진 Backup 과 Restore의 실행을 어떻게 할지를 결정하게 되는데, 이 catalog의 사용 여부가 우선 결정되어야 합니다. catalog 없이 RMAN 을 사용할 때의 단점은 recovery catalog 의 overhead 가 없는 대신, Point-In-Time recovery가 어려우며, control file 손상시에 recovery 할 수 없고, stored script 를 사용할 수 없습니다. 오라클에서는 RECOVERY CATALOG 사용을 권장합니다.

- Flash recovery area 사용.

oracle 10g에서 제공하는 flash recovery area는 rman에 의해 자동으로 관리됩니다.
물론 일반 disk나 tape으로도 backup이 가능하나, flashback database 등의 새로운 기능 등을 사용하려면 flash recovery area를 사용하는 것이 바람직합니다. 물론 disk 공간이 backup strategy에 충분할 정도는 있어야 겠죠.

- Backup Strategy

RMAN은 incremental backup이 가능하므로 full backup, incremental backup으로 backup strategy를 미리 정해야 합니다. 물론 저녁마다 full backup이 가능하다면 특별한 backup 전략은 필요 없겠지만요. (당신이 그러한 경우라면... 좋은 환경에서 일하시는 겁니다 ㅋ)

 * 백업 전략은 이전 post ( oracle - recovery manager (RMAN)) 를 참고

- RMAN configuration.

RMAN 관련한 default configuration 입니다.
(note 305565.1 Persistent Controlfile configurations for RMAN in 9i and 10g. 참고)


RMAN에 관련된 configuration은 다음의 명령으로 확인 가능합니다.

RMAN> Show all;

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/app/oracle/product/9.2.0/dbs/snapcf_V9201.f'; # default

그럼 다음 POST에선 RMAN 셋팅 방법에 대해 posting 하겠습니다.. ^^

RMAN 시작전에 알아야 할 몇몇 것(!) 들..

RMAN은 oracle8에서 처음 소개된 oracle에서 제공하는(!) backup & recovery tool 입니다.

Database files, Archive logs, 그리고 Control files들을 Backup하고 Restore하기 위하여 사용되어지며, Complete 또는 Incomplete Database Recovery 수행이 가능합니다. 또 10g 부터인가는 database의 validation check 까지 해 준다는 군요. (output을 보니 거의 DBV와 비슷한 내용인 것 같지만..)

다음은 RMAN에서 사용하는 주요 용어와 개념들 입니다.

- Target database
backup,restore,recovery action 이 수행될 대상 데이타베이스 입니다.

- Recovery Catalog
RMAN에서 사용하는 Information 저장장소입니다.
target database 의 물리적 스키마, datafile 과 archivelog 의 backup sets 과 pieces,
backup script 등을 포함하고 있습니다.

- channel
allocation channel 은 target database 의 backup,restore,recover에 대한 server process 초기화를 합니다. 즉, 이 channel은 disk 를 포함 기타 OS device를 지정하게 되며, 이에 따라 parallelization 의 degree 가 결정됩니다.

- Backup sets
하나 또는 그 이상의 Datafiles 또는 Archivelogs를 포함하며, 
Backup pieces의 Complate Set으로, Full 또는 Incremental Backup으로 구성 됩니다.
Oracle proprietary format을 사용합니다.

- Backup Pieces
하나의 Backup Set은 하나 또는 그 이상의 Backup Pieces로 구성이 되어지며,
각 Backup Piece는 Single Output File로  O/S의 File system Size의 제한을 갖고 있습니다.


- Image Copies
Single file( datafile, archivelog or controlfile)의 Copy 본으로 O/S의 copy와 매우 유사합니다.
Backup set이나 Backup Piece가 아니며, Compression이 수행되지 않는 제한이 있습니다.


- Full Backup Sets
하나 또는 그 이상의 Datafiles에 대한 Backup으로 Datafile의 모든 사용되어진 Block을 포함합니다.
미사용 Block은 Backup되지 않으며, 압축형태로 저장됩니다.


- Incremental Backup Sets
하나 또는 그 이상의 Datafiles에 대한 Backup으로
같거나 낮은 Level의 이전 Backup이후 변경되어진 부분만 Backup을 하며, 압축형태로 저장됩니다.


- Recovery window
현재 시점과 recovery 가능한 가장 오래된 시점 사이를 나타내는 retention policy를 말합니다..

- SBT
System backup to tape

- Flash Recovery Area
control file, online redo log copies, archived logs, flashback logs 같은 recovery 와 관련된 file 들을 저장하기 위해 사용할 수 있는 선택적인 디스크 영역입니다.  오라클과  RMAN은 이러한 파일들은 flash recovery area 에 자동으로 관리합니다. 물론 일정 보관주기 설정이 가능하며, tape 등의 backup device로 backup이 가능합니다. flash recovery area는 target database에 설정됩니다.

가끔 database를 운영하다 보면 멀정히 잘 돌던 SQL이 plan이 바뀌어서 응답 시간이 터무니 없이 느려지곤 해 이로 인해 운영 장애가 발생하기도 하죠.

"같은 SQL을 같은 환경에서 사용하는데, SQL이 왜 느려지냐 ? "

뭐 이런 얘기를 DB 운영하는 사람이라면 가끔 들었을 법한 애기 입니다.
대개 이런 얘기는 높은 분들이 하기때문에 설명하기도 귀찮고 해서
그냥 "글쎄요.." 하고 넘어가기도 하는데,

사실 따지고 보면 데이터도 바뀌고, 변수값도 바뀌기 때문에
엄밀히 말하자면 같은 환경은 아닙니다.

bind peeking 기능으로 인해 hard parse 단계의 변수값에 영향을 받기도 하고,
automatic optimizer statistic collection 기능으로 인해 statistic 정보가 바뀔수도 있고,
dynamic sampling에 따라서 block 정보에 의해서도 바뀔수도 있죠.


이런 현상을 막으려면 부지런히 analyze 정보를 update 해주면 이러한 현상을 좀 줄어들 수도 있겠지만, 요거는 plan을 고정하기 보다는 plan을 최적화 한는 방법이죠.
뭐 잘되면 더 좋은 성능을 내겠지만,

그러나 다들 공감하시겠지만 성능 좋아진건 별로 눈에 띄지 않죠.
높은 분들도 잘 모르시고... ㅋㅋ

만약 특정 sql의 plan을 변경되지 않게 하기 위해서는 여러 방법이 있겠지만,
크게 outline을 사용하거나 hint를 sql에 적용하는 방법을 많이 사용합니다.

그러나 가끔 hint를 사용했는데, plan이 바뀌는 경우가 있는데, 이러한 경우는 대부분 hint를 꼼꼼히 부여하지 않아 optimizer가 hint를 무시하게 됩니다.

hint를 지정할때는 1) join method 2) join order 3) access method 를 전부 다 기술해 줘야 합니다.
요넘의 optimizer에게 일을 시키려면 절대 빠져나갈 구멍을 주면 안됩니다.. ㅋㅋ

오라클 매거진 2009.1월에 연재된 그 유명한 TOM의 dynamic sampling에 대한 기고문입니다.


On Dynamic Sampling
By Tom Kyte

Our technologist samples dynamically, considers usage, and sets levels.

My questions are related to dynamic sampling. What does it really do, when would I consider using it, and what are the meanings of all the different levels it can be set to?

Dynamic sampling first became available in Oracle9i Database Release 2. It is the ability of the cost-based optimizer (CBO) to sample the tables a query references during a hard parse, to determine better default statistics for unanalyzed segments, and to verify its “guesses.” This sampling takes place only at hard parse time and is used to dynamically generate better statistics for the optimizer to use, hence the name dynamic sampling.

The optimizer uses a variety of inputs to come up with a plan. It uses any and all constraints defined on the table; system statistics—information about your server’s I/O speeds, CPU speed, and the like; and statistics gathered from the segments involved in the query. The optimizer uses statistics to estimate cardinalities—the number of rows each step in a given plan is expected to return—and those cardinalities are a major variable in computing the cost of a query. When cardinalities are incorrectly estimated, the optimizer may choose an inefficient query plan. The No. 1, and some might say only, reason for an inefficient plan’s being generated by the optimizer is inaccurate cardinality estimations. I like to say “right cardinality equals right plan; wrong cardinality equals wrong plan.”

So, “right” is the motivation behind dynamic sampling: to help the optimizer get the right estimated cardinality values. Feeding the optimizer more information and more-accurate information, specific to the query itself, will help the optimizer come up with the optimal execution plan.

Dynamic sampling offers 11 setting levels (0 through 10), and I’ll explain the different levels, but note that in Oracle9i Database Release 2, the default dynamic sampling level value is 1, whereas in Oracle Database 10g Release 1 and above, it defaults to 2.

Ways Dynamic Sampling Works

There are two ways to use dynamic sampling:

• The OPTIMIZER_DYNAMIC_SAMPLING parameter can be set at the database instance level and can also be overridden at the session level with the ALTER SESSION command.
• The DYNAMIC_SAMPLING query hint can be added to specific queries.

In this column, I’m going to use the hinting capability to demonstrate the effect of dynamic sampling, but you may well use the session-level capability to increase the use of dynamic sampling, especially in a reporting or data warehouse situation.

As stated before, dynamic sampling is used to gather statistics for unanalyzed segments and to verify “guesses” made by the optimizer. I’ll look at each of these uses in turn.

Unanalyzed Segments

The optimizer will use default statistic values if a segment is not analyzed and you do not use dynamic sampling to get a quick estimate. These default cardinality values are documented in Oracle Database Performance Tuning Guide. These default statistic values are typically not very realistic, because using them is a one-size-fits-all approach. The estimated row counts are based on guesses at the number of blocks of data in the table and an average row width. Without dynamic sampling, these guesses will be off—by a large amount. Consider:

SQL> create table t

  2    as

  3    select owner, object_type

  4      from all_objects

  5    /

Table created.



SQL> select count(*) from t;



  COUNT(*)

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

      68076

Now I’ll look at the estimates for a query that accesses this unanalyzed table. To see the default cardinalities the optimizer would use, I have to disable dynamic sampling (it is enabled by default in Oracle9i Database Release 2 and above). I achieve this via the DYNAMIC_SAMPLING hint, with a level of zero—zero being the value that disables dynamic sampling—as shown in Listing 1.

Code Listing 1: Disabling dynamic sampling to see default cardinalities

 

SQL> set autotrace traceonly explain

SQL> select /*+ dynamic_sampling(t 0) */ * from t;



Execution Plan

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

Plan hash value: 1601196873



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

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

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

|   0 | SELECT STATEMENT  |      | 16010 |   437K|    55   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T    | 16010 |   437K|    55   (0)| 00:00:01 |

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

As you can see, the estimated cardinality is 16,010, which is very far off from the real cardinality, about 68,000. If I permit dynamic sampling, I get a much more realistic cardinality estimate, as shown in Listing 2.

Code Listing 2: More-realistic cardinalities with dynamic sampling enabled

SQL> select * from t;



Execution Plan

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

Plan hash value: 1601196873



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

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

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

|   0 | SELECT STATEMENT  |      | 77871 |  2129K|    56   (2)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T    | 77871 |  2129K|    56   (2)| 00:00:01 |

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



Note

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

- dynamic sampling used for this statement

Now, 77,871 is not exactly 68,000 (obviously), but it is much closer to reality than 16,010 was. In general, the optimizer will choose better query plans for queries accessing this unanalyzed table when using dynamic sampling.

An inaccurate cardinality estimate can swing either way, of course. In Listing 1, I showed the optimizer radically underestimating the cardinality, but it can overestimate as well. Consider the estimate in Listing 3.

Code Listing 3: Overestimating cardinalities

SQL> delete from t; 

68076 rows deleted. 



SQL> commit; 

Commit complete. 



SQL> set autotrace traceonly explain

SQL> select /*+ dynamic_sampling(t 0) */ * from t;



Execution Plan 

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

Plan hash value: 1601196873



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

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

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

|   0 | SELECT STATEMENT  |      | 16010 |   437K|    55   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T    | 16010 |   437K|    55   (0)| 00:00:01 |

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



SQL> select * from t;



Execution Plan

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

Plan hash value: 1601196873



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

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

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

|   0 | SELECT STATEMENT  |      |     1 |    28 |    55   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T    |     1 |    28 |    5    (0)| 00:00:01 |

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



Note

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

- dynamic sampling used for this statement

Think about what might happen if the optimizer guessed 16,010 rows instead of 1 row in this case. For queries accessing table T, the optimizer would grossly overestimate the rows that will be returned from T and generate incorrect plans as a result.

So, where would dynamic sampling be useful? First and foremost, dynamic sampling is useful when you are accessing any table that has been created and loaded but not yet analyzed. Starting in Oracle Database 10g Release 1, the CBO is the only supported optimizer, and it needs accurate statistics to perform its job correctly. If a table exists that hasn’t had statistics gathered yet, the optimizer will be flying blind. Dynamic sampling gives the CBO the information it needs in order to operate correctly.

The second use for dynamic sampling is with global temporary tables. Often global temporary tables do not have statistics, and dynamic sampling can provide the optimizer with information about these tables. Your application would load the global temporary table, and the first hard parse of any query that utilized the temporary table would dynamically sample it to ascertain the correct size of the temporary table.

When the Optimizer Guesses

In addition to providing the optimizer with necessary statistics when it is accessing unanalyzed segments, dynamic sampling may also help the optimizer to validate any of its guesses. The optimizer has access to statistics about “single things” in general; when you gather statistics by using DBMS_STATS, the optimizer receives information about

• The table, the number of rows, average row widths, and the like.
• Each individual column, the high value, the low value, the number of distinct values, histograms (maybe), and the like. (Oracle Database 11g can even gather statistics on an expression, but it is still a single expression). Additionally, Oracle Database 11g can gather statistics on groups of columns, and these statistics can be used in equality comparisons.
• Each individual index, the clustering factor, the number of leaf blocks, the index height, and the like.

So, given a table with various columns, the optimizer has lots of information to work with, but it is missing some vital information, including statistics about how the various columns interact with each other and statistics about any correlations in column values. For example, suppose you have a table of census information that includes a record for everyone on the planet. One of the table’s attributes—MONTH_BORN_IN—is a character string field containing each person’s birth month. Another column—ZODIAC_SIGN—contains character strings with the name of each person’s zodiac sign.

After gathering statistics, you ask the optimizer to estimate how many people were born in December, and it would almost certainly be able to come up with a very accurate estimate of 1/12 of the data (assuming a normal distribution of birth dates). If you asked the optimizer to estimate how many people are Pisces, it would again likely come up with an accurate estimate of 1/12 of the data again.

So far, so good. But now you ask, “How many people born in December are Pisces?” All Pisces were born in either February or March, but the optimizer isn’t aware of that. All the optimizer knows is that December will retrieve 1/12 of the data and that Pisces will retrieve 1/12 of the data; it assumes that the two columns are independent and, using very simple logic, says, “The number of people born in December who are also Pisces will be 1/12 times 1/12, or 1/144, of the data.” The actual number of rows—zero—will be very far off from the optimizer’s guess of 1/144th of the rows in the table, and the result is typically a suboptimal plan, due to the poor cardinality estimates.

Dynamic sampling can help solve this. When it is set high enough, to level 3 or above, the optimizer will validate its guesses by using a dynamic sample.

To demonstrate this, I’ll create a table with some very specific data. Note that in this table, if the FLAG1 column has a value of Y, the FLAG2 column will have a value of N, and vice versa. All the data is either Y, N, or N, Y—there are no Y, Y records and no N, N records. Listing 4 shows the creation of the table and the gathering of statistics.

Code Listing 4: Creating the “FLAG” table and gathering statistics

SQL> create table t

  2    as select decode( mod(rownum,2), 0, 'N', 'Y' ) flag1,

  3                 decode( mod(rownum,2), 0, 'Y', 'N' ) flag2, a.*

  4      from all_objects a

  5    /

Table created.



SQL > create index t_idx on t(flag1,flag2);

Index created.



SQL > begin

  2              dbms_stats.gather_table_stats

  3              ( user, 'T',

  4              method_opt=>'for all indexed columns size 254' );

  5     end;

  6     /

PL/SQL procedure successfully completed.

So I have the table and have gathered statistics, including histograms for the FLAG1 and FLAG2 columns. The following shows the number of rows in the table, half the number, and a quarter of the number:

SQL> select num_rows, num_rows/2, 

num_rows/2/2 from user_tables 

where table_name = 'T';



NUM_ROWS  NUM_ROWS/2  NUM_ROWS/2/2

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

   68076       34038         17019

If I look at the data in the table, I can see how many of the rows would be returned for FLAG1 = 'N’ (half of the data, given how I constructed it) and how many would be returned for FLAG2 = 'N’ (again half of the data). I can verify this by using autotrace again, as shown in Listing 5.

Code Listing 5: Good cardinality estimates, looking at half of the table data

SQL> set autotrace traceonly explain

SQL> select * from t where flag1='N';



Execution Plan

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

Plan hash value: 1601196873



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

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

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

|   0 | SELECT STATEMENT  |      | 33479 |  3432K|   292   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T    | 33479 |  3432K|   292   (1)| 00:00:04 |

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



Predicate Information (identified by operation id):

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

   1 - filter("FLAG1"='N')



SQL> select * from t where flag2='N';



Execution Plan

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

Plan hash value: 1601196873



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

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

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

|   0 | SELECT STATEMENT  |      | 34597 |   3547K|   292   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T    | 34597 |   3547K|   292   (1)| 00:00:04 |

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



Predicate Information (identified by operation id):

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



   1 - filter("FLAG2"='N')

So far, so good—the estimated cardinalities are accurate, and the optimizer can generate optimal query plans.

Last, I can see the value the optimizer will “guess” by default if I query FLAG1 = 'N’ and FLAG2 = 'N’—in this case, about a quarter of the rows in the table, as shown in Listing 6.

Code Listing 6: Poor cardinality estimates, looking at a quarter of the table data

SQL> select * from t where flag1 = 'N' and flag2 = 'N';



Execution Plan

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

Plan hash value: 1601196873



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

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

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

|   0 | SELECT STATEMENT  |      | 17014 |  1744K|   292   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T    | 17014 |  1744K|   292   (1)| 00:00:04 |

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



Predicate Information (identified by operation id):

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



   1 - filter("FLAG1" = 'N' AND "FLAG2" = 'N')

Listing 6 shows that when the predicate gets just a little more complex, the optimizer misses the estimated cardinality by a huge amount—it doesn’t know about the relationship between FLAG1 and FLAG2. Enter dynamic sampling, shown in Listing 7.

Code Listing 7: Good cardinality estimate, looking at only six rows

SQL> select /*+ dynamic_sampling(t 3) */ * from t where flag1 = 'N' and flag2 = 'N';



Execution Plan

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

Plan hash value: 470836197



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

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

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

|   0 | SELECT STATEMENT            |       |    6 |   630 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    6 |   630 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | T_IDX |    6 |       |     1   (0)| 00:00:01 |

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



Predicate Information (identified by operation id):

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



   2 - access("FLAG1"='N' AND "FLAG2"='N')

Note the much better row estimate (6 instead of more than 17,000) in Listing 7, compared to Listing 6, and also note the very different (and now optimal) plan. Instead of a full table scan, the optimizer has decided to use the index, and this execution plan will definitely be optimal compared to the original full table scan, because in reality, no data will be returned. The index will discover that instantly.

The Dynamic Sampling Levels

A frequently asked question about dynamic sampling is, “What do all of the levels mean?” The answer is pretty straightforward and documented in Oracle Database Performance Tuning Guide.

I reproduce that information here:

The sampling levels are as follows if the dynamic sampling level used is from a cursor hint or from the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter:

• Level 0: Do not use dynamic sampling.
• Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).
• Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
• Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
• Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
• Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.
• Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.

Note that in Oracle9i Database Release 2, the default setting for dynamic sampling is level 1. At this setting, the optimizer will tend to use dynamic sampling only in some cases when a table is unanalyzed. This includes the case of global temporary tables without statistics but doesn’t ensure that dynamic sampling will be used against all unanalyzed tables. In Oracle Database 10g Release 1 and above, the default setting was raised to 2. This setting ensures that the optimizer will have a good estimate whenever it optimizes a query that accesses a table with no statistics.

I used level 3 in the “FLAG” table example in Listing 7. Level 3 instructs the optimizer to collect a sample to validate a guess it might have made. For example, if I turn on SQL_TRACE and run the example in Listing 7, I will find the SQL in Listing 8 in my trace file.

Code Listing 8: Trace file SQL generated by Listing 7 query

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE

   NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')

   NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_00"),

   NVL(SUM(C2),:"SYS_B_01"), NVL(SUM(C3),:"SYS_B_02")

FROM

  (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T")

   NO_PARALLEL_INDEX("T") */ :"SYS_B_03" AS C1, CASE WHEN "T"."FLAG1"=

   :"SYS_B_04" AND "T"."FLAG2"=:"SYS_B_05" THEN :"SYS_B_06" ELSE :"SYS_B_07"

   END AS C2, CASE WHEN "T"."FLAG2"=:"SYS_B_08" AND "T"."FLAG1"=:"SYS_B_09"

   THEN :"SYS_B_10" ELSE :"SYS_B_11" END AS C3 FROM "T" SAMPLE BLOCK

   (:"SYS_B_12" , :"SYS_B_13") SEED (:"SYS_B_14") "T") SAMPLESUB

The bold code in Listing 8 shows the optimizer trying to validate its guess—it is looking for any correlation between the FLAG1 and FLAG2 columns. By executing this sampling query during the hard parse, the optimizer was able to figure out that very few rows would be returned by this WHERE clause, adjust the estimated cardinality, and arrive at the correct plan.

When to Use Dynamic Sampling

“When should I use dynamic sampling?” is a tricky question. As with any other feature, there are times to use it and times to avoid it. So far I’ve concentrated on the “goodness” of dynamic sampling, and based on that, it seems that you should set the level to 3 or 4 and just let the optimizer always use dynamic sampling to validate its guesses.

That makes sense in an environment in which you spend most of your time executing SQL and very little of your overall time hard-parsing the SQL. That is, the SQL you are executing runs for a long time and the parse time is a small portion of the overall execution time, such as in a data warehousing environment. There, dynamic sampling at levels above the default makes complete sense. You are willing to give the optimizer a little more time during a hard parse (when sampling takes place) to arrive at the optimal plan for these complex queries.

That leaves the other classic type of environment: the online transaction processing (OLTP) system. Here, in general, you are executing queries thousands of times per second and spend very little time executing a given query—the queries are typically small and fast. Increasing the parse time in an OLTP system might well cause you to spend more time parsing than executing SQL. You do not want to increase the parse times here, so higher levels of dynamic sampling would not be advisable.

So what happens when you need the benefit of the dynamic sample at level 3 or above in an OLTP system? That is when it would be time to look into SQL Profiles, a new feature of Oracle Database 10g Release 1 and above (download.oracle.com/docs/cd/B28359_01/server.111/b28274/sql_tune.htm#PFGRF02605).

A SQL profile is roughly equivalent, in some respects, to a really aggressive dynamic sample—it, too, validates any guesses, but it has the ability to persist this information in the data dictionary. In some respects, a SQL profile is like a “static sample,” as opposed to the dynamic samples we’ve been discussing. Using a SQL profile is a bit like gathering statistics for a query and storing that information for the optimizer to use at hard parse time—it saves on the cost of dynamic sampling by “sampling” once and persisting the values.

So, why would you not just always use SQL Profiles? Well, in a data warehouse and many reporting systems, you do not utilize bind variables—you put the literal values into the SQL query itself, so the optimizer has good insight into exactly the data you are interested in. In a data warehouse system, query performance is paramount and the users are typically generating SQL in an ad hoc fashion. They do not run the same SQL from day to day, so there is no SQL from which to create a SQL profile! The SQL is generated, executed, and maybe never executed again. Dynamic sampling is perfect for such a data warehouse situation; it quickly validates the optimizer guesses at hard parse time and gives the optimizer the information it needs right then and there. Because that query will likely not be executed over and over again, the information need not be persisted in the data dictionary.

In the OLTP system, the opposite is true. You do use bind variables; you do use the same SQL over and over and over again. Avoiding any bit of work you can at runtime is your goal in this kind of system, because you want subsecond response times. Here the SQL profile makes more sense: you gather the statistics for the query once and persist them for all subsequent optimizations of that query.

More Information

In addition to the Oracle documentation at otn.oracle.com/documentation, Jonathan Lewis’ excellent book Cost-Based Oracle Fundamentals (Apress, 2006) and my book Effective Oracle by Design (Oracle Press, 2003) provide more information on dynamic sampling and other optimizer topics.

2009년 1월 오라클 매거진에 실린 tablespace encrypting에 관한 설명입니다.
이전의 column encrypting의 제한을 없앴다고하네요..

전 써보진 않았지만.. 유용할 듯..


Encrypting Tablespaces
By Arup Nanda

Encrypt tablespaces transparently—without affecting performance.

In an era when data security is critical in almost every type of business, databases are particularly important. This is where the organization’s crown jewels live—credit card numbers, Social Security information, names and addresses—the list is endless. The data security risk increases when business data leaves the security of an organization’s databases in the form of backup tapes. Historically, these offsite tapes have been the source of many data thefts. And because the tapes can be restored on any equivalent machine, attackers can browse the data at their leisure, and there is nothing the organization can do to protect the data then.

How can you prevent this from happening? The simple answer is encryption—encrypt the data in the database and store the encryption key in a different place. Thieves might be able to access the backup tapes and restore the database on a different server, but without the encryption key, they won’t be able to see the data.

Oracle introduced transparent data encryption (TDE) column encryption in Oracle Advanced Security with the release of Oracle Database 10g Release 2. (For more information, see “Transparent Data Encryption” in the September/October 2005 issue of Oracle Magazine.) Although this feature enables you to encrypt columns of a table without writing a single piece of code and helps with several key compliance regulations, it can negatively affect performance in applications when an encrypted column is used in range scans or as a foreign key. Column encryption is performed transparently within the database SQL layer, and indexes on an encrypted column are built on the encrypted values. Because encrypted values look random and disconnected, range scan operations on encrypted columns are not effective. Although column encryption occurs quickly, the range scan limitation is a real challenge in many real-world situations.

Enter TDE Encrypted Tablespaces

The Oracle Advanced Security option for Oracle Database 11g introduces a nifty new TDE feature—tablespace encryption—that allows a whole tablespace to be encrypted and therefore addresses the range scan and foreign key limitation of column-level encryption. The new tablespace is created as encrypted, and the data, whether tables or indexes, is stored encrypted in that tablespace, as shown in Figure 1. When a user selects that data, the server process moves the data from storage to the buffer cache and subsequently to the user’s session. The data is decrypted before being loaded into the buffer cache, so it’s always in cleartext (unencrypted) inside the buffer cache. Almost all types of data access—including index scans, table joins, and so on—happen in the buffer cache, so the performance of operations involving data from the encrypted tablespace is no different from that involving data from a normal, unencrypted tablespace.

Encryption requires at least two things: an encryption key and an algorithm. TDE uses what is known as a two-tier key architecture: both column and tablespace encryption keys are stored in the database but are encrypted with another key called the master key. The master key is stored outside the database in a special container called an external security module, which can be something as easy to set up as an Oracle wallet or as sophisticated as a hardware security module device. The Oracle wallet is a file formatted according to Public Key Cryptography Standard No. 12 and encrypted with a password. For using the wallet as the external security module, a password must be provided to make the master key accessible to the database. Unless the right password is supplied, the wallet can’t be opened and the encrypted data can’t be retrieved. The wallet is automatically closed when the database instance is shut down and must be reopened by a security officer when the instance starts. So although thieves might be able to restore a database from tapes, without the wallet and the password, they will not be able to view the encrypted data. (In the hardware security module case, the hardware device must be made available to the database in a manner specified by the vendor of the device.)

Figure 1: Loading of data buffers

With tablespace encryption, before data buffers are written back to disk (as a result of the checkpoint process), they are encrypted by DB Writer processes (DBWn), as shown in Figure 2. Operations, such as direct path inserts and reads that manipulate the data directly in the database, perform encryption inline. When the log buffers are written to the redo logs by the log writer process, they are encrypted as well, so the initial and subsequently archived redo logs contain only encrypted data.

Figure 2: Flushing of buffers from cache to disk

Tablespace Encryption Setup

Let’s look at how to set up TDE tablespace encryption, using a file-based wallet. Note that the compatibility of the database must be set to 11.1 or higher. First, if you don’t have one, create the wallet:

1. Make sure the ORACLE_BASE variable has been set. If it has not, set it by issuing

$ export ORACLE_BASE=/opt/oracle


2. Change to the ORACLE_BASE directory and then to the admin subdirectory for that instance. In my case, the instance is named prolin1, so I issue 

$ cd $ORACLE_BASE/admin/prolin1


3. Create a directory called “wallet” to hold the wallet: 

$ mkdir wallet


4. Create the wallet, along with a secure password, preferably containing a mix of alphanumeric characters, such as “T45rustMe54”: 

$ sqlplus / as sysdba


SQL> alter system set encryption key 


identified by "T45rustMe54";



The password is case-sensitive.

The preceding step will create the wallet as well as open it. You need to create the wallet only once. After the database is opened, the wallet remains open until either the wallet is explicitly closed or the database is shut down, at which time the wallet automatically closes. You can reopen this wallet after the database is restarted, by using

SQL> alter system set wallet open 




identified by "T45rustMe54";





System altered.

Now that the wallet is set up, you can create the encrypted tablespace.

1. The following code sets up an encrypted tablespace named enc128_ts:

 





create tablespace enc128_ts





datafile '/u01/oracle/database/





enc128_ts.dbf'





size 1M autoextend on next 1M





encryption using 'AES128'





default storage (encrypt)





/

Note the special encryption using 'AES128’ clause, which indicates that the AES algorithm is to be used with a 128-bit key. You can also use the values AES192 and AES256 (in place of AES128, the default value) to use 192- and 256-bit keys, respectively.

2. Once the tablespace is created, you can create objects in it. For instance, the following code creates a table called ACCOUNTS_ENC:

create table accounts_enc (





  ACC_NO        NUMBER           NOT NULL,





  FIRST_NAME  VARCHAR2(30)  NOT NULL,





... other columns ...





)





tablespace enc128_ts;

That’s it; no special clause is required. All the columns of the table (or anything else created in this tablespace) will be encrypted.

To confirm encryption, you can insert a record with a value “David” for FIRST_NAME and search the datafile you created for the enc128_ts tablespace for that value:

 





SQL> insert into accounts_enc values (1, 'David' , ...); 





$ strings enc128ts_ts.dbf | grep David

This will not show any output, because the “David” value has been stored in an encrypted tablespace. Searching for a cleartext value found nothing, as expected. (Note that if you do a string search in a file of an unencrypted tablespace, a successful search will return the cleartext from the file.)

Tablespace Encryption and Performance

An issue with any encryption method is the negative impact on performance. For example, searches for patterns in the values in an encrypted column may or may not be able to use indexes. That is exactly where TDE tablespace encryption excels—it allows data to be in cleartext in the buffer cache, where all the searching occurs.

To accurately ascertain the performance impact of encryption in the tablespace, we can do a small test. First, let’s create one normal tablespace:

create tablespace normal_ts





datafile '/u01/oracle/database/





normal_ts.dbf'





size 1M autoextend on next 1M;

Next, let’s create two identical tables—ACCOUNTS_REG and ACCOUNTS_ENC—in tablespaces normal_ts and enc128_ts (the encrypted tablespace we created earlier), respectively, and populate the tables in the same way. Finally, let’s create an index on each of the tables on the FIRST_NAME column. The code for creating the tables and populating the data is available in the sample code download for this article.

With the data set up, we run a query against one table, ACCOUNTS_REG, to find all the first names starting with D. This query uses an index on the FIRST_NAME column, as shown in Listing 1. Next, we run the same query but replace ACCOUNTS_REG with ACCOUNTS_ENC, as shown in Listing 2. The execution time is approximately the same for the query in both the unencrypted and encrypted tablespaces, with about the same number of blocks fetched in both cases. This shows that there is no significant performance penalty for querying tables in encrypted tablespaces.

Code Listing 1: Index scan on table in unencrypted tablespace

SQL> set autot on explain stat





SQL> set timing on





SQL> select first_name





  2    from accounts_reg





  3    where first_name like 'D%'





  4    /











... the rows come here ...











50161 rows selected.











Elapsed: 00:05:36.38











Execution Plan





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





Plan hash value: 966430551











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





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





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





|   0 | SELECT STATEMENT |              |   210K|  1442K|  513    (1)| 00:00:07 |





|*  1 |  INDEX RANGE SCAN|IN_ACC_REG_FN |   210K|  1442K|  513    (1)| 00:00:07 |





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





Predicate Information (identified by operation id):





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











   1 - access("FIRST_NAME" LIKE 'D%')





        filter("FIRST_NAME" LIKE 'D%')

















Statistics





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





           1  recursive calls





           0  db block gets





        3458  consistent gets





         127  physical reads





...

Code Listing 2: Index scan on table in encrypted tablespace

SQL> set autot on explain stat





SQL> set timing on





SQL> select first_name





  2    from accounts_enc





  3    where first_name like 'D%'





  4    /











... the rows come here ...











Elapsed: 00:05:33.85











Execution Plan





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





Plan hash value: 399953395











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





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





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





|   0 | SELECT STATEMENT  |               |   210K|  1442K|   513   (1)| 00:00:07 |





|*  1 |  INDEX RANGE SCAN | IN_ACC_ENC_FN |   210K|  1442K|   513   (1)| 00:00:07 |





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











Predicate Information (identified by operation id):





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











   1 - access("FIRST_NAME" LIKE 'D%')





        filter("FIRST_NAME" LIKE 'D%')

















Statistics





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





           1  recursive calls





           0  db block gets





        3427  consistent gets





         127  physical reads





...

Comparison of Tablespace and Column-Level Encryption

Column-level TDE allows you to encrypt data in a specific column only. This is how to encrypt the FIRST_NAME column in the ACCOUNTS_REG_ENC table, created from the ACCOUNTS_REG table:

create table accounts_reg_enc 





nologging as 





select * from accounts_reg; 











alter table accounts_reg_enc





modify first_name encrypt using 'AES128'





no salt;

After this modification, the FIRST_NAME column values will be stored encrypted in the ACCOUNTS_REG_ENC table. All other columns will be in cleartext. However, because the blocks of this table in the buffer cache will be replicas of the table, the FIRST_NAME column will still be encrypted in the buffer cache. So, there will be a severe impact on the performance of the index scans using FIRST_NAME.

Let’s examine the impact by running a small test. We issue a query against the ACCOUNTS_REG_ ENC table (which is in an unencrypted tablespace but with its FIRST_NAME column encrypted). We search for first names starting with D, and the query uses the index. We repeat the query against the ACCOUNTS_ENC table (in the encrypted tablespace). Then we examine the impact of the encrypted column on query performance, by issuing autotrace on explain stat, which shows the optimization plan used as well as statistics such as consistent gets. Listing 3 shows the commands and the output.

Code Listing 3: Query comparison of column-level and tablespace encryption

/* Run a query on the ACCOUNTS_REG_ENC table (in the unencrypted */





/* tablespace, but with the encrypted first_name column): */











SQL> set autotrace on explain stat





SQL> set linesize 120





SQL> select count(1) from accounts_reg_enc





  2    where first_name like 'D%';











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





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





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





|   0 | SELECT STATEMENT        |               |      1 |       7 |   686   (5)| 00:00:09 |





|   1 |  SORT AGGREGATE         |               |      1 |       7 |            |          |





|*  2 |     INDEX FAST FULL SCAN| IN_ACC_REG_FN |  50000 |     341K|   686   (5)| 00:00:09 |





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











Predicate Information (identified by operation id):





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





 





   2 - filter(INTERNAL_FUNCTION("FIRST_NAME") LIKE 'D%')





 





 





Statistics





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





          0  recursive calls





          0  db block gets





      13963  consistent gets





...











/* Now, run the same query on the ACCOUNTS_ENC table (in the encrypted tablespace): */











SQL> select count(1) from accounts_enc 





  2    where first_name like 'D%';











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





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





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





|   0 | SELECT STATEMENT  |               |     1 |      7 | 513      (1)| 00:00:07 |





|   1 |  SORT AGGREGATE   |               |     1 |      7 |             |          |





|*  2 |   INDEX RANGE SCAN| IN_ACC_ENC_FN |   210K|   1442K| 513      (1)| 00:00:07 |





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





 





Predicate Information (identified by operation id):





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











   2 - access("FIRST_NAME" LIKE 'D%')





        filter("FIRST_NAME" LIKE 'D%')

















Statistics





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





          0  recursive calls





          0  db block gets





        120  consistent gets





...

Consider the difference. The access path changed from INDEX FAST FULL SCAN for the table with the encrypted column (ACCOUNTS_REG_ENC) to INDEX RANGE SCAN for the table in the encrypted tablespace (ACCOUNTS_ENC), which resulted in a reduction of consistent gets from 13,963 to a mere 120—less than 1 percent of the original value. This means that the query against the encrypted tablespace table generated just 1 percent of the logical I/O of the query on the table with the encrypted column.

A second major difference is in column restrictions. Certain datatypes can’t be encrypted with TDE column encryption, columns under TDE can’t be used for foreign keys, only B-tree indexes can be created against the columns under TDE—and these are just some of the limitations of TDE column encryption. However, there is no restriction on these objects in an encrypted tablespace.

TDE column encryption has additional space requirements. Typically, encrypted values are larger than unencrypted values, causing a table with column encryption to be larger overall. To check for the exact increase, we can use the show_space procedure to show the space inside the tables—ACCOUNTS_REG (the table in the unencrypted tablespace), ACCOUNTS_ENC (the table in the encrypted tablespace), and ACCOUNTS_REG_ENC (the ACCOUNTS_REG table with only the FIRST_NAME column encrypted). Listing 4 shows the output. Note that the space consumption (of full blocks) is about the same for both forms of the table—in the unencrypted and encrypted tablespaces. However, the table in the unencrypted tablespace with the encrypted column takes about 15K full blocks, against about 10K in the table in the encrypted tablespace—about 50 percent more space. So, not only is the performance better with TDE tablespace encryption, but there is also no discernible additional space consumption, which, in turn, boosts performance by reducing I/O.

Code Listing 4: Space consumption on various forms of the table

SQL> set serveroutput on











SQL> exec show_space ('ACCOUNTS_REG','ARUP')





Unformatted Blocks .....................                                 0





FS1 Blocks (0-25)  .....................                                 2





FS2 Blocks (25-50) .....................                                 0





FS3 Blocks (50-75) .....................                                 0





FS4 Blocks (75-100).....................                                 0





Full Blocks        .....................                            10,938





Total Blocks............................                            11,088





Total Bytes.............................                        90,832,896





Total MBytes............................                                86





Unused Blocks...........................                                 1





Unused Bytes............................                             8,192





Last Used Ext FileId....................                                 6





Last Used Ext BlockId...................                            40,969





Last Used Block.........................                               847











SQL> exec show_space ('ACCOUNTS_ENC','ARUP') 





Unformatted Blocks .....................                                 0





FS1 Blocks (0-25)  .....................                                 1





FS2 Blocks (25-50) .....................                                 0





FS3 Blocks (50-75) .....................                                 0





FS4 Blocks (75-100).....................                                 1





Full Blocks        .....................                            10,967





Total Blocks............................                            11,120





Total Bytes.............................                        91,095,040





Total MBytes............................                                86





Unused Blocks...........................                                 4





Unused Bytes............................                            32,768





Last Used Ext FileId....................                                 7





Last Used Ext BlockId...................                            10,249





Last Used Block.........................                               876

















SQL> exec show_space ('ACCOUNTS_REG_ENC','ARUP')





Unformatted Blocks .....................                                 0





FS1 Blocks (0-25)  .....................                                 2





FS2 Blocks (25-50) .....................                                 0





FS3 Blocks (50-75) .....................                                 0





FS4 Blocks (75-100).....................                                 1





Full Blocks        .....................                            15,680





Total Blocks............................                            15,856





Total Bytes.............................                       129,892,352





Total MBytes............................                               123





Unused Blocks...........................                                 6





Unused Bytes............................                            49,152





Last Used Ext FileId....................                                 6





Last Used Ext BlockId...................                            29,705





Last Used Block.........................                               490

Encrypted Tablespace Administration

This article has demonstrated how to use the command line to create encrypted tablespaces, but you can also create encrypted tablespaces via Oracle Enterprise Manager. To create an encrypted tablespace in Oracle Enterprise Manager, from the main Database page, choose the Server tab and then click the Tablespaces link under Storage. On the page, click Create, which brings up a screen similar to the one shown in Figure 3. Check the Encryption check box, and click Encryption Options. That will lead to a new screen where you can specify the type of encryption algorithm.

Figure 3: Encrypted tablespace creation via Oracle Enterprise Manager

Different database views can help you monitor encrypted tablespaces by indicating the tablespace number, the encryption algorithm, and whether a tablespace is encrypted. The ENCRYPTED column in the DBA_TABLESPACES data dictionary view shows whether a tablespace is encrypted (YES or NO). If a tablespace is encrypted, the relevant information is shown in a different view: V$ENCRYPTED_TABLESPACES.

The V$ENCRYPTED_TABLESPACES view includes the following columns:

• TS#. The tablespace number
• ENCRYPTIONALG. The encryption algorithm, such as AES128
• ENCRYPTEDTS. Indicates whether the tablespace is encrypted (value is YES or NO)

Note that when you recover an encrypted tablespace, the wallet must be open, and you can transport an encrypted tablespace to a different database, but the other database must have the same wallet (and therefore the same master key). The wallet password for the other database can be different, but the wallet must be the same. If you transport an encrypted tablespace across platforms, the target platform must have the same endianness for encrypted tablespaces.

Finally, note that you can only create encrypted tablespaces; you cannot modify existing tablespaces to encrypt them. So, when you need existing data in encrypted tablespaces, the best solution is to first create encrypted tablespaces and then move the objects from the unencrypted tablespaces to them.

Conclusion

In general, encryption solves a security issue while introducing a new one: degraded performance. The latter is not acceptable in many real-world situations, so many organizations have little choice but to sacrifice encryption in favor of performance. With transparent tablespace encryption, however, degraded performance is no longer an issue—the performance in an encrypted tablespace is on a par with cleartext performance in unencrypted tablespaces while the data is protected at the storage level via encryption. And best of all, encryption is done transparently without your having to write a single line of code.

Transparent tablespace encryption is an example of a feature that lets you have your cake and eat it too.

OTN 11g new feature 자료입니다.

Oracle Database 11g: The Top New Features for DBAs and Developers by Arup Nanda

In this multipart series, learn how important new features such as Database Replay, Flashback Data Archive, and SecureFiles work via simple, actionable how-to's and sample code.

Change, although constantly present, is seldom risk-free. Even if the change is relatively minor (creating an index for example), your goal is probably to predict its precise impact as accurately as possible and then take appropriate action

Many new change assurance (or "Real Application Testing," as Oracle calls it) features in Oracle Database 11g bring that dream closer to reality. The Database Replay tool, for example, allows you to capture production database workload and replay it in a test (or even the same) database to assess the impact of change. Or consider SQL Performance Analyzer, which predicts the performance impact of changes to SQL before they are made. In my opinion, this Real Application Testing functionality alone justifies the upgrade.

Overall, Oracle Database 11g makes database infrastructure far more efficient, resilient, and manageable. For example, very compelling new features in the realm of partitioning ease the design and management of partitioned tables immensely.

In this series (as in the previous series focusing on Oracle Database 10g), you will learn how these new features work via simple, actionable how-to's and sample code.

Enjoy the series, and the release!

Download series as PDF (4.35MB zip)

---

Subscribe to this series via

Database Replay Explore Database Replay, the new tool that captures SQL statements and lets you replay them at will.	Partitioning Learn about Referential, Internal, and Virtual Column partitioning; new sub-partitioning options; and more.
Transaction Management Get an introduction to Flashback Data Archive and explore Enterprise Manager's LogMiner interface.	Schema Management Add columns with a default value easily and explore invisible indexes, virtual columns, and read only tables.
SQL Plan Management Use bind variables that pick the right plan every time and ensure a new execution plan is perfect before it's used.	SQL Performance Analyzer Accurately assess the impact of rewriting of SQL statements and get suggested improvements.
SQL Access Advisor Get advice about optimal table design based on actual use of the table, not just data.	PL/SQL: Efficient Coding Triggers that fire several times at different events and ability to force triggers of the same type to follow a sequence are some new gems.
RMAN Explore Data Recovery Advisor, do parallel backup of the same file, and create and manage virtual catalogs.	Security Learn about Tablespace Encryption, case-sensitive passwords, data masking, and other features.
Automatic Storage Management Learn about new SYSASM role, variable extent sizes, and other ASM improvements.	Manageability Explore automatic memory management, multicolumn statistics, online patching, and more features.
Caching and Pooling Explore SQL Result Cache, PL/SQL Function Cache, and Database Resident Connection Pooling.	SQL Operations: Pivot and Unpivot Present information in a spreadsheet-type crosstab report from any relational table using simple SQL, and store any data from a crosstab table to a relational table.
SecureFiles Explore next-generation LOBs: LOB encryption, compression, deduplication, and asynchronicity.	Resiliency Explore Automatic Health Monitor, Automatic Diagnostic Repository, and other new resiliency features.
Data Guard Query the physical standby database in real time without shutting down recovery, just for starters.	PL/SQL Performance Explore in-lining of code, "real" native compilation, PLS timer, use of simple integer, and more.
Data Warehousing and OLAP Get a tour of new features in these areas, including Cube Organized MVs.	And Don't Forget... COPY command, Export/Imports, Data Pump improvements, and more.

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.

이전 dictionary managed tablespace 방식에서는 엄청난 extent가 발생했을 때
이에 대한 정리작업 (drop, delete 등)을 수행할 경우
UET$와 FET$ dictionary table 갱신에 많은 시간이 소모 되었었죠..

다음의 내용은 OTN에 있는 내용을 가져 왔습니다.

제품 : ORACLE SERVER

작성날짜 :

DICTIONARY MANAGED TABLESPACE를 사용 대비, LOCALLY MANAGED TABLESPACE를 사용하는 것의 장점
===========================================================================================

PURPOSE

---

이 문서는, Locally Managed Tablespace에 대한 설명과 함께,
Dictionary Managed Tablespace 대비, 장점을 기술하는 데 목적이 있다.

Explanation

---

1. LOCALLY MANAGED TABLESPACE

Locally Managed Tablespace는, 자체 extent에 대한 관리를 각각의 데이터 파일에 비트맵 형식으로 저장하여 관리하는 테이블스페이스로, 데이터 파일을 구성하는 블럭이 비어 있는지, 사용 중인지에 대한 정보를 관리한다. 비트맵의 각각의 비트는, 하나의 블럭 또는 블럭의 그룹에 해당하는 정보를 나타낸다.

익스텐트가 할당되거나, 비워지거나, 재사용될 때, 오라클에서는 블럭의 새로운 상태를 나타내기 위해 비트맵의 값을 변경한다. 이와 같은 변경사항은 기본 방식인 Dictionary Managed Tablespace와는 달리, rollback 정보를 생성하지 않는데, 이것은 데이터 딕셔너리의 테이블을 갱신하지 않기 때문이다 (테이블스페이스 별 quota 정보는 제외).

1) 공간 정보 관리를 위한 내부 작업을 줄임.
2) 데이터 딕셔너리 테이블에 대한 경합 감소됨.
3) 익스텐트 관리와 관련된 관련 rollback 생성이 되지 않음.
4) Coalescing 이 불필요함.

2. 테이블스페이스의 공간 관리

1) 사용되지 않는 익스텐트 정보가 비트맵에 의해 관리됨.
(따라서, 테이블스페이스의 일부분이 비트맵 정보를 저장하는 데 사용됨)
2) 각 비트는, 블럭이나, 블럭의 그룹의 정보를 나타냄.
3) 비트 정보는, 사용 중인지, 그렇지 않은지를 나타냄.
4) DBA_EXTENTS 나 DBA_FREE_SPACE 등의 뷰는 동일하게 사용됨.

3. 구문 규칙

EXTENT MANAGEMENT 절의 LOCAL 옵션을 사용하면, 테이블스페이스가
Locally Managed 방식으로 생성된다.

Extent_management_clause:
[EXTENT MANAGEMENT
{DICTIONARY | LOCAL
{AUTOALLOCATE | UNIFORM [SIZE integer M] }}

옵션 설명:

DICTIONARY : 테이블스페이스에 대해 Dictionary Table를 사용하여 공간 정보를 관리. (default)

LOCAL : 테이블스페이스가에 대해 비트맵을 사용하여 Locally Managed 방식으로 공간 정보 관리

AUTOALLOCATE : 테이블스페이스에 대한 익스텐트 관리를 시스템에서 관장 (사용자는 익스텐트의 크기를 수동으로 지정할 수 없음)

UNIFORM : 테이블스페이스가 동일한 크기의 익스텐트로 구성되도록 지정함. 크기는 기본적으로 바이트 단위로 지정 ( 익스텐트 크기를 KB 또는 MB 단위로 지정하기 위해서는 K 또는 M 을 사용하여 지정) 이 옵션을 사용하게 되면, DEFAULT Storage 절, MINIMUM EXTENT 또는 TEMPORARY 옵션을 사용할 수 없다.

Oracle 9.2 이전 버젼에서는, EXTENT MANAGEMENT 절을 SYSTEM 테이블스페이스를 제외한 permanent tablespace나, temporary tablespace 생성 시 지정할 수 있었다. Oracle 9.2부터는 SYSTEM 테이블스페이스를 포함한 모든 테이블스페이스를 Locally managed 방식으로 생성할 수 있다.

CREATE DATABASE에서 EXTENT MANAGEMENT LOCAL 절을 사용하게 되면, 오라클에서는 SYSTEM 테이블스페이스를 Locally Managed Tablespace로 생성하며, 익스텐트의 크기는 오라클에서 결정하게 된다. 이 기능을 사용하기 위해서는, COMPATIBLE 옵션이 9.2 또는 그 이상으로 지정되어 있어야 한다.

Locally Managed SYSTEM tablespace는 기본적으로 AUTOALLOCATE 방식을 사용하게 되며, Locally Managed 방식으로 SYSTEM 테이블스페이스를 생성할 때, UNIFORM extent 크기를 지정할 수 없다.

다음 storage parameter(NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS)와 DEFAULT STORAGE는 Locally Managed Tablespace에서는 사용할 수 없다.

테이블스페이스 생성 후에는 공간관리 방법을 변경할 수 없다.

Oracle 8.1.6부터, Dictionary Managed Tablespace를 Locally Managed Tablespace로 마이그레이션을 할 수 있으나, 8.1.5에서는 그와 같은 작업을 수행할 수 없다.

기존에 사용해 왔던 테이블스페이스를 Locally Managed 방식으로 전환 시키기 위해서는 DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL 프로시져를 사용하면 된다.
그리고, DBMS_SPACE_ADMIN 패키지는 Locally Managed Tablespace를 관리하는 데 필요한 각종 프로시저를 제공한다.

다음 문장에서, 데이터베이스 블럭 크기가 2K인 것을 가정하였다.

CREATE TABLESPACE tbs_1
DATAFILE 'file_tbs1.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 128K

위 문장을 수행시키면, Locally Managed Tablespace를 생성하며, 모든 extent의 크기를 128K로 할 때, 비트맵의 각 비트는 64개 블럭에 대한 정보를 나타낸다. 기본적으로, 데이터베이스 블럭의 크기의 기본값을 2K로 가정하였을 때, 각 비트는 하나의 extent(128K)에 대한 정보를 나타내므로,
각 비트맵은 64개의 오라클 블럭을 필요로 하게 된다.

( 64 블럭 = 128K UNIFORM SIZE / 2K ORACLE BLOCK SIZE )

4. Dictionary Managed Tablespace를 사용하는 것 대비, Locally Managed Tablespace를 사용하였을 경우의 장점

1) Locally Managed Tablespace는 가용한 공간에 대한 정보를 데이터 딕셔너리에 저장하지 않으므로, 데이터 딕셔너리에 대한 경합을 줄이게 된다.

2) Extent에 대한 local management를 통해, 인접한 가용 공간의 정보를 자동으로 관리하게 되므로, 가용 extent에 대한 coalesce 작업을 수행하지 않아도 된다.

3) 공간 관리를 위한 데이터베이스 내부 처리 작업을 피할 수 있다. 반면 이와 같은 내부 처리 작업은 Dictionary Managed Tablespace에서는 필요한데, extent를 사용하거나, 반납을 하는 등의 작업이 발생할 때마다 rollback segment나 데이터 딕셔너리 테이블의 공간을 사용하거나 반납하는 등의 작업이 필요하게 된다.

4) Locally Managed Tablespace에서의 extent의 크기는, 시스템에 의해 자동적으로 관리된다. 반면, 모든 extent는 Locally Managed Tablespace 에서는 동일한 크기를 사용하게 할 수도 있다.

5) Extent에 대한 정보를 나타내는 비트맵의 변경 사항은 rollback 정보를 생성하지 않는다. 이것은, 데이터 딕셔너리의 정보를 변경하지 않기 때문이다. ( 테이블스페이스에 대한 quota 정보와 같은 일부 사항은 예외 )

6) Fragmentation을 줄일 수 있다.

Example

---

Reference Documents

---

<Note:93771.1> Locally Managed Tablespace in Oracle 8i
<Note:103020.1> Migration from Dictionary Managed to Locally Managed
Tablespaces
<Note:105120.1> Advantages of Using Locally Managed vs Dictionary Managed Tables
paces

문제가 발생했을때 자동으로 인지하고 필요한 dump를 수행하도록 해주는 LTOM 입니다.
처음 설정후 사용자는 ltom 기동만 해주면 되니 잘만 쓰면 편리한 툴이죠..

다음의 내용은 OTN에 있는 내용입니다.


제품 : Database

작성날짜 : 2007-12-21

PURPOSE

---

실시간 성능 자료 취합과 진단하는 LTOM 툴의 사용 방법을 소개한다.

CONTENTS

---

1. Automatci Hang Detection
2. System Profiler
3. Automatic Session Tracing
4. LTOM 설치하기
5. LTOM 수행하기
6. 부록 - 디렉토리구조

EXPLANATIONS

---

LiTe Onboard Monitor (LTOM) 은 자바로 개발된 실시간 성능진단툴로 DB 자료뿐만 아니라 UNIX OS 성능자료까지 취합하고, 미리 지정한 룰에따라 Hang 과 Slow Performance 시점을 인지하여 덤프와 트레이스파일 자동 생성해주므로 성능이슈 해결에 소요되는 시간과 리소스를 절감할 수 있다.

DB 또는 OS 성능이슈 진단시 가장 어려운 점은 "문제 발생 시점에" 필요한 "정확한 데이터"를
취합하는 것 자체가 힘들다는 것이다. 특히 언제 발생할지도 모르고, 몇초 단위의 짧은 시간동안만
지속되는 성능이슈라면 재현되기를 기다려서 분석자료를 얻는데만도 많은 리소스와 시간이
소진될뿐더라 거의 불가능한 경우가 많아 여러번의 시행착오를 겪어야한다.

LTOM 은 이런 유형의 성능이슈 해결을 위해 주요 3가지 기능을 제공한다.

. Automatic Hang Detection
. System Profiler
. Automatic Sesssion Tracing

1. Automatci Hang Detection

- 주의! 이 기능은 Oracle Support 나 경험 많은 DBA 의 안내에따라 사용해야한다.
덤프 설정 레벨별로 생성되는 트레이스 양을 인지하고, 시스템에 부하를 테스트 후 적용해야한다.

(1) 용도
v$session 또는 v$session_wait 의 wait event 를 기준으로 hang detect rule 을 설정한다.
예를들어, 새벽 2시 아무도 없을때 갑자기 'latch free' wait event 가 누적되면서 몇초간
DB hang 이 발생하는 경우에는 hang detect rule 을 'latch free' 가 15초 이상 지속되면
systemstate 덤프나 hanganalyze 덤프들을 자동 생성하도록 설정할 수 있다.

(2) 장점
. systemstate 덤프와 hanganalyze 덤프를 문제발생 시점에 사용자 개입없이 자동생성한다
. hang 덤프를 24*7 동안 취합가능하다.
. Automatic Hang Detection 이 발생하면 email notification 을 보낸다.
($TOM_HOME/src/ltommail.sh 에 email 설정)
. 과도한 트레이스 방지위해 동일 hang 이 반복적으로 발생해도 Automatic Hang Detect 설정
후 처음 hang 인지시점에만 덤프를 수행한다.

(3) 설정파일 : $TOM_HOME/init/hangDetect.properties
. 모니터링할 wait event, hang detect time 기준, 생성할 덤프들을 지정한다.
. HangDetect.properties 의 기본적인 덤프생성은 아래 순서이나, 사용자가 순서 및 레벨을
변경할 수 있다.

HangAnalyze Level 3
Systemstate Level 266
Wait 60 seconds
HangAnalyze Level 3
Systemstate Level 266

자세한 설정방법과 설정단위는 LTOM 에 포함된 hangDetect.properties 파일참고.

(4) 결과물
. $TOM_HOME/hanglog/hang*.log : systemstate 덤프 와 hanganalyze 덤프 내용 정리결과
. $TOM_HOME/hanglog/hang*.report : hang detect 히스토리와 생성된 덤프 리스트
. 실제 systemstate 덤프와 hanganalyze 덤프들은 udump 에 생성된다.

(6) 사용예제

TM enqueue wait 이 발생한 경우 Automatic Hang Detect 로 진단하는 절차이다.

. $TOM_HOME/init/hangDetect.properties 파일에 rule 지정 방법

a. 문제가 되는 wait event 와 waiting time 임계치 지정
EVENT= enq: TM - contention,VALUE=15

event name 은 v$event_name 의 event name 과 정확히 일치해야한다.
VALUE=15 는 초단위로 해당 wait event 가 15초이상 지속되면 지정한 덤프를 수행한다.

b. ACTION PLAN 추가
SYSTEMSTATE=266
DELAY=30
HANGANALYZE=4
BLOCKERS=Y

SYSTEMSTATE level 266 => 30초 기다린 후 => HANGANALYZE level 4 => Blocker 덤프

. $./startltom.sh 수행
===> Enter 1 to Start Auto Hang Detection
===> polling 간격 5초 지정

. TM enqueue wait 시나리오 만들기

세션1 연결
$ sqlplus scott/tiger
SQL> lock table junk in exclusive mode;

세션2 연결
$ sqlplus scott/tiger
SQL> lock table junk in exclusive mode;

. 몇분 기다리면 LTOM 화면에도 hang detect 되는 시점에 메시지가 나오고,
$TOM_HOME/hanglog/hang*.report 에도 hang detect 정보가 있다.
. $TOM_HOME/hanglog/hang*.log 에서 systemstate 덤프 정리내용과 hanganalyze 내용을 확인한다.

2. System Profiler

(1) 용도
AWR 과 Statspack 의 단점은 DB 성능이슈 진단시 non-Oracle 프로세스정보와 OS 리소스
(MEMORY, CPU, IO) 에대한 자료가 포함되지 않아 "전체적인 관점(a holistic point of view)"으로
성능이슈를 접근하기 힘들다는 것이다. 더구나 snapshot 들이 "분" 단위이기 때문에, "초" 단위의
일시적인 성능이슈에는 해당시점의 정보만 분리해서 분석할 수 없어서 정확한 원인규명을위해
추가자료를 취합해야한다.

(2) 장점
. DB 성능정보 뿐만아니라 OS 성능정보와 non-Oracle 프로세스 정보까지 포함한다.
. 초단위로 성능정보가 자동 취합된다.
. 결과를 OS 텍스트 파일로 저장하므로 정보취합시 DB 부하 없다
. LTOMg 라는 그래픽 툴로 결과를 확인하고 html 결과 리포트도 생성할 수 있다.

(3) 결과물
. $TOM_HOME/recordings/profile/proXXXXX.log (주기적으로 파일크기 정리 필요)
OS 취합정보 - top, vmstat, iostat
DB 취합정보 - v$session, v$process, v$sesson_wait, v$system_event, v$system_statistics

(4) 사용예제

. System Profiler 시작
$ ./startltom.sh
===> Enter 3 to Start System Profiling
===> Polling frequency = 5
===> Profile Which Oracle Sessions =A
===> Profile Unix Top Processes = Y
===> Profile Unix Vmstat Info = Y
===> Profile Unix Iostat Info = Y
===> Profile CPU Statistics = Y
===> Profile Current SQL Executing = R
===> Profile Which Level = 2

. 10분 후에 System Profiler 종료
===> Enter 4 to Stop System Profiling

. 결과확인
a. $TOM_HOME/recordings/profile 에서 텍스트파일을 직접 열어서 볼수 있다.
b. LTOMg 로 진단 결과를 그래픽하게 확인할 수 있다.
$ cd $TOM_HOME/ltomg
$ java -jar ltomg.jar -i $TOM_HOME/recordings/profile/pro*.log (최종 profile 로그명)

개별그래프를 온라인으로 확인할 수도 있고, "P" 옵션을 선택하면 그래프 및 설명 포함한
HTML 형태 리포트파일이 생성된다. HTML 파일은 $TOM_HOME/ltomg/profile 의 서브디렉토리로
생성되므로 해당 디렉토리에서 HTML 을 열어보거나, 서브디렉토리를 압축해서 다른 시스템으로
이동하여 웹브라우저로 확인하다.

3. Automatic Session Tracing

- 주의! 이 기능은 Oracle Support 나 경험 많은 DBA 의 안내에따라 사용되야 한다.
덤프 설정 레벨별로 생성되는 트레이스 양을 인지하고, 시스템에 부하를 테스트 후 적용해야한다.

(1) 용도
SQL trace 는 DB 성능이슈 진단시 가장 중요한 정보 중 하나이나, 언제 이슈가 발생할지
모르고 발생 전까지는 트레이싱할 세션들을 결정할 수 없을때 기존에는 전체 DB 에
트레이스를 설정하는 위험을 감수했다. Automatic Session Tracing 은 Wait Event ,
CPU 사용량, DB User 기준으로 룰을 미리 설정하여 문제시점을 자동으로 인지하여
해당되는 세션들만 event 10046 level 12 트레이스를 자동 생성한다.

(2) 장점
. 성능이슈 발생 시점에만 10046 트레이싱 한다.
. 특정 Oracle wait event 나 CPU 사용량에따라 트레이스 결정한다.
. 룰에 해당하는 세션중 트레이싱할 세션수를 제한할 수도 있다.
. 성능이슈 원인이 되는 SQL 과 USER 를 쉽게 찾아낼 수 있다.
. 메모리 버퍼 트레이싱하므로 대량 트레이스 파일 생성을 피할 수 있고 문제시점 전후
내용만 트레이스 파일로 생성하므로 디스크 부하가 적다.
. 메모리 버퍼 크기를 지정할 수 있고, 수동으로 메모리 버퍼를 파일로 내릴 수도 있다.
. Automatic Session Tracing exit 할때 트레이싱 중이던 세션들이 자동 tracing off 되나
임의로 세션 트레이스 종료하려면 $TOM_HOME/recordings/session/stopsessions.sql 을
수행하면 된다.

(3) 설정파일 : $TOM_HOME/init/sessionRecorder.properties

sessionRecorder.properties 파일내에 설정방법 & 예제가 포함되어있다.

EVENT=A,VALUE=B,C ====> EVENT 로 룰 설정시
where A = wait event you want to trigger on. The event must match exactly
the event name from v$event_name.
where B = the average wait time to turn on session tracing in microseconds
where C = the average wait time to dump memory tracing to a file in
microseconds. This value only necessary if using in memory
tracing. This value is ignored if you will be tracing to a file.

CPU=,VALUE=B,C ====> CPU 점유로 룰 설정시
where B = the average cpu time to turn on session tracing in 10s of
milliseconds
where C = the average cpu time to dump memory tracing to a file in 10's
of milliseconds. This value only necessary if using in memory
tracing. This value is ignored if you will be tracing to a file.

USER=A ====> DB USER 설정시
where A = db user you want to turn trace on. T

(4) 결과물
. $TOM_HOME/recordings/session - 트레이싱 히스토리
. 실제 트레이스 파일들은 udump, bdump 에 생성된다

(5) 사용예제

<문제상황>
고객사 업무기준에 모든 트랜잭션은 1초안에 끝나야하나 종종 1초이상 지속되는 트랜잭션이 있다.
LTOM 의 System Profile 으로 초단위 모니터링 결과 'global cache cr request' wait event 가
문제시점에 체크되었다. 하지만 1000 여개의 세션 중 어떤 SQL이 문제인지 알기위해 모든 세션에
10046 trace 를 설정하게되면 트레이싱 부하로 정상적인 트랜잭션까지 1초이상 걸리게된다.

<분석방법>
Automatic Session Recorder 가 'global cache cr request' wait event 가 1초이상 지속되는 시점에 해당 세션들을 트레이싱하게 설정한다. 이 방법은 전체 세션에 계속 10046 트레이스를 설정하는 것에 비하면 부하가 현저히 줄어든다.

<설정절차>

. 룰 지정
a. $TOM_HOME/init/sessionRecorder.properties 에 추가

EVENT=global cache cr request, VALUE=100000,1000000

event name 은 v$event_name 에있는 event name 과 space 까지 정확히 일치해야함.
VALUE 단위는 microsecond(1/1000000초)이고 100000(0.1초)는 메모리 트레이싱 시작 기준,
1000000(1초)은 메모리를 파일로 내리는 기준이다.

b. $ ./startltom.sh 수행
===> Enter 7 to Start Session Tracing
===> Enter a polling frequency in seconds : default 5초
===> Trace sessions to memory or file : default M to memory
===> Enter amount of memory for each trace buffer in bytes :
세션당 사용할 트레이싱 버퍼 지정 default 50000 bytes
===> Enter max processes to trace : 5-10 세션정도로 지정

c. 트레이싱 세션정보 LTOM 화면에서 모니터링
Enter 71 to Display Sessions Traced
Enter 72 to Dump All Trace Buffers
Enter 73 to Dump Specific Trace Buffer

Select option 74 to stop a specific session from being traced

d. 트레이싱 종료
===> Enter 8 to Stop Session Tracing

e. 각 10046 트레이스는 bdump, udump 에서 확인

4. LTOM 설치하기

(1) Supported Platforms: Solaris, Linux, HP-UX, AIX, Tru64
(2) 다운로드 : Metalink 에서 Note 352363.1 에 링크있음.
(3) 압축풀기
uncompress ltom.tar.Z
tar xvfp ltom.tar
(4) 초기화 : ~/ltom/tom_base/install/autoinstall.sh 수행

5. LTOM 수행하기

$ cd ~/tom_base/tom ($TOM_HOME)
$ ./startltom.sh

---

menu example

---

Enter 1 to Start Auto Hang Detection
Enter 2 to Stop Auto Hang Detection
Enter 3 to Start System Profiling
Enter 4 to Stop System Profiling
Enter 7 to Start Session Tracing
Enter 71 to Display Sessions Traced
Enter 72 to Dump All Trace Buffers
Enter 73 to Dump Specific Trace Buffer
Enter 8 to Stop Session Tracing

Enter S to Update status
Enter Q to End Program
CURRENT STATUS: HangDetection=OFF ManRec=OFF SessionRec=OFF
Please Select an Option:

---

6. 부록 - 디렉토리구조

tom_base /install - 설치관련 파일들
/tom /ltomg /gif - ltomg 결과 gif 파일들
/src - ltomg source 파일들
/profile - html 결과 파일들
/init - 환경설정 파일들
/hanglog - Automatic Hang Detection 시 생성 로그
/recordings /event - 사용안함
/profile - System Profiler 결과
/smart - 사용안함
/session - Automatic Session Tracing 로그
/src - ltom 용 SQL과 shell scripts
/tmp - ltom 용 임시파일

References

---

Note 352363.1 : LTOM - The On-Board Monitor User Guide
Note.461052.1 : LTOM System Profiler - Sample Output
Note 461050.1 : The LTOM Graph (LTOMg) User Guide
Note.461228.1 : The LTOM Graph FAQ

oracle database 내에 일정한 시간 수행해야 할 일들을 job으로 등록해 놓으면, 사용자가 일일이 수행해 줄 필요가 없어 무지 편하죠.. 또 특별한 일 없으면 손 봐줄 것도 없고..
그러다 보니 job이 failure가 16번 발생해 broken으로 빠지는 경우가 가끔 발생하곤 하죠..

이렇게 job을 등록해 놓았는데, 자동으로 수행이 되지 않을 때 다음의 checklist로 점검 해보세요..



다음은 job이 자동으로 수행되지 않은 경우 참고할 만한 노트 입니다.


참고 :
얼마전에 알게된 내용인데, shutdown 중 control + C로 끊는 경우 shared pool 내의 job을 자동으로 수행하게끔 해주는 flag 값이 0으로 설정되어 더이상 자동으로 진행이 안된다는 군요. 그럴땐..

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


저자 - Jeffrey Hunter

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

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

얼마전에  "smon recovery 중인 작업이 언제쯤 끝날까요? <undo_rollback_info.sql>"라는 포스팅을 했었죠. 오늘은 "transaction recovery를 조금 더 빨리 진행하게 하는 방법"에 대해 이야기 해보겠습니다. 

smon(pmon)이 recovery를 하고 있다는 것은 이미 user process가 해당 recovery를 cancel (kill -9 <process id>) 시키거나, database를 강제로 shutdown 시킨 이후의 상황이죠. 이 상황에서 recovery를 빠르게 하려면 두가지 방법이 있습니다.


여러개의 프로세서로 동시에 transaction recovery 하는 방법은 oracle 8i에서 소개된 fast start parallel recovery 기능이 있습니다. 이 기능은 fast_start_parallel_recovery parameter의 설정으로 가능합니다.

이 parameter는 (LOW/HIGH/FALSE)의 값을 지정할 수 있으며 LOW 지정시에는 CPU * 2개의 parallel slave process까지 기동할 수 있으며, HIGH의 경우 CPU * 4개의 parallel slave process가 기동 됩니다. parallel recovery는 v$fast_start_servers와 v$fast_start_transactions view로 확인이 가능합니다. 가끔 parallel recovery가 smon과의 통신으로 인해 성능 저하가 발생할 경우가 있다는데, 이럴 경우 FALSE로 설정하여 serial하게 recovery를 진행할 수 있습니다.



두번째 한번 수행시 많은 양의 recovery를 하게끔 유도하는 방법은 cleanup_rollback_entries parameter로 조정가능합니다. 기본적으로 smon은 한번에 20개의 undo entriy를 정리하고 잠깐 sleep 하게 됩니다. 따라서 많은 양의 transaction recovery가 필요한 경우 이 값을 많이 늘려 한번에 수행되는 양을 많게 설정합니다. 유의할 점은 smon에게 한번에 많은 양의 recovery를 지정하였으므로 CPU를 과도하게 소모할 수 있으므로 recovery시 수행되는 다른 작업에 영향을 끼칠 수 도 있습니다.

참고:
fast-start parallel rollback 기능에 대한 추가 설명

가끔 이전 데이터 backup을 이용해서 과거의 데이터를 추출해 내야 될 경우들이 있다.
또 어느 누군가 데이터를 삭제하거나 큰 사고로 특정 시점으로 돌아가야 하는 불행한 사태가 아주 가끔 벌어지곤 한다.

다행이 backup 받은게 남아 있다면 time-recovery를 수행하면 해당 시점까지의 복구가 가능하다. 그런데, 가끔 time-base recovery를 진행하다 보면 가끔 요런 메세지가 뜨기도 한다.



간단히 말하자면  "지금 reset log로 올리면 안올라 갈수 도 있으니 좀 더 recovery 하셔야 되요" 라는 내용이다. 이러한 메세지는 datafile header에 fuzzy bit가 설정되어 발생하는 메세지 이다.

fuzzy bit는 해당 datafile에 적용되어야 할 변경사항이 아직 남아 있다는 의미이다. 더 엄밀히 말하자면 fuzzy bit를 해제할 fuzzy marker가 필요하다는 의미이다.

 fuzzy bit는 hot-backup fuzzy, online fuzzy, media recovery fuzzy,absolute fuzzy의 4 가지가 있으며, 일반적으로 hot-backup fuzzy bit가 recovery시 종종 문제가 되곤 한다.

hot-backup buzzy bit는 tablespace에 begin backup이 수행되면 해당 datafile의 header에 fuzzy bit가 설정된다. 이후 end backup이 수행되면 해당 시점의 redo log내에 fuzzy marker가 등록되어 이 redo log를 적용하면 fuzzy bit가 해제 되게 된다.

따라서 hot backup으로 backup을 받은 datafile은 반드시 end backup 시점의, fuzzy marker를 갖고 있는 redo log file이 필요하게 된다.

v$datafile_header의 fuzzy column으로 fuzzy bit 상태를 알 수 있다.

OTN Discussion Forums : FUZZY BIT에 대해서 (DATAFILE의 FUZZY 상태) ...

오라클 11g release 1 documents library 입니다.

oracle 11g release 1 documents library

oracle 11g release 2 documents library

얼마전에 지금 "smon recovery 중인 작업이 언제쯤 끝날까요? <undo_rollback_info.sql>"라는 내용으로 SMON에서 recovery하는 내용을 모니터링하는 방법을 포스팅하긴 했지만..

이 방법은 수행중인 transaction을 무시하고 oracle을 내려버려서 smon이 transaction을 가지고 있는 rollback segment 별로 얼마나 rollback을 해야하는지 시간을 대충 산정할 수 있는 방법이죠.

현재 수행중인 transaction이 얼마나 작업을 했을까, 또 요거 끊으면 얼마나 rollback하는데 시간이 소모도리까? 이걸 알 수 있는 방법은 없을까요?




뭐 대략적인 방법이긴 하지만,  v$transaction의 used_ublk, used_urec column으로 대충 산정할 수 있습니다.

다음의 SQL은 transaction을 모니터링 할 수 있는 SQL 입니다. 뭐 v$session과 v$transaction을 join 한 단순한 script 입니다.

그럼, 우선 테스트를 할 table을 만들어 보고요..

이제 transaction을 만들어 보겠습니다. 시간이 좀 걸려야 하므로 1000만건 정도 만들겠습니다.

(음.. 제 PC내의 vmware에 설치한 oracle로 테스트를 하니 난리군요..
log buffer switch, log file switch completion,  .. disk IO가 안따라 줍니다..--;)

암튼 이 transaction이 수행되는 과정에서 used_urec는 10초당 12000 ~ 13000 씩, 빠를 때는 15000 개씩 쌓이는 군요..

총 insert 되는 데 걸린 시간은 약 3분 55초 09 이며, USED_UREC는 150593, USED_UBLK는 3772 개 입니다.  (used_urec와 실제 처리도니 row 수의 차이가 많군요.. 요건 나중에 함 알아보죠 ^^;)


rollback시 used_urec는 10초당 대충 3000 ~ 5000개 정도 씩 정리하네요..
이 수치만 보면 rollback 작업이 실제 transaction 보다 약 3배 정도 이상 느리게 나오는 걸로 보이네요..  (실제 used_urec를 모니터링 해보면 특정 시간당 정리하는 갯수의 편차가 좀 있습니다. )

실제 rollback에 걸린 시간은 11분 09초 94 입니다. transaction 보다 약 3배 이상 걸렸군요..
그리고 transaction 생성시엔 used_urec가 10초당 15000 정도인데 반해서 rollback시는 10초당 5000 정도.. (음 생각보단 performance가 많이 안좋게 나왔네요..)

위의 테스트는 제 PC에서 수행된 거라 운영시스템에서의 환경과는 많이 다를 수 있겠지만,
일반적으로 rollback이 transaction보다 시간은 많이 소모됩니다. 이러한 테스트를 관리하는 장비에서 미리 한번씩 해본다면 실제 이러한 문제 발생시 좀더 확실하게 대처 할 수 있겠죠..



물론 위에서 한 테스트는 rollback 의 시간에 영향을 많이 끼치는 cleanup_rollback_entries 의 기본 설정환경에서 진행 되었죠.
다음 번에는 cleanup_rollback_entries parameter의 값 조정과
user process에서의 recovery와 smon에서의 recovery의 시간 비교를 한번 해보겠습니다...
언제? 글쎄요.. ^^;

가끔 오라클이 대량 작업 중에 죽거나 abort로 내렸을 경우
alert log에 보면 다음과 같이 SMON이 transaction rollback을 하는 메세지를 볼 수 있다.



"음 .. 뭔가 큰 transaction이 있었군.."
"어? 왜 아직도 안끝나지? 언제 끝나는 거야? "


다음의 SQL은 rollback 대상 extent size를 확인할 수 있는 sql 문입니다.
이외에 undo segment header dump로 확인하는 방법도 있긴하지만,
요게 시간 산정하기는 더 편하겠지요..

오라클 9i online documents 입니다.

Oracle 9i release 1 online documents
Oracle 9i release 2 online documents

오라클 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에 데이터를 저장하게 됩니다.


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



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


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

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


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

DB 관리하면서 가장 긴장되는 순간이 ..
당연히 recovery를 수행해야 하는 순간이죠...

잘못하면 데이터 다 날라가거나,
아니면 restore하는데, 10여시간씩 걸리는데,
한번 잘못 명령어 날리면 ..

상상만 해도 끔찍하죠..ㄷㄷ

다음의 dictionary view는 recovery시 꼭 챙겨봐야 할 dictionary view 입니다.
돌다리도 두들겨 보고 건넙니다.. ^^



: 해당 datafile의 현황 및 현재 상태를 check할 수 있다.


: 해당 datafile의 fuzzy 상태를 확인할 수 있다. 만약 fuzzy 상태라고 하면 v$backup에서 정상적으로 end backup이 수행되었는지,
추가로 archive log를 적용해야 하는지의 판단이 필요하다.


: 최종에 Online Backup받은 file들에 대한 정보를 가지고 있다.
즉, Hot Backup이 수행되고 있는 Tablepsace가 아직 Backup Mode로 있는지 아니면 Backup이 완료 된 상태인지 확인 할 수 있다.
만약 Online Backup을 수행 하면서 ALTER TABLESPACE ~ END BACKUP; 을 수행하지 않았다면 STATUS가 ACTIVE로 남아있게 되며, 
해당 file을 Backup받은 후에 ALTER TABLESPACE ~ END BACKUP command를 실행하지 않은 것이므로 즉시 ALTER TABLESPACE ~ END BACKUP command 를 실행해야 한다.

(fuzzy bit에 대한 자세한 내용..)


: archive log의 현황및 상태를 확인할 수 있다.


: archive된 archive log의 정보를 controlfile로 부터 보여준다.


: controlfile의 현황 정보를 확인할 수 있다.


: tablespace의 현황 정보를 확인할 수 있다.


: media recovery를 위해 적용해야할 archive log의 정보를 확인할 수 있다.


: recovery가 필요한 datafile 정보를 확인할 수 있다.
 

: recovery를 수행하고 있는 oracle process에게 각각의 datafile의 recovery 정보를 보여준다. 다른 세션에서는 정보가 보이지 않는다.


: v$session_longops의 subview로 해당 recovery 작업 시간 산정등 recovery operation을 tracking 할때 사용한다.


: recovery를 수행하고 있는 oracle process에게 현재의 recovery process의 statistic 수치를 보여준다. 다른 세션에서는 정보가 보이지 않는다.

오라클 모니터링 할 때 모니터링 툴을 많이 사용하시죠.
oracle의 Sql Developer, 웨어밸리의 Orange, 퀘스트의 TOAD, 엑셈의 MaxGuage, 데이타헤븐의 Intuvision,..

GUI tool을 사용하면 한눈에 시스템 전체를 파악하기 용이하고, 마우스 클릭 몇번으로 금방 정보를 확인할 수 있습니다. 그러나 oracle의 data dictionary view나 dynamic performance view의 모든 내용을 포함하기는 어렵고, 또 다 포함하고 있다고 해도 그에 대한 사용 방법에 대해 사용자가 인지하고 있긴 어렵죠.

일반적으로 DBA 들이 주로 사용하는 dictionary view는 열손가락 꼽을 정도 밖에 안되지 않을까 합니다. dba_users, dba_tablespaces, v$tablespace, dba_data_files, v$filestat, v$sysstat, v$session_wait ... (음, 10개는 넘겠군요.. )

그런데 dictionary view를 보면 dba_로 시작하는 view와 v$로 시작하는 view 들이 있습니다.
(물론, all_ 이나 user_, gv$로 시작하는 view 들도 있긴 하죠)

dba_ view와 v$ view의 차이점을 무얼까요?




이 차이에 대해 평소에 별로 생각해 본적이 없어 google에서 찾아 봤더니 oracle faq's blog에 잘 설명 되어 있네요.

Oracle FAQ's


v$ view는 memory structure data를 보여주며, dba_ table(?)은 system tablespace 상의 data dictoinary data를 보여줍니다. 10g에서 awr 관련 view들이 만들어져서 dba_hist view 등에서 memory structure statistic data를 system tablespace에 저장해서 보여주기도 하죠.

다음의 HP's oracle blog에는 좀더 자세한 내용이 실려 있습니다..

HP's Oracle Blog


동감입니다 !
tool을 사용하는 것이 더 효율적이라 생각하실지 모르지만
마지막엔 DBA가 직접 sqlplus에서 dictionary view들을 query해야 하는 경우가 허다하죠..

너무 툴에 익숙해지지 마세요.. ^^

oracle은 9i로 version이 올라오면서 이전 버전의 OPS(oracle parallel server)를 RAC(real application cluster)로 명칭을 바꾸고 새로운 기능들을 대폭 추가했다. 양 노드간의 데이터의 cache level에서의 이전을 지원하는 cache fusion, 일부 노드의 장애에 대해 자동으로 client 의 접속을 다른 노드로 넘겨주는 TAF, CTF 등의 추가로 HA 기능을 강조하던 이전의 버전에서 cluster 기능을 대폭 강화 했다.


oracle 10g rac에서의 가장 눈에 띄는 변화는 CRS의 등장이다. CRS는 서로 다른 platform에서도 동일한 interface를 구현하기 위해 설계되었다. 동일한 interface의 구현이라면 platform과는 상관없이 데이터베이스에서의 동일한 기능 구현을 말하는 듯 하다.

(혹시 다른 CRS(crystal red shrimp) 찾아 오신분들을 위해 사진 올립니다... ㅋ)



CRS는 여러 application을 가지고 있다. 이 application들은 발생하는 event를 모니터링해서 미리 설정된 action을 취하게 된다. public network의 단절이나, private network의 단절, disk controller와의 통신 문제, system 장애에 대해 이러한 action을 지정할 수 있다.
이러한 미리 정의된 action으로 사용자는 시스템 레벨에서의 장애 상황에 따른 application level의 조치 없이 운영이 가능하다.

물론 각 장애를 인지하기 위한 timeout까지의 시간, 강제 종료된 instance에 대한 recovery 시간등이 필요하므로 이에 대한 업무의 영향은 있을 것이다.

왜 필요할 때 마다 이건 기억이 안나지.. 참..

현재 수행한 SQL의 plan을 확읺는 방법 2가지입니다..
select * from table(dbms_xplan.display);

SQL> desc dbms_xplan
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TABLE_NAME                     VARCHAR2                IN     DEFAULT
 STATEMENT_ID                   VARCHAR2                IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT

explain plan
for
sql ..

utlxpls.sql 수행
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

active session들의 현재 wait event와 sql 정보를 buffer get 기준으로 보여주는 sql script 입니다.
세션 모니터링의 기본이죠..

tablespace를 만들떄 local managed tablespace를 많이 사용하시죠?
예전의 dictionary-managed 방식에 비해 LMT는 많은 이점이 있죠..
뭐 이점이 있다기 보다는 dictionary-managed 방식의 단점이라고 보는게 더 타당할 지 모르겠네요..^^;

LMT의 extent 관리 방식에는 uniform size와 system managed 방식이 있습니다.
간단히 말하자면, uniform size는 extent size를 정해서 해당 tablespace에 생성되는 segment 들은 동일한 extent size로 설정되게 되고, system managed 방식은 extent size에 대한 설정을 oracle에서 관리해 주는 거죠..

system managed 방식을 쓰면 extent size에 대해 크게 신경을 안써도 되니 좋긴 한데, 오라클에서 어떻게 extent를 관리할까요?

심심해서 함 해봤는데, 엄청난 매카니즘이 숨겨 있더군요.. ㅋ


처음에 64k로 extent가 생성됩니다. 다음 extent도 64K가 생성되죠.. 뭐 이렇게 16개가 만들어지고,
다음은 1024K로 extent가 생성된후... 64개까지 만들어 지고...
..

이런 식으로 extent가 할당되면 1024K가 생성될 때 즈음이면
1024K 이하의 extent는 영원히 짜투리로 남을 수도 있겠죠?

LOCALLY MANAGED TABLESPACE IN ORACLE8I ...