asked on

Oracle Performance bottleneck (Urgent)

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.

ASKER CERTIFIED SOLUTION

ORACLEtune

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Geerlings

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?

netbanker

How about trying to increase the size of your large_pool_size?

MTS require more memory from there.

best regards,

Moondancer

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

gmyers

[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...

gmyers

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')

jpkemp

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