Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-13345

Missing index on search,

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: CLOSED
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.5.0
    • Fix Version/s: 2.5.2, 2.6.0
    • Component/s: Search
    • Labels:
      None

      Description

      From Drew

      Hi Ian,

      Just a little change that can improve the performance of a query that related to searchbuilderitem:

      create index ISEARCHBUILDERITEM_STA_ACT on searchbuilderitem (SEARCHSTATE,SEARCHACTION);
      drop index ISEARCHBUILDERITEM_STA;

      Please see the details in the forwarded message.

      Thanks,
      Drew

      From: John Leasia <jleasia@umich.edu>
      Date: 8 April 2008 15:59:21 BDT
      To: Drew Zhu <qszhu@umich.edu>, CTools Ops <ctops@umich.edu>
      Subject: Re: A new index in production database

      Drew,
      You should let Ian Boston know - maybe this is something that should make it's way into the standard release.
      John

      Drew Zhu wrote:

      When analyzing last night's high CPU usage period, a search related query comes on the top of the awr report as the second highest CPU consumer. After analyzing the query, I think adding a composite index will help the performance of that query.
      create index ISEARCHBUILDERITEM_STA_ACT on searchbuilderitem (SEARCHSTATE,SEARCHACTION);
      drop index ISEARCHBUILDERITEM_STA;

      I tested different search conditions. With the new index in place, the execution plans change and the new index is used and the performance of that query improve from, depending on the search conditions, 5, 14, 20 seconds, to 0.1, 1, 3 seconds, respectively. So, I added the new index into production and drop one of the old indexes, ISEARCHBUILDERITEM_STA, which index the SEARCHSTATE, because the new index will cover the old one. See the execution plans for details.

      I have tested and implemented in CTLOAD also and I will make the changes in other databases as well.

      If you have any concerns or questions, please let me know.

      Thanks,
      Drew

      CPU Elapsed CPU per % Total
      Time (s) Time (s) Executions Exec (s) DB Time SQL Id
      ---------- ---------- ------------ ----------- ------- -------------
      13,094 13,096 1,692,291 0.01 45.6 fx67b3j5m3ura
      Module: JDBC Thin Client
      select AX.SESSION_ID,AX.SESSION_SERVER,AX.SESSION_USER,AX.SESSION_IP,AX.SESSION_
      USER_AGENT,AX.SESSION_START,AX.SESSION_END from SAKAI_SESSION AX inner join SAKA
      I_PRESENCE A ON AX.SESSION_ID = A.SESSION_ID where A.LOCATION_ID = :1

      1,262 1,265 223 5.66 4.4 fq6takf7q2qvn
      select count from searchbuilderitem where searchstate = :1 and searchaction <
      > :2

      1,192 1,217 18,264 0.07 4.2 8cqv4wzk2aby0
      Module: JDBC Thin Client
      select count as col_0_0_ from MFR_MESSAGE_T messageimp0_, MFR_UNREAD_STATUS_T
      unreadstat1_ where messageimp0_.surrogateKey=:1 and unreadstat1_.USER_C=:2 and
      messageimp0_.ID=unreadstat1_.MESSAGE_C and unreadstat1_.READ_C=:"SYS_B_0" and me
      ssageimp0_.DRAFT=:"SYS_B_1" and messageimp0_.DELETED=:"SYS_B_2"

      1,170 1,171 204,094 0.01 4.1 9ufzxsj9tfyr8
      Module: JDBC Thin Client
      select count(:"SYS_B_0") from SAKAI_REALM_RL_FN where REALM_KEY in (select REALM
      _KEY from SAKAI_REALM where SAKAI_REALM.REALM_ID IN (:1,:2,:3,:4,:5,:6)) and FU
      NCTION_KEY in (select FUNCTION_KEY from SAKAI_REALM_FUNCTION where FUNCTION_NAME
      = :7) and (ROLE_KEY in (select ROLE_KEY from SAKAI_REALM_RL_GR where ACTIVE = :

      932 933 159,264 0.01 3.2 bzt6df74tdug6
      select count(:"SYS_B_0") from SAKAI_REALM_RL_FN where REALM_KEY in (select REALM
      _KEY from SAKAI_REALM where SAKAI_REALM.REALM_ID IN (:1,:2,:3,:4,:5,:6,:7)) and
      FUNCTION_KEY in (select FUNCTION_KEY from SAKAI_REALM_FUNCTION where FUNCTION_N
      AME = :8) and (ROLE_KEY in (select ROLE_KEY from SAKAI_REALM_RL_GR where ACTIVE

      INDEX_NAME COLUMN_NAME COLUMN_POSITION
      ------------------------------ ------------------------------ ---------------
      SYS_C0084702 ID 1
      SYS_C0084703 NAME 1
      ISEARCHBUILDERITEM_CONTEXT CONTEXT 1
      ISEARCHBUILDERITEM_ACT SEARCHACTION 1
      ISEARCHBUILDERITEM_STA SEARCHSTATE 1

      select count from searchbuilderitem where searchstate = 5 and searchaction <> 1;

      select count from searchbuilderitem where searchstate = 2 and searchaction <> 1;

      select count from searchbuilderitem where searchstate = 5 and searchaction <> 2;

      select count from searchbuilderitem where searchstate = 2 and searchaction <> 2;

      SQL> select count from searchbuilderitem where searchstate = 5 and searchaction <> 1;
      Elapsed: 00:00:00.01

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 4124391043

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

      Id Operation Name Rows Bytes Cost (%CPU) Time

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

      0 SELECT STATEMENT   1 4 7435 (3) 00:01:45
      1 SORT AGGREGATE   1 4    
      • 2
      VIEW index$_join$_001 787K 3075K 7435 (3) 00:01:45
      • 3
      HASH JOIN          
      • 4
      INDEX RANGE SCAN ISEARCHBUILDERITEM_STA 787K 3075K 1214 (5) 00:00:18
      • 5
      INDEX FAST FULL SCAN ISEARCHBUILDERITEM_ACT 787K 3075K 3056 (3) 00:00:43

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

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - filter("SEARCHSTATE"=5 AND "SEARCHACTION"<>1)
      3 - access(ROWID=ROWID)
      4 - access("SEARCHSTATE"=5)
      5 - filter("SEARCHACTION"<>1)

      SQL> select count from searchbuilderitem where searchstate = 2 and searchaction <> 1;
      Elapsed: 00:00:00.00

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 4124391043

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

      Id Operation Name Rows Bytes Cost (%CPU) Time

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

      0 SELECT STATEMENT   1 4 7435 (3) 00:01:45
      1 SORT AGGREGATE   1 4    
      • 2
      VIEW index$_join$_001 787K 3075K 7435 (3) 00:01:45
      • 3
      HASH JOIN          
      • 4
      INDEX RANGE SCAN ISEARCHBUILDERITEM_STA 787K 3075K 1214 (5) 00:00:18
      • 5
      INDEX FAST FULL SCAN ISEARCHBUILDERITEM_ACT 787K 3075K 3056 (3) 00:00:43

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

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - filter("SEARCHSTATE"=2 AND "SEARCHACTION"<>1)
      3 - access(ROWID=ROWID)
      4 - access("SEARCHSTATE"=2)
      5 - filter("SEARCHACTION"<>1)

      SQL> select count from searchbuilderitem where searchstate = 5 and searchaction <> 2;
      Elapsed: 00:00:00.00

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 4124391043

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

      Id Operation Name Rows Bytes Cost (%CPU) Time

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

      0 SELECT STATEMENT   1 4 7435 (3) 00:01:45
      1 SORT AGGREGATE   1 4    
      • 2
      VIEW index$_join$_001 787K 3075K 7435 (3) 00:01:45
      • 3
      HASH JOIN          
      • 4
      INDEX RANGE SCAN ISEARCHBUILDERITEM_STA 787K 3075K 1214 (5) 00:00:18
      • 5
      INDEX FAST FULL SCAN ISEARCHBUILDERITEM_ACT 787K 3075K 3056 (3) 00:00:43

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

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - filter("SEARCHSTATE"=5 AND "SEARCHACTION"<>2)
      3 - access(ROWID=ROWID)
      4 - access("SEARCHSTATE"=5)
      5 - filter("SEARCHACTION"<>2)

      SQL> select count from searchbuilderitem where searchstate = 2 and searchaction <> 2;
      Elapsed: 00:00:00.00

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 4124391043

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

      Id Operation Name Rows Bytes Cost (%CPU) Time

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

      0 SELECT STATEMENT   1 4 7435 (3) 00:01:45
      1 SORT AGGREGATE   1 4    
      • 2
      VIEW index$_join$_001 787K 3075K 7435 (3) 00:01:45
      • 3
      HASH JOIN          
      • 4
      INDEX RANGE SCAN ISEARCHBUILDERITEM_STA 787K 3075K 1214 (5) 00:00:18
      • 5
      INDEX FAST FULL SCAN ISEARCHBUILDERITEM_ACT 787K 3075K 3056 (3) 00:00:43

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

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - filter("SEARCHSTATE"=2 AND "SEARCHACTION"<>2)
      3 - access(ROWID=ROWID)
      4 - access("SEARCHSTATE"=2)
      5 - filter("SEARCHACTION"<>2)

      SQL> create index ISEARCHBUILDERITEM_STA_ACT on searchbuilderitem(searchstate,searchaction) tablespace ctools_indexes;

      Index created.

      Elapsed: 00:00:27.17
      SQL> set autotrace traceonly
      SQL> select count from searchbuilderitem where searchstate = 5 and searchaction <> 1;

      Elapsed: 00:00:01.58

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 356389705

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

      Id Operation Name Rows Bytes Cost (%CPU) Time

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

      0 SELECT STATEMENT   1 4 796 (8) 00:00:12
      1 SORT AGGREGATE   1 4    
      • 2
      INDEX FAST FULL SCAN ISEARCHBUILDERITEM_STA_ACT 529K 2069K 796 (8) 00:00:12

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

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - filter("SEARCHSTATE"=5 AND "SEARCHACTION"<>1)

      Statistics
      ----------------------------------------------------------
      1 recursive calls
      0 db block gets
      2495 consistent gets
      0 physical reads
      0 redo size
      515 bytes sent via SQL*Net to client
      492 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1 rows processed

      SQL> select count from searchbuilderitem where searchstate = 2 and searchaction <> 1;

      Elapsed: 00:00:02.09

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 356389705

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

      Id Operation Name Rows Bytes Cost (%CPU) Time

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

      0 SELECT STATEMENT   1 4 796 (8) 00:00:12
      1 SORT AGGREGATE   1 4    
      • 2
      INDEX FAST FULL SCAN ISEARCHBUILDERITEM_STA_ACT 529K 2069K 796 (8) 00:00:12

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

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - filter("SEARCHSTATE"=2 AND "SEARCHACTION"<>1)

      Statistics
      ----------------------------------------------------------
      0 recursive calls
      0 db block gets
      2495 consistent gets
      0 physical reads
      0 redo size
      515 bytes sent via SQL*Net to client
      492 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1 rows processed

      SQL> select count from searchbuilderitem where searchstate = 5 and searchaction <> 2;

      Elapsed: 00:00:01.56

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 356389705

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

      Id Operation Name Rows Bytes Cost (%CPU) Time

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

      0 SELECT STATEMENT   1 4 796 (8) 00:00:12
      1 SORT AGGREGATE   1 4    
      • 2
      INDEX FAST FULL SCAN ISEARCHBUILDERITEM_STA_ACT 529K 2069K 796 (8) 00:00:12

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

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - filter("SEARCHSTATE"=5 AND "SEARCHACTION"<>2)

      Statistics
      ----------------------------------------------------------
      0 recursive calls
      0 db block gets
      2534 consistent gets
      0 physical reads
      0 redo size
      515 bytes sent via SQL*Net to client
      492 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1 rows processed

      SQL> select count from searchbuilderitem where searchstate = 2 and searchaction <> 2;

      Elapsed: 00:00:02.43

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 356389705

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

      Id Operation Name Rows Bytes Cost (%CPU) Time

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

      0 SELECT STATEMENT   1 4 796 (8) 00:00:12
      1 SORT AGGREGATE   1 4    
      • 2
      INDEX FAST FULL SCAN ISEARCHBUILDERITEM_STA_ACT 529K 2069K 796 (8) 00:00:12

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

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - filter("SEARCHSTATE"=2 AND "SEARCHACTION"<>2)

      Statistics
      ----------------------------------------------------------
      0 recursive calls
      0 db block gets
      2495 consistent gets
      0 physical reads
      0 redo size
      518 bytes sent via SQL*Net to client
      492 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1 rows processed

        Gliffy Diagrams

          Zeplin

            Attachments

              Issue Links

                Activity

                  People

                  • Assignee:
                    Unassigned
                    Reporter:
                    ianeboston Ian Boston
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    0 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved:

                      Git Integration