Professional Web Applications Themes

Very Strange - Anyone Seen This - Oracle Performance - Coldfusion Database Access

When running this query in CF 6 updater 4 on a production server, it takes about 100 seconds to get results. When the query is run in sqlplus on the same CF server it takes about 5 seconds, even after flushing oracle buffer cache. I've tried different types of datasources, which had no effect. Any ideas? Thanks in advance. SELECT to_char(m.prid) prid, m.dt_disp, m.locklevel_level, m.locklevel, m.parent_prid, u.unit_state, u.unit_state_id, u.unit_local_id, parent.ref_type ref_type_p, parent.dispid disp, parent.localid, DECODE(parent.ref_type, 1, 'EMS', 2, 'Hospital', 0, 'Other', NULL, 'Other') as reftype, DECODE(parent.ih,1,'Scene',2,'IH','') ih_name, DECODE(parent.ref_type, 1, (SELECT name FROM ems WHERE id=parent.ref_id), 2, (SELECT name FROM hospital ...

  1. #1

    Default Very Strange - Anyone Seen This - Oracle Performance

    When running this query in CF 6 updater 4 on a production server, it takes
    about 100 seconds to get results. When the query is run in sqlplus on the same
    CF server it takes about 5 seconds, even after flushing oracle buffer cache.
    I've tried different types of datasources, which had no effect. Any ideas?
    Thanks in advance.

    SELECT to_char(m.prid) prid, m.dt_disp, m.locklevel_level, m.locklevel,
    m.parent_prid,
    u.unit_state, u.unit_state_id, u.unit_local_id, parent.ref_type
    ref_type_p, parent.dispid disp, parent.localid,
    DECODE(parent.ref_type, 1, 'EMS', 2, 'Hospital', 0, 'Other', NULL,
    'Other') as reftype,
    DECODE(parent.ih,1,'Scene',2,'IH','') ih_name,
    DECODE(parent.ref_type,
    1, (SELECT name FROM ems WHERE id=parent.ref_id),
    2, (SELECT name FROM hospital WHERE hosp_id=parent.ref_id),
    (SELECT name FROM tb_prref_common WHERE id=parent.ref_id)) ref_name,
    DECODE(parent.rec_type,
    1, (SELECT name FROM ems WHERE id=parent.rec_id),
    2, (SELECT name FROM hospital WHERE hosp_id=parent.rec_id),
    (SELECT name FROM tb_prref_common WHERE id=parent.rec_id)) rec_name,
    tb_ref.name othername, tb_ref.addr1,
    PT_MAIN.lname as pt_lname, PT_MAIN.fname as pt_fname, pt_main.anon,
    tb_cat.name as cat_name, b.name as base_name,
    tb_proutcome.name outcome_name
    , -1 region_read_at, -1 req_status , services.service_name
    FROM PR_MAIN m, units u, pr_address tb_ref,
    PT_MAIN, tb_prcategory tb_cat, PR_MAIN parent, bases b,
    PR_LETTERS l, tb_proutcome
    , services
    WHERE m.parent_prid=parent.prid(+)
    AND m.prid=l.prid(+)
    AND parent.base_id=b.id(+)
    AND parent.unit_id = u.unit_id(+)
    AND parent.prid = tb_ref.prid(+)
    AND tb_ref.refrec(+)=1
    AND m.ptid = pt_main.ptid(+)
    AND parent.category=tb_cat.id(+)
    AND parent.outcome=tb_proutcome.id(+)
    AND m.service_id=services.service_id(+)
    AND UPPER(PT_MAIN.lname) LIKE UPPER('smith')
    AND m.service_id=21
    ORDER BY
    m.DT_DISP

    alanbk Guest

  2. #2

    Default Re: Very Strange - Anyone Seen This - Oracle Performance

    Try adding blockfactor="100" to your CFQUERY tag, as this parameter is the
    Maximum rows to get at a time from server. Range: 1 - 100.. I use stored
    procedures exclusively with my application that accesses Oracle, but the poor
    performance for returning large result sets using CFPROCRESULT caused me to
    have to set this same parameter to 100 in my CFSTOREDPROC tags. Since the
    default value of this optional parameter is "1", you may realize a much better
    throughput if you set it to the maximum value in your queries. I am unaware of
    any negative performance impacts for setting it to 100 for all of your queries.

    Phil

    paross1 Guest

  3. #3

    Default Re: Very Strange - Anyone Seen This - Oracle Performance

    Phil,

    The query returns 2 rows. I found blockfactor=100 yesterday, and tried it,
    but since only 2 rows are involved, that is not our problem. We are running
    oracle 10g (10.1.0.3) with RAC, 2 nodes. However we do see a delay with
    returning data when many rows are involved. So we will try the blockfactor to
    see if it helps.

    The performance difference really makes no sense at all. The db processes the
    query very fast, but in CF it takes so long. CF debugging was used to gather
    the timing for inside CF, and PL/SQL developer was used outside CF. I'll try
    the re-write as you suggest. We are at a loss here, any other ideas?

    Thanks,

    Alan


    alanbk Guest

  4. #4

    Default Re: Very Strange - Anyone Seen This - Oracle Performance

    Turned out that the problem was with using CF bind variables in the query.
    The UPPER and the bind variables would cause the oracle optimizer not to use
    the function based index, forcing full table scans. Not using CF bind
    vairiables for the oracle query solved the performace issue. This is probably
    due to an oracle bug.

    alanbk Guest

Similar Threads

  1. cfflash form and strange performance in IE 7
    By SilentBob'secretfusion in forum Coldfusion Flash Integration
    Replies: 0
    Last Post: March 4th, 06:37 PM
  2. ORACLE ON AIX PERFORMANCE PROBLEMS
    By agis in forum AIX
    Replies: 12
    Last Post: September 26th, 11:20 AM
  3. Strange Problem with perl-Oracle
    By alessandro.lagostena in forum PERL Modules
    Replies: 8
    Last Post: August 28th, 07:37 PM
  4. LAN connection has strange effect on computer performance
    By John in forum Windows Networking
    Replies: 0
    Last Post: July 15th, 09:35 PM
  5. Oracle 8.1.7.4 Performance Issues
    By Burkhard Kiesel in forum Oracle Server
    Replies: 4
    Last Post: December 16th, 10:55 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139