RE: DBA Humor (Twisted, sick sort of stuff...)
Date: Fri, 29 Mar 2013 20:35:43 +0000
Message-ID: <CAOuMUT5=U00=1XAJKeCLKBLxfKyYgiMBtHfk=KnKY+4aaj-JOQ_at_mail.gmail.com>
That sounds to me like a type 1 dimension. People sometimes code type 1 dimension loads by taking an entire copy of the source table into staging and then do a simple merge statement like: Merge into a
USING b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET a.COL1 = b.COL1,
a.COL2 = b.COL2, ...
WHEN NOT MATCHED THEN
INSERT (id, COL1,COL2,...)
VALUES (b.id, b.COL1,B.COL2);
A very simple an effective change here is to use minus to filter out unchanged records before the merge
Merge into a
USING (select * from b minus
Select * from a) c
ON (a.id = c.id)
WHEN ...
Still not an optimal solution but its a drastic improvement.
On 29 Mar 2013 19:38, "Schauss, Peter (ESS)" <peter.schauss_at_ngc.com> wrote:
> Several years ago my management asked me to see what I could do to make
> the hourly ETLs (yes- hourly) feeding the data warehouse run in less than
> 65-75 minutes. "The previous DBA used to reload fragmented tables and
> indexes and then the ETL would run faster."
>
> Looking the table which recorded the start and stop times for each step of
> the ETL on each ETL run, I identified the longest running step. I found
> that it taking about 20-25 minutes. Then I turned on 10046 tracing and
> picked out the trace file which corresponded to that step. The trace file
> showed that this ETL step was updating every row in a 1.2 million row table
> every hour
>
> I made a copy of the offending table into another schema using export and
> import and, comparing it to a copy made after the next ETL, found that
> fewer than 1% of the rows had changed.
>
> I suggested to management that they might want to rethink that ETL step.
>
> - Peter Schauss
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Sheehan, Jeremy
> Sent: Thursday, March 28, 2013 8:46 AM
> To: rjamya_at_gmail.com; Ram Raman
> Cc: ORACLE-L
> Subject: EXT :RE: DBA Humor (Twisted, sick sort of stuff...)
>
> Someone at my work was complaining of slowness in one of our DB's.
> Groaning with the lack of specificity, I went to go an check and found
> that the database was being hammered. Looking around Grid, I found one
> statement was causing the problem. It was a developer selecting from 8
> different tables with no parameters. Yeah. Cartesian join on 8 tables.
>
> Jeremy
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of rjamya
> Sent: Thursday, March 28, 2013 8:36 AM
> To: Ram Raman
> Cc: ORACLE-L
> Subject: Re: DBA Humor (Twisted, sick sort of stuff...)
>
> What queries? LOL !! I said "consultants designed datamart". Which means
> they had the datamart, but, 1. no one had defined how it will be used 2. No
> one knew how to query it 3. No one could query it once it was loaded since
> they did so much of transformation in their (ahem) ETL process.
>
> So, it was a datamart that loaded about 40m_ rows every night into a DB,
> which were promptly useless. Data retention was supposed to be 7 years, we
> fought hard to bring it down to 3. Mind this, it was a standalond db, with
> a DR, (they also wanted a HA as well as a GG replicated copy for
> "reporting" purpose). After listening to costs, only primary and HA
> remained.
>
> I dont know the current status, everything else was running smoothly,
> except for this one.
>
> Raj
>
>
> On Wed, Mar 27, 2013 at 4:56 PM, Ram Raman <veeeraman_at_gmail.com> wrote:
>
> > I am sure that speeded up the inserts on the tables with the concerned
> > indexes.
> >
> > Did that speed up some of the queries?
> >
> >
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 29 2013 - 21:35:43 CET