Search

OakieTags

Who's online

There are currently 0 users and 34 guests online.

Recent comments

Affiliations

Uncategorized

How to get dreadful database performance

#555555;" align="left">#2970a6;" href="http://www.kylehailey.com/sample-page/current/">

 

I love this video on “getting dreadful database performance” by Stephane Faroult.
The video is hilarious and informative:

Screen Shot 2014-05-21 at 8.08.47 AM
http://www.roughsea.com/vids/SQL_Best_Practices.html

SQL*Plus Procedures and Password Encryption

#555555;" align="left">#2970a6;" href="http://www.kylehailey.com/sample-page/current/">

Sampling Performance Data on Oracle

#555555;" align="left">#2970a6;" href="http://www.kylehailey.com/sample-page/current/">

I want to be a simplifier – thanks Steve Jobs

#555555;" align="left">#2970a6;" href="http://www.kylehailey.com/sample-page/current/">

 

#555555;">

Any intelligent fool can make things bigger and more complex. It takes a touch of genius – and a lot of courage – to move in the opposite direction.

Why did Google succeed? Yahoo shuts altavista down

#555555;" align="left">#2970a6;" href="http://www.kylehailey.com/sample-page/current/">

Yahoo shut  #2970a6;" href="http://online.wsj.com/article/SB10001424052748703395204576024024258782758.html">Altavista down a couple of years ago. Why did Google succeed when Altavista had index the web before Google ever started?

Dan Rosensweig former COO at Yahoo responding to the question: Why did Google succeed in search over Yahoo?

Date conversions

#555555;" align="left">#2970a6;" href="http://www.kylehailey.com/sample-page/current/">

#555555;">Dates are a surprising pain for correlating data an at the same time being understandable to the human reader.

#555555;">For a generic form we can take seconds since 1970, on UNIX, with

#555555;">$ date '+%s'
1311372938

#555555;">but then how to we convert it back? There is a neat trick for that, “date -d @”

#555555;">$ date -d @1311372938  +%H:%M:%S
22:15:38

#555555;">so in scripts we can load up the date into seconds, that can be subtracted to get deltas and at the same time converted easily to any date format :

I/O wait histograms in Oracle

#555555;" align="left">#2970a6;" href="http://www.kylehailey.com/sample-page/current/">

#555555;">DBA_HIST_EVENT_HISTOGRAM has a history of I/O latency histograms. DBA_HIST_EVENT_HISTOGRAM is only on 11g, so for 10g, it requires collecting the histogram deltas manually off of V$EVENT_HISTOGRAM, something like

#555555;">   
set pagesize 150
col event format a25
col tm format a14
select event,
       to_char(sysdate,'J')||to_char(round(sysdate-trunc(sysdate),5)) tm,
       wait_time_milli,
       wait_count
from v$event_histogram
where event in ('log file parallel write' ,
                'db file scattered read' ,
                'db file sequential read' )
order by event;

#555555;">and collecting this information every N seconds and taking the deltas.

Version control a multi-terrabyte database?! Yes !

#555555;" align="left">#2970a6;" href="http://www.kylehailey.com/sample-page/current/">

9009_03

Can you version control database?

How do you manage databases in application development?  For all the advances in application development such as machine virtualization , agile development, devops practices, the database still stands as a huge barrier to development agility.

NFS max “rsize” on Solaris – gotcha

Laptop and Stethoscope

#555555;">When mounting NFS file systems there is an option to set the max rsize requested. For example:

#555555;">mount -o rsize=1048576,wsize=1048576,proto=tcp,vers=3  192.168.1.10:/foo /foo

#555555;">The general rsize used is 32K,  for example in Oracle documentation, but for large sequential I/O the larger rsize can make a big difference. In some tests the larger rsize was twice as fast.

Query slower on exact clone

#555555;">A customer  was working with a clone copy of their production database for reports, but the cloned database reports were running slower than the same reports on production and they wanted to know why.

#555555;">Ostensibly the machines were similar and the memory was the same, though it’s always worth verifying.  The first thing I checked was the load profile. If you have OEM you can look at the performance page and/or top activity page to see the load profile.  The load profile of production was much more CPU bound and much less I/O bound than the clone and their was clearly a difference.

#555555;">The customer knew the time they the report was run on the clone  but was unsure exactly when it was run on production, though knew it had been run in the past day.