Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle/VMS performance problems with Multithreaded Server

Re: Oracle/VMS performance problems with Multithreaded Server

From: I.A. Saez <_at_tue.nl>
Date: Tue, 31 Oct 2000 07:41:40 +0100
Message-ID: <39FE69A4.2DED1146@tue.nl>

OpenVMS fully supported by Oracle? No way! Here is my list of complaints about Oracle on OpenVMS:

That Oracle is better supported on Unix than OpenVMS is a fact. Statements of direction say nothing to me. They sound like "the very soon now" frases of Microsoft. The daily pratice is what matters to me. I hope that Oracle will support OpenVMS better than in the past, but for us it will be too late and I'm sorry because I find OpenVMS a very good OS.

Syltrem wrote:
>
> Oh! Oh!
>
> When I read this, it sounded an alarm in my head! How could 7336 be the last
> "real" version of Oracle on OpenVMS, given the current agreements between
> Oracle and Compaq? And how about the fact that the customer said they will
> move to Unix? Hopefully not only because they think Oracle will be more
> supported on that platform...
>
> In short, OpenVMS is FULLY supported by Oracle, in details, see the comment
> on that from Alan Belancik, OpenVMS Relationship Manager with Oracle at
> Compaq.
>
> « As the OpenVMS Relationship Manager with Oracle, I am in a position to
> tell you that our two groups are working very closely together. Our goal is
> to offer customers high performance solutions that are based on AlphaServers
> running the Oracle database. These have been put to use in many of the world
> 's most mission critical applications requiring extremely high
> availability.
> «Your question dealt with the nature of the Oracle database and its
> applicability to the OpenVMS world. I can assure you that there is a very
> astute team of engineers and managers in place who work on the porting of
> the Oracle database to OpenVMS. These people are very well acquainted with
> the OpenVMS operating system and work to ensure that the products available
> from Oracle take advantage of OpenVMS's unique attributes. Our pioneering
> work in clustering has been reflected in Oracle's Parallel Server offerings.
> Engineers at Oracle have been working on the interplay between new OpenVMS
> technologies, such as the Galaxy Software architecture for dynamic
> partitioning, and the new "WildFire" series of AlphaServers, the GS80, GS160
> and GS320.
> «True, the porting of the Oracle database begins with a set of common code,
> however once it is released to the various "platform groups" with in Oracle,
> they begin to customize the final product to reflect the features of that
> operating system and hardware combination.
> «Unfortunately, there is always some confusion in the field. We at Digital
> Equipment, and now Compaq, have unfortunately contributed to that confusion.
> One item of confusion would be the perceived differences between VMS and
> OpenVMS. Some people have erroneously concluded that VMS is for VAX systems
> and OpenVMS is for AlphaServer systems. Actually, OpenVMS was the new name
> applied to ALL VMS in order to better reflect its adherence to many of the
> world's standards for openness. I mention this because you reference version
> 7.3.3 6 as being the last real VMS version. It is true that Oracle database
> products were not ported to support VAX systems once Oracle made the
> transition from their version 7.x products to their 8.x version products. It
> is very close to being correct that version 7.3.3.6 was the last version for
> VAX systems. However, I do not see how this can be said of AlphaServer
> systems running OpenVMS.
> «Nonetheless, I will confer with my Oracle colleagues to see if they can
> share any insight into this contention. Naturally, it is in any company's
> best interest to try to standardize the core of their product in order to
> reduce operating expenses. Still, they need to be able to customize the
> final product to best suit the platform it is intended for as well as
> optimizing its performance on that platform. We have been working closely
> with the engineers in the porting group and in the performance groups to
> ensure that this is the case.
> «We will continue to work with Oracle as our premier database partner. Our
> user surveys show that approximately 88% of our OpenVMS customers who use a
> commercial database use one from Oracle. We will continue to invest in our
> relationship. One benefit that we expect from this relationship is a product
> from Oracle that is best suited for our AlphaServer systems running
> OpenVMS..
> «Please let me know if more information comes to light or how I can be of
> further assistance. Again, thank you for bringing this information to our
> attention. We share your belief that OpenVMS continues to be one of the
> world's best operating environments - EVER!
> Thanks and merci milles fois,
> Alan
> Alan Belancik
> Oracle Relationship Manager
> OpenVMS System Software Group
> 603-884-0363
> fax: 603-884-2006
>
> "I.A. Saez @tue.nl>" <"i.a.saez.<secret> wrote in message
> news:39F91C4B.ECB2555E_at_tue.nl...
> > Malcolm,
> >
> > We have the same problem with Oracle 7.3.4.4 on OpenVMS 7.1. We have now
> > a mix
> > of MTS and dedicated connections. Dedicated for long running apps and
> > MTS for short connections. Oracle did investigate the problem and told
> > me it still exists in 8.1.6 for OpenVMS. Oracle 7.3.3.6 was the last
> > real VMS version (probably made by VMS especialists).
> >
> >
> > See (some) of ther tar:
> >
> >
> > 27-SEP-00 12:35:39
> >
> >
> >
> > 27-SEP-00 13:24:41
> >
> > To reproduce the slow MTS performance (for development)
> > - The following patches are installed here:
> > 686524-(Base BUG#544858) ORA-602 RUNNING PRO*C WITH TO_DATE AND BIND
> > VARIABLES.
> > 1055554-(Base BUG#553138) ORA-12612 ON W95 CLIENTS AFTER APPLYING
> > 520734(430972.
> > 1218450-(Base BUG#845454) ORA-1403 REFERRING TO CERTAIN PL/SQL TABLE
> > ELEMENTS.
> > 1326425- ORA-1013 when sqlnet.expire_time > 0.
> > 1309610-(Base BUG#1021962) MTS SLOW.
> > 1326725- Listener/Dispatchers consumes Buffered IO.
> > - Used the following MTS parameters in V7.3.4.4 INIT.ORA (OTS14):
> >
> >
> > 27-SEP-00 14:19:34
> >
> > mts_listener_address =
> > "(ADDRESS=(PROTOCOL=tcp)(HOST=nlvms2)(PORT=1521))"
> > MTS_DISPATCHERS="(ADDRESS=(PARTIAL=TRUE)(PROTOCOL=TCP)\
> > (HOST=nlvms2)(PORT=5000))(DISPATCHERS=1)"
> > MTS_DISPATCHERS="(ADDRESS=(PARTIAL=TRUE)(PROTOCOL=TCP)\
> > (HOST=nlvms2)(PORT=5001))(DISPATCHERS=1)"
> > mts_servers = 2
> > mts_service = ots14
> > - Restart the instance --> will register the dispatchers at the
> > listener
> > - Now within the database using SCOTT:
> > - Create a BIGEMP table from EMP with a large number of rows in it
> > (I used BIGEMP with 458752 rows) ---> BIGEMP.DMP export file
> > - Create two connect aliases, one for MTS (OTS14) and one for DEDICATED
> > server
> > (OTS14D) :
> > ots14.world =
> > (DESCRIPTION =
> > (ADDRESS_LIST =
> > (ADDRESS =
> > (PROTOCOL = TCP)
> > (Host = nlvms2)
> > (Port = 1521)
> > )
> > )
> > (CONNECT_DATA =
> > (SID = ots14)
> > (GLOBAL_NAME = ots14.world)
> > )
> > )
> > ots14d.world =
> > (DESCRIPTION =
> > (ADDRESS_LIST =
> > (ADDRESS =
> > (PROTOCOL = TCP)
> > (Host = nlvms2)
> > (Port = 1521)
> > )
> > )
> > (CONNECT_DATA =
> > (SID = ots14)
> > (GLOBAL_NAME = ots14.world)
> > (SERVER = DEDICATED)
> > )
> > )
> > - Create the following test script TEST.SQL
> > select to_char(sysdate, 'hh24:mi:ss') from dual;
> > set termout off;
> > select * from bigemp where ename='KING';
> > set termout on;
> > select to_char(sysdate, 'hh24:mi:ss') from dual;
> > spool a.a
> > select count(*) from bigemp;
> > spool off;
> > select to_char(sysdate, 'hh24:mi:ss') from dual;
> > exit
> > - and reproduce the problem as follows:
> > $ sqlplus scott/tiger_at_ots14d --> dedicated server
> > SQL> @test
> > 14:08:16 --> start time
> > 14:08:55 --> end of query with where clause
> > count(*) --> 458752
> > 14:09:11 --> end time
> > Total elapsed time : 39 seconds for the first part, 16 seconds for the
> > second
> > Now with MTS:
> > $ sqlplus scott/tiger_at_ots14 --> shared server
> > SQL> @test
> > 14:12:15 --> start time
> > 14:18:12 --> end of query with where clause
> > count(*) --> 458752
> > 14:18:29 --> end time
> > Total elapsed time : 357 (!!) seconds for the first part, 17 seconds
> > for the
> > second part, a huge difference with DEDICATED connection.
> >
> >
> > 27-SEP-00 15:47:41
> >
> > .
> > Tested with V8.1.6.0 on OpenVMS (same configuration, same data):
> > The following patches are installed on this installation:
> > patch#1335503 - ORA-1013 on queries when sqlnet.expire_time > 0.
> > $ sqlplus scott/tiger_at_ots16d ----> dedicated server
> > SQL> @test
> > 15:39:41 --> start time
> > 15:40:16 --> end of query with where clause
> > count(*) --> 458752
> > 15:40:29 --> end time
> > Total elapsed time : 35 seconds for the first part, 13 seconds for the
> > second part
> > Now with MTS:
> > $ sqlplus scott/tiger_at_ots16 ----> shared server
> > SQL> @test
> > 15:45:26 --> start time
> > 15:47:34 --> end of query with where clause
> > count(*) --> 458752
> > 15:47:47 --> end time
> > Total elapsed time : 128 seconds for the first part, 13 seconds for the
> > second part
> > Conclusion: MTS with V816 is better performing than with V7344 but
> > elapsed
> > time differs a lot from dedicated server connections.
> > Above needs to be transferred to development.
> >
> >
> > 27-SEP-00 17:06:13
> >
> > Advised customer to not use MTS where elapsed time is impacted and
> > database
> > links are accessed. Customer will setup prespawned server (although not
> > error free) this evening. AGreed on assisting in tuning such prespawn
> > environment when needed...
> >
> >
> > 27-SEP-00 17:33:19
> >
> >
> >
> > 04-OCT-00 12:47:50
> >
> > Awaiting the results of the case as distributed to development...
> >
> >
> > 12-OCT-00 13:02:28
> >
> > Problem is reproduced by development and under investigation...
> >
> >
> > 13-OCT-00 15:06:49
> >
> > Discussed status with customer:
> > - Problem is under control as a mix is made with both MTS and dedicated
> > (for the long running jobs) server connections
> > - Explained that I do not expect a solution on short term based on my
> > contacts with development. As the problem also reproduces with V816
> > I expect the problem to be fixed in a future release (and if possible
> > wil be backported)
> > - Customer stated that a move will be made to Unix within the next half
> > year
> > === Agreed ===
> > We decided to close the problem for now as a 'working' environment is
> > setup
> > now. Will update this TAR with the 'internal' developments on this
> > matter and
> > therefore leave status at 14 'Partner (=development) Action'
> >
> >
> >
> >
> > Kind regards,
> >
> > Ivan Saez
> > i.a.saez.scheihing_at_tue.nl
> >
> > Malcolm Dunnett wrote:
> >
> >
> >
> > >
> > > I'm running Oracle 8.1.6 on a VMS 7.2 system. I've tried
> > > setting up the MultiThreaded server on this system. It works
> > > but the performance with my programs is terrible ( queries
> > > that take 1 second with a dedicated server process might take 5 or 6
> > > with the Multithreaded server, for example ) [ ie all I do
> > > to get the performance difference is change the client connect
> > > to include a "SERVER=DEDICATED" parameter]. The programs are
> > > written using Oracle7 OCI calls, they fetch single rows of
> > > data at a time from the database - I realize they could be
> > > more efficient using array fetch, but it's never been an issue
> > > with dedicated server processes.
> > >
> > > I realize this is a pretty broad question without more detail,
> > > but does anyone have similar experiences ( or alternately, good
> > > experiences with the MTS on a VMS system ). Any ideas if there's
> > > anything I could do to make MTS useful?
> > >
> > >
> ============================================================================
> =
> > > Malcolm Dunnett Malaspina University-College Email:
 dunnett_at_mala.bc.ca
> > > Information Systems Nanaimo, B.C. CANADA V9R 5S5 Tel: (250)755-8738
Received on Tue Oct 31 2000 - 00:41:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US