Frederick Tang Weblog

Thanks to the Rosh, I have discovered a few cooling fans that fits in the 5.25″ drive bays. These fans would occupy 3 optical drive slots, with a 120mm fan that sucks air from outside the case to inside the case – improving air flow inside the case.

I found 3 products available on the market so far:

Scythe Kama Bay

http://www.scythe-usa.com/product/acc/008/sckb1000_detail.html

SilverStone

http://www.silverstonetek.com/products/p_contents.php?pno=cfp51&area=usa

Cooler Master 4-in-3 Device Module

http://www.coolermaster.com/products/product.php?act=detail&tbcate=65&id=2542

The latter two converts 3 5.25″ drive bays into 3.25″ HDD drive bays with a 120mm fan at the front.

The Scythe Kama Bay is purely a fan with an air filter to which it can be removed and cleaned.

The question is, why can’t I just have the bracket without a fan, so I can stick a Noctua NF-S12 in there myself?

http://www.silentpcreview.com/article695-page4.html

In one of the databases we are running here, a Statspack snapshot is taken every hour. I wanted to track the CPU Time of every 60 mins window on a particular day. There must be a smart query that can tell me this – but instead, I chose to do it the dumb/manual way – generate a series of Statspack report and plot the CPU Time in each on a spreadsheet.

So I wrote a PL/SQL for that, I’m beginner at this… so don’t laugh :S

DECLARE
s_sid NUMBER;
e_sid NUMBER;
stime VARCHAR(20);
CURSOR SSCursor IS
select snap_id as s_snap_id, lead(snap_id) over (order by snap_id asc) e_snap_id,
to_char(snap_time, ‘hh24′) snap_time
from stats$snapshot
where to_char(snap_time, ‘dd/mm/yyyy’)=’20/05/2007′ and instance_number=’1′
order by snap_id;
BEGIN
OPEN SSCursor;
LOOP
FETCH SSCursor INTO s_sid, e_sid, stime;
EXIT WHEN SSCursor%NOTFOUND;
dbms_output.put_line(‘define begin_snap=’s_sid);
dbms_output.put_line(‘define end_snap=’e_sid);
dbms_output.put_line(‘define report_name=/tmp/sp_20052007_’stime’.lst’);
dbms_output.put_line(‘@?/rdbms/admin/spreport.sql’);
dbms_output.put_line(”);
END LOOP;
CLOSE SSCursor;
END;

That generated a series of reports into the /tmp directory for Statspack snapshots generated on the 20th May 2007. What’s missing is before midnight of 20th and after midnight of 20th – but that’s some details I was okay to lose.

So I ran the PL/SQL, paste the commands into a text file and ran them. The spreport.sql prompts for begin snap, end snap and report name, defining them avoids being prompted.

I done this for random 3 days in the month. I opened each file and extracted the CPU Time, put them on a spreadsheet and out comes a graph – someone please tell me you have a query to do this? -.-

So what’s this telling me?

One of the hardware servers at work is getting a memory upgrade. It houses the web application and the Oracle database. I set out to take a look whether the memory needs tuning for this particular database.

I will concentrate on talking about what I found with PGA in this post, because I have learnt a thing or two in this case.

1. I first took at look at the current PGA settings.

SQL> select * from v$pgastat;

NAME                                          VALUE UNIT
———————————- —————- ———-
aggregate PGA target parameter          268,435,456 bytes
aggregate PGA auto target               219,930,624 bytes
cache hit percentage                             90 percent

I cut out most of the statistics… but you can see the target parameter is 256MB and cache hit is 90%. The formula to calculate cache hit percentage seems to be (100 * total bytes processed) / (total bytes processed + extra bytes read/written) – as given here.

Next is take a look at why 90%.

2. SQL Workarea Histogram

One of the similar view of the SQL Workarea is v$sysstat:

SQL> select name, value from v$sysstat
  2  where name like ‘workarea executions%’;

But to see a thorough breakdown of what is actually happening, the v$sql_workarea_histogram is more useful. The full description is available in the Oracle9i Database Reference here.

SQL> ;
  1  SELECT
  2  case when low_optimal_size < 1024*1024
  3  then to_char(low_optimal_size/1024,’999999′) ||
  4     ‘kb <= PGA < ‘ ||
  5     (HIGH_OPTIMAL_SIZE+1)/1024|| ‘kb’
  6     else to_char(low_optimal_size/1024/1024,’999999′) ||
  7             ‘mb <= PGA < ‘ ||
  8                     (high_optimal_size+1)/1024/1024|| ‘mb’
  9     end pga_size,
 10     optimal_executions as opt_execs,
 11     onepass_executions as ops_execs,
 12     multipasses_executions as mps_execs
 13     from v$sql_workarea_histogram
 14     where total_executions <> 0
 15*    order by low_optimal_size
 16  /

PGA_SIZE                        OPT_EXECS  OPS_EXECS  MPS_EXECS
—————————— ———- ———- ———-
      8kb <= PGA < 16kb          11878328          0          0
     16kb <= PGA < 32kb             18322          0          0
     32kb <= PGA < 64kb              4581          0          0
     64kb <= PGA < 128kb             5178          0          0
    128kb <= PGA < 256kb              280          0          0
    256kb <= PGA < 512kb              308          0          0
    512kb <= PGA < 1024kb             208          0          0
      1mb <= PGA < 2mb                985          0          0
      2mb <= PGA < 4mb                 40          0          0
      4mb <= PGA < 8mb                125          0          0
      8mb <= PGA < 16mb                77       1011          0
     16mb <= PGA < 32mb                 0         22          0
     32mb <= PGA < 64mb                 0         40          0

12 rows selected.

That’s telling me there are 1011 workarea executions requiring more than 8MB but less than 16MB of memory. The interesting question is why an additional pass is needed for these executions when the PGA target is set at 256MB?

According to MetaLink Note:147806.1

PGA_AGGREGATE_TARGET limits both the global PGA consumption and the size of a workarea i.e. the memory allocated to a single SQL operator is also limited to min(5% PGA_AGGREGATE_TARGET, 100MB) for serial operations, and to 30% PGA_AGGREGATE_TARGET/DOP for parallel operations (DOP=Degree of Parallelism).

In this case, 5% of 256MB is 12.80MB. It led me to think that is why the 1011+22+40 executions had to perform an additional pass.

3. PGA Advisory

See the full description of v$pga_target_advice here. So what is the PGA Advisory recommending I set the PGA target to?

SQL> select round(pga_target_for_estimate/1024/1024)

  2         target_size,  
  3         estd_pga_cache_hit_percentage as est_hit_pct
  4  from v$pga_target_advice;

TARGET_SIZE EST_HIT_PCT
———– ———–
         32          83
         64          87
        128          89
        192          90
        256          90
        307          99
        358          99
        410          99
        461          99
        512          99
        768         100
       1024         100
       1536         100
       2048         100

So the PGA Advisory estimates that if I increase the PGA target size by 50MB (from 256MB to 307MB), the PGA Cache Hit percentage will increase to 99%.

In this case, 5% of 307MB is 15.35MB, which would accommodate most of the 1011 in an optimal pass execution, but the 22+40 executions would still be a multipass execution.

The statistics in the series of v$ views are… [source]

…reset at instance startup and when the value of the PGA_AGGREGATE_TARGET initialization parameter is dynamically modified.

I picked up quite a few tips in this post from these sites:

http://www.quest-pipelines.com/newsletter-v5/0304_A.htm

http://www.dbasupport.com/oracle/ora9i/active_monitor_tune.shtml

[Written using Windows Live Writer - finally got it installed on my laptop - how nice!!]

Recently I found this interface tool called Enso, it combines GUI with CLI (Command Line). It allows you to quickly open an application, open a webpage very quickly. In one of the demo I have seen, if you highlight a city and type “add map”, a google map will be automatically added.

Another example is the calculator, I just type “open calculator” rather than click “Start > All Programs > Accessories > Calculator” – you will find typing is faster than clicking+mouse moving (if you are a fast typer that is…).

One of the feature I use most is the calculator feature, and I combine it with Memento. Whenever I want some numbers calculated quick, I click open a note, type in a formula (or copy and paste from a webpage or a Word document – that I don’t want to modify), type “CA” in Enso, the formula will become the result.

Take a look at the Enso demo, you will like it…