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.
MATCH_CODE_INDIVIDUAL
and another onMATCH_CODE_DATE_OF_BIRTH
now? It looks like the "before" explain plan indicates there was an indexXIE6_MATCH_IND
which was onMATCH_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