Re: Apex performance

On Tuesday, July 8, 2014 6:45:35 PM UTC+1, joel garry wrote:
> On Tuesday, July 8, 2014 10:28:33 AM UTC-7, r..._at_neurozoid.com wrote:
>
>
>
> >
>
> > Hi, nah got rid of the hint. No difference, its not locking anything, just smashing the IO subsystem with those direct reads...weird stuff...
>
>
>
> No idea, but see http://www.freelists.org/post/oracle-l/READ-wait-events-when-inserting-data-into-a-CLOB and I have a vague memory of Tanel or Jonathan or someone posting something more on a blog about this type of issue. You may have to delve into tracing to see what apex is doing different.
>
>
>
> jg
>
> --
>
> _at_home.com is bogus.
>
> http://www.informationweek.in/informationweek/interviews/296936/oracle-srinivasan-rangaswami-importance-bi-analytics-commerce-players

Ran the trace. Just loads of direct read events against the LOB. I never let it finish (because I suspect it never would...)

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse      397      0.05       0.00          0          0          0           0
Execute    658      0.14       0.09        142        235        188          26
Fetch      759      0.18       0.90         49       2661        520         705
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1814      0.38       0.99        191       2896        708         731

Misses in library cache during parse: 9
Misses in library cache during execute: 7

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        4        0.00          0.00
  db file sequential read                        44        0.90          0.90
  db file scattered read                         19        0.00          0.00
  asynch descriptor resize                        3        0.00          0.00
  utl_file I/O                                   56        0.00          0.00
  direct path read                            13000        1.14        177.98

Looks like a ghastly bug to me. I'm going to do an even more ghastly work around...actually it will be a work around a work around...brilliant.

Just for laughs, here is oracle's apex code that is "executing" whilst all this is happening:

DECLARE
   rc__ NUMBER;
   simple_list__ OWA_UTIL.vc_arr;
   complex_list__ OWA_UTIL.vc_arr;
BEGIN
   OWA.init_cgi_env (:n__, :nm__, :v__);    HTP.htbuf_len := 63;
   NULL;
   NULL;

   simple_list__ (1) := 'sys.%';
   simple_list__ (2) := 'dbms\_%';
   simple_list__ (3) := 'utl\_%';
   simple_list__ (4) := 'owa\_%';
   simple_list__ (5) := 'owa.%';
   simple_list__ (6) := 'htp.%';
   simple_list__ (7) := 'htf.%';

   IF (   (wwv_flow_epg_include_modules.authorize ('f') = FALSE)
       OR (owa_match.match_pattern (p_string                 => 'f'
                                                                   /* */
           ,
                                    p_simple_pattern         => simple_list__,
                                    p_complex_pattern        => complex_list__,
                                    p_use_special_chars      => FALSE
                                   )
          )
      )
   THEN
      rc__ := 2;
   ELSE
      NULL;
      NULL;
      f (p => :p);

      IF (WPG_DOCLOAD.is_file_download)
      THEN
         rc__ := 1;
         WPG_DOCLOAD.get_download_file (:doc_info);
         NULL;
         NULL;
         NULL;
         COMMIT;
      ELSE
         rc__ := 0;
         NULL;
         NULL;
         NULL;
         COMMIT;
         OWA.get_page (:data__, :ndata__);
      END IF;