오라클 모니터링 할 때 모니터링 툴을 많이 사용하시죠.
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$% views return data from memory structures. Data is lost when the instance is restarted.
DBA_% tables return data from the database's data dictionary (SYSTEM tablespace). Data persists across instance restarts.
DBA_% tables return data from the database's data dictionary (SYSTEM tablespace). Data persists across instance restarts.
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
V$ & DBA_
Static Data Dictionary Views and Dynamic Performance Views are the core of database administration. They are of crucial importance. Whoever wants to learn Oracle database administration has to get familiar with these views.
The Data Dictionary tables contain meta data on objects, privileges, roles, users etc.
Whenever you issue DML statements Oracle consults its data dictionary to make sure a table is there, it has the right columns and you have privileges on it. And whenever you issue DDL statements Oracle updates its data dictionary.
All information needed by an instance on its database is kept in the data dictionary. Except some storage related information which is in the datafile headers and some information in controlfile and spfile which is needed at instance startup.
At runtime the oracle instance stores information about itself, about its CPU, memory or I/O performance, wait statistics and everything else which is going on at the instance in dynamic performance tables. Dynamic performance tables are non persistent. You can’t see past instance startup. After shutdown or instance crash all information contained in dynamic performance tables is lost. *1)
Through static dictionary views users can get at information kept in data dictionary tables.
And through dynamic performance views users can look at non persistent runtime information contained in dynamic performance tables.
But why are those views that important, you might ask? All information necessary for database administration can be found in those static dictionary views. And basically all information necessary for instance diagnostic and tuning can be obtained through those dynamic performance views at runtime.
Of course, GUI tools like Enterprise Manager or SQL Developer offer the same information
in a more user friendly manner. But all those GUI tools rely on static dictionary views and dynamic performance views.
As a DBA you will likely get into situations where there is no GUI tools available. At times you will be alone with your SQL*Plus skills. Your mastery of data dictionary and performance views will make all the difference!
Static Data Dictionary Views and Dynamic Performance Views are the core of database administration. They are of crucial importance. Whoever wants to learn Oracle database administration has to get familiar with these views.
The Data Dictionary tables contain meta data on objects, privileges, roles, users etc.
Whenever you issue DML statements Oracle consults its data dictionary to make sure a table is there, it has the right columns and you have privileges on it. And whenever you issue DDL statements Oracle updates its data dictionary.
All information needed by an instance on its database is kept in the data dictionary. Except some storage related information which is in the datafile headers and some information in controlfile and spfile which is needed at instance startup.
At runtime the oracle instance stores information about itself, about its CPU, memory or I/O performance, wait statistics and everything else which is going on at the instance in dynamic performance tables. Dynamic performance tables are non persistent. You can’t see past instance startup. After shutdown or instance crash all information contained in dynamic performance tables is lost. *1)
Through static dictionary views users can get at information kept in data dictionary tables.
And through dynamic performance views users can look at non persistent runtime information contained in dynamic performance tables.
But why are those views that important, you might ask? All information necessary for database administration can be found in those static dictionary views. And basically all information necessary for instance diagnostic and tuning can be obtained through those dynamic performance views at runtime.
Of course, GUI tools like Enterprise Manager or SQL Developer offer the same information
in a more user friendly manner. But all those GUI tools rely on static dictionary views and dynamic performance views.
As a DBA you will likely get into situations where there is no GUI tools available. At times you will be alone with your SQL*Plus skills. Your mastery of data dictionary and performance views will make all the difference!
동감입니다 !
tool을 사용하는 것이 더 효율적이라 생각하실지 모르지만
마지막엔 DBA가 직접 sqlplus에서 dictionary view들을 query해야 하는 경우가 허다하죠..
너무 툴에 익숙해지지 마세요.. ^^
'Oracle Database' 카테고리의 다른 글
| Oracle AWR 이란? (0) | 2009.01.07 |
|---|---|
| oracle media recovery시 챙겨봐야 할 oracle dictionary view들 (0) | 2009.01.05 |
| oracle 10g RAC & CRS (1) | 2008.12.31 |
| Oracle SQL plan 확인하는 방법 (0) | 2008.12.16 |
| active session의 event 정보를 보여주는 sql script <wait_buffer.sql> (0) | 2008.11.25 |