Re: Oracle/VMS performance problems with Multithreaded Server

From: I.A. Saez <_at_tue.nl>
Date: Fri, 27 Oct 2000 08:10:19 +0200
Message-ID: <39F91C4B.ECB2555E@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 Fri Oct 27 2000 - 01:10:19 CDT