Frederick Tang Weblog

As a DBA nowadays, I find the need to do some simple arithmetic everyday, like converting Bytes into Megabytes, adding several numbers together. I also need a calculator to do some simple money management, simply adding my $$ from different accounts, income minus expense for the month… etc.

Humanized Enso provided a very useful tool to do calculation, I type out a mathematical formula in text, in any application, highlight and press ‘Caps Lock+calc’ and the formula is replaced by the number I need.

Another tool I found today is called CalcEd. It does a similar thing, but in its own application window. It doesn’t support highlight and calculate like Enso does. One feature I liked CalcEd over Enso-calc is my formula is not replaced by the result, it is appended at the end of the line. I found copy and paste works fine with CalcEd, except from OneNote.

One of the databases at work was using dictionary managed tablespaces (DMT), and the database ran out of disk space to grow… so I was in need of finding some free space.

A good FAQ on claiming free space from fragmentation I used is here, but to find which table(s) that needs to be rebuilt onto another tablespace, I needed to see which tables or indexes I can move… because coalesce only combines adjacent free blocks.

I used this query to display visually where the free spaces are amongst extents and segments.

SELECT tablespace_name, segment_name, file_name,
       start_block, end_block, kb
FROM (
  SELECT t.tablespace_name, t.segment_name, db.file_name,

         t.block_id start_block, t.blocks num_blocks,

         t.block_id + t.blocks -1 end_block, t.bytes/1024 kb
  FROM DBA_EXTENTS t, DBA_DATA_FILES db
  WHERE t.tablespace_name = UPPER(‘&1′)
    AND t.file_id = db.file_id
  UNION
  SELECT fs.tablespace_name,

         ‘–>>FREE SPACE<<–’ as segment_name, df.file_name,

         fs.block_id start_block, fs.blocks num_blocks,

         fs.block_id + fs.blocks -1 end_block, fs.bytes/1024 kb
  FROM DBA_FREE_SPACE fs, DBA_DATA_FILES df
  WHERE fs.tablespace_name = UPPER(‘&1′)
    AND fs.file_id = df.file_id
)
ORDER BY tablespace_name, file_name, start_block ASC;

Looks like Google disabled such automated requests from another search engine. The same Google Error message was displayed for the “Web” and “Answers”, works for other settings.

I found the mouse-over enlarge frame slightly annoying. When the frame enlarges, the content shifts and changes position (due to widening of the frame) so you lose whatever you were looking at, or if you are trying to click something. There is a “Split Screen Lock” at the top right hand corner of the content window, but it is difficult to see if the browser isn’t enlarged to full screen.

Clicking on URLs from the Search Results will open the website *inside* the SearchBoth search frame. It should open in the same window in my opinion, just like it would if you were using Google alone.

Screenshot was taken today 5th July 2007 – http://www.searchboth.com/.

Today I had a look at a PL/SQL script, and found that it has two FOR loops generating and executing an INSERT statement. This script on average generates 5000 INSERT statements each execution (due to data size) and takes about 2 hours to run.

Database schema and logic aside, it is basically doing the following:

FOR i IN CUSROR_1 LOOP               – Outer for-loop
   FOR j IN 1 .. i.QUANTITY LOOP     – Inner for-loop 

        INSERT INTO <table>  ….    – Insert statement
   END LOOP;
END LOOP;

To find out how long it is really taking, I produced some test data in my test database:

CREATE TABLE SOURCETABLE (
  STR VARCHAR2(50),
  QTY NUMBER(5)
);

CREATE TABLE DESTTABLE (
  STR VARCHAR2(50),
  QTY NUMBER(5)
);

DECLARE
BEGIN
   FOR i IN 1..500 LOOP
      INSERT INTO SOURCETABLE VALUES

      (dbms_random.string(‘U’, 10), dbms_random.value(1,1000));
   END LOOP;
   COMMIT;
END;
/

I first reproduced the Outer-Inner FOR loop and see how long it runs.

declare
  CURSOR C1 IS
        select str, qty from sourcetable;
  v_time number;
begin
  v_time := dbms_utility.get_time;
  dbms_output.put_line(‘Two For Loops’);
  FOR i IN C1 LOOP
    FOR j IN 1..i.qty LOOP
      INSERT INTO desttable values(i.str, i.qty);
    END LOOP;
  END LOOP;
  dbms_output.put_line(‘Time used: ‘ || (dbms_utility.get_time – v_time)
    / 100 || ‘ secs’);
  dbms_output.put_line(”);
end;
/

Two For Loops
Time used: 95 secs

PL/SQL procedure successfully completed.

I then tried to use the PL/SQL Bulk Inserts feature – FORALL. I had to first declare an associative array.

declare
  TYPE ARRAY IS TABLE OF sourcetable%ROWTYPE

  INDEX BY PLS_INTEGER;
  l_array ARRAY;
  v_time NUMBER;
  counter NUMBER := 1;
  CURSOR c1 IS
    select str, qty from sourcetable;
begin
  v_time := dbms_utility.get_time;
  dbms_output.put_line(‘FORALL’);
  FOR i IN c1 LOOP
    FOR j IN 1..i.qty LOOP
      l_array(counter) := i;
      counter := counter + 1;
    END LOOP;
  END LOOP;
  FORALL k IN 1..l_array.count
   INSERT INTO desttable values l_array(k);
   dbms_output.put_line(‘Time used: ‘ || (dbms_utility.get_time – v_time)
    / 100 || ‘ secs’);
  dbms_output.put_line(”);
end;
/

FORALL
Time used: 8.13 secs

PL/SQL procedure successfully completed.

The difference is due to the context switching between the PL/SQL and SQL engine. I suspect this solution uses slightly more memory (and uses up more lines of code). But I thought 95 seconds vs. 8 seconds isn’t too bad a tradeoff…

The Official Oracle documentation can be found here.

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm#23723
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/13_elems22.htm#34325

I was driving home around 9pm, and stopped at a traffic light in front of a shopping centre. It was after-hours, and the automated doors were locked. On either side of the automated doors were manual doors.

At that moment, I saw was an elderly couple pushing open the manual doors on the right side to get out. I also saw a younger couple standing in front of the automated doors and found it was locked.

The lady of the younger couple pointed to her partner indicating the right manual door works and they should follow the elderly couple to exit. The partner half-ignoring, walked to the left manual door and pushed it open. The lady then followed.

It was interesting how the younger couple went about the problem – “to find an exit”. I can somewhat relate to how the couple thinks because I think like that guy. The lady saw a working exit. What is more interesting to me was what the guy was thinking at the time – motivated by curiosity or ego – to try the other door.

Curiosity – if the right door works, would the left door work as well? What have we learnt if the curiosity has or has not paid off? Why has the security only left one manual door unlocked or has he left both manual doors opened? What is the cost of making the wrong decision?

Ego – to demonstrate to his partner, that he too can find a working exit. At the same time, risk offending his partner when refusing to listen to her suggestion and being stubborn.

What was obvious to me, was the guy was taking a risk by opting to try the left door. If it doesn’t work, then both himself and his partner would have to walk to the right door to exit.

In a more day-to-day situation, when faced with two alternatives to a problem, and one of them obviously is a working solution, which one would you go for? If you know one alternative works, what are the chances that the other alternative will also work? What would influence you to try the unknown alternative? the pay-off, the risk? how many things goes through your mind before making a decision?

This reminds me of the Monty Hall Problem (obviously, the problem mentioned here is totally different).