Using 11.2.0.3.0 on unix sprac server, 8 cpus 32 cores 12TB storage. We have 16 batch servers doing inserts and ~100 users doing mostly queries.
We have a wierd issue, we have a long query that takes 30 seconds to run 1st time then 10-20 secs every other time. Based on knowledge of DB we expected it to take a lot less. Then a developer reformatted it by taking removing extra white space and line breaks. all of sudden query takes 6 secs first time and 0.8 every other time. we tripled checked to ensure they are identical and that only difference is white space and line breaks. We tried it with SQLDeveloper, TOAD, SQLPLUS, from out desktops, appserver. we could reproduce it every time, with white space 10-20 secs without it 0.8 secs. Query is 200 lines long with white space 30 without it.
Why would having white space make such a dramatic difference? because the query is longer eventhough its identical? We checked parsing times, etc, no difference. Is there some network setting, oracle parameter, sqlnet setting? We spent hours looking on google and found nother
Slow:
| SELECT grt_student.student_id | AS student_id, |
| grt_student.last_name | AS last_name, |
| grt_student.first_name | AS first_name, |
| grt_buyerinstance.buyerinstance_id | AS buyerinstance_id, |
| grt_buyerinstance.buyerfamily_id | AS buyerfamily_id, |
| grt_buyer.buyerfamily_acronym | AS buyerfamily_acronym, |
| grt_reporting_utls_pkg.convert_gmrt_battery(grt_buyerinstance.buyerfamily_id,grt_buyer.battery) | AS battery, |
fast:
| SELECT grt_student.student_id | AS student_id, grt_student.last_name | AS last_name, grt_student.first_name AS first_name, grt_buyerinstance.buyerinstance_id AS buyerinstance_id, grt_buyerinstance.buyerfamily_id | AS buyerfamily_id, |
grt_buyer.buyerfamily_acronym AS buyerfamily_acronym, grt_reporting_utls_pkg.convert_gmrt_battery(grt_buyerinstance.buyerfamily_id,grt_buyer.battery) AS battery,