We have our DEV database of 11.2.0.2 on Redhat 5.2, and our production database is a two node RAC of the same version on same ype of servers. Recently, we noticed that when run the same query on the two server, the DEV is 300 to 1000 times faster than on production, while the exec plan cost of DEV is one third of that of PRD. The query is
SELECT PERSON_X_IDTY.PERSON_ID AS Facet_ID,1.0 AS Facet_Rank
FROM (IDTY INNER JOIN PERSON_X_IDTY ON ((IDTY.IDTY_ID = PERSON_X_IDTY.IDTY_ID)))
WHERE (((IDTY.IDTY_NAME_FIRST IS NOT NULL AND LOWER(IDTY.IDTY_NAME_FIRST) = LOWER('DAVID'))
AND (IDTY.IDTY_NAME_LAST IS NOT NULL AND LOWER(IDTY.IDTY_NAME_LAST) = LOWER('MILLER'))
AND (IDTY.IDTY_BIRTH_DATE IS NOT NULL
AND (IDTY.IDTY_BIRTH_DATE BETWEEN TO_DATE('1981-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND TO_DATE('2000-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS')))
AND (IDTY.IDTY_GENDER IS NOT NULL AND IDTY.IDTY_GENDER = 'M'))) AND ROWNUM<= 300
I know the query looks stupid, but that is generated and we are not to change it.
The exec plan show indexes are used for both table, but cost on PRD are heavier for index RANGE SCAN or BY INDEX ROWID. The index used for table IDTY is a functional index on IDTY.IDTY_NAME_LAST. I rebuild the indexes used but there is no improvement.
What parameters or configuration of database I need to check to find the reason for this case?
Thanks.