Oracle DB 10g¿¡¼ ¼º´É Æ©´× Çâ»ó
Oracle 10g includes many performance tuning enhancements including:
Oracle 10g´Â ¸¹Àº ¼º´É Æ©´× Çâ»óÀ» Æ÷ÇÔÇÕ´Ï´Ù:
Automatic Performance Diagnostic and Tuning Features
Automatic Shared Memory Management
Wait Model Improvements
Automatic Optimizer Statistics Collection
Dynamic Sampling
CPU Costing
Optimizer Hints
Rule Based Optimizer Obsolescence
Tracing Enhancements
SAMPLE Clause Enhancements
Hash Partitioned Global Indexes
ÀÚµ¿ ¼º´É ÃøÁ¤°ú Æ©´× Ư¼ºµé
ÀÚµ¿ °øÀ¯ ¸Þ¸ð¸® °ü¸®
´ë±â ¸ðµ¨ Áõ´ë
ÀÚµ¿ ÃÖÀûȱâ Åë°è ¼öÁý
µ¿Àû »ùÇøµ
CPU ¼Òºñ
ÃÖÀûȱâ Hintµé
±ÔÄ¢ ±â¹Ý ÃÖÀûȱ⠹æ½Ä ¹ö¸²
Tracing °È
»ùÇà ±¸¹® °Èµé
Hash·Î ºÐÇÒµÈ Àü¿ª À妽ºµé
Automatic Performance Diagnostic and Tuning Features
ÀÚµ¿ ¼º´É ÃøÁ¤°ú Æ©´× Ư¼ºµé
Oracle 10g includes several features related to automatic performance diagnostics and tuning including:
Oracle 10g´Â ÀÚµ¿ ¼º´É ÃøÁ¤°ú Æ©´×°ú ¿¬°üµÈ ¿©·¯°¡Áö Ư¼ºµéÀ» Æ÷ÇÔÇÕ´Ï´Ù:
Automatic Optimizer Statistics Collection - The name says it all.
Automatic Workload Repository (AWR) - An extended version of the STATSPACK repository that is the heart of all the new diagnostics and tuning features.
Automatic Database Diagnostic Monitoring (ADDM) - An automatic diagnostics and tuning tool which uses the information stored in the AWR.
Automatic SQL Tuning Advisor - A built in SQL tuning feature.
Most of these features are beyond the scope of this article and as such will be dealt with in separate aticles.
ÀÚµ¿ ÃÖÀûȱâ Åë°è ¼öÁý - ±× À̸§ÀÌ ±×°ÍÀÇ ¸ðµç °ÍÀ» ¸»ÇØÁÝ´Ï´Ù.
ÀÚµ¿ ÀÛ¾÷ºÎÇÏ ÀúÀå¼Ò(AWR) - STATSPACK ÀúÀå¼ÒÀÇ ÇϳªÀÇ È®ÀåµÈ ¹öÀü
ÀÚµ¿ DB ÃøÁ¤ ¸ð´ÏÅ͸µ(ADDM) - AWR¿¡ ÀúÀåµÈ Á¤º¸¸¦ »ç¿ëÇÏ´Â ÇϳªÀÇ ÀÚµ¿ ÃøÁ¤°ú Æ©´×Åø
ÀÚµ¿ SQL Æ©´× Á¶¾ðÀÚ - ³»ÀåµÈ SQL Æ©´× Ư¼º
´ëºÎºÐÀÇ ÀÌ·¯ÇÑ Æ¯¼ºµéÀº ÀÌ ÆäÀÌÁöÀÇ ¹üÀ§¸¦ ¹þ¾î³ª¸ç ±×·± °Í ¶§¹®¿¡ ´Ù¸¥ ÆäÀÌÁö¿¡¼ ´Ù·ïÁú °ÍÀÔ´Ï´Ù.
Automatic Shared Memory Management
ÀÚµ¿ °øÀ¯ ¸Þ¸ð¸® °ü¸®
Automatic Shared Memory Management puts Oracle in control of allocating memory within the SGA. The SGA_TARGET parameter sets the amount of memory available to the SGA. This parameter can be altered dynamically up to a maximum of the SGA_MAX_SIZE parameter value. Provided the STATISTICS_LEVEL is set to TYPICAL or ALL and the SGA_TARGET is set to a value other than "0" Oracle will control the memory pools which would otherwise be controlled by the following parameters:
ÀÚµ¿ °øÀ¯ ¸Þ¸ð¸® °ü¸®´Â OracleÀÌ SGA ¾È¿¡ ÇÒ´çµÈ ¸Þ¸ð¸®ÀÇ Á¦¾î ¾Æ·¡¿¡ ÀÖµµ·Ï ÇÕ´Ï´Ù. SGA_TARGET ÆĶó¸ÞÅÍ´Â SGA¿¡¼ °¡¿ëÇÑ ¸Þ¸ð¸®ÀÇ ¾çÀ» ¼³Á¤ÇÕ´Ï´Ù. ÀÌ ÆĶó¸ÞÅÍ´Â SGA_MAX_SIZE ÆĶó¸ÞÅÍ °ª±îÁö ÃÖ´ëÇѵµ·Î µ¿ÀûÀ¸·Î º¯°æµÉ ¼ö ÀÖ½À´Ï´Ù. STATISTICS_LEVELÀÌ TYPICAL ¶Ç´Â ALL·Î ¼³Á¤ÇÏ°í SGA-TARGETÀÌ 0ÀÌ ¾Æ´Ñ °ªÀ¸·Î ¼³Á¤µÇµµ·ÏÇϸé OracleÀº ¸Þ¸ð¸®Ç®À» Á¦¾îÇÒ ¼ö ÀÖ½À´Ï´Ù. ¾Æ´Ï¸é ¸Þ¸ð¸® Ç®Àº ´ÙÀ½ÀÇ ÆĶó¸ÞÅ͵éÀ» ÅëÇؼ Á¦¾îµÉ °ÍÀÔ´Ï´Ù:
Âü°í - SGA¶õ? : OSÀÇ ¸Þ¸ð¸®¿Í °ü·ÃµÇ¾î ORACLE¿¡¼ ¸»ÇÏ´Â SGA¶õ ORACLE ÀνºÅϽºÀÇ µ¥ÀÌÅÍ, Á¦¾î Á¤º¸¸¦ º¸°üÇÏ°í ÀÖ´Â °øÀ¯ ¸Þ¸ð¸® ±¸Á¶¸¦ ¸»ÇÕ´Ï´Ù. °øÀ¯¶ó´Â Àǹ̴ ÀνºÅϽº¿¡ ¿¬°áµÈ ¿©·¯ »ç¿ëÀÚµéÀÌ ¼·Î °øÀ¯ÇÑ´Ù´Â ¶æÀ̹ǷΠ¸Þ¸ð¸® ¿µ¿ª Áß ¿©·¯ »ç¿ëÀÚµéÀÌ ¼·Î °øÀ¯ÇØ¾ß ÇÏ´Â ºÎºÐÀ» SGA ¿µ¿ªÀÌ¶ó º¸¸é µË´Ï´Ù.
DB_CACHE_SIZE (default block size) (±âº» ºí·Ï Å©±â)
SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
If these parameters are set to a non-zero value they represent the minimum size for the pool. These minimum values may be necessary if you experience application errors when certain pool sizes drop below a specific threshold.
¸¸¾à ÀÌ·± ÆĶó¸ÞÅ͵éÀÌ 0ÀÌ ¾Æ´Ñ °ªÀ¸·Î ¼³Á¤ÀÌµÇ¸é ±×µéÀº Ç®¿¡ ´ëÇÑ ÃÖ¼ÒÇÑÀÇ Å©±â¸¦ ³ªÅ¸³À´Ï´Ù. ÀÌ·± ÃÖ¼ÒÇÑÀÇ °ªµéÀº ¸¸¾à ´ç½ÅÀÌ Æ¯Á¤ Ç® Å©±â°¡ ƯÁ¤ÇÑ °æ°èÁ¡ ¾Æ·¡·Î ¶³¾îÁ³À» ¶§ ÀÀ¿ë »óÀÇ ¿¡·¯¸¦ ¸¸³ª¸é ÇÊ¿äÇÒ °ÍÀÔ´Ï´Ù.
The following parameters must be set manually and take memory from the quota allocated by the SGA_TARGET parameter:
´ÙÀ½ÀÇ ÆĶó¸ÞÅ͵éÀº ¼öµ¿ÀûÀ¸·Î ¼³Á¤µÇ¾î¾ß ÇÏ°í, SGA_TARGET ÆĶó¸ÞÅÍ¿¡ÀÇÇØ ÇÒ´çµÈ ¿µ¿ªÀ¸·ÎºÎÅÍ ¸Þ¸ð¸®¸¦ Àâ°ÔµË´Ï´Ù:
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_nK_CACHE_SIZE (non-default block size) (±âº» ºí·Ï Å©±â°¡ ¾Æ´Ñ)
STREAMS_POOL_SIZE
LOG_BUFFER
Wait Model Improvements
´ë±â ¸ðµ¨ Áõ´ë
A number of views have been updated and added to improve the wait model. The updated views include:
´ë±â ¸ðµ¨À» Áõ´ë½ÃÅ°±â À§ÇØ ¼ö¸¹Àº ºäµéÀÌ °»½ÅµÇ¾ú°í Ãß°¡µÇ¾ú½À´Ï´Ù. °»½ÅµÈ ºäµéÀº ÀÌ·¸½À´Ï´Ù:
V$EVENT_NAME
V$SESSION
V$SESSION_WAIT
The new views include:
»õ·Î Ãß°¡µÈ ºäµéÀº ÀÌ·¸½À´Ï´Ù:
V$ACTIVE_SESSION_HISTORY
V$SYSTEM_WAIT_HISTORY
V$SESS_TIME_MODEL
V$SYS_TIME_MODEL
V$SYSTEM_WAIT_CLASS
V$SESSION_WAIT_CLASS
V$EVENT_HISTOGRAM
V$FILE_HISTOGRAM
V$TEMP_HISTOGRAM
The following are some examples of how these updates can be used.
´ÙÀ½ÀÇ °ÍµéÀº ¾î¶»°Ô ÀÌ °»½ÅµéÀÌ »ç¿ëµÉ ¼ö ÀÖ´ÂÁö¿¡ ´ëÇÑ ¿¹µéÀÔ´Ï´Ù.
The V$EVENT_NAME view has had three new columns added (WAIT_CLASS_ID, WAIT_CLASS# and WAIT_CLASS) which indicate the class of the event. This allows easier aggregation of event details:
V$EVENT_NAME ºä´Â ¼¼ °³ÀÇ »õ·Î¿î Ä÷³ÀÌ Ãß°¡µÇ¾ú½À´Ï´Ù(WAIT_CLASS_ID, WAIT_CLASS# and WAIT_CLASS). ÀÌ Ä÷³µéÀº À̺¥Æ®ÀÇ Å¬·¡½º¸¦ ³ªÅ¸³À´Ï´Ù. ÀÌ°ÍÀº À̺¥Æ®ÀÇ »ó¼¼ÇÑ ³»¿ëÀ» ´õ ½±°Ô °£·«È½Ãų ¼ö ÀÖµµ·Ï ÇØÁÝ´Ï´Ù:
-- Display time waited for each wait class.
-- °¢°¢ÀÇ ´ë±â Ŭ·¡½º¸¦ ±â´Ù·È´ø ½Ã°£À» Ç¥½Ã
SELECT a.wait_class, sum(b.time_waited)/1000000 time_waited
FROM v$event_name a
JOIN v$system_event b ON a.name = b.event
GROUP BY wait_class;
WAIT_CLASS TIME_WAITED
--------------------------- -----------
Application .013388
Commit .003503
Concurrency .009891
Configuration .003489
Idle 232.470445
Network .000432
Other .025698
System I/O .095651
User I/O .109552
9 rows selected.
9°³ ¿ÀÌ ¼±ÅõǾú½À´Ï´Ù.
The V$SESSION view has had several columns added that include blocking session and wait information. The wait information means it's no longer necessary to join to V$SESSION_WAIT to get wait information for a session:
V$SESSION ºä´Â ¼¼¼ÇÀ» ºí·ÏÅ·ÇÏ°í ´ë±â Á¤º¸¸¦ Æ÷ÇÔÇÏ´Â ¿©·¯ °³ÀÇ Ä÷³µéÀÌ Ãß°¡µÇ¾ú½À´Ï´Ù. ´ë±â Á¤º¸°¡ ÀǹÌÇÏ´Â °ÍÀº ¼¼¼Ç¿¡ ´ëÇÑ ´ë±â Á¤º¸¸¦ ¾ò±â À§ÇØ ´õ ÀÌ»ó V$SESSION_WAIT¿¡ Âü¿©ÇÒ ÇÊ¿ä°¡ ¾ø´Ù´Â °ÍÀ» ÀǹÌÇÕ´Ï´Ù.
-- Display blocked session and their blocking session details.
-- ºí·Ï´çÇÑ ¼¼¼Ç°ú ±×µéÀÇ ºí·ÏÅ· ¼¼¼ÇÀÇ ¼¼ºÎ ¸ñ·ÏÀ» Ç¥½ÃÇÕ´Ï´Ù.
SELECT sid, serial#, blocking_session_status, blocking_session
FROM v$session
WHERE blocking_session IS NOT NULL;
no rows selected.
¾Æ¹«·± ¿µµ ¼±ÅõÇÁö ¾Ê¾Ò½À´Ï´Ù.
-- Display the resource or event the session is waiting for.
-- ¼¼¼ÇÀÌ ´ë±âÇÏ°í ÀÖ´Â ÀÚ¿ø ¶Ç´Â À̺¥Æ®¸¦ Ç¥½ÃÇÕ´Ï´Ù.
SELECT sid, serial#, event, (seconds_in_wait/1000000) seconds_in_wait
FROM v$session
ORDER BY sid;
SID SERIAL# EVENT SECONDS_IN_WAIT
---------- ---------- ----------------------------------- ---------------
131 20 SQL*Net message from client .000015
133 501 wakeup time manager .000138
134 28448 SQL*Net message to client 0
135 4 queue messages .000003
137 8 SQL*Net message from client .000132
....
167 1 rdbms ipc message 0
168 1 rdbms ipc message 0
169 1 rdbms ipc message .079485
170 1 pmon timer .092645
29 rows selected.
29 ¿ÀÌ ¼±ÅõǾú½À´Ï´Ù.
The V$SESSION_WAIT_CLASS view allows you to see the session wait information broken down by wait class for each session:
V$SESSION_WAIT_CLASS ºä´Â °¢°¢ÀÇ ¼¼¼Ç¿¡ ´ëÇÑ ´ë±â Ŭ·¡½º¿¡ ÀÇÇØ ¸Á°¡Áø ¼¼¼Ç ´ë±â Á¤º¸¸¦ º¼ ¼ö ÀÖµµ·Ï ÇØÁÝ´Ï´Ù:
-- Display session wait information by wait class.
-- ´ë±â Ŭ·¡½º¿¡ ÀÇÇÑ ¼¼¼Ç ´ë±â Á¤º¸¸¦ Ç¥½ÃÇÕ´Ï´Ù.
SELECT *
FROM v$session_wait_class
WHERE sid = 134;
SID SERIAL# WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED
---------- ---------- ------------- ----------- ------------------- ----------- -----------
134 28448 4217450380 1 Application 2 0
134 28448 3875070507 4 Concurrency 1 2
134 28448 2723168908 6 Idle 57 392127
134 28448 2000153315 7 Network 68 5
4 rows selected.
4°³ ¿ÀÌ ¼±ÅõǾú½À´Ï´Ù.
The V$SYSTEM_WAIT_HISTORY view shows historical wait information which allows you to identify issues after the session has ended.
V$SYSTEM_WAIT_HISTORY ºä´Â ¼¼¼ÇÀÌ Á¾·áµÈ ÈÄ¿¡ ¹ß»ýµÈ ´ë±â Á¤º¸µéÀ» ±¸ºÐÇϵµ·Ï ÇØÁÖ´Â ¼ø¼ÀûÀÎ ´ë±â Á¤º¸¸¦ º¸¿©ÁÝ´Ï´Ù.
Automatic Optimizer Statistics Collection
ÀÚµ¿ ÃÖÀûȱâ Åë°è ¼öÁý
By default Oracle 10g automatically gathers optimizer statistics using a scheduled job called GATHER_STATS_JOB. By default this job runs within a maintenance windows between 10 P.M. to 6 A.M. week nights and all day on weekends. The job calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC internal procedure which gathers statistics for tables with either empty or stale statistics, similar to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The main difference is that the internal job prioritizes the work such that tables most urgently requiring statistics updates are processed first.
±âº»ÀûÀ¸·Î Oracle 10g´Â ÀÚµ¿ÀûÀ¸·Î ÃÖÀûȱâ Åë°è¸¦ GATHER_STATS_JOBÀ̶ó°í ºÒ¸®´Â ½ºÄÉÁì¿¡ µû¸£´Â ÀÛ¾÷À» »ç¿ëÇؼ ÃÖÀûȱâ Åë°è¸¦ ¸ðÀ¾´Ï´Ù. ±âº»ÀûÀ¸·Î ÀÌ ÀÛ¾÷Àº À¯Áö À©µµ¿ì ¾È¿¡¼ ÁÖ°£ ³»³»¿Í ¸ðµç ÁÖ¸»ÀÇ Àú³áÀÎ 10 P.M.¿¡¼ 6 A.M.¿¡ ¼öÇàµË´Ï´Ù. ÀÛ¾÷Àº ³»ºÎ ÇÁ·Î½ÃÁ®ÀÎ DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROCÀ» ºÎ¸¨´Ï´Ù. ÀÌ ÇÁ·Î½ÃÁ®´Â, DBMS_STATS.GATHER_DATABASE_STATS°¡ GATHER AUTO ¿É¼ÇÀ» »ç¿ëÇÏ´Â °Í°ú ºñ½ÁÇÏ°Ô, ºñ¾îÀְųª »óÅ°¡ ¾ÈÁÁÀº Åë°è Áß¿¡ Çϳª¸¦ °¡Áø Å×ÀÌºí¿¡ ´ëÇÑ Åë°è¸¦ ¸ðÀ¾´Ï´Ù. ÁÖ¿äÇÑ Â÷ÀÌÁ¡Àº ³»ºÎ ÀÛ¾÷Àº °¡Àå ±ä±ÞÈ÷ Åë°è¸¦ ¾÷µ¥ÀÌÆ®¸¦ ÇØ¾ß ÇÏ´Â Å×À̺í°ú °°Àº ÀÛ¾÷À» ¸ÕÀú ó¸®Çϵµ·Ï ¿ì¼±¼øÀ§¸¦ ¸Å±ä´Ù´Â Á¡ÀÔ´Ï´Ù.
In some cases automatically gathering statistics can cause problems. Highly volatile tables and load tables may have their statistics gathered when there is an unrepresentative number of rows present. These situations can be avoided by using one of two methods:
¸î¸îÀÇ °æ¿ì¿¡ ÀÚµ¿ÀûÀ¸·Î Åë°è¸¦ ¸ðÀ¸´Â °ÍÀº ¹®Á¦¸¦ ¾ß±âÇÒ ¼ö ÀÖ½À´Ï´Ù. ¸Å¿ì ÀÚÁÖ ¹Ù²î´Â Å×À̺íµé°ú ¸Å¿ì ÀÚÁÖ ºÒ·ÁÁö´Â Å×À̺íµéÀº ÇϳªÀÇ ³ªÅ¸³»Áö ¾Ê´Â ¼ýÀÚÀÇ ¿ÀÌ ÀÖÀ» ¶§ ±×µéÀÇ Åë°èÁ¤º¸°¡ ¸ð¾ÆÁú ¼ö ÀÖ½À´Ï´Ù. ÀÌ·¯ÇÑ »óȲµéÀº Çϳª ¶Ç´Â µÎ°³ÀÇ ¸Þ¼µå Áß¿¡ Çϳª·Î ÇÇÇØÁú ¼ö ÀÖ½À´Ï´Ù.
The current statistics can be deleted and locked to prevent DBMS_STATS from gathering new statistics. If the OPTIMIZER_DYNAMIC_SAMPLING parameter is set to 2 (the default) or higher the necessary statistics will be gathered as part of the query optimization stage (See Dynamic Sampling):
ÇöÀçÀÇ Åë°èµéÀº Áö¿öÁú ¼ö ÀÖ°í »õ·Î¿î Åë°èµéÀ» ¸ðÀ¸´Â °Í¿¡¼ DBMS_STATSÀ» ¹æÁöÇϵµ·Ï lockµÉ ¼ö ÀÖ½À´Ï´Ù. ¸¸¾à OPTIMIZER_DYNAMIC_SAMPLING ÆĶó¸ÞÅÍ°¡ 2·Î ¼³Á¤µÇ°Å³ª(±âº»°ª) ´õ ³ôÀ¸¸é ¿ä±¸µÇ´Â Åë°èµéÀº Äõ¸® ÃÖÀûÈ ´Ü°èÀÇ ºÎºÐó·³ ¸ð¾ÆÁú °ÍÀÔ´Ï´Ù(µ¿Àû »ùÇøµÀ» º¸¼¼¿ä):
BEGIN
DBMS_STATS.delete_table_stats('MY_SCHEMA','LOAD_TABLE');
DBMS_STATS.lock_table_stats('MY_SCHEMA','LOAD_TABLE');
END;
/
The statistics can be gathered then locked at a time when the table contains the appropriate data:
Åë°èµéÀº ¸ð¾ÆÁø ´ÙÀ½¿¡ ÀûÀýÇÑ µ¥ÀÌÅ͸¦ Æ÷ÇÔÇÏ°Ô µÉ ¶§ lockµÉ ¼ö ÀÖ½À´Ï´Ù.
BEGIN
DBMS_STATS.gather_table_stats('MY_SCHEMA','LOAD_TABLE');
DBMS_STATS.lock_table_stats('MY_SCHEMA','LOAD_TABLE');
END;
/
System statistics and statistics for fixed object, such as dynamic performance tables, are not gathered automatically.
µ¿Àû ¼º´É Å×À̺í°ú °°Àº, ½Ã½ºÅÛ Åë°è¿Í ¼öÁ¤µÈ °´Ã¼¸¦ À§ÇÑ Åë°è´Â ÀÚµ¿ÀûÀ¸·Î ¼öÁýµÇÁö ¾Ê½À´Ï´Ù.
Dynamic Sampling
µ¿Àû »ùÇøµ
Dynamic sampling enables the server to improve performance by:
µ¿Àû »ùÇøµÀº ´ÙÀ½¿¡ ÀÇÇؼ ¼¹ö°¡ ¼º´ÉÀ» Çâ»óÇϵµ·Ï ÇØÁÝ´Ï´Ù:
Estimate single-table predicate selectivities where available statistics are missing or may lead to bad estimations.
Estimate statatistics for tables and indexes with missing statistics.
Estimate statatistics for tables and indexes with out of date statistics.
°¡¿ëÇÑ Åë°è°¡ ¾ø°Å³ª ¶Ç´Â À߸øµÈ Ãß»êÀ» ¾ß±âÇÏ´Â °÷¿¡¼ ´ÜÀÏ Å×À̺íÀÌ ¼±ÅÃÀ» È®½ÇÇÏ°Ô ´Ü¾ðÇϵµ·Ï Ãß»êÇÕ´Ï´Ù.
Åë°è°¡ ¾ø´Â Å×À̺íµé°ú À妽ºµéÀ» À§ÇÑ Åë°è¸¦ Ãß»êÇÕ´Ï´Ù.
³¯Â¥°¡ Áö³ Åë°èµéÀ» °¡Áø Å×À̺íµé°ú À妽ºµéÀ» Ãß»êÇÕ´Ï´Ù.
Dynamic sampling is controled by the OPTIMIZER_DYNAMIC_SAMPLING parameter which accepts values from "0" (off) to "10" (agressive sampling) with a default value of "2". At compile-time Oracle determines if dynamic sampling would improve query performance. If so it issues recursive statements to estimate the necessary statistics. Dynamic sampling can be beneficial when:
µ¿Àû »ùÇøµÀº OPTIMIZER_DYNAMIC_SAMPLING ÆĶó¸ÞÅÍ¿¡ ÀÇÇØ Á¦¾îµË´Ï´Ù. ±× °ªÀº "2"°¡ ±âº»°ªÀ̸ç, "0"(²¨Áü) ¿¡¼ "10"(°ø°ÝÀû »ùÇøµ)±îÁö Çã¿ëµË´Ï´Ù. ÄÄÆÄÀÏÇÏ´Â µ¿¾È OracleÀº ¸¸¾à µ¿Àû »ùÇøµÀÌ Äõ¸® ¼º´ÉÀ» Çâ»ó½ÃŲ´Ù¸é, ¿ä±¸µÇ´Â Åë°èµéÀ» Ãß»êÇϱâ À§ÇØ Àç±ÍÀûÀÎ ±¸¹®µéÀ» ¹ß»ý½Ãŵ´Ï´Ù. µ¿Àû »ùÇøµÀº ´ÙÀ½°ú °°Àº ¶§¿¡ ÀÌÁ¡ÀÌ ÀÖ½À´Ï´Ù:
The sample time is small compared to the overall query execution time.
Dynamic sampling results in a better performing query.
The query may be executed multiple times.
»ùÇà ½Ã°£ÀÌ Àü¹ÝÀûÀÎ Äõ¸® ½ÇÇà½Ã°£°ú ºñ±³ÇÏ¿© ÀûÀ» ¶§
µ¿Àû »ùÇøµÀÌ ½ÇÇàµÈ Äõ¸®º¸´Ù ³ªÀº °á°ú¸¦ ³ºÀ» ¶§
Äõ¸®°¡ ¿©·¯¹ø ½ÇÇàµÇ¾úÀ» ¶§
In addition to the OPTIMIZER_DYNAMIC_SAMPLING system parameter the dynamic sampling level can be set using the DYNAMIC_SAMPLING optimizer hint for specific queries like:
OPTIMIZER_DYNAMIC_SAMPLING ½Ã½ºÅÛ ÆĶó¸ÞÅÍ¿¡ µ¡ºÙ¿©¼, µ¿Àû »ùÇøµ µî±ÞÀº, ´ÙÀ½°ú °°Àº ƯÁ¤ Äõ¸®¸¦ À§Çؼ, DYNAMIC_SAMPLING ÃÖÀûȱâ hint¸¦ »ç¿ëÇؼ ¼³Á¤µÉ ¼ö ÀÖ½À´Ï´Ù:
SELECT /*+ dynamic_sampling(emp 10) */
empno, ename, job, sal
FROM emp
WHERE deptno = 30;
The results of dynamic sampling are repeatable provided no rows are inserted, updated or deleted from the sampled table. The OPTIMIZER_FEATURES_ENABLE parameter will turns off dynamic sampling if it is set to a version earlier than 9.2.0.
µ¿Àû »ùÇøµÀÇ °á°ú´Â ¾Æ¹« ¿µµ »ùÇÃÈµÈ Å×À̺í·Î ºÎÅÍ Ãß°¡, °»½Å, ¶Ç´Â »èÁ¦µÇÁö ¾Ê¾ÒÀ» ¶§, ¹Ýº¹ÀûÀ¸·Î Á¦°øµÉ ¼ö ÀÖ½À´Ï´Ù. OPTIMIZER_FEATURES_ENABLE ÆĶó¸ÞÅÍ´Â ¸¸¾à ±×°ÍÀÌ 9.2.0 ÀÌÀü ¹öÀüÀ¸·Î ¼³Á¤µÇ¸é, µ¿Àû »ùÇøµÀ» ²ü´Ï´Ù.
CPU Costing
CPU ¼Òºñ
By default the cost model for the optimizer is now CPU+I/O, with the cost unit as time.
±âº»ÀûÀ¸·Î ÃÖÀûȱ⸦ À§ÇÑ ¼Òºñ ¸ðµ¨Àº ÇöÀç ½Ã°£À» ¼Òºñ ´ÜÀ§·Î »ç¿ëÇÏ´Â CPU+I/O ¸ðµ¨ÀÔ´Ï´Ù.
Optimizer Hints
ÃÖÀûȱâ Hintµé
New hints:
»õ·Î¿î hintµé:
SPREAD_MIN_ANALYSIS - Specifies analysis options for spreadsheets.
USE_NL_WITH_INDEX - Specifies a nested loops join.
QB_NAME - Specifies a name for a query block.
NO_QUERY_TRANSFORMATION - Prevents the optimizer performing query transformations.
NO_USE_NL, NO_USE_MERGE, NO_USE_HASH, NO_INDEX_FFS, NO_INDEX_SS and NO_STAR_TRANSFORMATION - Excludes specific operations from the query plan.
INDEX_SS, INDEX_SS_ASC, INDEX_SS_DESC - Excludes range scans from the query plan.
SPREAD_MIN_ANALYSIS - ½ºÇÁ·¹µå½ÃÆ®¸¦ À§ÇÑ ºÐ¼® ¿É¼ÇµéÀ» ¸í½ÃÇÕ´Ï´Ù.
USE_NL_WITH_INDEX - ÁßøµÈ ·çÇÁ Á¶ÀÎÀ» ¸í½ÃÇÕ´Ï´Ù.
QB_NAME - Äõ¸® ºí·ÏÀÇ À̸§À» ¸í½ÃÇÕ´Ï´Ù.
NO_QUERY_TRANSFORMATION - ÃÖÀûȱⰡ Äõ¸® º¯Á¶¸¦ ¼öÇàÇÏ´Â °ÍÀ» ¸·½À´Ï´Ù.
NO_USE_NL, NO_USE_MERGE, NO_USE_HASH, NO_INDEX_FFS, NO_INDEX_SS and NO_STAR_TRANSFORMATION - Äõ¸® °èȹ¿¡¼ ƯÁ¤ ¿¬»êÀ» ¹èÁ¦ÇÕ´Ï´Ù.
INDEX_SS, INDEX_SS_ASC, INDEX_SS_DESC - Äõ¸® °èȹ¿¡¼ ¿µ¿ª °Ë»öÀ» ¹èÁ¦ÇÕ´Ï´Ù.
Updated hints:
°»½ÅµÈ hintµé:
Hints that specify table names have been expanded to accept Global Table Hints. This allows a base table within a view to be specified using the "view-name.table-name" syntax.
Hints that specify index names have been expanded to accept Complex Index Hints. This allows an index to be specified using the "(table-name.column-name)" syntax instead of the index name.
Some hints can now optionally accept a query block parameter.
Àü¿ª Å×À̺í HintµéÀ» Çã¿ëÇϱâ À§ÇØ È®ÀåµÈ ¸í½ÃµÈ Å×ÀÌºí ¸íÀ» ¸í½ÃÇÏ´Â Hintµé. ÀÌ°ÍÀº ºä ¾È¿¡ ÀÖ´Â ±âº» Å×À̺íµéÀÌ "ºä¸í, Å×À̺í¸í" ±¸¹®À» »ç¿ëÇؼ ¸í½ÃµÇ¾îÁöµµ·Ï ÇØÁÝ´Ï´Ù.
Complex À妽º hintµéÀ» Çã¿ëÇϱâ À§Çؼ È®ÀåµÈ À妽º¸íÀ» ¸í½ÃÇÏ´Â Hintµé. ÀÌ°ÍÀº ÇϳªÀÇ À妽º°¡ "(Å×À̺í¸í.Ä÷³¸í)" ±¸¹®À» À妽º¸í ´ë½Å¿¡ »ç¿ëÇؼ ¸í½ÃµÇ¾îÁöµµ·Ï ÇØÁÝ´Ï´Ù.
Renamed hints:
À̸§ÀÌ ¹Ù²ï hintµé:
NO_PARALLEL - Formally NOPARALLEL.
NO_PARALLEL_INDEX - Formally NOPARALLEL_INDEX.
NO_REWRITE - Formally NOREWRITE.
NO_PARALLEL - ÀÌÀü¿¡´Â NOPARALLEL.
NO_PARALLEL_INDEX - ÀÌÀü¿¡´Â NOPARALLEL_INDEX.
NO_REWRITE - ÀÌÀü¿¡´Â NOREWRITE.
Deprecated hints:
Æó¿ëµÈ hintµé:
AND_EQUAL
HASH_AJ
MERGE_AJ
NL_AJ
HASH_SJ
NL_SJ
EXPAND_GSET_TO_UNION
ORDERED_PREDICATES
ROWID
STAR
Rule Based Optimizer Obsolescence
±ÔÄ¢ ±â¹Ý ÃÖÀûȱ⠹ö¸²
The Rule Based Optimizer (RBO) is now obsolete in Oracle 10g. The functionality is still present but no new functionality has been included in it and it is no longer supported by Oracle. It is only present to provide backwards compatibility during the migration to the query optimizer (Cost Based Optimizer). The results of this osolescence are:
±ÔÄ¢ ±â¹Ý ÃÖÀûȱâ(RBO)´Â ÇöÀç Oracle 10g¿¡¼ ¹ö·ÁÁ³½À´Ï´Ù. ±× ±â´ÉÀº ¿©ÀüÈ÷ ÀÖÁö¸¸, ¾Æ¹«·± »õ·Î¿î ±â´ÉµéÀÌ ±×°Í¿¡ Ãß°¡µÇÁö ¾Ê¾Ò°í, ´õ ÀÌ»ó Oracle¿¡ ÀÇÇؼ Áö¿øµÇÁö ¾Ê½À´Ï´Ù. ±×°ÍÀº Äõ¸® ÃÖÀûȱâ(¼Òºñ ±â¹Ý ÃÖÀûȱâ)·ÎÀÇ ÀÌÀüÀ» ÇÏ´Â µ¿¾È ÀÌÀü°úÀÇ È£È¯¼ºÀ» Á¦°øÇϱâ À§Çؼ¸¸ Á¸ÀçÇÕ´Ï´Ù. ÀÌ°ÍÀÌ ¹ö·ÁÁö´Â °á°úµéÀº ÀÌ·¸½À´Ï´Ù:
The CHOOSE and RULE options for the OPTIMIZER_MODE parameter still exist but are no longer supported.
The default value for the OPTIMIZER_MODE parameter is ALL_ROWS.
The CHOOSE and RULE optimizer hints still exist but are no longer supported.
Code requiring the RBO must be migrated to use the query optimizer.
OPTIMIZER_MODE ÆĶó¸ÞÅ͸¦ À§ÇÑ CHOOSE¿Í RULE ¿É¼ÇÀº ¿©ÀüÈ÷ Á¸ÀçÇÏÁö¸¸, ´õÀÌ»ó Áö¿øµÇÁö ¾Ê½À´Ï´Ù.
OPTIMIZER_MODE ÆĶó¸ÞÅÍÀÇ ±âº»°ªÀº ALL_ROWSÀÔ´Ï´Ù.
CHOOSE¿Í RULE ÃÖÀûȱâ hintµéÀº ¿©ÀüÈ÷ Á¸ÀçÇÏÁö¸¸, ´õ ÀÌ»ó Áö¿øµÇÁö ¾Ê½À´Ï´Ù.
RBO¸¦ ¿ä±¸ÇÏ´Â ÄÚµåµéÀº Äõ¸® ÃÖÀûȱ⸦ »ç¿ëÇϱâ À§ÇØ ¹Ýµå½Ã ÀÌÀüµÇ¾îÁ®¾ß ÇÕ´Ï´Ù.
Tracing Enhancements
Tracing Çâ»ó
The Oracle Trace functionality has been removed from Oracle 10g. Instead the SQL Trace and TKPROF functionality should be used.
Oracle Trace ±â´ÉÀº Oracle 10g·ÎºÎÅÍ Á¦°ÅµÇ¾ú½À´Ï´Ù. ´ë½Å¿¡ SQL Trace¿Í TKPROF ÇÔ¼ö°¡ »ç¿ëµÇ¾ß ÇÕ´Ï´Ù.
In multi-tier environments where statements are passed to different sessions by the application server it can become difficult to trace an individual process from start to finish. To solve this problem Oracle have introduced End to End Application Tracing which allows a client process to be identified via the client identifier rather than the typical session id. Each piece of trace information is linked to the following information:
ÀÀ¿ë ¼¹ö¿¡ ÀÇÇؼ ±¸¹®µéÀÌ ´Ù¸¥ ¼¼¼ÇÀ¸·Î Àü´ÞµÇ´Â ´ÙÁß-tier ȯ°æ¿¡¼ ÇϳªÀÇ °³ÀÎÀûÀÎ ÇÁ·Î¼¼½ºÀÇ ½ÃÀÛºÎÅÍ ³¡±îÁö¸¦ traceÇÏ´Â °ÍÀº ¾î·Á¿öÁú ¼ö ÀÖ½À´Ï´Ù. ÀÌ ¹®Á¦¸¦ ÇØ°áÇϱâ À§Çؼ OracleÀº End-End ÀÀ¿ë TracingÀ» ¼Ò°³Çß½À´Ï´Ù. ±×°ÍÀº ÇÑ Å¬¶óÀ̾ðÆ® ÇÁ·Î¼¼½º°¡ ƯÁ¤ÇÑ »ç¿ëÀÚÀÇ ¼¼¼Ç ¾ÆÀ̵𺸴ٴ Ŭ¶óÀ̾ðÆ® ½Äº°ÀÚ¸¦ ÅëÇؼ ½Äº°µÇµµ·Ï ÇÕ´Ï´Ù. °¢°¢ÀÇ trace Á¤º¸µéÀº ´ÙÀ½ÀÇ Á¤º¸µé¿¡ ¿¬°áµÇ¾îÀÖ½À´Ï´Ù:
Client Identifier - Specifies the "real" end user. Set using the DBMS_SESSION.SET_IDENTIFIER procedure.
Service - Specifies a group of related applications. Created using the DBMS_SERVICE.CREATE_SERVICE procedure.
Module - Specifies a functional area or feature of an application. Set using the DBMS_APPLICATION_INFO.SET_MODULE procedure.
Action - Specifies the current action (INSERT, UPDATE, DELETE etc.) within the current module. Set using the DBMS_APPLICATION_INFO.SET_ACTION procedure.
Ŭ¶óÀ̾ðÆ® ½Äº°ÀÚ - "½ÇÁ¦" end user¸¦ ¸í½ÃÇÕ´Ï´Ù. DBMS_SESSION.SET_IDENTIFIER ÇÁ·Î½ÃÁ®¸¦ »ç¿ëÇؼ ¼³Á¤ÇÕ´Ï´Ù.
¼ºñ½º - ¿¬°üµÈ ÀÀ¿ëÇÁ·Î±×·¥ÀÇ Áý´ÜÀ» ¸í½ÃÇÕ´Ï´Ù. DBMS_SERVICE.CREATE_SERVICE ÇÁ·Î½ÃÁ®¸¦ »ç¿ëÇؼ ¼³Á¤ÇÕ´Ï´Ù.
¸ðµâ - ÇÔ¼öÀûÀÎ ¿µ¿ª ¶Ç´Â ÇϳªÀÇ ÀÀ¿ë ÇÁ·Î±×·¥ÀÇ Æ¯¼ºÀ» ¸í½ÃÇÕ´Ï´Ù. DBMS_APPLICATION_INFO.SET_MODULE ÇÁ·Î½ÃÁ®¸¦ »ç¿ëÇؼ ¼³Á¤ÇÕ´Ï´Ù.
¾×¼Ç - ÇöÀç ¸ðµâ¿¡ ¾È¿¡ ÀÖ´Â ÇöÀçÀÇ È°µ¿(INSERT, UPDATE, DELETE µîµî)À» ¸í½ÃÇÕ´Ï´Ù. DBMS_APPLICATION_INFO.SET_ACTION ÇÁ·Î½ÃÁ®¸¦ »ç¿ëÇؼ ¸í½ÃÇÕ´Ï´Ù.
End to end tracing can be managed via Enterprise Manager or a set of APIs and views. Here are some examples of how to enable and disable to various types of tracing:
End-End tracingÀº ±â¾÷ °ü¸®ÀÚ¸¦ ÅëÇؼ³ª API ÁýÇÕ°ú ºä¸¦ ÅëÇؼ ´Ù·ïÁú ¼ö ÀÖ½À´Ï´Ù. ¾î¶»°Ô ´Ù¾çÇÑ ÇüÅÂÀÇ tracingÀ» °¡´É/ºÒ°¡´ÉÇÏ°Ô ÇÒ ¼ö ÀÖ´ÂÁö ¿©±â¿¡ ¾à°£ÀÇ ¿¹Á¦°¡ ÀÖ½À´Ï´Ù:
BEGIN
-- Enable/Disable Client Identifier Trace.
DBMS_MONITOR.client_id_trace_enable (client_id => 'my_id');
DBMS_MONITOR.client_id_trace_disable (client_id => 'my_id');
-- Enable/Disable Service, Module and Action Trace (various overloads).
DBMS_MONITOR.serv_mod_act_trace_enable (
service_name => 'my_service');
DBMS_MONITOR.serv_mod_act_trace_enable (
service_name => 'my_service',
module_name => 'my_module');
DBMS_MONITOR.serv_mod_act_trace_enable (
service_name => 'my_service',
module_name => 'my_module',
action_name => 'INSERT');
DBMS_MONITOR.serv_mod_act_trace_disable (
service_name => 'my_service',
module_name => 'my_module',
action_name => 'INSERT');
-- Enable/Disable Session Trace (various overloads).
DBMS_MONITOR.session_trace_enable;
DBMS_MONITOR.session_trace_enable (
session_id => 15,
serial_num => 1234);
DBMS_MONITOR.session_trace_disable (
session_id => 15,
serial_num => 1234);
END;
/
Once the trace files are produced the trcsess command line utility can be used to filter out the relevant data from multiple files. The utility accepts the following parameters:
ÇÑ ¹ø trace ÆÄÀÏÀÌ »ý¼ºµÇ¸é, trcsess ¸í·É¾î À¯Æ¿¸®Æ¼´Â ´Ù¼öÀÇ ÆÄÀϵé·Î ºÎÅÍ »óÀÀµÇ´Â µ¥ÀÌÅ͸¦ »Ì¾Æ³»´Âµ¥ »ç¿ëµÉ ¼ö ÀÖ¾¹´Ï´Ù. À¯Æ¿¸®Æ¼´Â ´ÙÀ½ÀÇ ÆĶó¸ÞÅ͵éÀ» ¹Þ¾ÆµéÀÔ´Ï´Ù:
OUTPUT - Specifies the name of the consolidated trace file.
SESSION - Consolidates the file based on the specified session id (SID.SERIAL# columns from V$SESSION).
CLIENT_ID - Consolidates the file based on the specified client identifier (CLIENT_IDENTIFIER column from V$SESSION).
SERVICE - Consolidates the file based on the specified service (SERVICE_NAME column from V$SESSION).
MODULE - Consolidates the file based on the specified module (MODULE column from V$SESSION).
ACTION - Consolidates the file based on the specified action (ACTION column from V$SESSION).
TRACE_FILES - A space separated list of trace files to be searched. If omitted all files in the local directory are searched.
OUTPUT - ÅëÇÕµÈ trace ÆÄÀÏÀÇ À̸§À» ¸í½ÃÇÕ´Ï´Ù.
SESSION - ¸í½ÃµÈ ¼¼¼Ç ID¿¡ ÀÔ°¢ÇÏ¿© ÆÄÀÏÀ» ÅëÇÕÇÕ´Ï´Ù.(V$SESSIONÀ¸·ÎºÎÅÍÀÇ SID.SERIAL# Ä÷³µé).
CLIENT_ID - Ŭ¶óÀ̾ðÆ® ½Äº°ÀÚ¿¡ ÀÔ°¢ÇÏ¿© ÆÄÀÏÀ» ÅëÇÕÇÕ´Ï´Ù.(V$SESSIONÀ¸·ÎºÎÅÍÀÇ CLIENT_IDENTIFIER Ä÷³).
SERVICE - ¸í½ÃµÈ ¼ºñ½º¿¡ ÀÔ°¢ÇÏ¿© ÆÄÀÏÀ» ÅëÇÕÇÕ´Ï´Ù.(V$SESSIONÀ¸·ÎºÎÅÍÀÇ SERVICE_NAME Ä÷³).
MODULE - ¸í½ÃµÈ ¸ðµâ¿¡ ÀÔ°¢ÇÏ¿© ÆÄÀÏÀ» ÅëÇÕÇÕ´Ï´Ù.(V$SESSIONÀ¸·ÎºÎÅÍÀÇ MODULE Ä÷³).
ACTION - ¸í½ÃµÈ ¾×¼Ç¿¡ ÀÔ°¢ÇÏ¿© ÆÄÀÏÀ» ÅëÇÕÇÕ´Ï´Ù.(V$SESSIONÀ¸·ÎºÎÅÍÀÇ ACTION Ä÷³).
TRACE_FILES - ½ºÆäÀ̽º·Î ±¸ºÐµÈ °Ë»öÇϱâ À§ÇÑ trace ÆÄÀÏÀÇ ºÐÇÒµÈ ¸®½ºÆ®. ¸¸¾à »ý·«µÇ¸é, local µð·ºÅ丮 ³»ÀÇ ¸ðµç ÆÄÀϵéÀÌ °Ë»öµË´Ï´Ù.
At lease one of the search criteria must be specified. If more than one is specified only trace that matches all the criteria is consolidated. Examples of trcsess usage are:
ÃÖ¼ÒÇÑ °Ë»ö Á¶°ÇÁß Çϳª´Â ¸í½ÃµÇ¾î¾ß ÇÕ´Ï´Ù. ¸¸¾à Çϳª ÀÌ»óÀÇ °ÍÀÌ ¸í½ÃµÇ¸é ¸ðµç Á¶°ÇÀ» ¸¸Á·ÇÏ´Â traceµé¸¸ ÅëÇյ˴ϴÙ. trcsess »ç¿ëÀÇ ¿¹ÀÔ´Ï´Ù:
# Search all files for this session.
# ÀÌ ¼¼¼ÇÀ» À§Çؼ ¸ðµç ÆÄÀϵéÀ» °Ë»öÇϱâ
trcsess output=session.trc session=144.2274
# Search the specified files for this client identifier.
# ÀÌ Å¬¶óÀ̾ðÆ® ½Äº°ÀÚ¿¡ ´ëÇÑ ¸í½ÃµÈ ÆÄÀÏÀ» °Ë»öÇϱâ
trcsess output=client.trc client_id=my_id db10g_ora_198.trc db10g_ora_206.trc
# Search the specified files for this service, module and action combination.
# ÀÌ ¼ºñ½º, ¸ðµâ, ±×¸®°í ¾×¼Ç Á¶ÇÕÀ» À§ÇÑ ¸í½ÃµÈ ÆÄÀÏÀ» °Ë»öÇϱâ
trcsess output=client.trc service=my_service module=my_module action=INSERT db10g_ora_198.trc db10g_ora_206.trc
Once the consolidated trace file is produced it can be processed by the TKPROF utility like any other SQL Trace file.
By default statistics are gathered at the session level. The DBMS_MONITOR package allows this to be altered to follow the client identifier, service or combinations of the service, module and action:
Çѹø ÅëÇÕµÈ trace ÆÄÀÏÀÌ »ý¼ºµÇ¸é ±×°ÍÀº ´Ù¸¥ SQL Trace ÆÄÀϵéó·³ TKPROF À¯Æ¿¸®Æ¼¿¡ ÀÇÇؼ 󸮵ǾîÁú ¼ö ÀÖ½À´Ï´Ù.
BEGIN
-- Enable/Disable Client Identifier Statistics.
-- Ŭ¶óÀ̾ðÆ® ½Äº°ÀÚ Åë°è »ç¿ë/»ç¿ëºÒ°¡´É
DBMS_MONITOR.client_id_stat_enable (client_id => 'my_id');
DBMS_MONITOR.client_id_stat_disable (client_id => 'my_id');
-- Enable/Disable Service, Module and Action Statistics (various overloads).
-- ¼ºñ½º, ¸ðµâ, ±×¸®°í ¾×¼Ç Åë°è »ç¿ë/ºÒ°¡´É(´Ù¾çÇÑ ºÎÇÏ).
DBMS_MONITOR.serv_mod_act_stat_enable (
service_name => 'my_service');
DBMS_MONITOR.serv_mod_act_stat_enable (
service_name => 'my_service',
module_name => 'my_module');
DBMS_MONITOR.serv_mod_act_stat_enable (
service_name => 'my_service',
module_name => 'my_module',
action_name => 'INSERT');
DBMS_MONITOR.serv_mod_act_stat_disable (
service_name => 'my_service',
module_name => 'my_module',
action_name => 'INSERT');
END;
/
The gathered statistics can be displayed using the following views:
¸ð¾ÆÁø Åë°èµéÀº ´ÙÀ½ÀÇ ºäµéÀ» »ç¿ëÇؼ Ç¥½ÃµË´Ï´Ù:
DBA_ENABLED_AGGREGATIONS - Accumulated global statistics.
V$CLIENT_STATS - Accumulated statistics for the specified client identifier.
V$SERVICE_STATS - Accumulated statistics for the specified service.
V$SERV_MOD_ACT_STATS - Accumulated statistics for the specified service, module and action combination.
V$SVCMETRIC - Accumulated statistics for elapsed time of database calls and CPU usage.
DBA_ENABLED_AGGREGATIONS - ´©ÀûµÈ Àü¿ª Åë°èµé.
V$CLIENT_STATS - ¸í½ÃµÈ Ŭ¶óÀ̾ðÆ® ½Äº°ÀÚ¸¦ À§ÇÑ ´©ÀûµÈ Åë°èµé.
V$SERVICE_STATS - ¸í½ÃµÈ ¼ºñ½º¸¦ À§ÇÑ ´©ÀûµÈ Åë°èµé.
V$SERV_MOD_ACT_STATS - ¸í½ÃµÈ ¼ºñ½º, ¸ðµâ, ±×¸®°í ¾×¼Ç Á¶ÇÕÀ» À§ÇÑ ´©ÀûµÈ Åë°èµé.
V$SVCMETRIC - DB ¿äû¿¡ °É¸®´Â ½Ã°£°ú CPU »ç¿ë¿¡ ´ëÇÑ ´©ÀûµÈ Åë°èµé.
SAMPLE Clause Enhancements
»ùÇà ±¸¹® Çâ»ó
The SAMPLE clause allows a query to return a limited sample of data by specifying a percentage of rows or blocks to scan. This clause can now be present in complex queries:
»ùÇà ±¸¹®Àº °Ë»öÇϱâ À§ÇÑ ¿µé ¶Ç´Â ºí·ÏµéÀÇ ÆÛ¼¾Æ®¸¦ ¸í½ÃÇÔÀ¸·Î½á µ¥ÀÌÅÍÀÇ Á¦ÇÑµÈ »ùÇÃÀ» ¹ÝȯÇϱâ À§ÇÑ Äõ¸®¸¦ Çã¿ëÇÕ´Ï´Ù.
-- Query 10% or rows.
-- 100%³ª ¿µéÀ» Äõ¸®.
SELECT e.empno, e.ename, d.dname
FROM emp SAMPLE (10) e
JOIN dept d ON e.deptno = d.deptno;
-- Query 10% of blocks.
ºí·ÏÀÇ 10%¸¦ Äõ¸®.
SELECT e.empno, e.ename, d.dname
FROM emp SAMPLE BLOCK (10) e
JOIN dept d ON e.deptno = d.deptno;
Hash Partitioned Global Indexes
Hash·Î ºÐÇÒµÈ Àü¿ª À妽ºµé
Support for hash partitioned global indexes has been added in Oracle 10g which can improve performance when a small number of leaf blocks are experiencing high levels of contention. The syntax for creating of a hash paritioned global index is:
ÀÛÀº ¼öÀÇ ´Üµ¶ ºí·ÏµéÀÌ ³ôÀº µî±ÞÀÇ °æÇÕÀ» °æÇèÇÒ ¶§ ¼º´ÉÀ» Çâ»óÇÒ ¼ö ÀÖ´Â hash·Î ºÐÇÒµÈ Àü¿ª À妽ºµéÀ» Áö¿øÇϱâ À§Çؼ Oracle 10g¿¡ Ãß°¡µÇ¾ú½À´Ï´Ù. hash·Î ºÐÇÒµÈ Àü¿ª À妽º¸¦ »ý¼ºÇÏ´Â ±¸¹®Àº:
CREATE INDEX hgidx ON tab (c1,c2,c3) GLOBAL
PARTITION BY HASH (c1,c2)
(PARTITION p1 TABLESPACE tbs_1,
PARTITION p2 TABLESPACE tbs_2,
PARTITION p3 TABLESPACE tbs_3,
PARTITION p4 TABLESPACE tbs_4);
For further information see:
|