Solved

Oracle Performance bottleneck (Urgent)

Posted on 2002-05-29
8
1,664 Views
Last Modified: 2010-05-18
We have developed a multithreaded application using Proc. Our OS platform is solaris 2.8 (sun fire 4800 with 4cpu and 4GB RAM) and oracle version is 8.1.6.

we are initially linked our application with single threaded pro*c runtime library, in this configuration transaction speed was very high.

But since our application is multithreaded one hence we linked our application with multithreaded Pro*c runtime library (by specifing "exec sql enable threads"). After this we have observed our application performance has  detoriated drastically.

Please provide any solution urgently.




0
Comment
Question by:dip_sarkar
8 Comments
 
LVL 5

Accepted Solution

by:
ORACLEtune earned 250 total points
ID: 7043757
hi,

it is assumed you recompiled the programs.

1. Is the Oracle MTS configured... this architecture employs a shared multi-threaded approach to do load balancing.  In addition, such an  architecture will take advantage of multiprocessor SMP systems -
MTS is configured using MTS specific parameters  
defined in the initialization parameter file (INIT.ORA).  They include:        

MTS_SERVICE                                                 MTS_SERVERS                                                        
MTS_MAX_SERVERS                                                        
MTS_DISPATCHERS                                                      
MTS_MAX_DISPATCHERS                                            
MTS_LISTENER_ADDRESS              

2. below doc may be useful:

Doc ID:  Note:45703.1
Subject:  Multi-threading Example using Separate Connections
Type:  SCRIPT
Status:  PUBLISHED
 Content Type:  TEXT/PLAIN
Creation Date:  20-MAY-1997
Last Revision Date:  02-MAY-2002
 

This is an example program used for testing multiple threads/connections running
concurrently.  Thread 1 starts a large sort (done on the first fetch) while
thread 2 sleeps to give the first thread a chance to get started.  The result is
records returned from thread 2 followed shortly by the start of those from
thread 1 so the two threads are soon printing results one after the other.  
(Note the sleep time is high to ensure the large query will return before the
small one completes thus demonstrating the interleaved results.  The actual
sleep value required will depend on the number of rows in all_objects.)
 
The program is written using Solaris threads.  Though a mutex is created it is
not used by the program.  In the program this example was based on, the mutex
was locked by a thread whenever it wanted to update a shared (global) variable.

Caution
-------

The sample program in this article is provided for educational purposes only and
is NOT supported by Oracle Support Services.  It has been tested internally,
however, and works as documented.  We do not guarantee that it will work for you,
so be sure to test it in your environment before relying on it.
 
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#define      _EXC_OS_        _EXC__UNIX
#define      _CMA_OS_        _CMA__UNIX
 
#include <thread.h>
#include <sqlca.h>
 
/* Function prototypes */
void err_report();
void *do_transaction();
void logon();
void logoff();
 
#define CONNINFO "SCOTT/TIGER"
#define THREADS 2
 
struct parameters {  
        sql_context * ctx;
        int thread_id;
};
typedef struct parameters parameters;
 
mutex_t mutex;
 
/*
* Function: main()
*
* Description: This function spawns the threads
*/
void main()
{
        sql_context ctx[THREADS];
        thread_t thread_id[THREADS];
        int status;
        parameters params[THREADS];
        int i;
 
        /* Initialize a process in which to spawn threads. */
        EXEC SQL ENABLE THREADS;
 
        EXEC SQL WHENEVER SQLERROR DO err_report(sqlca, 99);
 
        /* Create THREADS sessions by connecting THREADS times, each
        connection in a separate runtime context. */
        for(i=0; i<THREADS; i++)
        {
                printf("Start Session %d....\n",i);
                EXEC SQL CONTEXT ALLOCATE :ctx[i];
                logon(ctx[i],CONNINFO);
        }
 
        /* Create mutex for transaction retrieval. */
        if (mutex_init(&mutex, USYNC_THREAD, NULL))
        {
                printf("Can't initialize mutex\n");
                exit(1);
        }
 
        /* Spawn threads. */
        for(i=0; i<THREADS; i++)
        {
                params[i].ctx=ctx[i];
                params[i].thread_id=i;
 
                printf("Thread %d... \n",i);
                if (status = thr_create (NULL, 0, do_transaction,  
                                         &params[i], 0, &thread_id[i]))
                        printf("Can't create thread %d\n",i);
                else
                        printf("Created\n");
        }
 
        /* Logoff sessions. */
        for(i=0;i<THREADS;i++)
        {
            printf("Thread %d ....\n",i); /* waiting for thread to end */
            status = 1;
            printf("Joining thread %d ....\n", thread_id[i]);
            if (thr_join(thread_id[i], NULL, NULL))
                    printf("Error waiting for thread to terminate\n");
            printf("Stop Session %d....\n", thread_id[i]);
 
            logoff(ctx[i]);
 
            EXEC SQL CONTEXT FREE :ctx[i];
        }
         
        /*Destroys mutex*/
        if (mutex_destroy(&mutex))
        {
                printf("Can't destroy mutex\n");
                exit(1);
        }
 
} /* end main() */
 
/*
* Function: do_transaction()
*
* Description: This function executes the transaction
*/
void *do_transaction(params)
parameters *params;
{
        struct sqlca sqlca;
        sql_context ctx = params->ctx;
        char col1[30];
        char col2[30];
        char col3[30];
        EXEC SQL VAR col1 IS STRING(30);
        EXEC SQL VAR col2 IS STRING(30);
        EXEC SQL VAR col3 IS STRING(30);
 
 
        EXEC SQL WHENEVER SQLERROR DO err_report(sqlca, params->thread_id);  
        EXEC SQL CONTEXT USE :ctx;
 
        EXEC SQL DECLARE cur CURSOR FOR
                SELECT A.OWNER, A.OBJECT_NAME, A.STATUS  
                FROM ALL_OBJECTS A, EMP E  
                WHERE e.JOB = 'CLERK'
                ORDER BY A.OWNER, A.OBJECT_NAME, A.STATUS;
 
        EXEC SQL DECLARE cur2 CURSOR FOR  
                SELECT ENAME, JOB, DNAME
                FROM EMP E, DEPT D
                /* WHERE E.DEPTNO = D.DEPTNO */;
 
        if (params->thread_id == 0)
        {
                EXEC SQL OPEN cur;
                printf("Cursor opened in thread %d\n", params->thread_id);
        }
        else
        {
                EXEC SQL OPEN cur2;
                printf("Cursor opened in thread %d\n", params->thread_id);
                sleep(80);
        }
 
        while ( 1 )
        {
                if (params->thread_id == 0)
                        EXEC SQL FETCH cur INTO :col1, :col2, :col3;
                else
                        EXEC SQL FETCH cur2 INTO :col1, :col2, :col3;
 
                if (sqlca.sqlcode == 1403)
                        break;
                printf("From thread %d:\t%s\t%s\t%s\n",
                       params->thread_id, col1, col2, col3);
        }
        if (params->thread_id == 0)
                EXEC SQL CLOSE cur;
        else
                EXEC SQL CLOSE cur2;
        return;
}
 
/*
* Function: err_report()
*
* Description: This routine prints the most recent error.
*/
 
void err_report(sqlca,thread_id)
struct sqlca sqlca;
int thread_id;
{
        if (sqlca.sqlcode < 0)
            printf("\n%.*s",sqlca.sqlerrm.sqlerrml,sqlca.sqlerrm.sqlerrmc);
        if (thread_id != 99)
            printf("\nEncountered in thread %d\n",thread_id);
        exit(1);
}
 
/*
* Function: logon()
*
* Description: This routine logs on to Oracle.
*/
void logon(ctx,connect_info)
sql_context ctx;
char * connect_info;
{
        EXEC SQL WHENEVER SQLERROR DO err_report(sqlca, 99);
        EXEC SQL BEGIN DECLARE SECTION;
                char *connstr = connect_info;
        EXEC SQL END DECLARE SECTION;
        EXEC SQL CONTEXT USE :ctx;
        EXEC SQL CONNECT :connstr;
        printf("Connected!\n");
 
        return;
}
 
/*
* Function: logoff()
*
* Description: This routine logs off from Oracle.
*/
void logoff(ctx)
sql_context ctx;
{
        EXEC SQL WHENEVER SQLERROR DO err_report(sqlca, 99);
        EXEC SQL CONTEXT USE :ctx;
        EXEC SQL COMMIT WORK RELEASE;
        printf("Logged off!\n");
 
        return;
.

end. good luck.
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 250 total points
ID: 7043863
There are many reasons for this.

Problem Description:
====================
You are running a multithreaded Pro*C application and find that the process
is repeatedly opening the file $ORACLE_HOME/precomp/mesg/sqlus.msb, possibly
resulting in the process running out of file descriptors.

*************************************************************
This article is being delivered in Draft form and may contain
errors.  Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
*************************************************************

Solution Description:
=====================
Make sure you are executing EXEC SQL ENABLE THREADS only once in the process.
DO NOT execute once per thread or repeatedly execute this call in a loop.
Threads are enabled for the entire process, not per thread.

Explanation:
============
The $ORACLE_HOME/precomp/mesg/sqlus.msb is referenced during the execution of
the EXEC SQL ENABLE THREADS command. No check is made to verify whether this
file is already opened since the command should not be called more than once
per process. Thus, multiple file descriptors are opened, each one associated
with the same file.


ANOTHER ONE:


Multi-threaded Server Processes
                    -------------------------------
 
How many dispatchers are running?
---------------------------------
When MTS is configured, a certain amount of dispatcher(d00x) and server(s00x)
processes will be started along with the Oracle Background processes at
instance start up.  
 
To see how many dispatchers/shared servers are started up with the RDBMS,
check the value of 'mts_dispatchers' and 'mts_servers' in the init.ora.
 
Example:
mts_dispatchers="ipc,4"
mts_servers=20
 
* You can also tell by looking in the alert.log(default location is
$ORACLE_HOME/rdbms/log).
 
Sample:
PMON started
DBWR started
LGWR started
RECO started
Thu Sep 14 09:24:15 1995
starting up 4 shared server(s) ...               <<-----------------
starting up 4 dispatcher(s) for network    <<------------------
 
* To see how many dispatcher processes are running and how many connections
have been established to each dispatcher:
 
% lsnrctl serv
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=V714))
Services Summary...
  V714has 4 service handlers
    DISPATCHER established:3 refused:0 current:0 max:170 state:ready
      D000 (machine: tcosf, pid: 13764)
  (ADDRESS=(PROTOCOL=ipc)(DEV=35)(KEY=#13764.1))
    DISPATCHER established:2 refused:0 current:0 max:170 state:ready
      D001 (machine: tcosf, pid: 13765)
    (ADDRESS=(PROTOCOL=ipc)(DEV=35)(KEY=#13765.1))
    DISPATCHER established:2 refused:0 current:0 max:170 state:ready
      D002 (machine: tcosf, pid: 13766)
      (ADDRESS=(PROTOCOL=ipc)(DEV=35)(KEY=#13766.1))
    DISPATCHER established:2 refused:0 current:0 max:170 state:ready
      D003 (machine: tcosf, pid: 13767)
      (ADDRESS=(PROTOCOL=ipc)(DEV=35)(KEY=#13767.1))
 
* To dynamically check the activity of dispatcher and server processes, use
SQL*DBA Monitor/MultiThreaded Server and then either shared server or
dispatcher.
 
Examples:  
Monitor: Shared Server
-----------------------------------------------------
S000  WAIT(COMMON)      261 (0) 0:10:34 (0) 0:0:3        .005 00
S001  WAIT(COMMON)        0 (0) 0:10:35   (0) 0:0:0 0 00
S002  WAIT(COMMON)        0 (0) 0:10:43   (0) 0:0:00 00
S003  WAIT(COMMON)        0 (0) 0:10:46 (0) 0:0:0           0 00
-----------------------------------------------------
 
Monitor:  Dispatcher
-----------------------------------------------------
D000  WAIT       YES    177   12744       3 (0) 0:10:13   (0) 0:0:1.001
D001  WAIT       YES    118    8496       2 (0) 0:10:17 (0) 0:0:0     .001
D002  WAIT       YES    118    84962    (0) 0:10:20   (0) 0:0:0 .001
D003  WAIT     YES    118    8496       2 (0) 0:10:23   (0) 0:0:0.001
-----------------------------------------------------
 
 
How many dispatchers or shared servers should I have?
-----------------------------------------------------
From PRE 1005259.6 MULTI-THREADED SERVER DIAGNOSTICS:
The MTS_SERVERS value should be set to a small but comfortable
value, so that the expense of process creation is doesn't outweigh gain of
recovering the resources from idle shared servers.
 
The shared servers are created by PMON as needed and terminated when not
needed.
 
Thus, shared servers will be automatically started as needed and you should
set a minimal value at RDBMS startup.
 
However, dispatchers can only be added by doing an alter database command.
 
Use this formula to determine the how many dispatchers to start:
Maximum # of Concurrent Sessions / Connections per Dispatcher
 
Connections per Dispatcher is limited by the Operating System parameter
Connections per Process.
 
Example:
You want to have 100 sessions running concurrently on your system (that is,
100 users)
Connections per Process is 50
100/50 = 2
You only need 2 dispatchers.
 
How do user processes connect to dispatchers and processes?
 
* Client Connection to a Dispatcher
 
First, the client connects to the SQL*Net V2 listener on the PORT defined in
the listener.ora (the default is 1521).  Then the listener gets a PORT # from
a dispatcher and passes it back to the client.  The client then connects
directly to the dispatcher on this new PORT # (note:  the PORT #s are
dynamically defined and are listed in the % lsnrctl serv output)  
 
(client)  -->  (SQL*Net V2 listener)    
(client) (SQL*Net V2 listener) --> (Dispatcher d000)
(client) <--   (SQL*Net V2 listener) <-- (Dispatcher d000)
(client) -->   (Dispatcher d000)
 
* Servers and the virtual circuit
There is no direct relationship between the server process(es) and the client
process or the dispatcher process.  From above, all requests from the client
are sent via the dispatcher.
 
The dispatcher(s) place these requests into a 'virtual circuit' which is
placed into SGA.
 
Server processes then pick up the virtual circuits, process the requests, and
then place them back into the SGA where the dispatcher will pick them up again.
 
This concept can be represented visually like this:
 
Dispatcher                 Server  
Processes -------------SGA---------....Processes  
(d000)........|...............................................................
|.........(s000)
 
...................|...........................................................
....|.........(s001)
(d001)........|...............................................................|
..........(s002)
....................|......................(vc1).
vc2)..(vc3)........|..........(s003)
....................--------------------------  
 
 
The server process s000 will work on all 3 requests.  A request will only go
to the second server s001 if the first server is busy.
 
Use SQL*DBA 'monitor' (see above) examples.  Also refer to:
PRE 1005259.6MULTI-THREADED SERVER DIAGNOSTICS
 
* How much memory will my processes use?
First, define the memory measurement:  real memory or virtual memory?
 
Virtual memory includes the SGA.  For details, see  
PRE 1012017.6 UNIX VIRTUAL MEMORY (soln#2060096.6 MONITORING MEMORY USE)
 
Sample:
 
Memory statistics from 'ps':
rssize(RSS)  Real memory (resident set) size of the process (in 1024 byte
units)
vsize (VSZ)   Process virtual address size
 
Testing on a DEC OSF/1 system running OSF 3.2 and V7.1.4
Total System Global Area      18568744 bytes (18M)
 
-----------------------------------------------------  
Baseline:  RDBMS startup with 4 shared servers & dispatchers started (all
idle)  
 
RSS %MEM TIME  %CPU COMMAND    VSZ
496K  0.4     0:00.03  0.0     ora_s000_V714   26.1M
496K  0.4     0:00.03  0.0     ora_s001_V714   25.9M
496K  0.4     0:00.03  0.0     ora_s002_V714   25.9M
496K  0.4     0:00.03  0.0     ora_s003_V714   25.9M
920K  0.7     0:00.09  0.0     ora_d000_V714   26.5M
920K  0.7     0:00.08  0.0     ora_d001_V714   26.5M
920K  0.7     0:00.08  0.0     ora_d002_V714   26.5M
920K  0.7     0:00.09  0.0     ora_d003_V714   26.5M
 
start 1st MTS-IPC connection
s000 increases from 496k to 2.7M (2.204M)
d000 increases from 920k to 936k (16k)
 
start 2nd MTS-IPC connection
d001 increases from 920k to 936k (16k)
 
start 3rd MTS-IPC connection
s000 increases from 2.7M to 2.8M (.1M)
d002 increases from 920k to 944k (24k)
 
Summary:
The initial connection increases shared server #1(sd00) by 2.204M.
Every other add'l connection increases this process by .1M
No other shared servers were used (9 connections made).
 
Every connection goes to an alternate dispatcher
Dispatcher processes increase by 16k-32k (higher as there are more
connections).

0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 7044528
Are you sure that the performance degradation was due to the relinking?  If so, can you relink back to a single-threaded version?  (I'm not an expert on ProC, so maybe that is not a reasonable suggestion.)

Did anything change in the database at the same time, like a large data load, or a large increase in the number of users, or even a smaller change like adding or dropping an index on a large table, or adding or deleting the statistics for a large table?  Do you have a way to monitor the database performance outside of the ProC app to see if the database performance is possibly the problem?
0
 

Expert Comment

by:netbanker
ID: 7083036
How about trying to increase the size of your large_pool_size?

MTS require more memory from there.

best regards,
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Expert Comment

by:Moondancer
ID: 7090238
Have you been helped here or is more needed?  This question was locked with a proposed answer, which moves it out of the Open Question queue, so few are likely to step in here and add information.

Moondancer - EE Moderator
0
 
LVL 5

Expert Comment

by:gmyers
ID: 7377288
[Having trouble posting the comment]
Anything new happened on this ?

Have you tried breaking down the query.
The obvious steps are to remove the group by.
Then strip the 'select' down to
 ap.ap_invoice_distributions_all.rowid,
 ap.ap_invoices_all.rowid,
 inv.mtl_system_items.rowid,
 ofbo.gl_code_combinations.rowid,
 ofbo.hr_employees.rowid,
 ofbo.pa_project_players.rowid,
 ofbo.pa_projects_all.rowid,
 ofbo.per_people_f.rowid,
 ofbo.po_releases.rowid,
 po.po_distributions_all.rowid,
 po.po_headers_all.rowid,
 po.po_line_locations_all
 po.po_line_types.rowid,
 po.po_lines_all.rowid,
 po.po_vendors.rowid

more...
0
 
LVL 5

Expert Comment

by:gmyers
ID: 7377291
Then, if that isn't making the difference clearer, strip the 'outer joined' tables out.
That will get you down to
QUERY_BASIC :
SELECT
  OFBO.PA_PROJECT_PLAYERS.ROWID,
  OFBO.PA_PROJECTS_ALL.ROWID,
  OFBO.PER_PEOPLE_F.ROWID
FROM
  OFBO.PA_PROJECT_PLAYERS,
  OFBO.PA_PROJECTS_ALL,
  OFBO.PER_PEOPLE_F
WHERE
 ( OFBO.PA_PROJECTS_ALL.PROJECT_ID=
       OFBO.PA_PROJECT_PLAYERS.PROJECT_ID  )    AND
 ( OFBO.PA_PROJECT_PLAYERS.PERSON_ID=
       OFBO.PER_PEOPLE_F.PERSON_ID  )    AND
 OFBO.PA_PROJECTS_ALL.NAME  =
         '26204-B767-004 Redelivery/Deli'

I've got an uneasy feeling about the join "PO.PO_LINES_ALL.LINE_TYPE_ID = PO.PO_LINE_TYPES.LINE_TYPE_ID", given that it doesn't have an outer-join, but the links around it do :
IE the access should be from these links
OFBO.PA_PROJECTS_ALL.PROJECT_ID=PO.PO_DISTRIBUTIONS_ALL.PROJECT_ID(+)
PO.PO_DISTRIBUTIONS_ALL.PO_LINE_ID=PO.PO_LINES_ALL.PO_LINE_ID(+)

So if QUERY_BASIC is the same on both, I'd try adding those tables/links in and see if it breaks.

PS. No-one seems to have asked the obvious question of, which is actually the 'right' answer. Should the query return rows ?

PPS. Try the original query with a
TO_CHAR('26204-B767-004 Redelivery/Deli')
0
 
LVL 5

Expert Comment

by:jpkemp
ID: 9017081
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Split between ORACLEtune and schwertner
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
jpkemp
EE Cleanup Volunteer
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now