0

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.

  • 1
    Did you look at the explain plan for the query? Increasing the block size may not help if you are doing full table scans and creating large buffer scans anyways. Have you taken a look at the active_session_history to see where the majority of waits are occurring? Are you following proper guidelines like using bind variable, doing the proper joins (again, look at your explain plan). If the query is running multiple times, are you getting multiple hash plans or are you re-executing a good plan and have plan stability? SQL Profiling? – Data Flux Nov 12 '15 at 17:12
  • have added the sql and explain plan for investigation – tesla747 Nov 13 '15 at 15:33
  • So is there an index on MATCH_CODE_INDIVIDUAL and another on MATCH_CODE_DATE_OF_BIRTH now? It looks like the "before" explain plan indicates there was an index XIE6_MATCH_IND which was on MATCH_CODE_INDIVIDUAL. If you now have separate indexes, one per column, you might try a composite index, but that might be a pretty big index. – Mark Stewart Nov 13 '15 at 16:11
  • I really do not want to slow down the response which is getting now. – tesla747 Nov 13 '15 at 16:12
0

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
)
  • have added the sql and explain plan for investigation. – tesla747 Nov 13 '15 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.