Very Strange - Anyone Seen This - Oracle Performance

[alanbk]

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

[paross1]

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

[alanbk]

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]

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.