DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: need help with fighting the dev team

  1. #1
    Join Date
    Feb 2003
    Location
    New York
    Posts
    75

    need help with fighting the dev team

    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.
    Still learning

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.
    Reddy,Sam

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Feb 2003
    Location
    New York
    Posts
    75
    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;
    Still learning

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    wow.. such a complex query. I gave up reading after first 4 lines.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    Re: need help with fighting the dev team

    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):
    ===============================================================
    Code:
    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):
    ========================================================
    Code:
    SELECT buffer_gets, executions, buffer_gets/executions, sql_text FROM v$sqlarea WHERE buffer_gets > 100000 ORDER BY buffer_gets;
    HTH.

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  10. #10
    Join Date
    Feb 2003
    Location
    New York
    Posts
    75
    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.
    Still learning

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width