敗家貓

1.oracle提供了一些view供dba自行解讀現行instance運行的情況 (ADDM可以自動幫忙解析一些資訊，並且給一些建議)

2.如要查看歷史的db運行情況，可使用ADDM/AWR/STATSPACK

3.Diagnostics Pack/Tuning Pack是要付費才能使用的，如關畢的話，將無法EM裏使用有關 performance
http://docs.oracle.com/cd/E11882_01/license.112/e10594/options.htm#CIHIHDDJ

4.10g以後，oracle提供了 time model 加強了 cost 的運作的判斷的客觀性
V$SYS_TIME_MODEL / V_SESS_TIME_MODEL

5.DB Time = DB Wait Time + DB CPU Time

6.DB CPU Time 如主機有多棵cpu的話，等同 nXcpu數

7.statistic levels 有 basic / typical / all
basic : 只有收集相關的db運行的資訊
typical : basic + advisories功能開起
all : typical + 收集oracle內部運作的資訊，多半用於無法明確定位出問題點在那裏的情況下使用
view : v$statistics_level

8.判斷db的資訊，由大至小 : v$sysstat --> v$service_stats --> v$sesstat (v$mystat)

9.Wait Class : v$session_wait_class / v$service_wait_class / v$system_wait_clasee

10.oracle除了tkprof可解析trace file，也可使用yapp 進行解析

11. 11gr2 提供了 RDA (Oracle Remote Diagnostic Agent)可幫忙dba一次性收集足夠相關資訊給oracle進行解決問題
>>> AWR <<<
1.SGA裏的資訊是由MMON(manageability monitor)把資訊保留到AWR snapshots
2.ASH是取樣現在運行中的session的資訊存SGA裏，如保留的資訊大小超過一定的量的話，將會把相關資訊保留到AWR
並且ASH裏的資訊會被重新覆寫的
3.11g2 snapshot 預設值保留8天(10g是7天)，每個小時產生一個snapshot
4.snapshot 建立baselines可當作效能的基礎點，建立成baselines後，除了dba手動刪除之外，將會保留到永遠
5.compare periods : 使用差異的方法，查看同時間點到底有什差異發生，可幫忙快速定位出問題點

心得:
dba在調整性能上，有時並不只能只在數值上打滾，應該是要以使用者的感受為最大考量，如使用登入的時間由10秒縮短為5秒，會比一個報表從30分鐘縮短成25分鐘，直接給使用者感受是完全不同的

A 'consistent get' is your server process telling the database "I need this dba (data block address) consistent with the point in time represented by this SCN, x."

So, lots of things can happen here. First, Oracle will look in the buffer cache for a CR (consistent read) buffer of the block that's consistent   the requested SCN. It may find it, if it does, that's counted as a 'consistent get' and either a 'consistent gets - no work' or 'consistent gets - cleanouts only', depending on whether the block needed to be cleaned out. (See V$SYSSTAT/V$SESSTAT for the statistics.) If it doesn't, it may take an existing CR buffer and roll it back further, or it may clone the current block and roll it back. If it needs to apply rollback (aka undo) then it will increment 'consistent gets' and either 'consistent gets - rollbacks only' or 'consistent gets - cleanouts and rollbacks'.

So, each 'consistent get' is your server process successfully getting access to the contents of a dba consistent   a particular SCN. This number should represent the number of buffer gets required to satisfy a particular query.

Now, 'db block gets'. A 'db block get' is a copy of the 'current mode block'. That is, the data in the block, as it exists currently, or at this point in time. Note that while multiple CR copies of a block may exist in the buffer cache, there can only ever be one current mode copy of a block in the buffer cache at any one time. (RAC is a special case,   shared current and exclusive current, but I'm not going to get into that here.) So, a 'db block get' is a buffer get in current mode. 'db block gets' are usually associated   DML, and in that scenario, will implicitly lock one or more rows in that block. Also, there is a notable case where db block gets can occur with a select statement. That will happen when you're doing a FULL TABLE SCAN on a table or a FAST FULL SCAN on an index. In those cases, the segment header is read (usually multiple times for some unknown reason) in current mode.

Next, 'physical reads': A physical read will occur any time a consistent get or a db block get goes looking for block and can't find it in the buffer cache. So, for each block read from disk, physical reads will be incremented. Gets which result in physical reads are counted both as as get and as a read in the statistics. So, if you do 10 consistent gets and 5 of them require physical reads, you should see consistent gets incremented by 10 and physical reads incremented by 5.
Now, what's up   arraysize? Well, arraysize is the client side setting for SQL*Plus specifying the size of the array that will receive result sets. The default, as you learned, is 15. Now, suppose you have a table where there are 30 records per block, and 3,000 rows in the table. But, your arraysize is 15. So, your server process will get the first 15 rows, and return them from the first block buffer. Now, for the next 15, you need to get that same block again, for rows 16-30. So, you need to do two buffer gets per block to get all the rows. For a 3,000 row table, you'll do (approximately) 3,000/15 = 200 buffer gets. If you change your arraysize to 30, you can get away   visitng each block only once and do 3,000/30 = 100 buffer gets.

So, consider that even after you've optimized a particular SQL statement, if the arraysize is too small, you're going to force your server process to do excess database calls (FETCH calls), and extra buffer gets as well. This can best be illustrated with a test similar to what you did, but try looking at the raw trace file for FETCH calls. The number of FETCH calls ought to be very close to (number of rows returned / arraysize). The 'r=xxx' in the FETCH call data in the trace file is the number of rows returned, which is probably what your arraysize is set to.

So, db block gets, consistent gets, and physical reads are all measured in buffers (or blocks). If the same block is requested multiple times, it will be counted that many times in these statistics. Oracle will always access data from the buffer cache by the buffer. All 'get' operations are by the buffer, never by row. After the buffer is 'gotten', Oracle parses the block to get the data for the relevant rows.
When a "consistent get" is done, this doesn't necessarily mean that Oracle had to do any rollback/undo of DML in order to get the consistent image. Rather, it just means that Oracle requested a copy of the block as of the point in time that the query was started (a "consistent snapshot"). So, I believe it is quite normal to have many, many consistent gets even if there is NO DML occuring.


http://www.itpub.net/thread-39939-1-1.html
consistent gets : 通過不帶for update的select 讀的次數
db block gets : 通過update/delete/select for update讀的次數.

原本的 update eri set texto = 'CONDUTOD MC'S 12234' ;

可改寫成下例方法
update eri set texto = 'CONDUTOD MC''S 12234' ;
update eri set texto = q'[CONDUTOD MC'S 12234]' ;
update eri set texto = 'CONDUTOD MC' || chr(39) || 'S 12234';

近期剛好在看一些db轉碼的問題，剛好看到DMU這東東 ...繼續閱讀

oracle推出檢核安裝的rpm，這對於很多人不愛看安裝文件的dba來說，算是非常有幫助的 ...繼續閱讀

最近看到了一個很不錯的免費DB管理工具

DataGuard是個很成熟的東西，較沒有什技術上的困難點 ...繼續閱讀