Oracle Logbook

May 21, 2014

Enqueue Waits in Oracle Database 10g

Filed under: Uncategorized — heliodias @ 8:04 pm

Alex Zeng's Blog

Enqueue waits events usually causes problems in busy Oracle databases. But oracle online document only have some of them until 11gR2, not sure why. I paste these information here for quick reference.

In Oracle Database 10g Release 1, each enqueue type is represented by its own wait event, making it much easier to understand exactly what type of enqueue the session is waiting for. You do not need to decipher the values from the P1, P2, P3, P1RAW, P2RAW, and P3RAW columns in the V$SESSION_WAIT or the V$SESSION view.

The following table lists all the enqueue waits in Oracle Database 10g Release 1 and describes what the enqueue is for. This information is available in the X$KSQST structure. The aggregated statistics for each of these enqueue types is displayed by the view V$ENQUEUE_STAT.

Enqueue TypeDescription
enq: AD – allocate AUSynchronizes accesses to a specific OSM (Oracle…

View original post 4,363 more words

April 1, 2014

Over-Configuring DBWR Processes – Part II.

Filed under: Uncategorized — heliodias @ 3:01 pm

Kevin Closson's Blog: Platforms, Databases and Storage

In my recent blog entry about over-configuring DBWR processes, I mentioned the processor cache effect of having too many DBWR processes doing work that a single DBWR process could handle. I’d like to provide more detail on the matter.

Few people really understand what DBWR does for a living. Yes, everyone knows DBWR is responsible for flushing modified buffers from the SGA, but that is really high level. DBWR:

  • 1. Builds “write batches” by examining the state of buffer headers on the LRU lists. It scans the LRU end considering the age of the buffer. At instance boot time, each DBWR process is assigned a set of LRUs to tend to.
  • 2. Manipulates cache buffers chains elements for such reasons as marking the buffer busy when it is in flight for an I/O and marking it as written after the flush operation.
  • 3. Performs I/O using the OSDs…

View original post 1,755 more words

February 28, 2014

How to use OLAP inside an Oracle update

Filed under: Uncategorized — heliodias @ 2:48 pm

Yesterday a client told me she couldn´t find any example of this on google,

It really surprised me, so I decided produce an example of this:



June 30, 2013

Query to find out the ideal size for redo log

Filed under: Uncategorized — heliodias @ 4:06 am

It´s well know among DBAs that the redo log must few times  as possible, some say  that is more than 4 times per hour others says 2.

Now one must do is run the below query, adjusting the interval of switch (the 15 number) and voila, you will get a projection

of redo size per instance and group.


select Instancia “Instance”,g1 Group ,trunc(bytes/1024/1024/round((tempo2-tempo1)*60*24)*15,2) “Redo MB Projected for 15 switch”,bytes/1024/1024 “Actual Redo MB” from ( select t1.thread# Instancia, g1,t1.first_time tempo1,min(t2.first_time) tempo2 from v$log t1,v$log t2 where t1.thread#=t2.thread# and<> and t2.first_time>t1.first_time group by t1.thread#,,t1.first_time ) t1,v$log t2 where Instancia=thread# and g1=group# Order by 1,3;

If you want to improve this query, change the v$log to archives view, than you will have a wide view.

Hope it help.

May 13, 2013

Benefits of Oracle Block Change Tracking

Filed under: Uncategorized — heliodias @ 8:53 pm

Regarding of amount of block change and the size of your database, one can have a huge decrease in backup time.

To activate:

alter database enable block change tracking using file ‘/u02/oracle/bct/bct.log’;

To following it:

SELECT filename, status, bytes
FROM v$block_change_tracking;

March 27, 2013

Query to check if your Oracle redo changing is too frequently

Filed under: Uncategorized — heliodias @ 7:12 pm

Everywhere is easy to find some paper regarding the frequency that redo should be filled up.

Personally I like the 1/2 hour changing, it doesn´t put a log of pressure at checkpoint, giving us a good balance between recovery time and wait of redo sync.

The query bellow will show if any changing during the working time (7AM to 7PM) is happening more often then twice an hour.

(It will check just the last ten days)

select to_number(to_char(t1.FIRST_TIME,’ddmm’)||t1.thread#||t1.sequence#) sequencia,
to_number(to_char(t1.FIRST_TIME,’hh24′))*60+ to_number(to_char(t1.FIRST_TIME,’mi’)) time ,
to_number(to_char(t2.FIRST_TIME,’hh24′))*60+ to_number(to_char(t2.FIRST_TIME,’mi’)) time2
from v$archived_log t1,v$archived_log t2
to_number(to_char(t1.FIRST_TIME,’ddmm’)||t1.thread#||t1.sequence#)=to_number(to_char(t2.FIRST_TIME,’ddmm’)||t2.thread#||t2.sequence#-1) and
(to_number(to_char(t2.FIRST_TIME,’hh24′))*60+ to_number(to_char(t2.FIRST_TIME,’mi’)) )

(to_number(to_char(t1.FIRST_TIME,’hh24′))*60+ to_number(to_char(t1.FIRST_TIME,’mi’)) )
<30  –redo changing more than 2 times per hour
/*  begining of time validation,
aiming to avoid batch operation */
and to_number(to_char(t1.FIRST_TIME,’hh24′))  between 7 and 19 and
to_number(to_char(t2.FIRST_TIME,’hh24′))  between 7 and 19
— end of time validation
and t1.first_time>sysdate-10
and t2.first_time>sysdate-10

March 21, 2013

Monitoring backup through Alert

Filed under: Uncategorized — heliodias @ 4:21 pm

This procedure will write into alert.log when the enviroment doesn´t have backup online working properly.


SELECT decode(count(*),0,’FAIL’,’OK’) INTO STATUS_ONLINE
dbms_system.ksdwrt(2,’ORA-00600: ONLINE BACKUP FAIL, NOT BACKING UP   +36 HOURS’);



March 8, 2013

Why Oracle automatic memory management is often a bad idea

Filed under: Uncategorized — heliodias @ 2:11 pm

Oracle might had created this feature for those whom doesn´t have a DBA onsite and need a database that can operate by itself.

The use of AMM is not free of charge, many peak of usage in one area often lead to future decrease in performance in another area.


e.g. If a bad query loads tons of cache in database buffer, and Oracle deallocate memory from shared pool to accomplish this request, eventually the whole database will pay a huge price with decreasing in performance due to lack of information at shared pool.


So the botton line is, if your database have someone watching close to it,  don´t use AMM.

February 25, 2013

The best advance in Oracle database 12c

Filed under: Uncategorized — heliodias @ 8:11 pm

Adaptive execution plans.

This new feature, by itself will justify many upgrade around the globe.

Most of database operation are repetitions, and as everywhere always have a “bad execution plan” due to lack of precise statistics. And this used to go over and over.

But now the database will gonna behaviour better and better.

I can imagine improvements in more 90% on hardest operation.

Decreasing in CPU demands and memory.





February 14, 2013

How to measure IO performance inside Oracle Database

Filed under: Uncategorized — heliodias @ 8:36 pm

The challenge was to create a baseline from Oracle database viewpoint which allow us to spot after a storage migration if the performance changed.

I created a “magical number” called Whole Mbytes per seconds. Which is the total write and read of the whole database operations, including background processes, redos write and of course the foreground processes divided by the IO wait per second.

select total_mb/trunc(value/100) from v$sysstat,
(select trunc(sum(value)/1024/1024) total_mb from v$sysstat where name in
(‘physical read total bytes’,’physical write total bytes’))
where name=’user I/O wait time’;

Next Page »

Blog at