RE: DBA Humor (Twisted, sick sort of stuff...)

  • From: "Schauss, Peter (ESS)" <peter.schauss@xxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 29 Mar 2013 19:37:39 +0000

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Sheehan, Jeremy
Sent: Thursday, March 28, 2013 8:46 AM
To: rjamya@xxxxxxxxx; 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxx> wrote:

> I am sure that speeded up the inserts on the tables with the concerned 
> indexes.
>
> Did that speed up some of the queries?
>
>


--
//www.freelists.org/webpage/oracle-l




--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2013