Skip navigation

While there are many different graphical user interface tools for running SQL statements, sometimes all you really want and need is a smaller, focused and thus simpler tool for modest tasks – such as opening and running a basic SQL script. For those specific, lesser needs I often utilize the popular freeware editor Notepad++. However it requires manual tweaking in order to have Notepad++ execute SQL statements via Oracle’s SQL*Plus. In this blog I’ll show you how. The goal will be to have Notepad++ look, feel and behave as shown in the Figure 1 below – where the top half of the screen displays the SQL statement, the bottom half of the screen shows the command’s output, and there is a key mapped to execute the SQL statement.

 

zzz1.png

Figure 1: Notepad++ Example

 

Here are the steps:

  1. Launch Notepad++
  2. Main menu -> Plugins -> Plugin Manager -> Show Plugin Manager
  3. Available Tab, Find and check NppExec plugin (see Figure 2 below)
  4. Press Install button to download & install plugin – restarts Notepad++
  5. Open a SQL script
  6. Press F6 key (NppExec’s default keyboard mapping for “Execute Statement”)
  7. Enter the following macro script into the Execute pop-up (see Figure 3 below)

set ORA_USER=bert

set ORA_PASS=bert1234

set ORA_SID= ORCL

npp_save

cmd /c copy /y "$(CURRENT_DIRECTORY)\$(FILE_NAME)" "$(SYS.TEMP)\$(FILE_NAME)" >nul 2>&1

cmd /c echo. >> "$(SYS.TEMP)\$(FILE_NAME)"

cmd /c echo exit >> "$(SYS.TEMP)\$(FILE_NAME)"

sqlplus -l $(ORA_USER)/$(ORA_PASS)@$(ORA_SID) @"$(SYS.TEMP)\$(FILE_NAME)"

 

  1. Change the first three variables for your database, username and password
  2. Press the OK button

 

zzz2.png

Figure 2: Notepad++ Plugin Manager – Enable NppExec

 

zzz3.png

Figure 3: Save your SQL*Plus execute macro

Servers everywhere are being virtualized. Yet many DBA’s are hesitant to embrace virtualized database servers. I’ve been mystified by this anomaly, so I’ve asked those opposed for their rationale. While there are numerous arguments against, basically two pervasive themes surface from amongst all the replies.

 

First and foremost, DBA’s almost universally claim that their databases are “mission critical” and cannot suffer any performance hit that virtualization would necessarily impose. I hypothesize that these people must either consider shared resources as negative or have read that virtualization overhead can be from 5% to 15% - and they cannot suffer that loss.

 

However those very same DBA’s quickly allowed the single most important database performance factor (Disk IO) to become shared well over a decade ago. We all quickly embraced new Storage Area Network (SAN) disk arrays in order to get large pools of storage. Yet very few of those SAN’s were dedicated to a single database, or even a single DBA’s multiple databases. SAN’s were generally shared resources, and often without the DBA fully aware of who was sharing their spindles. We simply asked for “black box” amounts of space that were assigned for our use as LUN’s.

 

Today we’re simply permitting the three remaining key components (CPU, memory and networking) to be shared like our storage. If we so brazenly accepted it for Disk IO back then, how can we now say that the much less important database performance factors cannot be shared? I believe it’s just resistance to change.

 

As for the virtualization overhead, it’s a non-factor. If we were simply going to virtualize the database server and place it back on the same physical server, then sure – there would be a slight performance reduction. However DBA’s generally order excess capacity for growth, thus most servers are idle more than 50% of the time overall. But most virtualization efforts are to replace smaller servers with much larger shared ones. So losing my four CPU and 16GB RAM physical server and then being allocated the same or more resources from a much larger shared server should be a non-issue. As long as there is not over-allocation of resources on the physical virtual servers (i.e. hosts), then the negative performance impact should range from minimal to non-existent. Thus if four quad CPU and 16GB database servers were re-hosted to a virtualized host that had 32 CPU’s and 128GB of memory – the performance could actually be better (or at worst about the same).

 

The second pervasive but veiled theme is one regarding “loss of control”. You’re not going to like this observation nor be happy with me for making it. But in the good old days the DBA was a god. We often had unfettered access to our hardware platform. It was not uncommon to have “root” access. We often performed numerous complex design and management tasks, including hardware platform research and ordering, operating system configuration and tuning, storage design and allocation, capacity monitoring and projections, and so on. Thus the DBA knowledge and responsibilities were Herculean – and we loved it that way.

 

But in a virtualized world, now the DBA simply treats everything as a “black box” that someone else both provides and manages. We cannot venture into the server room anymore and knowingly point to our static resources such as disks. Nor can we really know exactly where our virtual machine is being hosted, because it can move – sometimes dynamically. Plus we have to ask someone else for things we used to do for ourselves. It’s a bit unnerving for those who remember the good old days.

 

Yes – there are some very valid performance issues that must be addressed when you virtualize your database, and those cannot be left to the defaults or chance. But most people seem to object more in the abstract. You’re not going to stop virtualization – so might as well learn to embrace and even like it.

Bert Scalzo

Virtual Paranoia?

Posted by Bert Scalzo Nov 27, 2016

Lately I’ve been seeing what I think might be an interesting yet disturbing new trend: whenever there’s a problem that initially seems unusual and/or unexplainable, it’s not uncommon to quickly question virtualization as a potential source of the issue. I fully understand that virtualization adds a new variable to the basic equation – and represents greater complexity due to more moving parts. But it seems to be somewhat rushed to the head of the list. In some respects it reminds me of the days when many tech support calls started off with how much memory do you have, oh just that much, that’s your problem.

 

Now that’s not to say that virtualization is without some additional concerns that must be added to the mix – some of which can radically affect the results. But as far as true issues that virtualization breaks – I’ve run into just two so far. Some client operating system and database monitoring tools are very sensitive to the real time clock, and any skew between the host and client VM may yield slight variations that might possibly affect some results. And second, some older Windows versions have poorer memory management that seems not to work 100% reliably on VM’s. Shy of these, I’ve yet to encounter any problems that I can point the finger to the virtual machine software (e.g. VMware).

 

So when someone says “I can’t explain problem X and it doesn’t occur on non virtualized machine that’s setup exactly the same”, please make sure only to accept such arguments with a fair and reasonable amount of verification. Let’s not waste any precious resources and cycles chasing such an easy scapegoat. There may be issues where virtualization is legitimately the culprit – just not most of the time. So let’s not promote it from obscurity to the head of the class.

Many DBA’s and developers are now routinely using Oracle Virtual Box virtual machines to simplify database research and even development. I am one of those people. I also use VMware’s Workstation too. Today I’m sharing my Windows FIX_VDI.BAT script for compacting and zipping up my Virtual Box VM’s. For example when I’ve got a VM exactly the way I like it and want to create a version to save, I run this script. While some steps like compacting the VM can be done from the Virtual Box GUI, I just want to do it all at once – with minimal interaction from me. Oracle’s Virtual Box provides some very useful command line capabilities – but most users don’t know they have them. So I wrote and am sharing this script. Let’s assume that you have your VM’s in the C:\VM directory and now want to compact and zip up as a RAR file the LINUX_ORACLE12 VM, then you simply issue the following command:

 

                C:\> fix_vdi.bat  c:\vm linux_oracle12  rar

 

@REM ===========================================

@REM SCRIPT:  FIX_VDI.BAT

@REM AUTHOR:  BERT SCALZO

@REM USAGE:   %0  VM-DIR VM-NAME  {7z rar both}

@REM ===========================================

 

:PARMCOUNT

@set argC=0

@for %%x in (%*) do @Set /A argC+=1

@if %argC% GTR 3 @goto USAGE

@goto PARM1

 

:PARM1

@if "%1"=="" @goto USAGE

@if EXIST %1        @goto PARM2

@goto USAGE

 

:PARM2

@if "%2"=="" @goto USAGE

@if EXIST %1\%2     @goto PARM3

@goto USAGE

 

:PARM3

@if "%3"=="" @goto CONTINUE

@if "%3"=="7z" @goto CONTINUE

@if "%3"=="rar" @goto CONTINUE

@if "%3"=="both" @goto CONTINUE

@goto USAGE

 

:USAGE

@echo.

@echo ====================================================

@echo USAGE:  %0  VM-DIR VM-NAME  {7z rar both}

@echo.

@echo ERROR:  Mistake made in parameter list

@echo ====================================================

@goto RETURN

 

:CONTINUE

@set vm_dir=%1

@set vm_name=%2

@set zipping=%3

@FOR /F %%G IN ('DIR %1\%2\*.vdi /B') DO @SET vm_file=%%G

@if "vm_file"=="" @goto USAGE

 

@echo.

@echo ##################################################

@echo #### %0:

@echo ####     VM Dir:   %vm_dir%

@echo ####     VM Name:  %vm_name%

@echo ####     VM VMDK:  %vm_file%

@echo ###      Zipping:  %zipping%

@echo ##################################################

 

REM @goto RETURN

REM @goto SKIP

"C:\Program Files\Oracle\VirtualBox\VBoxManage" modifyhd --compact %vm_dir%\%vm_name%\%vm_file%

:SKIP

 

@if EXIST %vm_dir%\%vm_name%\Logs @del /f /q /s %vm_dir%\%vm_name%\Logs\*.*

@if EXIST %vm_dir%\%vm_name%\Logs @rmdir /q /s %vm_dir%\%vm_name%\Logs

 

@if EXIST %vm_dir%\%vm_name%\*.* @attrib -a %vm_dir%\%vm_name%\*.*

 

@echo ##################################################

@echo.

 

@SET isodate=%date:~10,4%-%date:~4,2%-%date:~7,2%

 

@if "%zipping%"=="7z"   @goto ZIP7Z

@if "%zipping%"=="rar"  @goto ZIPRAR

@if "%zipping%"=="both" @goto ZIPRAR

@goto RETURN

 

:ZIPRAR

@if EXIST %vm_dir%\%vm_name%-%isodate%.rar @del /f /q %vm_dir%\%vm_name%-%isodate%.rar

@"C:\Program Files\WinRAR\rar"   a -ac -ai -cl -ed -m5 -mt4 -r -s -t           %vm_dir%\%vm_name%-%isodate%.rar %vm_dir%\%vm_name%\

@if "%zipping"=="both" @goto ZIP7Z

@goto RETURN

 

:ZIP7Z

@if EXIST %vm_dir%\%vm_name%-%isodate%.7z  @del /f /q %vm_dir%\%vm_name%-%isodate%.7z

@"C:\Program Files\7-Zip\7z.exe" a -t7z -r -mx=9 -ms=on -mmt=4 -m1=LZMA -slp %vm_dir%\%vm_name%-%isodate%.7z %vm_dir%\%vm_name%\

@goto RETURN

 

:RETURN

Many DBA’s and developers are now routinely using VMware Workstation virtual machines to simplify database research and even development. I am one of those people. I also use Oracle’s Virtual Box too. Today I’m sharing my Windows FIX_VMDK.BAT script for fixing, defragging, compacting and zipping up my VMware VM’s. For example when I’ve got a VM exactly the way I like it and want to create a version to save, I run this script. While some steps like defragging and compacting the VM can be done from the VMware GUI, I just want to do it all at once – with minimal interaction from me. VMware’s Workstation provides some very useful command line capabilities – but most users don’t know they have them. So I wrote and am sharing this script. Let’s assume that you have your VM’s in the C:\VM directory and now want to fix, defrag, compact and zip up as a RAR file the LINUX_ORACLE12 VM, then you simply issue the following command:

 

                C:\> fix_vmdk.bat  c:\vm  linux_oracle12  rar

 

@REM ===========================================

@REM SCRIPT:  FIX_VMDK.BAT

@REM AUTHOR:  BERT SCALZO

@REM USAGE:   %0  VM-DIR VM-NAME  {7z rar both}

@REM ===========================================

 

:PARMCOUNT

@set argC=0

@for %%x in (%*) do @Set /A argC+=1

@if %argC% GTR 3 @goto USAGE

@goto PARM1

 

:PARM1

@if "%1"==""        @goto USAGE

@if EXIST %1        @goto PARM2

@goto USAGE

 

:PARM2

@if "%2"==""        @goto USAGE

@if EXIST %1\%2     @goto PARM3

@goto USAGE

 

:PARM3

@if "%3"==""        @goto CONTINUE

@if "%3"=="7z"      @goto CONTINUE

@if "%3"=="rar"     @goto CONTINUE

@if "%3"=="both"    @goto CONTINUE

@goto USAGE

 

:USAGE

@echo.

@echo ====================================================

@echo USAGE:  %0  VM-DIR VM-NAME  {7z rar both}

@echo.

@echo ERROR:  Mistake made in parameter list

@echo ====================================================

@goto RETURN

 

:CONTINUE

@set vm_dir=%1

@set vm_name=%2

@set zipping=%3

@FOR /F %%G IN ('DIR %1\%2\*.vmdk /B') DO @SET vm_file=%%G

@if "vm_file"==""  @goto USAGE

 

@echo.

@echo ##################################################

@echo #### %0:

@echo ####     VM Dir:   %vm_dir%

@echo ####     VM Name:  %vm_name%

@echo ####     VM VMDK:  %vm_file%

@echo ###      Zipping:  %zipping%

@echo ##################################################

 

REM @goto RETURN

REM @goto SKIP

"C:\Program Files (x86)\VMware\VMware Workstation\vmware-vdiskmanager.exe" -e %vm_dir%\%vm_name%\%vm_file%

"C:\Program Files (x86)\VMware\VMware Workstation\vmware-vdiskmanager.exe" -R %vm_dir%\%vm_name%\%vm_file%

"C:\Program Files (x86)\VMware\VMware Workstation\vmware-vdiskmanager.exe" -d %vm_dir%\%vm_name%\%vm_file%

"C:\Program Files (x86)\VMware\VMware Workstation\vmware-vdiskmanager.exe" -k %vm_dir%\%vm_name%\%vm_file%

:SKIP

 

@if EXIST %vm_dir%\%vm_name%\*.log   @del /f /q /s %vm_dir%\%vm_name%\*.log

@if EXIST %vm_dir%\%vm_name%\*.vmsd  @del /f /q /s %vm_dir%\%vm_name%\*.vmsd

@if EXIST %vm_dir%\%vm_name%\caches  @del /f /q /s %vm_dir%\%vm_name%\caches

@if EXIST %vm_dir%\%vm_name%\caches  @rmdir /q /s  %vm_dir%\%vm_name%\caches

 

@if EXIST %vm_dir%\%vm_name%\*.*     @attrib -a    %vm_dir%\%vm_name%\*.*

 

@echo ##################################################

@echo.

 

@SET isodate=%date:~10,4%-%date:~4,2%-%date:~7,2%

 

@if "%zipping%"=="7z" @goto ZIP7Z

@if "%zipping%"=="rar" @goto ZIPRAR

@if "%zipping%"=="both" @goto ZIPRAR

@goto RETURN

 

:ZIPRAR

@if EXIST %vm_dir%\%vm_name%-%isodate%.rar @del /f /q %vm_dir%\%vm_name%-%isodate%.rar

@"C:\Program Files\WinRAR\rar"   a -ac -ai -cl -ed -m5 -mt4 -r -s -t %vm_dir%\%vm_name%-%isodate%.rar %vm_dir%\%vm_name%\

@if "%zipping"=="both" @goto ZIP7Z

@goto RETURN

 

:ZIP7Z

@if EXIST %vm_dir%\%vm_name%-%isodate%.7z  @del /f /q %vm_dir%\%vm_name%-%isodate%.7z

@"C:\Program Files\7-Zip\7z.exe" a -t7z -r -mx=9 -ms=on -mmt=4 -m1=LZMA -slp   %vm_dir%\%vm_name%-%isodate%.7z  %vm_dir%\%vm_name%\

@goto RETURN

 

:RETURN

Bert Scalzo

The Not So Simple DELETE

Posted by Bert Scalzo Nov 24, 2016

The February issue of SQL Server Magazine (yes – much like Darth Vader I dabble on the dark side) had a very interesting article by Vic Newell titled “4 Ways to purge Non-Partitioned Data”. The basic premise is that in an ideal world all tables would be partitioned such that mass deletions could be accomplished via truncate or drop partition commands. But since we don’t live in an ideal world, what alternatives exist to delete millions or billions of rows from a table quickly and without excessive internalized processing (e.g. redo logs, undo, flashback, etc.). He refers to this as “batched deletes” and gives four examples that I’ll convert to equivalent Oracle syntax (where possible) – plus offer a few of my own specific to Oracle.

NOTE: I am not endorsing or promoting any method as being preferred or superior. Your results will of course vary depending on your situation, and I cannot say because X occurred for me that it will always occur. That would constitute committing the classical logic mistake known as “Modus ponens”, which means to assume that if A implies B, when A therefore B.

 

Baseline (a.k.a. Brute Force) Method

First I’ll establish the baseline scenario for comparisons. So I created and loaded a table as follows:

 

DROP TABLE BIG_TABLE PURGE;

CREATE TABLE BIG_TABLE ( COL1 INTEGER,

                       COL2 VARCHAR2(100),

                       COL3 CHAR(100),

                       COL4 DATE );                                  

BEGIN

  FOR I IN 1 .. 5 LOOP

    FOR J IN 1 .. 1000 LOOP

      INSERT INTO BIG_TABLE

        VALUES ( I, 'THIS IS A TEST', 'THIS IS A TEST', SYSDATE );

    END LOOP;

  END LOOP;

  COMMIT;

  FOR I IN 1 .. 10 LOOP

    INSERT INTO BIG_TABLE SELECT * FROM BIG_TABLE;

    COMMIT;

  END LOOP;

END;

/

CREATE INDEX BIG_TABLE_IDX ON BIG_TABLE ( COL1 );

COLUMN COUNT(*) FORMAT 999,999,999;

SELECT COUNT(*) FROM BIG_TABLE;

 

This table has 5.1 million rows where the first column’s (i.e. COL1’s) values range between one and five (controlled by the “I” loop variable). Therefore when we request the database delete all rows with a first column value equal to three that represents exactly 20% of the entire table or roughly one million rows. Here's the simple delete command code for that:

 

DECLARE

  TEST_NAME  VARCHAR2(10) := 'DELETE-0';

  RUN_TIME   NUMBER := 0;

  ROW_COUNT1 NUMBER := 0;

  ROW_COUNT2 NUMBER := 0;

  T1 DATE;

  T2 DATE;

BEGIN

  SELECT COUNT(*) INTO ROW_COUNT1 FROM BIG_TABLE;

  SELECT SYSDATE INTO T1 FROM DUAL;

  DELETE FROM BIG_TABLE WHERE COL1 = 3; ----<<<< SIMPLE DELETE

  COMMIT;

  SELECT SYSDATE INTO T2 FROM DUAL;

  SELECT (24 * 60 * 60) * (T2 - T1) INTO RUN_TIME FROM DUAL;

  SELECT COUNT(*) INTO ROW_COUNT2 FROM BIG_TABLE;

  INSERT INTO DELETE_TEST VALUES ( TEST_NAME, RUN_TIME, ROW_COUNT1, ROW_COUNT2 );

  COMMIT;

END;

/

Method #1 – Simple Batched Delete

The first method proposed by the original article is to repetitively delete fixed size batches of records that meet the filter criteria so as to keep the internal delete command’s operation reasonable (i.e. not stress UNDO space). Of course we’ve now added a loop, multiple commits, repetitive deletes, and the additional SQL overhead of an additional filter (i.e. ROWNUM <= BTACH_SIZE).

VARIABLE RUN_TIME NUMBER;

DECLARE

  T1 DATE;

  T2 DATE;

  BATCH_SIZE NUMBER := 10000;

BEGIN

  SELECT SYSDATE INTO T1 FROM DUAL;

  LOOP

    DELETE FROM BIG_TABLE WHERE COL1 = 3    ----<<<< BATCH DELETE

AND ROWNUM <= BATCH_SIZE;

    EXIT WHEN (SQL%ROWCOUNT=0);

    COMMIT;

  END LOOP;

  SELECT SYSDATE INTO T2 FROM DUAL;

  SELECT (24 * 60 * 60) * (T2 - T1) INTO :RUN_TIME FROM DUAL;

END;

/

Method #2 – Sliding Window Delete

The second method proposed by the original article simply seeks to improve upon the first method by scanning the big table just once recording all the addresses of the rows to delete and then performing essentially the same repetitive batched deletes using a sliding window of those addresses. Personally I feel that this method is fundamentally flawed since it relies on ROW ID’s recorded into a table and then used for the iterative deletes. The problem is that the rows’ addresses could change in between and/or during processing yielding “record not found” errors. Hence the correct method would be to place an exclusive lock on the original big table such that the record addresses remain static. But many people abhor using the LOCK TABLE command, so I left it out.

 

DROP TABLE ROWS_TO_DELETE PURGE;

CREATE TABLE ROWS_TO_DELETE ( XXX ROWID );

DECLARE

  TEST_NAME  VARCHAR2(10) := 'DELETE-2';

  RUN_TIME   NUMBER := 0;

  ROW_COUNT1 NUMBER := 0;

  ROW_COUNT2 NUMBER := 0;

  T1 DATE;

  T2 DATE;

  BATCH_SIZE NUMBER := 10000;

  LOBOUND    NUMBER := 1;

  HIBOUND    NUMBER := BATCH_SIZE;

BEGIN

  SELECT COUNT(*) INTO ROW_COUNT1 FROM BIG_TABLE;

  SELECT SYSDATE INTO T1 FROM DUAL;

  INSERT INTO ROWS_TO_DELETE

    SELECT ROWID FROM BIG_TABLE WHERE COL1 = 3;

  COMMIT;

  LOOP

    DELETE FROM BIG_TABLE WHERE ROWID IN ( ----<<<< WINDOWED DELETE

      SELECT  XXX

      FROM    (

        SELECT  XXX, ROWNUM AS RN

        FROM    ROWS_TO_DELETE

        )

      WHERE   RN BETWEEN LOBOUND and HIBOUND

    );

    EXIT WHEN (SQL%ROWCOUNT=0);

    COMMIT;

    LOBOUND := HIBOUND + 1;

    HIBOUND := HIBOUND + BATCH_SIZE;

  END LOOP;

  SELECT SYSDATE INTO T2 FROM DUAL;

  SELECT (24 * 60 * 60) * (T2 - T1) INTO RUN_TIME FROM DUAL;

  SELECT COUNT(*) INTO ROW_COUNT2 FROM BIG_TABLE;

  INSERT INTO DELETE_TEST VALUES ( TEST_NAME, RUN_TIME, ROW_COUNT1, ROW_COUNT2 );

  COMMIT;

END;

/

Method #3 – Minimal Impact DELETE

The third method proposed by the original article simply seeks to improve upon the second method by only doing a record at a time so as to lessen the impact of the operation on the system. That way this simple operation does not skew normal processing times by attempting something too large all at once. Of course that should mean the longest total run time with the least spike in demand for completing the delete operation, the principle idea being to get the job done without impacting anything and/or anyone else. However note once again the use of ROW ID. And now that the run time will be longer the problem of changing row addresses is only worsened (i.e. more likely). So once again not one of my favorites.

 

DROP TABLE ROWS_TO_DELETE PURGE;

CREATE TABLE ROWS_TO_DELETE ( XXX ROWID );

DECLARE

  TEST_NAME  VARCHAR2(10) := 'DELETE-3';

  RUN_TIME   NUMBER := 0;

  ROW_COUNT1 NUMBER := 0;

  ROW_COUNT2 NUMBER := 0;

  T1 DATE;

  T2 DATE;

BEGIN

  SELECT COUNT(*) INTO ROW_COUNT1 FROM BIG_TABLE;

  SELECT SYSDATE INTO T1 FROM DUAL;

  INSERT INTO ROWS_TO_DELETE

    SELECT ROWID FROM BIG_TABLE WHERE COL1 = 3;

  COMMIT;

  FOR C1 IN (SELECT XXX FROM ROWS_TO_DELETE) LOOP

    DELETE FROM BIG_TABLE WHERE ROWID = C1.XXX; ----<<<< MINIMIZED DELETE

    COMMIT; 

  END LOOP;

  SELECT SYSDATE INTO T2 FROM DUAL;

  SELECT (24 * 60 * 60) * (T2 - T1) INTO RUN_TIME FROM DUAL;

  SELECT COUNT(*) INTO ROW_COUNT2 FROM BIG_TABLE;

  INSERT INTO DELETE_TEST VALUES ( TEST_NAME, RUN_TIME, ROW_COUNT1, ROW_COUNT2 );

  COMMIT;

END;

/

Method #4 – Bulked DELETE

The fourth method proposed by the original article simply seeks to leverage database specific features to streamline the internal processing – which in the case of the original article using SQL Server applied the INNER JOIN on a DELETE command. Since that’s not available for Oracle, I instead used the FORALL bulk capability. Note that the record list table variable has to fit in the database server’s memory (since PL/SQL executes on the server) so it has some obvious limitations. I guess I could have applied the prior batched logic so as to keep the record list variable to a known and reasonable size. However with cheap memory these days it just did not seem worthwhile. Your situation will of course vary. Finally note that this method also suffers from the ROW ID dilemma – but since it’s as close to an atomic operation as we’re likely to get it’s possible that the LOCK TABLE could be skipped in this solution. You decide.

 

DECLARE

  TEST_NAME  VARCHAR2(10) := 'DELETE-4';

  RUN_TIME   NUMBER := 0;

  ROW_COUNT1 NUMBER := 0;

  ROW_COUNT2 NUMBER := 0;

  T1 DATE;

  T2 DATE;

  TYPE DELLIST IS TABLE OF ROWID;

  RECLIST DELLIST;

BEGIN

  SELECT COUNT(*) INTO ROW_COUNT1 FROM BIG_TABLE;

  SELECT SYSDATE INTO T1 FROM DUAL;

  SELECT ROWID BULK COLLECT INTO RECLIST

    FROM BIG_TABLE WHERE COL1 = 3;

  FORALL I IN RECLIST.FIRST .. RECLIST.LAST

    DELETE FROM BIG_TABLE WHERE ROWID = RECLIST(I); ----<<<< BULKED DELETE

  COMMIT;

  SELECT SYSDATE INTO T2 FROM DUAL;

  SELECT (24 * 60 * 60) * (T2 - T1) INTO RUN_TIME FROM DUAL;

  SELECT COUNT(*) INTO ROW_COUNT2 FROM BIG_TABLE;

  INSERT INTO DELETE_TEST VALUES ( TEST_NAME, RUN_TIME, ROW_COUNT1, ROW_COUNT2 );

  COMMIT;

END;

/

Method #5 – Parallel DELETE

The fifth method is my first additional technique not mentioned per se in the original article – using the parallel DML capabilities of Oracle via an optimizer hint. Of course many people prefer not to hard code optimizer hints within application code so maybe altering the table would be preferable. So once again you decide. However note that choosing the parallel degree is not just specifying your number of CPU’s. You’ll need to consider concurrent workloads, disk subsystem IO throughput, how many slave processes will be created (i.e. GROUP BY and ORDER BY increase the count), and whether an index or full table scan will result in better results. All that said this method will probably be the best universal choice for many people.

 

ALTER SESSION ENABLE PARALLEL DML;

DECLARE

  TEST_NAME  VARCHAR2(10) := 'DELETE-5';

  RUN_TIME   NUMBER := 0;

  ROW_COUNT1 NUMBER := 0;

  ROW_COUNT2 NUMBER := 0;

  T1 DATE;

  T2 DATE;

BEGIN

  SELECT COUNT(*) INTO ROW_COUNT1 FROM BIG_TABLE;

  SELECT SYSDATE INTO T1 FROM DUAL;

  DELETE /*+ FULL(t) PARALLEL(t,8) */ FROM BIG_TABLE t ----<<<< PARALLEL DELETE

    WHERE COL1 = 3;

  COMMIT;

  SELECT SYSDATE INTO T2 FROM DUAL;

  SELECT (24 * 60 * 60) * (T2 - T1) INTO RUN_TIME FROM DUAL;

  SELECT COUNT(*) INTO ROW_COUNT2 FROM BIG_TABLE;

  INSERT INTO DELETE_TEST VALUES ( TEST_NAME, RUN_TIME, ROW_COUNT1, ROW_COUNT2 );

  COMMIT;

END;

/

Method #6 – DBMS_PARALLEL_EXECUTE

The sixth and final method is my second and last additional technique not mentioned per se in the original article – using the Oracle supplied package DBMS_PARALLEL_EXECUTE. While this technique may seem overly complex, under the right circumstances the results could well warrant learning and using this new PL/SQL package. Of course my example below should have EXCEPTION handling logic – but I wanted to keep the example simple. I also suspect that the ALTER SESSION to enable parallel DML may not be strictly necessary.

 

ALTER SESSION ENABLE PARALLEL DML;

DECLARE

  TEST_NAME  VARCHAR2(10) := 'DELETE-6';

  RUN_TIME   NUMBER := 0;

  ROW_COUNT1 NUMBER := 0;

  ROW_COUNT2 NUMBER := 0;

  T1 DATE;

  T2 DATE;

  l_sql_stmt VARCHAR2(1000);

  l_try NUMBER;

  l_status NUMBER;

BEGIN

  SELECT COUNT(*) INTO ROW_COUNT1 FROM BIG_TABLE;

  SELECT SYSDATE INTO T1 FROM DUAL;

 

  -- Create the TASK

  DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask');

 

  -- Chunk the table by SELECT using ROWID

  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', 'BERT', 'BIG_TABLE', true, 10000);

 

  -- Execute the DML in parallel

  l_sql_stmt := 'DELETE /*+ ROWID (dda) */ FROM BIG_TABLE

      WHERE col1 = 3 and rowid BETWEEN :start_id AND :end_id';

  DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', l_sql_stmt, DBMS_SQL.NATIVE,

                                 parallel_level => 8);

 

  -- Done with processing; drop the task

  DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask');

 

  COMMIT;

  SELECT SYSDATE INTO T2 FROM DUAL;

  SELECT (24 * 60 * 60) * (T2 - T1) INTO RUN_TIME FROM DUAL;

  SELECT COUNT(*) INTO ROW_COUNT2 FROM BIG_TABLE;

  INSERT INTO DELETE_TEST VALUES ( TEST_NAME, RUN_TIME, ROW_COUNT1, ROW_COUNT2 );

  COMMIT;

END;

/

 

The Results

OK, we now have a total of seven ways to write a very simple delete command. So which solution is the best (at least on my tests systems and for my test scenario)? Remember don’t extrapolate my results to suggest any universal conclusions. You’ll need to experiment to be sure on a case by case basis. Here are my results using two different test systems.

 

 

Run Time in SECONDS

 

Dual Core CPU, 8GB RAM,

Solid State Disk (SSD)

Quad Core CPU, 16GB RAM,

8 Disk RAID-0 with 512 MB Cache

Original Simple DELETE

82

30

Simple Batched DELETE

100

62

Sliding Window DELETE

137

87

Minimal Impact DELETE

267

193

“FORALL” Bulk DELETE

119

74

Parallel DML DELETE

61

21

DBMS_PARALLEL_EXECUTE

159

99

 

So for my server hardware and test scenarios it looks like the simple delete command with and without the optimizer hint are my best choices. Would you have guessed that? Of course in my test case I had no other concurrent applications or code running, so I did not have to contend with sharing UNDO, REDO, TEMP space and other resources. I’m sure that if I had then the results could well have been different – with a different set of winners. So test these approaches to be sure, because often what sounds good in theory may not always be good in reality. You generally need empirical results from a similar situation in order to choose the real winner for any SQL or PL/SQL code that you write.

Bert Scalzo

The Physics of Data

Posted by Bert Scalzo Nov 24, 2016

Sometimes information systems professionals can draw some very useful analogies from the hard sciences. For example physics offers us one very useful concept – the states of matter. Figure 1 shows an excellent diagram that I copied from Wikipedia explaining the four states of matter.

 

yyyyy.png

 

Looking at these four states, I’m going to suggest that from a higher level there are really just two fundamental states – bound and liberated. Bound connotes that molecular cohesion binds the matter into a semi solid to solid state, while liberated signifies that the matter overcomes such molecular cohesion to more freely “bounce around” and thus be anti solid.

 

It’s my contention that data is matter and that it possesses just these two fundamental states: inert (i.e. at rest) and in motion. This concept can be very useful for understanding the oft typical rifts between DBA’s and database developers.

 

DBA’s see their role as related more to the data at rest state. For example DBA’s define the structural definitions to both effectively and efficiently contain the data, where by definition contain means data at rest. DBA’s also perform administrative tasks such as storage allocation and planning, which again are usually measured as “point in time” (again data that is not in motion). Plus DBA’s handle backup and recovery, which also is performed for a “point in time”. Thus they view the world fundamentally from the perspective of entropy – which by definition is the tendency for all matter and energy in the universe to evolve toward a state of inert uniformity.

 

Whereas database developers are tasked with putting the data into motion via programs and scripts in order to accomplish business functions. Thus they are the stimulus or catalyst for the database leaving the state of data at rest. In physics terms developers are the “force” that acts upon the data resulting in motion. So they are the fundamental cause for velocity, acceleration and displacement of the data. All of which force a temporary exit from the natural state of entropy.

 

  So DBA’s and database developers naturally start from a position of difference. Yes there might be many other reasons and personality types involved, but it’s key to note this basic difference. Because when we keep in mind that divergent initial viewpoint, it helps us to “walk a little in the other guy’s shoes” so that we can appreciate their concerns and thus communicate a little better. Well in theory at least.

Bert Scalzo

Data is an Asset

Posted by Bert Scalzo Nov 24, 2016

Sometimes information systems professionals can get “caught up” in our own worlds. For example, project managers and their developers might focus on latest and greatest application development methodologies such as Extreme Programming (XP) or Agile Development. Likewise enterprise architects might be adopting some new or updated modeling conventions. While database administrators (DBA’s) are often engaged and engrossed in the latest database versions and their new features. Now also include the new versions of the software we use to build systems, hardware’s exponential growth, new programming languages and everything else in a state of flux in our worlds – it’s easy to see why we’re often so overwhelmed that we forget some very basic facts.

 

  1. The “business” is our customer. We build computerized systems to support the business. Information systems people and costs are by their very definition what the business, executives and MBA’s consider “overhead”. That means the business could theoretically run without these people and costs. It might be less efficient, but it could work. Thus we need to always remember that and maintain a deep sense of humility.
  2. The “data” is a business asset. The business people should define it, own it and be the stewards for it. We need to learn from them enough business to build systems to support their needs (i.e. the business requirements). Thus we need to learn the meta-data from them – which is the data about the data. Then we need to provide two key facilities for handling their data:
    1. Data storage containers, whether records in files or tables in databases, to house the business data or assets at rest. And when using databases, we may be able to add certain “quality checks” (e.g. constraints and relationships) to help keep that data pristine (e.g. effective). But databases are essentially just containers for the data at rest – and nothing more.
    2. Data processing mechanisms (i.e. application code when executing) to affect the business data – which equates to data or assets in motion. The data assets should remain true to the business requirements during any such movement. As with the database, the application might include mechanisms or facilities for keeping data pristine – such as edit checks and data value transformations. But applications are simply computerized mechanisms for data in motion – and nothing more.

     

    So why am I up on a “soap box” preaching this information? Because I’m seeing more and more systems that fail to deliver or miss business expectations. In fact I’ve been an expert witness for the plaintiff in several cases for seven figure application development cost recoveries. I highly recommend reading Yourdon’s “Decline & Fall of the American Programmer for more information along these lines. It’s an older book, but the concepts generally remain the same – just substitute today’s hot terms for some of yesteryears’.

     

    Information systems people are very intelligent, highly motivated and well compensated. We just need to maintain some humility and focus. If web hosting providers can market 99.9% uptime, the software we build should be at least 99.9% correct when handling the companies most valuable asset – the business data, whether in motion or at rest.

    There are always those DBA’s who refuse to use GUI tools because they prefer scripts. And that’s OK – it’s a personal preference. But recently I’ve seen some stuff posted on the web and sent in email blasts suggesting that people who do use DBA GUI tools like DBArtisan, SQL Developer and Oracle Enterprise Manager (OEM) are somehow suspect as to their skill levels. And yes, I am sure there are some people who might not know Oracle very well but get along just well enough because of such tools. I’ve seen these people sometimes referred to as “posers”. I don’t fully agree. If the person using such DBA tools understands the foundational concepts and simply utilizes them to augment their productivity, then who cares if they can recite perfect syntax from memory. Managers hire people to get work done. Sometimes the how it’s done is not as important as just getting it done (and done right of course). So are GUI tools like DBArtisan, SQL Developer and Oracle Enterprise Manager (OEM) a lot like like loaded shotguns? Yes, but in the hands of competent and knowledgeable people shotguns put food on the table.

    Oracle 12.1.0.2 debuted recently and I decided to benchmark the IN MEMORY COLUMN STORE feature. There’s been a lot of press and excitement about this feature, so I wanted to add something of value to that dialog. So this blog covers what I learned while running industry standard TPC-H data warehousing benchmark utilizing the IN MEMORY COLUMN STORE feature (plus some other 11g and 12c features).

     

    So what exactly is the feature “IN MEMORY COLUMN STORE”? I think the August 2014 white paper from Oracle titled Oracle Database In-Memory is a great resource and a “must read”. I’ll try to summarize the concept with the following basic diagram. Note that the SGA memory now has a second data cache area called the “IN MEMORY” cache which is controlled by the “INMEMORY_SIZE” configuration parameter.

     

    inmemory.jpg

     

     

    So now Oracle offers to handle data in both tabular formats – row vs. column oriented. In fact Oracle automagically provides what they call the “dual format architecture”. Basically any object marked as being “in memory enabled” will simultaneously exist in both the DB Buffer Cache and the IN MEMORY Cache shown above. The objects located in the IN MEMORY Cache are compressed, so the overhead is minimal. Plus column oriented data tends to consume far less space as well. So Oracle claims to expect only 20% more memory required. Moreover the database maintains full transactional consistency across the two tabular formats, plus the optimizer will intelligently direct queries to the best tabular format based upon the nature of the queries. The only downside is that dual architecture objects must at some point get loaded into the IN MEMORY Cache, which can both consume significant CPU and take time.

     

    In order to enable the IN MEMORY COLUMN STORE feature I had to do the following:

     

    • Alter System
      • Before
        • § SGA_TARGET = 4G
        • § DB_FLASH_CACHE_SIZE = 0
      • After
        • § SGA_TARGET = 20G
        • § DB_FLASH_CACHE_SIZE = 16G
    • Alter each TPC-H table to enable in-memory:
      • Try #1 - INMEMORY PRIORITY CRITICAL
      • Try #2 - INMEMORY MEMCOMPRESS FOR QUERY HIGH PRIORITY CRITICAL
    • Restart the Database Instance
    • Wait for in memory objects to load (see queries that follow)

     

     

    Strictly speaking you don’t have to bounce the database to enable and use the IN MEMORY COLUMN STORE feature. However note that I marked each TPC-H table as “PRIORITY CRITICAL” in order to force those objects to load into memory when the database starts. My goal was to have those TPC-H objects 100% in memory before running the TPC-H queries so as to score the best results. Of course I first run the TPC-H benchmark without using the IN MEMORY COLUMN STORE feature to establish a comparative baseline. Note that I used HammerDB.

     

    Here are my run time results against a 10GB TPC-H scale factor – note that I also tested an older 11g feature (database flash cache) and another new 12c feature (big table caching):

     

    Test #

    Feature

    Debut

    Configuration Parameters

    Run Time (Mins:Secs)

    Compress

    Factor

    1

     

    SGA_TARGET = 4G

    *** Nothing Else – Baseline ***

    45:52

     

    2

    11g

    SGA_TARGET = 4G

    DB_FLASH_CACHE_SIZE = 16G

    DB_FLASH_CACHE_FILE = '/flash/flash01.dbf'

    23:19

     

    3

     

    SGA_TARGET = 20G

    *** Nothing Else – All memory buffer cache ***

    19:50

     

    4

    12c

    SGA_TARGET = 20G

    DB_BIG_TABLE_CACHE_PERCENT_TARGET = 80

    PARALLEL_DEGREE_POLICY=auto

    ORA-600

     

    5

    12c

    SGA_TARGET = 20G

    INMEMORY_SIZE = 16G

    Try #1 - INMEMORY (default)

    05:01

    1. 3.63X

    6

    12c

    SGA_TARGET = 20G

    INMEMORY_SIZE = 16G

    Try #2 - INMEMORY MEMCOMPRESS FOR QUERY HIGH

    04:56

    1. 4.68X

     

    Test Case #1 allocates just 4G of SGA. The reason for choosing this size was simple – my virtual machine limit for memory is 24G, so leaving 4G for the OS – the most memory I can allocate to the Oracle SGA is 20G. My later “IN MEMORY” test cases will set INMEMORY_SIZE = 16G, thus:  20G “Max possible SGA” minus 16G “IN MEMORY” leaves a remaining SGA of just 4G which is now the baseline size.

     

    Test case #2 then extends the baseline SGA from 4G to 20G (same total SGA in in the later “IN MEMORY” test cases) using the 11gR2 feature known as “Database Smart Flash Cache”. I therefore allocated 16G of flash which should have been sufficient to house all my tables and indexes, or very close to it. I had read Guy Harrison’s blog on database flash cache, and was hoping to test as he says a decent commercial SSD flash drive (i.e. OCZ Vertex 4).

     

    Test case #3 then extends the baseline SGA from 4G to 20G (same total SGA in in the later “IN MEMORY” test cases) using memory (i.e. DRAM) instead of SSD. I did so for two reasons. First I wanted to show the performance difference DRAM and SSD. Second I wanted to be able to compare old style row-oriented vs. new column-oriented with the same size SGA (i.e. 20G).

     

    Test Case #4 then sought to improve upon Test Case #3 by using the new 12.1.0.2.0 feature known as “Automatic Big Table Caching” (which also requires setting parallel degree policy = auto). However as the popular saying goes “the best-laid plans of mice and men often go awry”. Unfortunately using this new feature yielded ORA-600 errors that were not as yet documented on Oracle support. So I had to bail on testing it. That does not mean the feature doesn't work. It just means that I did not get it to work for my setup and scenarios. It could very well be user error on my part.

     

    Finally Test Cases #5 and #6 test utilizing the IN MEMORY COLUMN STORE feature, just with different compression options (i.e. default vs. MEMCOMPRESS FOR QUERY HIGH). Remember as I said above, I set all my table priorities to critical so that they load into memory on database startup – I just had to wait until that process completed before running the TPC-H benchmark. Note well that I did not have to do anything special for testing “IN MEMORY”. I did not drop any indexes nor collect statistics differently. Basically the dual architecture automagically handles when to use row-oriented vs. column-oriented data – so there’s nothing to drop or additional to do. My IN-MEMORY compression levels were from 3.63X to 4.68X (the calculation scripts follow).

     

    Now let’s review the results. As with any ad-hoc benchmarks, do not take these results as universally indicative of anything!

     

    • Test Case #1 è Test Case #1
      • Grow SGA from 4G to 20G via SSD
      • 49% reduction in run time vs. baseline
    • Test Case #2 è Test Case #3
      • Grow SGA from 4G to 20G via DRAM
      • 57% reduction in run time vs. baseline
      • 15% reduction in run time vs. SSD
    • Test Case #3 è Test Case #5
      • IN MEMORY = 16G default compression
      • 89% reduction in run time vs. baseline
      • 78% reduction in run time vs. SSD
      • 75% reduction in run time vs. DRAM
    • Test Case #3 è Test Case #6
      • IN MEMORY = 16G MEMCOMPRESS FOR QUERY HIGH
      • 89% reduction in run time vs. baseline
      • 79% reduction in run time vs. SSD
      • 75% reduction in run time vs. DRAM

     

    I think WOW sums it up best. The new IN MEMORY COLUMN STORE feature is easy to use, simply works as advertised – and in my case (remember your mileage will vary), I achieved nearly 5X compression and between 75% and 90% run time improvements depending on which comparison is made. In my books that’s a home run. Oracle has clearly done their homework in delivering this great new feature.

     

    Finally here are some scripts I used to monitor what objects were in memory enabled, when my objects were successfully loaded in memory, and how well they compressed in memory. We’ll take a quick look at each one.

     

    First, we need to know which tables for our test schema (BMF1) have been “in memory” enabled – and with which options. Remember that I tried two different scenarios with different compression settings, so this is the output for the first (default) scenario.

     

    -- im_tabs.sql

    set linesize 256

    set pagesize 999

    set verify off

    col OBJECT format a30

    SELECT owner||'.'||table_name OBJECT,

    inmemory INMEMORY,

    inmemory_priority PRIORITY,

    inmemory_distribute DISTRIBUTE,

    inmemory_compression COMPRESSION,

    inmemory_duplicate DUPLICATE

    FROM all_tables

    where owner like upper('%&1%')

    ORDER BY inmemory, owner||'.'||table_name;

     

    SQL> @im_tabs BMF1

     

    OBJECT INMEMORY PRIORITY DISTRIBUTE COMPRESSION       DUPLICATE

    ------------------------------ -------- -------- --------------- ----------------- ------------

    BMF1.H_CUSTOMER ENABLED  CRITICAL AUTO            FOR QUERY LOW     NO DUPLICATE

    BMF1.H_LINEITEM ENABLED  CRITICAL AUTO            FOR QUERY LOW     NO DUPLICATE

    BMF1.H_NATION ENABLED  CRITICAL AUTO            FOR QUERY LOW     NO DUPLICATE

    BMF1.H_ORDER ENABLED  CRITICAL AUTO            FOR QUERY LOW     NO DUPLICATE

    BMF1.H_PART ENABLED  CRITICAL AUTO            FOR QUERY LOW     NO DUPLICATE

    BMF1.H_PARTSUPP ENABLED  CRITICAL AUTO            FOR QUERY LOW     NO DUPLICATE

    BMF1.H_REGION ENABLED  CRITICAL AUTO            FOR QUERY LOW     NO DUPLICATE

    BMF1.H_SUPPLIER ENABLED  CRITICAL AUTO            FOR QUERY LOW     NO DUPLICATE

     

    Second, we need to know when our “in memory” enabled tables have been successfully loaded. In my tests I waited until all such enabled tables were COMPLETED so as to best or more fully leverage the in memory feature.

     

    -- im_segs.sql

    set linesize 256

    set pagesize 999

    set verify off

    col owner format a20

    col segment_name format a30

    select owner, segment_name, populate_status

    from v$im_segments;

     

    SQL> @im_segs

     

    OWNER                SEGMENT_NAME                   POP STATU

    -------------------- ------------------------------ ---------

    BMF1                 H_LINEITEM                     STARTED

    BMF1                 H_SUPPLIER                     COMPLETED

    BMF1                 H_PARTSUPP                     COMPLETED

    BMF1                 H_ORDER                        COMPLETED

    BMF1                 H_PART                         COMPLETED

    BMF1                 H_CUSTOMER                     COMPLETED

     

    Third, we need to know how well our “in memory” enabled tables compressed. This confirms the Oracle statement that in memory does not add much overhead (20%) because my tables compressed to reduce by almost 5X.

     

    -- im_pct.sql

    set linesize 256

    set pagesize 999

    set verify off

    select segment_name,ROUND(SUM(BYTES)/1024/1024/1024,2) "ORIG GB",

    ROUND(SUM(INMEMORY_SIZE)/1024/1024/1024,2) "IN-MEM GB",

    ROUND(SUM(BYTES-BYTES_NOT_POPULATED)*100/SUM(BYTES),2) "% IN_MEM",

    ROUND(SUM(BYTES-BYTES_NOT_POPULATED)/SUM(INMEMORY_SIZE),2) "COMP RATIO"

    from V$IM_SEGMENTS

    group by owner,segment_name

    order by SUM(bytes) desc;

     

    SQL> @im_pct -- Try #1 - INMEMORY (default)

     

    SEGMENT_NAME ORIG GB  IN-MEM GB   % IN_MEM COMP RATIO

    ------------------------------ ---------- ---------- ---------- ----------

    H_LINEITEM                           8.42       2.32        100 3.63

    H_ORDER                              1.75        .89        100       1.97

    H_PARTSUPP                             .6        .35        100       1.71

    H_PART .29        .13        100       2.29

    H_CUSTOMER                            .22        .19        100       1.19

    H_SUPPLIER                            .01        .02        100        .91

     

     

    SQL> @im_pct -- Try #2 - INMEMORY MEMCOMPRESS FOR QUERY HIGH

     

    SEGMENT_NAME ORIG GB  IN-MEM GB   % IN_MEM COMP RATIO

    ------------------------------ ---------- ---------- ---------- ----------

    H_LINEITEM                           8.42       1.77 98.17       4.68

    H_ORDER 1.75        .63        100       2.78

    H_PARTSUPP                             .6        .32        100       1.86

    H_PART                                .29        .09        100       3.16

    H_CUSTOMER                            .22        .15        100       1.52

    H_SUPPLIER                            .01        .01        100       1.31

    Microsoft has just released SQL Server 2016 Service Pack 1 (SP1). The release has created quite a stir because Microsoft has made a major licensing changes – most notably every edition of SQL Server now gets every feature. Quoting PC Week, the only time users will have to pay to upgrade to higher edition is for greater hardware limits such as maximum CPU socket/core counts and maximum RAM. All the latest and greatest, novel and powerful database features such as partitioning, in-memory, and all others are now free even the lowly express edition of the database. This will allow typical users to start at the lowest edition based upon hardware sizing, and then allow them to upgrade easily without having to worry about application impacts. Furthermore it will allow Independent Software Vendors (ISV's) to use the free express edition while still getting the benefit of all the advanced features for their product's repositories which users can then upgrade as needed. What effect will this move have on the database world? Can other database vendors maintain their existing pricing structure or will changes be required? Add to this latest announcement the prior announcement where Microsoft is already previewing its SQL Server port to Linux, and thus we surely have some interesting times ahead in 2017.

    In my last blog titled “Varying Database Benchmark Results” I tried to explain why people can often get varied performance results when running a specific database benchmark multiple times. I attempted to explain the possible reasons from a purely mechanics viewpoint. In other words it’s quite often the user background, experience and approach that can play a very significant role. I also stressed that running benchmarks that don’t push the hardware limits can also result in varied or non-repeatable results. But one of the most common questions people have historically asked in response to this explanation is why does expensive commercial database benchmarking tool X yield performance numbers ten times slower than freeware tool Y when running the very same benchmark test on the exactly the same sized data?

     

    Now I don’t want to name these referenced database benchmarking tools because I routinely use both (the commercial and the freeware). Both tools have their pros and cons. I don’t want to appear to either endorse or denounce any database benchmarking tool. But I am going to explain why it is possible to see ten times worse performance between those tools. Unsurprisingly the primary reasons point back to my first blog, specifically the database benchmark specs. I’ll reference the TPC-C benchmark in my example.

     

    The TPC-C benchmark spec dictates certain mandatory test characteristics, plus also offers those which allow for some user customization. The tables, columns, their data types, primary keys, foreign keys and minimum indexes are generally mandated. Moreover certain advanced database features or constructs are not permitted. Examples include using index organized tables or creating indexes that contain all the columns accessed by a specific SQL command (thereby eliminating the need to access the table). There are more than a few such limitations. But there are a few permissible customizations. Examples include horizontal partitioning, vertical table partitioning, data clustering and index clustering.

     

    So let’s now examine how database benchmarking tools can vary in their implementation of the spec. Of course this will require that we look at the generated DDL to validate its legitimacy. This comparison will provide the context for an apples to apples comparison, thus helping to explain performance variances.

     

    One database benchmarking tool creates all its tables with none of the required primary or foreign keys. It creates only unique and non-unique indexes. Thus it does not support the spec’s integrity rules. This is important because the database does not have to perform referential integrity checks, thereby gaining a speed advantage.

     

    One database benchmarking tool creates two of its tables as indexed organized tables – including the largest and most accessed table. This violates the spec’s general guidelines regarding what is known as benchmark special implementations – which result in highly specialized performance gains.

     

    One database benchmarking tool creates an index with extra columns to enhance one of the most recurring join conditions. This too violates the spec’s general guidelines regarding what is known as benchmark special implementations – which again result in highly specialized performance gains.

     

    Finally one database benchmarking tool runs all its transactions with a default zero wait or think time. This violates the spec’s wait time and response time requirements. Thus this database benchmarking tool submits all of its transactions as fast as possible while the other forcibly spaces out or delays all of its transactions. Clearly this will result in a highly skewed result all by itself.

     

    With such variations from the benchmark spec is it any wonder that one tool might appear to yield results ten times faster? So we really need to know both the benchmark spec and any specific tool’s implementation of that spec. Otherwise running the very same benchmark on the exactly the same sized data may well not yield reliable or even comparable results. As the song goes “You better look before you leap, still waters run deep”.

    I’ve been asked on numerous occasions why do peoples' database benchmark results often vary. In other words why one observed transaction per second (TPS) value today and sometimes 20% or more off tomorrow. In short, why aren’t database benchmark results reliably repeatable?

     

    Often people try to blame the benchmarking software. That is the logical choice since that software runs the benchmark and scores the result. But I’ve seen several database benchmarking tools exhibit this odd behavior. I know that commercial software gets rigorous QA testing, and open source or freeware tools have very active communities reporting issues. So I’m hesitant to throw any database benchmarking tools under the bus. The answer must lie elsewhere.

     

    Another source of potential issues are the database benchmarks themselves. Maybe (and I’m totally just conjecturing here) the specs contain items open to interpretation or misinterpretation by those coding database benchmarking tools. In fact, it might even be possible that those database benchmark specs possess some design flaws. It’s possible. But most database benchmarking tools have been around for years, some even well over a decade. My belief is that QA and communities would have reported real problems or bugs long ago. So again the real answer must lie elsewhere.

     

    However there’s yet another source of potential issues we could indirectly blame on the database benchmark specs – very few people actually read or fully comprehend them. TPS is such a simple concept that any computer literate person should be able to run a database benchmarking tool. Wrong! Most of us know how to drive an automobile, but how many of us would get behind the wheel of a formula one race car and expect good things to happen? In a dozen or so years of helping people do database benchmarking, I can honestly say that maybe 25% of the people had read and understood the benchmark spec well enough to select options or parameters in the database benchmarking tools in order to obtain desired results. So here is probably the principal source of observed performance variance. But even the minority of well-prepared people still see variance. Why?

     

    I have a belief that should make sense – but I cannot say that I have a mathematical proof or certainty that my explanation is 100% correct. But I will offer it nonetheless as I think at an absolute minimum it will radically reduce the observed performance variance for many people.

     

    Let’s use a very simple analogy based on an automobile’s speedometer (MPH) and tachometer (RPM). Remember that MPH is what the police measure to see if you get a speeding ticket. So MPH is like the average response time – that which the database user observes. TPS are more like RPM’s – how hard the database engine is working. This distinction is simple yet critical.

     

    If I don’t try to see the fastest speed at which the automobile can travel (i.e. I do not push it to its limit), then the RPM’s may well be meaningless. If I’m going 40 mph @ 2500 rpms, then it really matters what gear the automobile’s transmission is in. First gear may top out at 40 MPH, while fifth gear may permit speeds of 120 MPH or more at the same RPM’s.

     

    The same is true for database benchmarking. It’s my experience that benchmark tests that do not push the hardware to its limits tend to have widely varying results at lower levels. I don’t know if it’s a flaw in the benchmark design or the software running it. I just know that a database server needs tested at the upper end of its capacity for the results to be both meaningful and repeatable.

     

    Hence my advice to the people I work with doing database benchmarks is to size appropriately. My old notebook with an old Core 2 Duo (P8700) with 8 GB RAM and a first generation commercial grade SSD can handle a few hundred concurrent database sessions. Remember, a common transaction has “think time” in between operations, so 100 users might really mean just 10 concurrent operations at once. Therefore a current or one prior generation of XEON CPU with 128 GB RAM and an enterprise grade IO subsystem should be able to handle tens of thousands of concurrent sessions. Running tiny database benchmarks of under 1,000 users will fall into this area where results may well not be either reliable or repeatable. So test appropriately – even if it means more money for a properly sized tool. Testing workloads that are far too small for the hardware is really just an exercise in futility to place a checkmark somewhere that benchmarks were performed.

    Database benchmarking can be a very useful skillset to add to your DBA repertoire. And like any other tool on your tool belt it has a specific purpose and type of job it’s designed for. Much as you would not utilize a screwdriver to pound in a nail, database benchmarking has specific use cases where it provides critical answers to technical questions which may have basis in the business’ IT strategy. Here are some examples of common questions where database benchmarking might provide some useful insights.

     

    • Virtualization
      • What rough performance impact can we expect from switching our production databases on dedicated physical hosts to virtual machines?
      • What rough performance impact can we expect from upgrading our production virtualization hypervisor to a new release or another vendor?
      • What rough performance impact can we expect from upgrading our production virtualization server to newer processors and/or more memory?
      • How many virtual machines running production databases can we have share a virtualization server physical host?
    • Consolidation
      • What rough performance impact can we expect from switching our production databases on dedicated physical hosts or virtual machines to a single multi-instance high-powered SMP server for database consolidation?
      • What rough performance impact can we expect from switching our production databases on dedicated physical hosts or virtual machines to a single multi-instance Exadata database machine for database consolidation?
      • What rough performance impact can we expect from switching our production databases on dedicated physical hosts or virtual machines to a single 12c multi-tenant (i.e. pluggable databases) high-powered SMP server or Exadata database machine  for database consolidation?
    • Cloud DB’s
      • What rough performance impact can we expect from switching our on premise production databases on dedicated physical hosts or virtual machines to a cloud database platform? (i.e. is the network latency an issue)
      • What cloud instance type/size will we need to deploy in order to have approximately the same rough performance and scalability as we do currently?

     

    There are of course many more such questions where database benchmarking can help to formulate an opinion about large or radical technology changes. Some might correctly argue that a workload capture and replay is a far superior “what if” mechanism for an accurate comparison.  I cannot effectively argue against that position. But sometimes the cost and availability of workload capture and replay makes the better solution realistically unavailable. Thus database benchmarking can quite often suffice for rough analysis and comparison. Stated differently, a tool that’s inexpensive and readily available can often be pressed into service to obtain close enough results. Database benchmarking is such a choice.

     

    The first step of any database benchmarking effort should be to decide upon what it is you want to measure. Are you trying to compare “raw performance” or “real world performance”? Quite often people who want to test raw performance desire to measure and compare some extremely low level metrics such as IO’s per second (IOPS). Generally known industry standard database benchmarks by the Transaction Processing Performance Council (TPC) such as the TPC-C, TPC-E and TPC-H are designed to generally approximate real world type workloads who’s performance is expressed in more meaningful measurements such as transaction per second (TPS). Answering this first question often can help one to choose the right database benchmarking tool.

     

    Thus assuming you want to measure raw performance metrics like IOP’s, you’ll want to use a tool whose design and purpose more closely match that criteria. One of the more popular lower level tools is called SLOB, which stands for Simple Little Oracle Benchmark.  Many people swear by SLOB, but for such low level database benchmarks I prefer my own tool called DBBENCHMARK, which will also be shown and whose script can be found at the following link. Here’s an example of the command to run the utility and its output while running. DBBENCHMARK will create a date and timestamp named directory and zip file of the directories content. Here is an example of such a name: DBBENCHMARK_2016.06.26_21.54.50.

     

     

    [oracle@linux68 ~]$ ./dbbenchmark-oracle.sh -u bert -p bert1234 -d orcl11 -z small -s 1 -S 10 -i 1 -r 180 -P y

     

    ============================================================================

     

    CREDITS: dbbenchmark utility for testing database performance by Bert Scalzo

     

    PARAMETERS:

        DB_USERNAME    = bert

        DB_PASSWORD    = bert1234

        DB_DATABASE    = orcl11

        TEST_SIZE      = SMALL (10,000 rows / session)

        SESSION_START  = 1

        SESSION_STOP   = 10

        SESSION_INCR   = 1

        RUN_TIME       = 180 (seconds)

        PLOT_DATA      = Y

        DEF_TSP        = users

        AWR_SNAP       = N

        AWR_BASE       = N

     

    ============================================================================

     

    WORKING: Testing for gnuplot found executable in current $PATH

    WORKING: Testing for gnuplot must minimally be version >= 4.2

    WORKING: Testing for sqlplus found executable in current $PATH

    WORKING: Testing connect to database with supplied parameters

    WORKING: Processing steps for running benchmark test

    WORKING: ....Create DBBENCHMARK_RESULTS performance measurement table

    WORKING: ....Create DBBENCHMARK_TEST benchmark table & initial load data

    WORKING: Create DBBENCHMARK_TEST table's sole non-unique index

    WORKING: ....Create DBBENCHMARK_WORKLOAD procedure to call/execute

    WORKING: Capture DBBENCHMARK_RESULTS performance measurement start time

    WORKING: Executing 1 sessions against DBBENCHMARK_TEST table

    WORKING: ....Waiting on 1 sessions against DBBENCHMARK_TEST table

    WORKING: DBBENCHMARK_RESULTS performance measurement stop time

    WORKING: Capture DBBENCHMARK_RESULTS performance measurement start time

    WORKING: Executing 2 sessions against DBBENCHMARK_TEST table

    WORKING: ....Waiting on 2 sessions against DBBENCHMARK_TEST table

    WORKING: DBBENCHMARK_RESULTS performance measurement stop time

     

    The reason I like this utility is because there are many parameters to control how it executes and what results it captures. Moreover it plots charts for all the key performance metrics as jpeg files like those shown below. DBBENCHMARK makes running raw performance database benchmarks very easy.

     

    x1.png

     

    Now instead assume that you want to measure real world performance metrics like TPS, you’ll need to use a tool whose design and purpose more closely match real world type database workloads. One of the more popular industry standard database benchmarking tools is called HammerDB. Another tool worth consideration is Benchmark Factory. The freeware version limits you to 100 concurrent database sessions, but for many people that will be sufficient. While both tools used by various database vendors, storage vendors and storage review magazines, we’ll focus here on HammerDB since it’s free and has no such artificial limits like Benchmark Factory freeware.

     

    Now before you go off and run HammerDB it’s highly advisable to first review the benchmarks you have decided to run. For example if you choose to run the TPC-C  OLTP benchmark then you should read the TPC-C spec. Likewise if you choose to run the TPC-H data warehouse benchmark then you should read the TPC-H spec. The spec will define execution criteria and parameters necessary to initiate a meaningful test run. Furthermore knowing the table and index structures plus the SQL transaction natures will be of immense help. Finally the spec will define the criteria and metrics for rating and comparing test runs. Even though the database benchmarking tool will create the database objects, run the SQL transactions and provide some execution performance data – you may well be required to transform that data into proper results. For example the ever popular TPS is really not the proper way to score a TPC-C test run. In fact the TPC-C spec defines that as follows:

     

    The performance metric reported by TPC-C is a "business throughput" measuring the number of orders processed per minute. Multiple transactions are used to simulate the business activity of processing an order, and each transaction is subject to a response time constraint. The performance metric for this benchmark is expressed in transactions-per-minute-C (tpmC).

     

    Note that tpmC is not transactions per second or even per minute. It actually is restricted to just the new orders created (which is clearly a subset of all the SQL commands executed). The spec defines it as:

     

    The metric used to report Maximum Qualified Throughput (MQTh) is a number of orders processed per minute. It is a measure of "business throughput" rather than a transaction execution rate.

     

    Thus an official TPC-C test run reported in a disclosure report might look like this:

     

    MQTh, computed Maximum Qualified Throughput = 105 tpmC

     

    In reality very few people run database benchmarks 100% to spec and report on a collection of data points they feel more appropriately apply to their needs and comfort. Let’s look at running the same type of test as we did with DBBENCHMARK earlier – i.e. from one to ten concurrent database sessions incremented by one and run for 180 seconds per iteration. That will require us to use the HammerDB “autopilot” feature as shown here. Note that the series starts with 2 instead of 1 and continues to 11. This simple numeric increase is required as a coordinator process is required to manage the iterations and thus number range is increased by one (i.e. from start+1 to stop+1) as shown here. Note that you must specify the AWR driver instead of the standard driver in order to output performance data in the log file (i.e. /tmp/hammerdb.log).

     

    x2.png

     

    The HammerDB log file will be of the form:

     

     

    Hammerdb Log @ Mon Jun 27 13:38:03 CDT 2016

    +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

    Vuser 1:Beginning rampup time of 1 minutes

    Vuser 2:Processing 1000000 transactions with output suppressed...

    Vuser 1:Rampup 1 minutes complete ...

    Vuser 1:Rampup complete, Taking start AWR snapshot.

    Vuser 1:Start Snapshot 87 taken at 27 JUN 2016 13:39 of instance orcl11 (1) of database ORCL11 (4162312248)

    Vuser 1:Timing test period of 2 in minutes

    Vuser 1:1 ...,

    Vuser 1:2 ...,

    Vuser 1:Test complete, Taking end AWR snapshot.

    Vuser 1:End Snapshot 88 taken at 27 JUN 2016 13:41 of instance orcl11 (1) of database ORCL11 (4162312248)

    Vuser 1:Test complete: view report from SNAPID 87 to 88

    Vuser 1:2 Virtual Users configured

    Vuser 1:TEST RESULT : System achieved 52767 Oracle TPM at 17599 NOPM

    Hammerdb Log @ Mon Jun 27 13:42:04 CDT 2016

    +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

    Vuser 1:Beginning rampup time of 1 minutes

    Vuser 2:Processing 1000000 transactions with output suppressed...

    Vuser 3:Processing 1000000 transactions with output suppressed...

    Vuser 1:Rampup 1 minutes complete ...

    Vuser 1:Rampup complete, Taking start AWR snapshot.

    Vuser 1:Start Snapshot 89 taken at 27 JUN 2016 13:43 of instance orcl11 (1) of database ORCL11 (4162312248)

    Vuser 1:Timing test period of 2 in minutes

    Vuser 1:1 ...,

    Vuser 1:2 ...,

    Vuser 1:Test complete, Taking end AWR snapshot.

    Vuser 1:End Snapshot 90 taken at 27 JUN 2016 13:45 of instance orcl11 (1) of database ORCL11 (4162312248)

    Vuser 1:Test complete: view report from SNAPID 89 to 90

    Vuser 1:3 Virtual Users configured

    Vuser 1:TEST RESULT : System achieved 95957 Oracle TPM at 32319 NOPM

     

     

    In order to plot the results in a spreadsheet we need to parse the HammerDB log file using the following Linux commands which I placed in a shell script named hammerdb_results.sh:

     

    grep 'TEST RESULT :' hammerdb.log | awk 'BEGIN {print "TPM    NOPM"}{printf ("%s\t%s\n",$7,$11)}' > hammerdb_results.txt

     

    Resulting in the following data to now import into a spreadsheet and plot a graph.

     

    TPM       NOPM

    52767     17599

    95957     32319

    138388    46164

    139454    46583

    142115    47459

    140826    47104

    140316    46654

    133058    44250

    140009    47047

    133882    44920

     

    The graph for the above numbers looks like this:

     

    x3.png

     

    Now comes the interesting part, what have these two sets of radically different database benchmarks revealed and are those revelations simpatico? The raw performance type results from DBBENCHMARK indicate that three concurrent sessions is the sweet spot for this 4 CPU with 16GB RAM VM running on my quad core notebook with an SSD. Both the IOPS and SAR IO graphs peak at three. As for the real world type results from HammerDB also indicate that something notable happens at three concurrent sessions – the lines more or less plateau. Unfortunately the results do not always so perfectly match.

     

    The other industry standard database benchmark frequently run is the TPC-H – a collection of 22 very complex SELECT statements with lots of joins, sub-selects, multiple restrictions, group functions, plus group by and order by clauses. While you can run the TPC-H database benchmark with concurrent sessions in order to test true scalability, my experience is that just running one query set (i.e. 22 queries) for just one session is informative enough for many people. If the query set runs in 10 vs. 20 minutes on comparative test runs where for example upgrading from Oracle 10g R2 to oracle 12c R1, then you have discovered the key finding – the newer database version is far more efficient at optimizing queries. For many people that’s good enough. Note however that sometimes discerning whether that base efficiency also scales with multiple concurrent sessions in a similar fashion is worth knowing. In those cases you can once again run an autopilot scenario. However I’d recommend setting each session to repeat the queries in the query set more than once so that you get a mixture of different queries running together at different times.

     

    Now you have all the essentials to become a database benchmarking expert (with practice of course). Remember that benchmarking is not a dirty word, it’s simply a technique to perform comparisons when doing workload captures and reloads is neither easy nor viable. Under the right circumstances database benchmarking may permit any DBA to ascertain a technical foundation regarding some business critical options.  So don’t hesitate to add this technique and its tools to your DBA work belt and utilize it when appropriate. If the job calls for a hammer don’t grab a screwdriver – grab a hammer and pound away.

    Bert Scalzo

    Benchmarking Quick Bites

    Posted by Bert Scalzo Nov 16, 2016

    Normally, my white papers and PowerPoint presentations about benchmarking pick a particular area or focus – and drill deep into the performance issues and gains possible. But inevitably, many people email or stop up to ask me a bunch of very simple or generic questions. So I thought this time I’d write something to address those common questions which seem to get asked all the time. So this white paper may not be very deep, but it will nonetheless address the breadth of common questions most people seemingly would like to hear about. So below are the five questions I remember hearing the most often.

     

    1. Which Operating System yields better benchmark results – Windows or Linux?

     

    Wow – I’m starting with the most controversial and difficult question first. Let be start by freely admitting that I’m a UNIX and Linux bigot. But that’s simply because I have been doing UNIX twice as long and like the scripting languages. But Windows has matured so much the past few years as a server platform, that this question seems fair game – and on everyone’s’ minds. Below are the results of the TPC-C benchmark performed on identical hardware using both 32-bit and 64-bit versions of Windows 2003 Server Release 2 and. CentOS 4 Update 3 (a free Redhat enterprise variant).

     

    diag1.png

     

    Looks to me like a dead heat. So whichever operating system you’re more comfortable with or already have more system administrators for – that’s what you should choose.

     

    2. How many bits are best, 32 or 64 – and can that effect the operating system choice?

     

    We’ve had 64-bit UNIX servers for many, many years. But 64-bit Windows has only just become a reality (Yes, I know that Windows NT ran on the DEC Alpha, but that never really became mainstream). Now I’ve been partial to AMD’s Athlon-64 and Opteron processors – until mid 2006 that is, when Intel’s 2nd generation dual core CPU’s came out and performed so amazingly. Thus now I’ll go with whichever hardware’s’ current price gives me the most bang for the buck – with energy consumption and room cooling being included secondarily in the TCO calculations. But do 64-bits really make a noticeable difference? Because according to the chart above, it does not. But that’s because 64-bit’s primary advantage is increased addressable memory. Below are the results of the TPC-C benchmark performed once again – but now with increasing amounts of total system and database allocated memory.

     

    diag2.png

     

    Once again there are some very clear results. If your server has 2 GB or less, then there’s really no discernable difference. But as your server’s memory increases beyond 2 GB, now the 64-bit advantage comes into play. Even though some databases like Oracle have 32-bit linkage options to “trick-up” the database into accessing slightly more memory (known as the large memory model), but only up to a certain point – it’s clear the extra memory for both system and database makes ever increasing performance improvements a genuine reality. So for anything over say 4 GB, it’s a “no-brainer” – go with 64-bits. I must however mention one caveat: sometimes 32-bit Linux works better with certain hardware (i.e. drivers, iSCSI, etc) and newer database options (e.g. ASM, OCFS, etc).

     

    3. Which Database benchmarks best – Oracle 10g, SQL Server 2005 or MySQL 5.0?

     

    OK – here we go with another very controversial question (which I limited to just the three database that I get asked about most often – and I’m not skipping DB2-UDB, PostgreSQL, or any other database as an intentional slight). Moreover, once again I’ll own up to my prejudices right up front – I’ve been doing Oracle for 22+ years, so I’m an acknowledged Oracle bigot. I also should mention that the database vendors generally frown upon posting benchmarks, especially comparative ones. But nonetheless, this one question gets asked all the time. Thus below are the results of the TPC-C benchmark performed once again – but now for just those three databases asked about most often.

     

    diag3.png

     

    Whew – I won’t risk any vendors’ ire because the performance results once again are a dead heat. So whichever database you’re more comfortable with or already have more database administrators for – that’s what you should choose. Of course there are also the cost differences amongst the vendors, but since no one ever pays list price – it’s hard to give accurate TPC-C ratings that include those subjectively variable costs. Thus sticking just to the technologies themselves and their relative bencmark performance, we have yet another tie J

     

    4. How does one determine the maximum concurrent OLTP users a server can sustain?

     

    This is always a tough question to answer – because most often people want to hear something like a Dell 1850 can handle N concurrent users. But even servers in the same family and with the same amount of memory can vary by number of CPU’s, CPU clock speed, CPU cores, and cache sizes. So it’s not easy to compare servers, unless you compare nearly identically configured boxes. Plus you also need to compare identical network and disk IO scenarios. But assuming you do that, the question is how to read the benchmark results to accurately decide what the maximum concurrent user load is for that server. Below are the results of the TPC-C benchmark performed yet once again – but now for just one server where we need to determine the inflection point (i.e. the point where the user load begins to negatively affect the response time).

     

    diag4.png

     

    If you end-users require less than a two second response time (which is often the number quoted it seems), then 200 concurrent users is the point where you should probably stop. But the server in this example could support as many as 250 concurrent users before the response time reaches the point of unacceptably steep increase. Note too that in this particular case that’s also about the same point where the TPS rate begins to flatten or decrease. It’s not always this obvious, because sometimes the two inflection points don’t line up so darn perfectly. But when in doubt, always go with the response time for TPC-C or OLTP type transactions.

     

    5. How does one determine the maximum size data warehouse a server can sustain?

     

    This too is always a tough question to answer – because most often people want to hear something like how many Dell 1850’s are needed for N terabytes. And as before, even servers in the same family and with the same amount of memory can vary by number of CPU’s, CPU clock speed, CPU cores, and cache sizes. So once again it’s not easy to compare servers, unless you compare nearly identically configured boxes. Plus you also need to compare identical network and disk IO scenarios – especially the disk IO, because the TPC-H results are governed most by the number of spindles. But again assuming that you do that, the question is how to read the results to accurately decide what the maximum sized data warehouse is for that server or servers. Below are the test results of the TPC-H benchmark for several increasingly powerful Oracle RAC server configurations accessing 300 GB spread across multiple SAN’s and over 100 disks (many, many thanks to Dell and their Linux testing lab for making these results possible).

     

    diag5-1.png

    diag5-2.png

     

    Note that for the TPC-H we’re looking at both the total run time and average response time (which should both be in step with each other). Now don’t let the large time values dissuade you – the TPC-H queries are very complex, and often take hours or even days apiece for large data warehouses. In the above example, the best hardware setup takes about 5 hours to run with an average response time of approximately 4 hours. However the actual 22 queries’ response time results are highly skewed by just a few that take a majority of the time to run. So if your users can accept potentially 4 hour run times for highly complex decision support queries, that 8 node cluster would suffice. If not, then rather than adding more nodes – instead purchase more spindles, because it’s not too uncommon for terabyte sized warehouses to have 500-1000 spindles for optimal results.