5 Replies Latest reply: Jan 17, 2014 10:44 AM by Jocelyn Côté RSS

    Open Calendar page - perfomance issue

    Jocelyn Côté

      Hi,

      I've created a Calendar page based on a single table.

      The performance decreased by increasing the volume of data in a the current month: when the page opened at the first time, by default it loads all the data for the current month.

      Is there a manner to open a Calendar page and loading data of the current day or week instead of the current month?

      How could we improve the performance for this page?

      Regards

        • 1. Re: Open Calendar page - perfomance issue
          fac586

          Jocelyn Côté wrote:

           

          I've created a Calendar page based on a single table.

          The performance decreased by increasing the volume of data in a the current month: when the page opened at the first time, by default it loads all the data for the current month.

          Is there a manner to open a Calendar page and loading data of the current day or week instead of the current month?

          How could we improve the performance for this page?

          See

          • 2. Re: Open Calendar page - perfomance issue
            Jocelyn Côté

            Other things,

            1. the GATHER STATS for all schemas is already done
            2. the query used by the Calendar page is not the issue, it was already tested on SQL*Plus and it performs very well
            • 3. Re: Open Calendar page - perfomance issue
              TexasApexDeveloper

              Without more information it's kinda hard to help.. APEX Version? Database version? Table definition? Sample Data?

               

              Thank you,

               

              Tony Miller

              LuvMuffin Software

              Ruckersville, VA

              • 4. Re: Open Calendar page - perfomance issue
                Jocelyn Côté

                Hi Tony,

                APEX Version 4.2.3

                DB Version 11g XE 11.2.0.2.0

                Here is the table definition:

                create table KS_AGENDAS

                (

                  agenda_id          NUMBER not null,

                  entraineur_id      NUMBER not null,

                  client_id          NUMBER,

                  facture_service_id NUMBER,

                  duree              NUMBER,

                  planifie_le        DATE not null,

                  confirme           VARCHAR2(1) not null,

                  description        VARCHAR2(200),

                  type_agenda        VARCHAR2(1),

                  centre_sportif_id  NUMBER not null,

                  created_by         VARCHAR2(100),

                  creation_date      DATE,

                  last_updated_by    VARCHAR2(100),

                  last_update_date   DATE

                )

                tablespace KSUSER

                  pctfree 10

                  initrans 1

                  maxtrans 255

                  storage

                  (

                    initial 64K

                    next 1M

                    minextents 1

                    maxextents unlimited

                  );

                 

                Here is a sample of the data:

                AGENDA_ID: 818

                ENTRAINEUR_ID: 66

                CLIENT_ID: 102

                FACTURE_SERVICE_ID: 347

                DUREE: 60

                PLANIFIE_LE: 2014-01-15 16:01:00

                CONFIRME: O

                DESCRIPTION:

                DESF18 - Fabien Desbiens (Supervision d'entraînement)

                 

                TYPE_AGENDA: S

                CENTRE_SPORTIF_ID: 21

                CREATED_BY: KINE

                CREATION_DATE: 2014-01-15 02:56:07

                LAST_UPDATED_BY: KINE

                LAST_UPDATE_DATE: 2014-01-15 02:58:01

                • 5. Re: Open Calendar page - perfomance issue
                  Jocelyn Côté

                  Other things,

                  When I monitor a session waiting for the Calendar page, here is the SQL Text where the database session performing during 30 - 40 seconds:

                   

                  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;

                  end if;

                  :rc__ := rc__;

                  end;