Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle/VMS performance problems with Multithreaded Server
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?
> >
> >