Performance of Oracle 7.3 Import on Windows NT (3.5 sp#5) 2 processors Vs 4 processors

This is a multi-part message in MIME format.

--------------1F9C78FB7506

Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit

I would appreciate ANY THEORIES why concurrent imports might perform slightly worse on a server with 4 processors than with 2 processors.

Thanks in advance.
Hurriyet Gendj
(email: hxg_at_msg.ti.com or hurriyet_at_escortnet.com)

--------------1F9C78FB7506

Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="ORAIMP.TXT"



-MSG M#= 02702010 FR=MSGO TO=HXG    SENT=03/03/97  11:28 AM

     R#=086 ST=C DIV=0023 CC=00007 BY=MSGO AT=03/03/97 11:28 AM

To:      HXG

From: Corporate Pre-Sales Support - Manchester mhallas_at_uk.oracle.com

Subject: Re: Oracle Import/Create Index on NT

Via: MIMI 2.2 Gateway at Magic (TID DA851942 dated 03 Mar 97 17:25:18 +0000) From: "Michael Hallas (Corporate Pre-Sales Support - Manchester)" <MHALLAS_at_uk.o  racle.com>
To: hxg%mimi_at_magic.itg.ti.com
Subject: Re: Oracle Import/Create Index on NT X-Orcl-Application: In-Reply-To:
  UKUNIX2.UK.ORACLE.COM:HXG%mimi_at_magic.itg.ti.com's message of 03-Mar-97 15:56 Mime-Version: 1.0
X-Mailer: Oracle InterOffice (version 4.0.4.0.25) Content-Type: multipart/mixed; boundary="=_ORCL_10402542_0_11919703031827500"

--=_ORCL_10402542_0_11919703031827500

Content-Transfer-Encoding:7bit
Content-Type:text/plain; charset="US-ASCII"

Hurriyet,

        You're right - "imp"ort is currently a serial process with 7.3 ie only a single Oracle server task is actively involved in the import task. In fact "imp"ort just runs an insert statement via the array interface - the .dmp file contains Oracle data in native (portable) Oracle types for performance / portability. The import buffer size you used might be larger than the optimal size which is usually eg 4 Mb with COMMIT=Y set via the command line or parameter file mechanism.

        I suspect the difference between the 4 CPU and 2 CPU environment is "noise" in the measurement, perhaps non-randomly influenced by the number of CPUs. Unix systems tend to implement processor affinity (ie tasks are re-scheduled on the processor that most recently executed the task) which avoids the expense of processor cache misses and pipeline stall/refill. Perhaps the NT environment does not implement such a feature, in which case a 2 CPU machine will show a 50 % chance of re-scheduling on the same CPU, and a 4 CPU machine will show only a 25 % chance of re-scheduling on the same CPU. This might explain the marginal differences in performance, but I couldn't confirm that this is definitely the case.

                Thanks

                Mike
                ----

--=_ORCL_10402542_0_11919703031827500

Content-Type:message/rfc822

Date: 03 Mar 97 15:56:50
From:"HXG%mimi_at_magic.itg.ti.com" <HXG%mimi_at_magic.itg.ti.com> To:RMarekwi_at_de.oracle.com,Alberto_at_hpbbse.bbn.hp.com,mwan_at_us.oracle.com,mhallas@ uk.oracle.com,hgs_at_hpbbse.bbn.hp.com
Subject:Oracle Import/Create Index on NT Return-Path:<HXG%mimi_at_magic.itg.ti.com>
MIME-Version: 1.0
Content-Transfer-Encoding:7bit
Content-Type:text/plain; charset="US-ASCII"

Copy: Lynn Williams  XLPW                Dave Slade      DXNS
      John Carter    EIGG                Greg Hughlett   XGJH

From: Hurriyet Gendj HXG_at_msg.ti.com

Subj: Oracle Import/Create Index on NT

Rainer,

As you might recall, during our October benchmarks at HP Boeblingen, we exported the Oracle database from Unix to NT. We used the following approach as recommended by you:
- Import tables without the indexes,

  using options buffer=102400000 ignore=n
- Recreate the indexes

  using options unrecoverable (to avoid log IO) and parallel 4 to initiate   4 processes.

We ran 3 import and create index processes CONCURRENTLY to maximise the throughput. They were as follows:

1. Import DOBJ table (large table) and then create its 4 indexes.
2. Import DASC table and then create its 2 indexes.
3. Import all other tables (very small tables) and create remaining indexes

   (22 tables and 40 indexes in total).

I am now including the statistics to our NT/Oracle report, because they would be useful to our customers who will use Oracle export and import utility to defragment the database, and perhaps as a backup mechanism and/or to migrate from one operating system to another.

I noticed that the index creation processes were greatly affected by the number of processors. This is as expected. However, the imports of the two large tables took almost the same time on both 4-way and 2-way processors (in fact shorter on 2-way!). We ran the imports several times on 4-way. I also checked their times, but they are similar. I also plotted the graph corresponding to the below figures to visualise concurrency.

My hypothesis is as follows:

   IMPORT TABLE is not as CPU bound as CREATE INDEX (especially if CREATE    INDEX uses 4 parallel processes, - but I think this is irrelevant for    this point) and also IMPORT TABLE is a serial process.    Therefore 4 CPU's were not 100% utilised during the 3 concurrent imports,    but NT might have spent considerable time managing the workload between    4 CPU's. This may be the reason why the large imports performed slightly    worse on 4-way.

Would you agree with this? Did you come across this before?

I would appreciate your comments asap please. I am also copying this to:
- Alberto for his comments on NT,

• Milton Wan at Oracle US who is the NT product manager (I attended to his presentation at the Paris Developers Conference) and
• Mike Hallas at Oracle UK who is a performance specialist.

I hope they will have some input as well. Please at least point me to the appropriate documentation where I could find an explanation to this phenomenon.

Thanks in advance.

Best regards,

Hurriyet

Texas Instruments Software
European Technology Marketing

HP NetServer LXPro, 512MB memory (32MB Oracle SGA)

                                       Elapsed Time
Table   Operation                    4-way       2-way


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

DOBJ    Import table                3:12:09     3:07:25  <--???
        Create 4 indexes on DOBJ    0:55:53     1:49:25

DASC    Import table                1:04:18     1:03:08  <--???
        Create 2 indexes on DASC    0:28:42     2:21:59

Others  Import 22 tables            0:29:51     0:46:16
        Create 40 indexes           0:15:29     0:18:00

--=_ORCL_10402542_0_11919703031827500--

--------------1F9C78FB7506--
Received on Thu Mar 06 1997 - 00:00:00 CST