need help with fighting the dev team

[DeltaFun]

Hi,all
My situation is like this: I have been handed over a production database,which supports online transaction for mortgage brokers.
I look at the database's hit ratios, they are all >95%, version is 9.2. Everything is set to auto. db is run on sun server 880 has 4 gig mem and 4 cpus. I am pretty sure the database and sun sever are fine. the problem comes from the bad sql code. The home page is a 22 table join, which runs extremely slow. But the guy who developed the codes does not admit that his codes have problems. He said it is because the db can not handle it. Whenever there is a spike on cpu, I can pinpoint the bad sql which causes it. Now the situation is he said it is because of the database can not handle the connections. I said it's his bad sql which slows down the performance.
I'd like to know, what would you do if you were in my shoes? What kind of evidence should I provide to the management to prove that the db and sun sever is adequate? Thanks for help.

[sreddy]

Run the 22 table join sql in a session( with dummy values if not real production data) and trace the session(using TKPROF) and places the results on the table.

Which will open everybodys eyes...

You can also use statspack around the time the SQL runs and trace the results.

[slimdave]

ideally of course you would provide an alternative sql that runs more efficiently.

can you post the sql here?

and is the developer using bind variables?

[marist89]

A V880? Damn that's a nice box. Sounds like you have a credibility problem there...

I would trace the session, run an explain plan, and find an alternate query. Once I did, I would show the developer the differences. If he doesn't change his stuff, go to management. If all of the above don't work, go in behind the scenes and create some indexes that will help the query.

[DeltaFun]

Thanks to all for your input. here is one of the query:

SELECT
app.appsysid as appsysid,
COALESCE(SUM(d.broker_msg), 0) as brkmsg, COALESCE(SUM(d.followup),0) as followup, COALESCE(SUM(d.note),0) as note,
COALESCE(SUM(d.sysalert),0) as sysmsg, MIN(d.followupdate) as followupdate, MIN(d.datecreated) as dialogdate,
apph.stageid as stageid, app.sourceid,
lb.firstname as bor_firstname, lb.lastname as bor_lastname, lb.ssnumber as bor_ssn,
ad.address1 as address1, ad.address2 as address2, ad.city as city, ad.state as state, ad.zip as zip,
apps.stage as stage, lb.homephone,
app.regionid as regionid, app.DateCreated as DateCreated, app.LAST_MODIFIED as DateModified,
app.credit_report as credit, app.duplicate as duplicate, app.duplicate_appsysid as duplicateID
, status.status as status, apph.statusid as statusid, app.multiple as multiple, app.multiple_appsysid as multipleID
, app.HOT_LEAD, app.GRADE as CREDIT_GRADE, MAX(d.appointment) as dialog_appt, app.teamid as teamid
, COALESCE(du.firstname,'NOT') as user_firstname, COALESCE(du.lastname,'ASSIGNED') as user_lastname,
app.LEAD_CHANNEL as fmiloan, app.third_party_id as brokerID, COALESCE(bc.dba,bc.COMPANY) as company
, ABS(apph.areaid - :b2) as areasort
FROM
apppipelines app,
app_handler apph
LEFT OUTER JOIN app_user_current appuc ON (apph.areaid = appuc.areaid AND apph.appsysid = appuc.appsysid)
LEFT OUTER JOIN dfc_user du ON (appuc.userid = du.userid)
, dialogs d, loan_borrower lb, loan_address ad, dfc_appstage apps, dfc_appstatus status
, broker_company bc
WHERE
app.appsysid = d.appsysid
and d.complete = 0
and d.addressedto = :b1

and app.appsysid = apph.appsysid


and app.appsysid = lb.appsysid
and lb.borrower = 1
and lb.borrower_type = 0
and apph.stageid = apps.stageid
and apph.areaid = apps.areaid
and app.appsysid = ad.appsysid
and apph.stageid = status.stageid
and apph.statusid = status.statusid
and apph.areaid = status.areaid
and app.addressid = ad.addressid
and bc.third_party_id = app.third_party_id
GROUP BY app.regionid, app.sourceid,
app.appsysid, apph.stageid,
lb.firstname, lb.lastname, lb.ssnumber,
ad.address1, ad.address2, ad.city, ad.state, ad.zip, apps.stage,
app.DateCreated, app.LAST_MODIFIED,lb.homephone, app.credit_report, app.duplicate, app.duplicate_appsysid
, status.status, apph.statusid, app.multiple, app.multiple_appsysid
, app.HOT_LEAD, app.GRADE, app.teamid
, du.firstname, du.lastname, app.LEAD_CHANNEL, app.third_party_id
, bc.dba, bc.company
, apph.areaid

UNION

SELECT
app.appsysid as appsysid,
-1 as brkmsg, -1 as followup, -1 as note,
-1 as sysmsg, SYSDATE as followupdate, SYSDATE as dialogdate,
apph.stageid as stageid, app.sourceid,
lb.firstname as bor_firstname, lb.lastname as bor_lastname, lb.ssnumber as bor_ssn,
ad.address1 as address1, ad.address2 as address2, ad.city as city, ad.state as state, ad.zip as zip,
apps.stage as stage, lb.homephone,
app.regionid as regionid, app.DateCreated as DateCreated, app.LAST_MODIFIED as DateModified,
app.credit_report as credit, app.duplicate as duplicate, app.duplicate_appsysid as duplicateID

, status.status as status, apph.statusid as statusid, app.multiple as multiple, app.multiple_appsysid as multipleID

, app.HOT_LEAD, app.GRADE as CREDIT_GRADE, -1 as dialog_appt, app.teamid as teamid
, du.firstname as user_firstname, du.lastname as user_lastname,
app.LEAD_CHANNEL as fmiloan, app.third_party_id as brokerID,
COALESCE(bc.dba,bc.company)
, ABS(apph.areaid - :b2) as areasort

FROM
apppipelines app, app_handler apph, app_user_current appuc,
loan_borrower lb, loan_address ad, dfc_appstage apps, dfc_appstatus status
, dfc_user du, broker_company bc

WHERE
app.appsysid = apph.appsysid
and app.appsysid = appuc.appsysid
and apph.areaid = :b2
and appuc.userid = :b1
and apph.complete = 0
and apph.stageid <= :b3

and apph.stageid = status.stageid
and apph.statusid = status.statusid
and apph.areaid = status.areaid


and app.appsysid = lb.appsysid
and lb.borrower = 1
and lb.borrower_type = 0
and apph.stageid = apps.stageid
and apph.areaid = apps.areaid
and app.appsysid = ad.appsysid
and app.addressid = ad.addressid
-- additional constraints
and appuc.userid = du.userid
and bc.third_party_id = app.third_party_id


ORDER BY appsysid, sysmsg DESC, dialog_appt DESC, areasort ASC;

[SANJAY_G]

wow.. such a complex query. I gave up reading after first 4 lines.

[abhaysk]

Originally posted by DeltaFun
Thanks to all for your input. here is one of the query:

I have briefly gon thru ur query....i dont think its properly joined...
i dunoo ur logic ...but can suerly tell the all tables wud go for full table scan..


is index built on columns appsysid,areaid??????
coz those are the only column i can see to join with other tables and not all tables are joined with one another.. i mean thers no link betn som of the tables(dfc_user & broker_company)....i wonder u r getiing expected result out of tha query..

Abhay.

[ggnanaraj]

Originally posted by DeltaFun
I said it's his bad sql which slows down the performance.
I'd like to know, what would you do if you were in my shoes? What kind of evidence should I provide to the management

You can also use the following code the single out the queries causing problem:

SQL QUERY RESPONSIBLE FOR MOST DISK READS (PROBLEMATIC QUERY):
===============================================================

SELECT disk_reads, executions, disk_reads/executions, sql_text FROM v$sqlarea WHERE disk_reads > 5000 ORDER BY disk_reads;

to identify the SQL responsible for the most buffer hits (PROBLEMATIC QUERY):
========================================================

SELECT buffer_gets, executions, buffer_gets/executions, sql_text FROM v$sqlarea WHERE buffer_gets > 100000 ORDER BY buffer_gets;

HTH.

[DaPi]

Originally posted by abhaysk
not all tables are joined with one another.. i mean thers no link betn som of the tables(dfc_user & broker_company)....

If that is true (it seems to be, but I don't have the same degree of patience as abhaysk to check!) then use explain plan and look for the word "Cartesian". The GROUP BY might be hiding the fact that you are getting a massive Cartesian product (using FTS) at an intermediary stage, even if the final results are just a few rows.

It will take time, but if you want to turn round the situation you will have to help the developers, to get them on you side (or get Mr.Hanky to come over an shoot them all ). Do they have the knowledge and abilty to do their own explain plans? Even developers should have some pride in doing a good job.

[DeltaFun]

No, the developers obviously don't know how to tune their queries. The codes were written by the lead developer. He is trying to defend himself. He always blames the db engine and hardware. I need to show him the difference before the tuning and after the tuning. I know what to do now to turn around the situation. Thanks all for your helpful input.