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

Open Calendar page - perfomance issue

Jocelyn Côté Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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é Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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é Newbie
    Currently Being Moderated

    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é Newbie
    Currently Being Moderated

    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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points