Tuning read-intensive queries – Oracle

index-tuningoracleperformanceperformance-tuning

Now this might be very vague but Im sure I can provide more info on them.. my query is that is it recommended to use tablespaces if different block size because I have a table in which number of reads that would happen will be very high.. Application end users are saying that the response that they are getting is slow.. and they are asking me to speed up the query..

What options do I have? I have run the sql advisor and it asked me include an index,which I did and reponse got improved.. what else can I try?

Create a new tablespace with higher blocksize (currently 8k) and import the data into the new one — will that improve the performance? this is very much read intensive.

SELECT "ENTITY_WATCH_LIST_KEY"          ,
    "ENTITY_WATCH_LIST_NUMBER"      ,
    "WATCH_LIST_NAME"               ,
    "CATEGORY_DESC"                 ,
    "TYPE_DESC"                     ,
    "PROGRAMS"                      ,
    "TAX_ID"                        ,
    "TAX_ID_TYPE_CODE"              ,
    "IDENTIFICATION_ID"             ,
    "IDENTIFICATION_TYPE_DESC"      ,
    "DATE_OF_BIRTH"                 ,
    "YEAR_OF_BIRTH"                 ,
    "PLACE_OF_BIRTH"                ,
    "DECEASED_IND"                  ,
    "FIRST_NAME"                    ,
    "LAST_NAME"                     ,
    "MIDDLE_NAME"                   ,
    "ENTITY_NAME"                   ,
    "ENTITY_TITLE"                  ,
    "OCCUPATION_DESC"               ,
    "ADDRESS"                       ,
    "CITY_NAME"                     ,
    "STATE_NAME"                    ,
    "POSTAL_CODE"                   ,
    "COUNTRY_CODE"                  ,
    "COUNTRY_NAME"                  ,
    "FULL_ADDRESS"                  ,
    "CITIZENSHIP_COUNTRY_CODE"      ,
    "CITIZENSHIP_COUNTRY_NAME"      ,
    "NATIONALITY_COUNTRY_CODE"      ,
    "NATIONALITY_COUNTRY_NAME"      ,
    "ORG_COUNTRY_OF_BUSINESS_CODE"  ,
    "ORG_COUNTRY_OF_BUSINESS_NAME"  ,
    "POLITICALLY_EXPOSED_PERSON_IND",
    "CREATE_DATE"                   ,
    "UPDATE_DATE"                   ,
    "EXCLUDE_IND"                   ,
    "CHANGE_BEGIN_DATE"             ,
    "CHANGE_END_DATE"               ,
    "CHANGE_CURRENT_IND"            ,
    "MATCH_CODE_ADDR_LINES"         ,
    "MATCH_CODE_CITIZENSHIP"        ,
    "MATCH_CODE_CITY"               ,
    "MATCH_CODE_COUNTRY"            ,
    "MATCH_CODE_FULL_ADDRESS"       ,
    "MATCH_CODE_INDIVIDUAL"         ,
    "MATCH_CODE_NATIONALITY"        ,
    "MATCH_CODE_ORGANIZATION"       ,
    "MATCH_CODE_ORG_COUNTRY"        ,
    "MATCH_CODE_STATE" FROM "FCFCORE"."FSC_ENTITY_WATCH_LIST_DIM" WHERE
    (
            "MATCH_CODE_DATE_OF_BIRTH"  = :V1
            AND "MATCH_CODE_INDIVIDUAL" = :V2
    )
    OR
    (
            "MATCH_CODE_DATE_OF_BIRTH"  = :V3
            AND "MATCH_CODE_INDIVIDUAL" = :V4
    )
    OR
    (
            "MATCH_CODE_DATE_OF_BIRTH"  = :V5
            AND "MATCH_CODE_INDIVIDUAL" = :V6
    )
    OR
    (
            "MATCH_CODE_DATE_OF_BIRTH"  = :V7
            AND "MATCH_CODE_INDIVIDUAL" = :V8
    )
    OR
    (
            "MATCH_CODE_DATE_OF_BIRTH"  = :V9
            AND "MATCH_CODE_INDIVIDUAL" = :V10
    )

Explain plan before running the advisor::

PLAN_TABLE_OUTPUT
Plan hash value: 4020047834
| Id  | Operation                            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |     1 |  4351 |    33   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR                     |                           |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| FSC_ENTITY_WATCH_LIST_DIM |     1 |  4351 |    33   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | XIE6_MATCH_IND            |    14 |       |    18   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
Predicate Information (identified by operation id):

   2 - filter("MATCH_CODE_INDIVIDUAL"=:V2 AND "MATCH_CODE_DATE_OF_BIRTH"=:V1 OR
              "MATCH_CODE_INDIVIDUAL"=:V4 AND "MATCH_CODE_DATE_OF_BIRTH"=:V3 OR "MATCH_CODE_INDIVIDUAL"=:V6 AND
              "MATCH_CODE_DATE_OF_BIRTH"=:V5 OR "MATCH_CODE_INDIVIDUAL"=:V8 AND "MATCH_CODE_DATE_OF_BIRTH"=:V7 OR
              "MATCH_CODE_INDIVIDUAL"=:V10 AND "MATCH_CODE_DATE_OF_BIRTH"=:V9)
   3 - access("MATCH_CODE_INDIVIDUAL"=:V2 OR "MATCH_CODE_INDIVIDUAL"=:V4 OR "MATCH_CODE_INDIVIDUAL"=:V6
              OR "MATCH_CODE_INDIVIDUAL"=:V8 OR "MATCH_CODE_INDIVIDUAL"=:V10)

Inputs would be appreciated.

Best Answer

Using a different blocksize would probably be the last thing to try, and only if there is a good reason to do so. A good reason might include

  • The majority of queries do full table scans that happen to retrieve several blocks from disk at once
  • The table includes large objects (LOBs) that are bigger than 8k

But in general, I would say a larger blocksize won't help for most environments; and for those that need it, it would be a minor improvement. Derek's comment above has good things to look at; if you post the query and the explain plan, we can provide better advice.

Based on the query and explain plan you provided, you may want to try a composite index; the order of the columns below is assuming there are fewer unique values for MATCH_CODE_DATE_OF_BIRTH than for MATCH_CODE_INDIVIDUAL:

create index IXE6_MATCH_DATE_IND 
on FSC_ENTITY_WATCH_LIST_DIM (
    MATCH_CODE_DATE_OF_BIRTH,
    MATCH_CODE_INDIVIDUAL
    );

Also, are all the bind variables always populated with a value, or are sometimes some of them null? IE: if :V9 and :V10 are often null, you might be able to do something to your query like 

...
OR
(
  :v9 is not null and :v10 is not null and
  "MATCH_CODE_DATE_OF_BIRTH"  = :V9
        AND "MATCH_CODE_INDIVIDUAL" = :V10
)
Related Question