Forum Stats

  • 3,728,699 Users
  • 2,245,675 Discussions
  • 7,853,706 Comments

Discussions

Adding an index to WWV_FLOW_DATA

Tim Roberts
Tim Roberts Member Posts: 4 Red Ribbon
edited September 2018 in APEX Discussions

G'day chaps,

Based on the top SQLs on AWR, one of our DBAs examined WWV_FLOW_DATA (which is one of the most heavily used tables in our Apex based system) in order to see if there are things to improve. They found the followings:


1. Below is the one of Top SQLs. (this is the actual top query)

--7taas77dsqh3z


SELECT ITEM_NAME, ITEM_VALUE_VC2, ITEM_VALUE_CLOB, ITEM_ID, NVL(ITEM_FILTER, 'N'), NVL(IS_ENCRYPTED, 'N')

FROM WWV_FLOW_DATA WHERE ( FLOW_ID = :B3 OR ITEM_ID < 0 ) AND FLOW_INSTANCE = :B2 AND ITEM_NAME IS NOT NULL AND ITEM_NAME NOT LIKE 'D:%' AND ITEM_NAME <> :B1

With the following index, the elapsed time of query above would be reduced:

CREATE INDEX APEX_050100.WWV_FLOW_DATA_IDX2 ON APEX_050100.WWV_FLOW_DATA

(FLOW_ID,FLOW_INSTANCE)

TABLESPACE APEX;

2. The following update/delete statements are also heavily used:

-- gfj1f8y1xwdj4

UPDATE WWV_FLOW_DATA SET ITEM_VALUE_VC2 = :B5 , ITEM_VALUE_CLOB = :B4 , SESSION_STATE_STATUS = DECODE(SESSION_STATE_STATUS,'R','I','U'), IS_ENCRYPTED = :B3

WHERE FLOW_INSTANCE = :B2 AND ITEM_ID = :B1 ;

-- 47s8s1zw21nff

DELETE FROM WWV_FLOW_DATA WHERE FLOW_INSTANCE = :B2 AND ITEM_ID = :B1

As index WWV_FLOW_DATA_PK (FLOW_INSTANCE, ITEM_ID) is used as PRIMARY KEY (PRIMARY KEY constraint enabled) but the index has been created without UNIQUE , index range scan is seen on the Execution Plan rather than index unique scan which, we believe, requires more effort.

SQL> select table_name,index_name,uniqueness from dba_indexes where index_name = 'WWV_FLOW_DATA_PK';

TABLE_NAME           INDEX_NAME           UNIQUENES

-------------------- -------------------- ---------

WWV_FLOW_DATA        WWV_FLOW_DATA_PK     NONUNIQUE

SQL> select constraint_name, constraint_type,table_name,status from dba_constraints where constraint_name = 'WWV_FLOW_DATA_PK';  2

CONSTRAINT_NAME      CONSTRAINT_TYPE      TABLE_NAME           STATUS

-------------------- -------------------- -------------------- --------

WWV_FLOW_DATA_PK     P                    WWV_FLOW_DATA        ENABLED

No duplicate rows found: (no row returned from the query below)

select FLOW_INSTANCE,ITEM_ID, count(*)

from WWV_FLOW_DATA

group by FLOW_INSTANCE,ITEM_ID

having count(*) > 1;

I guess the discussion is whether creating a new index...

CREATE INDEX APEX_050100.WWV_FLOW_DATA_IDX2

ON APEX_050100.WWV_FLOW_DATA(FLOW_ID,FLOW_INSTANCE)

TABLESPACE APEX;

... and recreating index 'WWV_FLOW_DATA_PK' is advisable.

Tagged:
Scott WesleyTim Roberts

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 19,567 Black Diamond
    edited August 2018
    Tim Roberts wrote:I guess the discussion is whether creating a new index...CREATE INDEX APEX_050100.WWV_FLOW_DATA_IDX2ON APEX_050100.WWV_FLOW_DATA(FLOW_ID,FLOW_INSTANCE)TABLESPACE APEX;... and recreating index 'WWV_FLOW_DATA_PK' is advisable.

    In a word, no...

    Scott Wesley
  • Christian Neumueller-Oracle
    Christian Neumueller-Oracle Member Posts: 844 Employee
    edited August 2018

    Hi Tim,

    thanks for the suggestions. I filed bug #28581425. Your DBA is probably right that the PK should not be unique. I am hesitant to add a new index, though, because it slows down DML. Perhaps I will add flow_id as 3rd column, so the index range scan can already exclude records for other applications.

    Nobody can stop you or your DBA from making changes, but see the link in the other answer for reasons not to do that. Btw, just because something shows up in AWR does not mean that it's an issue. For example, there is no reason to tune based on AWR output if the system is almost idle. You might have actual issues with statements, of course. In such a case, please open a support request and upload the AWR for us to analyze and make APEX better for everyone.

    Regards,

    Christian

  • Tim Roberts
    Tim Roberts Member Posts: 4 Red Ribbon
    edited September 2018

    Thanks Christian,

    We've logged SR #3-18199468541.

  • Christian Neumueller-Oracle
    Christian Neumueller-Oracle Member Posts: 844 Employee
    edited September 2018

    Hi Tim,

    thank you. If you haven't already, can you please upload an AWR report and tell support that development has filed bug #28581425 for this issue?

    Regards,

    Christian

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited September 2018
    Based on the top SQLs on AWR, one of our DBAs examined WWV_FLOW_DATA (which is one of the most heavily used tables in our Apex based system) in order to see if there are things to improve. They found the followings:1. Below is the one of Top SQLs. (this is the actual top query)--7taas77dsqh3z SELECT ITEM_NAME, ITEM_VALUE_VC2, ITEM_VALUE_CLOB, ITEM_ID, NVL(ITEM_FILTER, 'N'), NVL(IS_ENCRYPTED, 'N')FROM WWV_FLOW_DATA WHERE ( FLOW_ID = :B3 OR ITEM_ID < 0 ) AND FLOW_INSTANCE = :B2 AND ITEM_NAME IS NOT NULL AND ITEM_NAME NOT LIKE 'D:%' AND ITEM_NAME <> :B1With the following index, the elapsed time of query above would be reduced:CREATE INDEX APEX_050100.WWV_FLOW_DATA_IDX2 ON APEX_050100.WWV_FLOW_DATA(FLOW_ID,FLOW_INSTANCE)TABLESPACE APEX;

    The plan I have for that query on my teeny tiny XE APEX uses the WWV_FLOW_DATA_PK for this, it only accesses on FLOW_INSTANCE as that's the only thing it can be used for. The worst case scenario on my instance is: read 25 rows. Presumably you have a case where this index causes you to read many more rows?

    The index you are suggesting would require the CBO to split the query up into 2 parts to actually be useful:

    select ..

    from WWV_FLOW_DATA WHERE ( FLOW_ID = :B3 OR ITEM_ID < 0 ) AND FLOW_INSTANCE = :B2 AND ITEM_NAME IS NOT NULL AND ITEM_NAME NOT LIKE 'D:%' AND ITEM_NAME <> :B1

    union

    select ..

    from WWV_FLOW_DATA WHERE ( FLOW_ID = :B3 OR ITEM_ID < 0 ) AND FLOW_INSTANCE = :B2 AND ITEM_NAME IS NOT NULL AND ITEM_NAME NOT LIKE 'D:%' AND ITEM_NAME <> :B1

    Looking at the data in my instance (again, yours will be different) for the best case improvement to the worse case scenario, the first part of that will require visiting 5 rows from the table, the second will need to read 2 rows from the table.

    So it may look like for the worst case scenario you can do 1/4 of the work... not quite. Those rows are half the story, they will be stored in blocks and are probably quite nicely packed together. My 25 rows are within 2 blocks, the reduced 7 rows are spread among the same 2 blocks - so I don't really gain any performance. Add that to the work done in reading the additional index, say 2 extra blocks and BAM this way is slower. Of course, these are TINY amounts of work - the only reason this query could be giving you such a hard time is if you have huge amounts of rows sharing the same flow_instance, or the table is massively scattered (unlikely) or a session is just calling it over and over with no chance of stopping. I'd be interested in seeing the results of:

    select * from (select flow_id, flow_instance, count(*), count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from apex_050100.WWV_FLOW_DATA group by flow_id, flow_instance order by count(*) desc) where rownum <5

    /

    select * from (select flow_instance, count(*), count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from apex_050100.WWV_FLOW_DATA group by flow_instance order by count(*) desc) where rownum <5

    /

    select * from (select flow_instance, count(*), count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from apex_050100.WWV_FLOW_DATA where item_id < 4 group by flow_instance order by count(*) desc) where rownum <5

    /

    2. The following update/delete statements are also heavily used:-- gfj1f8y1xwdj4UPDATE WWV_FLOW_DATA SET ITEM_VALUE_VC2 = :B5 , ITEM_VALUE_CLOB = :B4 , SESSION_STATE_STATUS = DECODE(SESSION_STATE_STATUS,'R','I','U'), IS_ENCRYPTED = :B3WHERE FLOW_INSTANCE = :B2 AND ITEM_ID = :B1 ; -- 47s8s1zw21nffDELETE FROM WWV_FLOW_DATA WHERE FLOW_INSTANCE = :B2 AND ITEM_ID = :B1As index WWV_FLOW_DATA_PK (FLOW_INSTANCE, ITEM_ID) is used as PRIMARY KEY (PRIMARY KEY constraint enabled) but the index has been created without UNIQUE , index range scan is seen on the Execution Plan rather than index unique scan which, we believe, requires more effort.

    The extra effort is about 1 logical IO per 200 logical IOs. You are never going to notice that.

    I guess the discussion is whether creating a new index...CREATE INDEX APEX_050100.WWV_FLOW_DATA_IDX2ON APEX_050100.WWV_FLOW_DATA(FLOW_ID,FLOW_INSTANCE)TABLESPACE APEX;... and recreating index 'WWV_FLOW_DATA_PK' is advisable

    I doubt it.

    Do you really have a performance problem? The number one rule is to make sure you scope out your diagnostics properly -make sure you are only looking at the process that you have a performance problem with. If it's a slow page then use dbms_monitor.serv_mod_act_trace_enable to enable extended SQL tracing on that page load (temporally), use tkprof to analyze the trace file and review where the time is spent. I would put good money (1 pint) that these statements aren't even in the top 5.

  • Tim Roberts
    Tim Roberts Member Posts: 4 Red Ribbon
    edited September 2018

    Hey Andrew,

    See below for results...

    1.png2.png

    3.png

  • Tim Roberts
    Tim Roberts Member Posts: 4 Red Ribbon
    edited September 2018

    Will do buddy, thanks for investigating.

  • Jean Choi
    Jean Choi Member Posts: 6 Red Ribbon
    edited September 2018

    Here is the result at 2:30 PM  -  (total record count is more than 1.4 million)

    pastedImage_0.png

    pastedImage_1.png

    pastedImage_2.png

    pastedImage_3.png

    Tim Roberts
  • Jean Choi
    Jean Choi Member Posts: 6 Red Ribbon
    edited September 2018

    Hi Andrew,

    Regarding the non unique PK,

    >>The extra effort is about 1 logical IO per 200 logical IOs. You are never going to notice that.

    Agree.

    But we have more than 400,000 executions within a hour for the UPDATE, more than 100,000 executions for SELECT and 30,000 DELETEs within a hour.

    All 3 statements use the WWV_FLOW_DATA_PK

    ( I have experience before: sending millions SMS using non-unique index takes 1.5-2 times than using PK (unique - not null) index )

    We have performance problem every Monday.

    Thanks,

    Jean.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited September 2018

    (I’ve just noticed I said to use < 4 in that query, I meant < 0 But this won’t matter)

    These numbers still look fairly small, sure you’d save a handful of logical IO (these blocks are almost certainly cached and ready for you) but none of your users will really notice. I’m talking 0.0006 seconds for one execution (probably much less).

    Of course, I am assuming that nothing “bad” has happened to make the current index choice much worse than it should be (we should review a tkprof of a query if we wanted to see that). And I’m assuming you are doing the same index plan I’m seeing on my system - if not then just fixing that execution plan will give you good results. Have you checked out the execution plan (review https://ctandrewsayer.wordpress.com/2017/03/21/4-easy-lessons-to-enhance-your-performance-diagnostics/  for information about how to do that)

    Even later on, it doesn’t look like youd get any noticeable improvements.

    Regarding the unique index, the difference for one hour would be about 500,000/200 logical IOs = 2,500 logical IO spread over an hour... that’ll give you nothing (on the order of 250,000 nanoseconds)

    As I said before, if you have a slow process that needs to be faster then look at where the time is going while it’s being slow. Extended sql trace makes this incredibly easy, takes out the guess work and is completely supported. Once you’ve identified where the time is going (if you aggregate and sort by elapsed time for the tkprof report, it’ll show you the top offenders up front), figuring out what to do becomes a methodical process that can easily be tested.

    It’s unlikely that an APEX query on an APEX table is responsible for any slow period, its 99.9% of the time going to be your own application SQL or something slowing down the network connection (Remember AWR doesn’t look at idle time).

  • Jean Choi
    Jean Choi Member Posts: 6 Red Ribbon
    edited September 2018

    Hi Andrew, Thanks for the comment.

    Here is my concern:

    Regarding the PK,

    The Plan is quite simple for update/delete, which is the Index scan WWV_FLOW_DATA_PK but I was surprised as I saw the index range scan rather index unique scan that I expected.

    I agree there won't be much performance difference whether it is index range scan or unique scan in normal circumstance.

    Hope any tiny improvement may help during our peak time. Again, we don't have any performance issue for 99% of time but we are in trouble during the peak time..so we have to try to do whatever we can reduce CPU usage.

    Regarding adding index for the query below:

    --7taas77dsqh3z

    SELECT ITEM_NAME, ITEM_VALUE_VC2, ITEM_VALUE_CLOB, ITEM_ID, NVL(ITEM_FILTER, 'N'), NVL(IS_ENCRYPTED, 'N')

    FROM WWV_FLOW_DATA WHERE ( FLOW_ID = :B3 OR ITEM_ID < 0 ) AND FLOW_INSTANCE = :B2 AND ITEM_NAME IS NOT NULL AND ITEM_NAME NOT LIKE 'D:%' AND ITEM_NAME <> :B1

    pastedImage_2.png

    The SQL is one of top SQL and the average elapsed time of the query is 0.11 second, which is, I think quite big number as the number of executions is more than 100,000 within a hour. That's why we are trying to improve this query.

    The row count of the table changes from 40,000 to 1,700,000 within a day, response time may vary depending on an input and no performance issue during normal hours so not sure if a single tkprof output helps on this.

    Hope you understand our situation.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited September 2018

    0.11 seconds to read less than 300 buffers from the cache is slower than one would expect, either:

    The plan is different to what I think it is, you haven’t shared it yet though...

    Youre doing physical IO, which should be unlikely as these rows would be recently inserted, if there’s physical IO then your buffer cache probably isn’t very big

    But really, 0.11 seconds is no time for a page load, if you do it multiple times for one page load then that would be another story - but in those cases you’d expect the data to be very much cached.

    I recommended before enabling tracing for the page load during the bad performance period using dbms_monitor, all you need to do is turn it on during the bad time, wait a few seconds until you have enough sample traces that could have been due to suffering sessions then turn it off. The trace data will show you everything -

    If you call this sql many times in a page load

    If this sql is doing physical IO (and where)

    If some other query is being run

    If the time on the DB for the page load is small enough to rule it out

    Once you‘ve got this information, it will be so much easier to say confidently whether any changes will really help.

    I did guess earlier that your cache could be too small and you could check, but you should get the tkprof out first to confirm the problem is related to physical IO where it shouldn’t be. Otherwise you are just working through lists of guesses, that is the most usual waste of HUMAN time (and company money) when performance tuning. As soon as you have the tkprof of a slow page load to point you in the correct direction, work required to address the problem goes down to a fixed small amount of time. Most importantly you’ll be able to say (with confidence): “If we do this, then this will be X times faster so overal process Y will be Z times faster, that is acceptable by our SLA so I can now move on and do something else“

  • Jean Choi
    Jean Choi Member Posts: 6 Red Ribbon
    edited September 2018

    Here is some info while we had performance issue on Monday 10-12.

    7taas77dsqh3z (SELECT ITEM_NAME, ITEM_VALUE_VC2 ...)  Avg response time : 0.17 sec and 700 buffer gets - no physical IO

    pastedImage_2.png

    Plan and Plan Statistics:  -- No disk read observed

    pastedImage_2.png

    pastedImage_1.png

    We added the index on our Test DB: (CREATE INDEX APEX_050100.WWV_FLOW_DATA_IDX2 ON APEX_050100.WWV_FLOW_DATA (FLOW_ID,FLOW_INSTANCE) TABLESPACE APEX;)

    Here is the new plan on Test DB:

    pastedImage_0.png

    pastedImage_1.png

  • Jean Choi
    Jean Choi Member Posts: 6 Red Ribbon
    edited September 2018

    Regarding adding the index on WWV_FLOW_DATA, I collected a trace file on TEST environment. Here is the output of tkprof:

    Any advise on adding the index would be much appreciated.

    Before adding the index:

    --------------------------------------------------------------------------

    SQL ID: 7taas77dsqh3z Plan Hash: 3446182478

    SELECT ITEM_NAME, ITEM_VALUE_VC2, ITEM_VALUE_CLOB, ITEM_ID, NVL(ITEM_FILTER,'N'), NVL(IS_ENCRYPTED,'N')

    FROM

    WWV_FLOW_DATA WHERE ( FLOW_ID = :B3 OR ITEM_ID < 0 ) AND FLOW_INSTANCE = :B2

      AND ITEM_NAME IS NOT NULL AND ITEM_NAME NOT LIKE 'D:%' AND ITEM_NAME <> :B1

    call     count       cpu    elapsed       disk      query    current        rows

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    Parse        1      0.00       0.00          0          0          0           0

    Execute      1      0.00       0.00          0          1          0           0

    Fetch        1      0.00       0.00          0       1053          0         172

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    total        3      0.00       0.00          0       1054          0         172

    Rows (1st) Rows (avg) Rows (max)  Row Source Operation

    ---------- ---------- ----------  ---------------------------------------------------

           172        172        172  CONCATENATION  (cr=1053 pr=0 pw=0 time=1005 us)

             0          0          0   TABLE ACCESS BY INDEX ROWID BATCHED WWV_FLOW_DATA (cr=5 pr=0 pw=0 time=34 us cost=4 size=56 card=1)

             3          3          3    INDEX RANGE SCAN WWV_FLOW_DATA_PK (cr=3 pr=0 pw=0 time=19 us cost=3 size=0 card=1)(object id 183040)

           172        172        172   TABLE ACCESS BY INDEX ROWID BATCHED WWV_FLOW_DATA (cr=1048 pr=0 pw=0 time=449 us cost=14 size=728 card=13)

           552        552        552    INDEX RANGE SCAN WWV_FLOW_DATA_IDX1 (cr=1031 pr=0 pw=0 time=789 us cost=7 size=0 card=239)(object id 183041)

    After adding the index:

    --------------------------------------------------------------------------

    SQL ID: 7taas77dsqh3z Plan Hash: 519958527

    SELECT ITEM_NAME, ITEM_VALUE_VC2, ITEM_VALUE_CLOB, ITEM_ID, NVL(ITEM_FILTER,'N'), NVL(IS_ENCRYPTED,'N')

    FROM

    WWV_FLOW_DATA WHERE ( FLOW_ID = :B3 OR ITEM_ID < 0 ) AND FLOW_INSTANCE = :B2

      AND ITEM_NAME IS NOT NULL AND ITEM_NAME NOT LIKE 'D:%' AND ITEM_NAME <> :B1

    call     count       cpu    elapsed       disk      query    current        rows

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    Parse        1      0.00       0.00          0          0          0           0

    Execute      1      0.00       0.00          0          0          0           0

    Fetch        1      0.00       0.00          0         15          0         172

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    total        3      0.00       0.00          0         15          0         172

    Rows (1st) Rows (avg) Rows (max)  Row Source Operation

    ---------- ---------- ----------  ---------------------------------------------------

           172        172        172  CONCATENATION  (cr=15 pr=0 pw=0 time=1094 us)

             0          0          0   TABLE ACCESS BY INDEX ROWID BATCHED WWV_FLOW_DATA (cr=4 pr=0 pw=0 time=39 us cost=3 size=56 card=1)

             3          3          3    INDEX RANGE SCAN WWV_FLOW_DATA_PK2 (cr=2 pr=0 pw=0 time=17 us cost=2 size=0 card=1)(object id 214020)

           172        172        172   TABLE ACCESS BY INDEX ROWID BATCHED WWV_FLOW_DATA (cr=11 pr=0 pw=0 time=617 us cost=2 size=728 card=13)

           186        186        186    INDEX RANGE SCAN WWV_FLOW_DATA_IDX2 (cr=3 pr=0 pw=0 time=378 us cost=1 size=0 card=15)(object id 214015)

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited September 2018

    The old plan on your test instance is different to the plan you are getting on your production instance, but it is the one I expected to see. The tkprof tells us that the only difference between using the new index and the existing one is amount of physical reads, the amount of logical reads was exactly the same - this just tells us that the new index was more cached for the data required. I would be quite sure that for real world use, with a reasonably sized cache, you wouldn’t see those physical reads.

    This test was not fair as your test instance is for some reason different to production. As I stated before, one of the possible reasons your statement was slow in production under high load was because it isn’t doing the execution plan I expected. The plan you are getting involved quite intensive cpu operations of bitmap conversion from rowid. It’s chosen this plan because the statistics tell it it’s a good idea. My suggestion would be to get it to use the same plan as you were seeing on test. For this you’ll need to figure out why the plan is the way it is, replicate it in test, figure out what to do to change it in test then do the same for production.

    As this is most likely a statistics thing, I suggest exporting your table+index statistics for this table from production (see the dbms_stats package docs for your Oracle DB version) and importing them to your test DB. Do you now get the same bitmap conversiony plan? If so then you would then move on to how to address it -

    )You could see if gathering statistics on the production table (with cascade set to true) does the job. In order to test this you would have to not apply the statistics to the production instance, you would either gather the statistics as pending and then export/import, or spin up an instance with production like data volumes and test it there.

    )You could use sql plan management and force the execution of this sql to always use a fixed plan - this will involve creating a baseline on the existing child cursor, forcing the plan for this sql with hints in your own session, associating that execution plan with the baseline you created, switching the attributes of the new plan so that it is used and the old plan so that it is not used.

    )You could modify a hidden parameter which turns off this bitmap conversion from rowid transformation, this would either be for the whole system or just for the APEX user (which you might be able to set up in the admin screens, or create a login trigger that fires for it)

    All of this will want careful testing. You’re in a more difficult spot because your test instance does not represent your production instance fairly.

  • Jean Choi
    Jean Choi Member Posts: 6 Red Ribbon
    edited September 2018

    Thanks for the comments.

    As long as I know, there is no physical read on both tkprof outputs. disk : 0 (pr=0) but numbers of logical read are different : 15 vs 1053 (cr=15 vs 1053) and the returned rows is the same : 172.

    Please double check.

    I understand the test environment is different and not easy to have similar result as Prod environment but just want to show you something with the new index.

  • fac586
    fac586 Senior Technical Architect Member Posts: 19,567 Black Diamond
    edited September 2018
    Tim Roberts wrote:Based on the top SQLs on AWR, one of our DBAs examined WWV_FLOW_DATA (which is one of the most heavily used tables in our Apex based system) in order to see if there are things to improve.

    This problem is similar to a previously common question on this forum, which was also usually posted based on AWR reports. Just because AWR identifies a query or piece of code as being executed frequently, it doesn't follow that the solution to any perceived performance problem is to tune that piece of code to death. It may simply be a pointer to a problem that exists elsewhere.

    Jean Choi wrote:But we have more than 400,000 executions within a hour for the UPDATE, more than 100,000 executions for SELECT and 30,000 DELETEs within a hour.All 3 statements use the WWV_FLOW_DATA_PK

    Before thinking about adding an index to speed up these accesses (or as Christian points out, potentially slow them down due to the overhead of maintaining it) I would want to understand what the applications are doing that involves an average of 110 changes to session state per second over that hour. That doesn't sound like human beings using data entry forms (unless there are a lot of users and forms with lots of items).

    See for a recent example. Note their findings:

    Apparently bad code, redundant and superfluous information in dynamic action in submitting page items, more number of dynamic actions unconditionally calling same page load code after submit page caused bulk of the problem

    All of those issues might contribute to unnecessary updating of session state values.

    ( I have experience before: sending millions SMS using non-unique index takes 1.5-2 times than using PK (unique - not null) index )

    Why would this be comparable to maintaining APEX session state values?

    We have performance problem every Monday.

    So find out what is actually happening every Monday. Use the APEX_WORKSPACE_ACTIVITY_LOG to identify which users, applications and pages are active during the performance bottleneck. Run these pages in debug mode to identify what they are actually doing. Give the application(s) a health check using the Advisor. Review the application(s) for inefficient code and design patterns.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited September 2018
    Jean Choi wrote:Thanks for the comments.As long as I know, there is no physical read on both tkprof outputs. disk : 0 (pr=0) but numbers of logical read are different : 15 vs 1053 (cr=15 vs 1053) and the returned rows is the same : 172. Please double check.I understand the test environment is different and not easy to have similar result as Prod environment but just want to show you something with the new index.

    Apologies, I was reading the numbers on my phone screen and must not have followed the table right. 

    If you look at the time taken, you'll see that using the new index was actually a little slower.

    My guess is that the original plan you had in your testing environment would be perfectly fine in production.

    Do note what fac586 wrote, this query is being hit a TON during that report period, might be worth figuring out why - the extended sql trace will show you what other things are happening in between hits to this query but it will be quite a long read, you might find googling the query will give you clues to what structures on your pages will require this query to run and how often.

This discussion has been closed.