Forum Stats

  • 3,768,991 Users
  • 2,252,892 Discussions
  • 7,874,830 Comments

Discussions

Adding an index to WWV_FLOW_DATA

Tim Roberts
Tim Roberts Member Posts: 4 Red Ribbon
edited Sep 19, 2018 7:45AM 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
«1

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,090 Red Diamond
    edited Aug 28, 2018 6:13PM
    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: 846 Employee
    edited Aug 31, 2018 5:21AM

    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 Sep 5, 2018 12:36AM

    Thanks Christian,

    We've logged SR #3-18199468541.

  • Christian Neumueller-Oracle
    Christian Neumueller-Oracle Member Posts: 846 Employee
    edited Sep 5, 2018 5:29AM

    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 Sep 5, 2018 8:31AM
    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 Sep 5, 2018 5:40PM

    Hey Andrew,

    See below for results...

    1.png2.png

    3.png

  • Tim Roberts
    Tim Roberts Member Posts: 4 Red Ribbon
    edited Sep 5, 2018 5:41PM

    Will do buddy, thanks for investigating.

  • Jean Choi
    Jean Choi Member Posts: 6 Red Ribbon
    edited Sep 6, 2018 12:41AM

    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 Sep 6, 2018 12:59AM

    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 Sep 6, 2018 3:34AM

    (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).

This discussion has been closed.