Oracle Logbook

September 18, 2020

Cloud Exadata for free

Filed under: Uncategorized — Helio Dias @ 9:01 pm

Have you ever wonder, how it feels to be “piloting” an Oracle Exadata?

I started using daily Exadata since 2015, and can assure you, feels like being in a rocket, and now Oracle provide a lifetime Oracle Exadata for free, everyone should have one, not just for testing, but maybe to do some real hard calculations, I myself have 2 personal free cloud Exadata.

Just for the sake of comparison, I did a 10 billion rows average query on PostgreSQL, and it took 3 hours. On Exadata a 6 Billion rows average took 23 SECONDS.

The Oracle table had 161 GB.

select avg(lo_quantity) from ssb.lineorder;
25.49

Duration: 23 seconds

Maybe this column have tons of nulls?

Let’s check:

select COUNT(*) from ssb.lineorder WHERE LO_QUANTITY IS NOT NULL;

5999989709

🙂 no it is really just the power of Exadata.

There are indeed some limitation with this Cloud Exadata Free Lifetime, But the key aspect is that it brings this “lineorder” sample table with 161GB, which you can create your own tables around, then join with it, and see how much Exadata can do for your company.

May 21, 2014

Enqueue Waits in Oracle Database 10g

Filed under: Uncategorized — Helio Dias @ 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 Type Description
enq: AD – allocate AU Synchronizes 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 — Helio Dias @ 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.

Backgrounder
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 — Helio Dias @ 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:

UPDATE
APOIO4 B
SET B.COL_VAL = 10
WHERE ROWID IN
(SELECT ROWID FROM (SELECT ROWID,RANK() OVER (PARTITION BY COLPK1, COLPK2
ORDER BY COL4 DESC, COL5) SEQUENCIA
FROM APOIO4
WHERE COLFILTER1=’VD902′
AND COLFILTER2 = ‘100161’) WHERE SEQUENCIA=1);

 

June 30, 2013

Query to find out the ideal size for redo log

Filed under: Uncategorized — Helio Dias @ 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,t1.group# g1,t1.first_time tempo1,min(t2.first_time) tempo2 from v$log t1,v$log t2 where t1.thread#=t2.thread# and t1.group#<>t2.group# and t2.first_time>t1.first_time group by t1.thread#,t1.group#,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 — Helio Dias @ 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 — Helio Dias @ 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
where
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 — Helio Dias @ 4:21 pm

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

 

CREATE OR REPLACE PROCEDURE SYS.L3_BACKUP_CHECK
AS
STATUS_ONLINE VARCHAR2(10);
BEGIN
SELECT decode(count(*),0,’FAIL’,’OK’) INTO STATUS_ONLINE
FROM V$RMAN_BACKUP_JOB_DETAILS T1 WHERE START_TIME>SYSDATE-36/24
AND INPUT_TYPE NOT LIKE ‘%ARCHIVE%’;
IF STATUS_ONLINE=’FAIL’ THEN
dbms_system.ksdwrt(2,’ORA-00600: ONLINE BACKUP FAIL, NOT BACKING UP   +36 HOURS’);
END IF;
END;

 

 

March 8, 2013

Why Oracle automatic memory management is often a bad idea

Filed under: Uncategorized — Helio Dias @ 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 — Helio Dias @ 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.

 

 

 

 

Next Page »

Create a free website or blog at WordPress.com.