monto1
asked on
Query Running Slow
11.2.0.1
AIX 6.1
I have following query which is executed from Application,the query comes back within 2 secs for not so common last_name combined with gender but it is taking longer time for common last_names like 'BROWN' or 'JONES' or 'SMITH' with gender 'MALE' .
Execution plan for the SMITH Last_Name and Genderid='2'---which is Male
VIEW
PERSONPROFILENAMES-16 Milion rows
LEGALENTITYADDRESSES-34 Milion rows
PERSONPROFILE-16 Milion rows
LEGALENTITY 17 Milion rows
refcontactdetails 45 rows
Thanks
AIX 6.1
I have following query which is executed from Application,the query comes back within 2 secs for not so common last_name combined with gender but it is taking longer time for common last_names like 'BROWN' or 'JONES' or 'SMITH' with gender 'MALE' .
Execution plan for the SMITH Last_Name and Genderid='2'---which is Male
SQL ID: f9nud0zpm2cac
Plan Hash: 3208566710
select customerin0_.ROW_NUMBER as ROW1_22596_, customerin0_.ADDRESS_ID as
ADDRESS2_22596_, customerin0_.addressTypeId as addressT3_22596_,
customerin0_.city as city22596_, customerin0_.CONTACT_DETAILS_CODE_ID as
CONTACT5_22596_, customerin0_.county as county22596_, customerin0_.countyID
as countyID22596_, customerin0_.dob as dob22596_, customerin0_.eyecolor as
eyecolor22596_, customerin0_.eyecolorid as eyecolorid22596_,
customerin0_.FIRST_NAME as FIRST11_22596_, customerin0_.gender as
gender22596_, customerin0_.genderid as genderid22596_,
customerin0_.ID_NUMBER as ID14_22596_, customerin0_.JURISDICTION_CODE as
JURISDI15_22596_, customerin0_.LAST_NAME as LAST16_22596_,
customerin0_.LEGAL_ENTITY_ID as LEGAL17_22596_,
customerin0_.LEGAL_ENTITY_NUMBER as LEGAL18_22596_,
customerin0_.LEGAL_ENTITY_TYPE as LEGAL19_22596_, customerin0_.MIDDLE_NAME
as MIDDLE20_22596_, customerin0_.STREET_ADDRESS_1 as STREET21_22596_,
customerin0_.suffix_value as suffix22_22596_, customerin0_.ZIP_CODE as
ZIP23_22596_, customerin0_.ZIP_PLUS_4 as ZIP24_22596_
from
CUSTOMER_IND_SEARCH_VIEW customerin0_ where (customerin0_.LAST_NAME like :1)
and customerin0_.genderid=:2 and rownum<=200
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 18 1.88 123.53 8245 12403 0 179
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18 1.88 123.53 8245 12403 0 179
Misses in library cache during parse: 0
Parsing user id: 135 (???)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
ges message buffer allocation 259 0.00 0.00
gc cr grant 2-way 259 0.00 0.08
db file sequential read 10126 1.40 148.07
SQL*Net message from client 19 0.02 0.10
SQL*Net message to client 18 0.00 0.00
********************************************************************************PLAN_TABLE_OUTPUT
SQL_ID f9nud0zpm2cac, child number 0
-------------------------------------
select customerin0_.ROW_NUMBER as ROW1_22596_, customerin0_.ADDRESS_ID
as ADDRESS2_22596_, customerin0_.addressTypeId as addressT3_22596_,
customerin0_.city as city22596_, customerin0_.CONTACT_DETAILS_CODE_ID
as CONTACT5_22596_, customerin0_.county as county22596_,
customerin0_.countyID as countyID22596_, customerin0_.dob as dob22596_,
customerin0_.eyecolor as eyecolor22596_, customerin0_.eyecolorid as
eyecolorid22596_, customerin0_.FIRST_NAME as FIRST11_22596_,
customerin0_.gender as gender22596_, customerin0_.genderid as
genderid22596_, customerin0_.ID_NUMBER as ID14_22596_,
customerin0_.JURISDICTION_CODE as JURISDI15_22596_,
customerin0_.LAST_NAME as LAST16_22596_, customerin0_.LEGAL_ENTITY_ID
as LEGAL17_22596_, customerin0_.LEGAL_ENTITY_NUMBER as LEGAL18_22596_,
customerin0_.LEGAL_ENTITY_TYPE as LEGAL19_22596_,
customerin0_.MIDDLE_NAME as MIDDLE20_22596_,
customerin0_.STREET_ADDRESS_1 as STREET21_22596_,
customerin0_.suffix_value as suffix22_22596_, customerin0_.ZIP_CODE as
ZIP23_2
Plan hash value: 3208566710
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5751 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID | REF_COUNTIES | 1 | 12 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | XPK_REF_COUNTIES | 1 | | 0 (0)| |
| 3 | TABLE ACCESS BY INDEX ROWID | REF_EYE_COLORS | 1 | 10 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | XPK_REF_EYE_COLORS | 1 | | 0 (0)| |
| 5 | TABLE ACCESS BY INDEX ROWID | REF_GENDER | 1 | 9 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | XPK_REF_GENDER | 1 | | 0 (0)| |
|* 7 | COUNT STOPKEY | | | | | |
| 8 | NESTED LOOPS OUTER | | 201 | 41808 | 5751 (1)| 00:01:10 |
| 9 | NESTED LOOPS | | 201 | 34572 | 5160 (1)| 00:01:02 |
| 10 | NESTED LOOPS | | 805 | 73255 | 3477 (1)| 00:00:42 |
| 11 | NESTED LOOPS | | 805 | 51520 | 1866 (1)| 00:00:23 |
| 12 | NESTED LOOPS | | 805 | 33005 | 254 (0)| 00:00:04 |
|* 13 | TABLE ACCESS BY INDEX ROWID| REF_CONTACT_DETAILS | 1 | 10 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | XAK1_REF_CD_VALUE | 1 | | 0 (0)| |
| 15 | TABLE ACCESS BY INDEX ROWID| PERSON_PROFILE_NAMES | 805 | 24955 | 253 (0)| 00:00:04 |
|* 16 | INDEX RANGE SCAN | IDX$$_D7D50001 | 805 | | 4 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | PERSON_PROFILE | 1 | 23 | 2 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | XPK_PERSON_PROFILE | 1 | | 1 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID | LEGAL_ENTITY | 1 | 27 | 2 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | XPK_LEGAL_ENTITY | 1 | | 1 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | LEGAL_ENTITY_ADDRESSES | 1 | 81 | 3 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | LENUM_CODE_ID | 1 | | 2 (0)| 00:00:01 |
|* 23 | TABLE ACCESS BY INDEX ROWID | LEGAL_ENTITY_IDENTIFICATIONS | 1 | 36 | 3 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | XIF1_LEGAL_ENTITY_ID | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("REFCOUNTIES"."ID"=:B1)
4 - access("REFEYECOLORS"."ID"=:B1)
6 - access("REFGENDER"."ID"=:B1)
7 - filter(ROWNUM<=200)
13 - filter("REFCONTACTDETAILS"."MODE_TYPE"=4)
14 - access("REFCONTACTDETAILS"."VALUE"='PRA')
16 - access("PERSONPROFILENAMES"."LAST_NAME" LIKE :1 AND "PERSONPROFILENAMES"."IS_DELETED"=0)
filter(("PERSONPROFILENAMES"."LAST_NAME" LIKE :1 AND "PERSONPROFILENAMES"."IS_DELETED"=0))
17 - filter("SYS_ALIAS_2"."GENDER_ID"=:2)
18 - access("SYS_ALIAS_2"."PP_LEGALENTITY_NUMBER"="PERSONPROFILENAMES"."PP_LEGALENTITY_NUMBER")
19 - filter(("LEGALENTITY"."IS_DELETED"=0 OR "LEGALENTITY"."IS_DELETED"=NULL))
20 - access("LEGALENTITY"."LEGAL_ENTITY_NUMBER"="SYS_ALIAS_2"."PP_LEGALENTITY_NUMBER")
21 - filter("LEGALENTITYADDRESSES"."IS_DELETED"=0)
22 - access("LEGALENTITY"."LEGAL_ENTITY_NUMBER"="LEGALENTITYADDRESSES"."LEGAL_ENTITY_NUMBER" AND
"REFCONTACTDETAILS"."ID"="LEGALENTITYADDRESSES"."CONTACT_DETAILS_CODE_ID")
23 - filter(("LEGAL_ENTITY_ID"."END_DATE">=SYSDATE@! AND "LEGAL_ENTITY_ID"."EFFECTIVE_DATE"<=SYSDATE@!))
24 - access("LEGAL_ENTITY_ID"."LEGAL_ENTITY_NUMBER"="LEGALENTITY"."LEGAL_ENTITY_NUMBER")
VIEW
CREATE OR REPLACE FORCE VIEW XMATAPP.CUSTOMER_IND_SEARCH_VIEW (
ROW_NUMBER,
LEGAL_ENTITY_NUMBER,
LEGAL_ENTITY_ID,
LEGAL_ENTITY_TYPE,
FIRST_NAME,
LAST_NAME,
MIDDLE_NAME,
ID_NUMBER,
DOB,
GENDERID,
GENDER,
EYECOLORID,
EYECOLOR,
SUFFIX_VALUE,
CONTACT_DETAILS_CODE_ID,
ADDRESSTYPEID,
STREET_ADDRESS_1,
CITY,
COUNTYID,
JURISDICTION_CODE,
ZIP_CODE,
ZIP_PLUS_4,
ADDRESS_ID,
COUNTY,
IS_DELETED,
NAME_TYPE,
IS_PENDED
)
AS
SELECT name_type || legal_entity_number AS ROW_NUMBER,
legal_entity_number,
legal_entity_id,
legal_entity_type,
first_name,
last_name,
middle_name,
id_number,
dob,
gender1 genderid,
gender,
eye_color_id eyecolorid,
eyecolor,
suffix_value,
contact_details_code_id,
addresstypeid,
street_address_1,
city,
county1 countyid,
jurisdiction_code,
zip_code,
zip_plus_4,
address_id,
county,
is_deleted,
name_type,
is_pended
FROM (SELECT v2.*,
(SELECT refgender.description
FROM ref_gender refgender
WHERE v2.gender1 = refgender.id)
gender,
(SELECT refeyecolors.description
FROM ref_eye_colors refeyecolors
WHERE v2.eye_color_id = refeyecolors.id)
eyecolor,
(SELECT refcounties.description
FROM ref_counties refcounties
WHERE refcounties.id = v2.county1)
county
FROM (SELECT v1.*
FROM (SELECT legalentity.legal_entity_number,
legalentity.legal_entity_id,
legalentity.legal_entity_type,
personprofilenames.first_name,
personprofilenames.last_name,
personprofilenames.middle_name,
legal_entity_id.id_number,
personprofile.dob,
legalentityaddresses.contact_details_code_id,
legalentityaddresses.address_type
addresstypeid,
legalentityaddresses.street_address_1,
legalentityaddresses.city,
legalentityaddresses.country_id
countyid,
legalentityaddresses.jurisdiction_code,
legalentityaddresses.zip_code,
legalentityaddresses.zip_plus_4,
legalentityaddresses.id address_id,
legalentity.is_deleted is_deleted,
personprofilenames.name_type_id
name_type,
personprofile.eye_color_id,
personprofilenames.suffix_value
suffix_value,
personprofilenames.transaction_id
ptxnId,
personprofile.gender_id gender1,
legalentityaddresses.county county1,
legalentity.is_pended is_pended
FROM legal_entity legalentity
LEFT OUTER JOIN
legal_entity_identifications legal_entity_id
ON legal_entity_id.legal_entity_number =
legalentity.legal_entity_number
AND (SYSDATE BETWEEN legal_entity_id.effective_date
AND legal_entity_id.end_date)
INNER JOIN
person_profile personprofile
ON legalentity.legal_entity_number =
personprofile.pp_legalentity_number
INNER JOIN
person_profile_names personprofilenames
ON personprofile.pp_legalentity_number =
personprofilenames.pp_legalentity_number
INNER JOIN
legal_entity_addresses legalentityaddresses
ON legalentity.legal_entity_number =
legalentityaddresses.legal_entity_number
INNER JOIN
ref_contact_details refcontactdetails
ON refcontactdetails.id =
legalentityaddresses.contact_details_code_id
AND refcontactdetails.VALUE =
'PRA'
AND refcontactdetails.mode_type =
4
WHERE (legalentity.is_deleted = 0
OR legalentity.is_deleted = NULL)
AND legalentityaddresses.is_deleted =
0
AND personprofilenames.is_deleted =
0) v1) v2) v3;PERSONPROFILENAMES-16 Milion rows
LEGALENTITYADDRESSES-34 Milion rows
PERSONPROFILE-16 Milion rows
LEGALENTITY 17 Milion rows
refcontactdetails 45 rows
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks flow01.Here are the details.
It came from Application,i generated the paln from OEM(Top Activity).
Traced own session from sqlplus
Explain Plan for rare names
There are 45411 rows with SMITH and genderid=2,Personprofilena
SMITHS are 85079 and browns 51767.
9195489 out of 16 milion records in person_profile
SMITH with genderid=3(female) takes lesser time than Male ,is still unacceptable to the client.
any joins to person_profile table is slowing down the query,like even the combination of last_name and dob is also very slow.
Regards
Did your explain come from an execution of your application with the values 'SMITH%' and Genderid='2' or did you do the explain plan generate by yourself like
It came from Application,i generated the paln from OEM(Top Activity).
Traced own session from sqlplus
SELECT customerin0_.ROW_NUMBER AS ROW1_22596_,
customerin0_.ADDRESS_ID AS ADDRESS2_22596_,
customerin0_.addressTypeId AS addressT3_22596_,
customerin0_.city AS city22596_,
customerin0_.CONTACT_DETAILS_CODE_ID AS CONTACT5_22596_,
customerin0_.county AS county22596_,
customerin0_.countyID AS countyID22596_,
customerin0_.dob AS dob22596_,
customerin0_.eyecolor AS eyecolor22596_,
customerin0_.eyecolorid AS eyecolorid22596_,
customerin0_.FIRST_NAME AS FIRST11_22596_,
customerin0_.gender AS gender22596_,
customerin0_.genderid AS genderid22596_,
customerin0_.ID_NUMBER AS ID14_22596_,
customerin0_.JURISDICTION_CODE AS JURISDI15_22596_,
customerin0_.LAST_NAME AS LAST16_22596_,
customerin0_.LEGAL_ENTITY_ID AS LEGAL17_22596_,
customerin0_.LEGAL_ENTITY_NUMBER AS LEGAL18_22596_,
customerin0_.LEGAL_ENTITY_TYPE AS LEGAL19_22596_,
customerin0_.MIDDLE_NAME AS MIDDLE20_22596_,
customerin0_.STREET_ADDRESS_1 AS STREET21_22596_,
customerin0_.suffix_value AS suffix22_22596_,
customerin0_.ZIP_CODE AS ZIP23_22596_,
customerin0_.ZIP_PLUS_4 AS ZIP24_22596_
FROM xmatapp.CUSTOMER_IND_SEARCH_VIEW customerin0_
WHERE (customerin0_.LAST_NAME LIKE 'SMITH%')
AND customerin0_.genderid = 2
AND ROWNUM <= 200
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.05 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 15 5.05 167.51 14050 20246 0 200
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 17 5.09 167.56 14050 20246 0 200
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
41 TABLE ACCESS BY INDEX ROWID REF_COUNTIES (cr=58 pr=0 pw=0 time=0 us cost=1 size=12 card=1)
41 INDEX UNIQUE SCAN XPK_REF_COUNTIES (cr=17 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 308432)
7 TABLE ACCESS BY INDEX ROWID REF_EYE_COLORS (cr=11 pr=0 pw=0 time=0 us cost=1 size=10 card=1)
7 INDEX UNIQUE SCAN XPK_REF_EYE_COLORS (cr=4 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 308404)
1 TABLE ACCESS BY INDEX ROWID REF_GENDER (cr=2 pr=0 pw=0 time=0 us cost=1 size=9 card=1)
1 INDEX UNIQUE SCAN XPK_REF_GENDER (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 308759)
200 COUNT STOPKEY (cr=20175 pr=14050 pw=0 time=2540633 us)
200 NESTED LOOPS OUTER (cr=20175 pr=14050 pw=0 time=2540633 us cost=103 size=1248 card=6)
200 NESTED LOOPS (cr=19756 pr=13775 pw=0 time=17003952 us cost=80 size=1032 card=6)
200 NESTED LOOPS (cr=19304 pr=13586 pw=0 time=14271285 us cost=55 size=1092 card=12)
200 NESTED LOOPS (cr=19017 pr=13453 pw=0 time=13593988 us cost=31 size=768 card=12)
4923 NESTED LOOPS (cr=4640 pr=4614 pw=0 time=63281900 us cost=7 size=492 card=12)
1 TABLE ACCESS BY INDEX ROWID REF_CONTACT_DETAILS (cr=2 pr=0 pw=0 time=0 us cost=1 size=10 card=1)
1 INDEX UNIQUE SCAN XAK1_REF_CD_VALUE (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 308425)
4923 TABLE ACCESS BY INDEX ROWID PERSON_PROFILE_NAMES (cr=4638 pr=4614 pw=0 time=63269280 us cost=6 size=372 card=12)
4923 INDEX RANGE SCAN IDX$$_D7D50001 (cr=30 pr=16 pw=0 time=60326 us cost=2 size=0 card=12)(object id 311149)
200 TABLE ACCESS BY INDEX ROWID PERSON_PROFILE (cr=14377 pr=8839 pw=0 time=0 us cost=2 size=23 card=1)
4923 INDEX UNIQUE SCAN XPK_PERSON_PROFILE (cr=9454 pr=4288 pw=0 time=0 us cost=1 size=0 card=1)(object id 308484)
200 TABLE ACCESS BY INDEX ROWID LEGAL_ENTITY (cr=287 pr=133 pw=0 time=0 us cost=2 size=27 card=1)
200 INDEX UNIQUE SCAN XPK_LEGAL_ENTITY (cr=225 pr=80 pw=0 time=0 us cost=1 size=0 card=1)(object id 308494)
200 TABLE ACCESS BY INDEX ROWID LEGAL_ENTITY_ADDRESSES (cr=452 pr=189 pw=0 time=0 us cost=3 size=81 card=1)
200 INDEX RANGE SCAN LENUM_CODE_ID (cr=281 pr=94 pw=0 time=0 us cost=2 size=0 card=1)(object id 307952)
200 TABLE ACCESS BY INDEX ROWID LEGAL_ENTITY_IDENTIFICATIONS (cr=419 pr=275 pw=0 time=0 us cost=4 size=36 card=1)
200 INDEX RANGE SCAN XIF1_LEGAL_ENTITY_ID (cr=219 pr=77 pw=0 time=0 us cost=2 size=0 card=1)(object id 308082)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
ges message buffer allocation 8576 0.00 0.02
library cache lock 7 0.00 0.00
library cache pin 7 0.00 0.00
SQL*Net message to client 15 0.00 0.00
Disk file operations I/O 19 0.01 0.03
gc cr grant 2-way 8554 0.01 3.09
db file sequential read 14050 0.91 158.32
SQL*Net message from client 15 32.28 35.97
gc cr grant congested 8 0.00 0.01
********************************************************************************Explain Plan for rare names
SELECT customerin0_.ROW_NUMBER AS ROW1_22596_,
customerin0_.ADDRESS_ID AS ADDRESS2_22596_,
customerin0_.addressTypeId AS addressT3_22596_,
customerin0_.city AS city22596_,
customerin0_.CONTACT_DETAILS_CODE_ID AS CONTACT5_22596_,
customerin0_.county AS county22596_,
customerin0_.countyID AS countyID22596_,
customerin0_.dob AS dob22596_,
customerin0_.eyecolor AS eyecolor22596_,
customerin0_.eyecolorid AS eyecolorid22596_,
customerin0_.FIRST_NAME AS FIRST11_22596_,
customerin0_.gender AS gender22596_,
customerin0_.genderid AS genderid22596_,
customerin0_.ID_NUMBER AS ID14_22596_,
customerin0_.JURISDICTION_CODE AS JURISDI15_22596_,
customerin0_.LAST_NAME AS LAST16_22596_,
customerin0_.LEGAL_ENTITY_ID AS LEGAL17_22596_,
customerin0_.LEGAL_ENTITY_NUMBER AS LEGAL18_22596_,
customerin0_.LEGAL_ENTITY_TYPE AS LEGAL19_22596_,
customerin0_.MIDDLE_NAME AS MIDDLE20_22596_,
customerin0_.STREET_ADDRESS_1 AS STREET21_22596_,
customerin0_.suffix_value AS suffix22_22596_,
customerin0_.ZIP_CODE AS ZIP23_22596_,
customerin0_.ZIP_PLUS_4 AS ZIP24_22596_
FROM xmatapp.CUSTOMER_IND_SEARCH_VIEW customerin0_
WHERE (customerin0_.LAST_NAME LIKE 'KUMAR%')
AND customerin0_.genderid = '2'
AND ROWNUM <= 200
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.05 0.07 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 15 0.02 0.03 0 1829 0 200
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 17 0.07 0.10 0 1829 0 200
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
23 TABLE ACCESS BY INDEX ROWID REF_COUNTIES (cr=37 pr=0 pw=0 time=0 us cost=1 size=12 card=1)
23 INDEX UNIQUE SCAN XPK_REF_COUNTIES (cr=14 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 308432)
5 TABLE ACCESS BY INDEX ROWID REF_EYE_COLORS (cr=9 pr=0 pw=0 time=0 us cost=1 size=10 card=1)
5 INDEX UNIQUE SCAN XPK_REF_EYE_COLORS (cr=4 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 308404)
1 TABLE ACCESS BY INDEX ROWID REF_GENDER (cr=2 pr=0 pw=0 time=0 us cost=1 size=9 card=1)
1 INDEX UNIQUE SCAN XPK_REF_GENDER (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 308759)
200 COUNT STOPKEY (cr=1781 pr=0 pw=0 time=15124 us)
200 NESTED LOOPS OUTER (cr=1781 pr=0 pw=0 time=14726 us cost=103 size=1248 card=6)
200 NESTED LOOPS (cr=1383 pr=0 pw=0 time=35422 us cost=80 size=1032 card=6)
200 NESTED LOOPS (cr=980 pr=0 pw=0 time=20198 us cost=55 size=1092 card=12)
200 NESTED LOOPS (cr=741 pr=0 pw=0 time=15422 us cost=31 size=768 card=12)
289 NESTED LOOPS (cr=129 pr=0 pw=0 time=3648 us cost=7 size=492 card=12)
1 TABLE ACCESS BY INDEX ROWID REF_CONTACT_DETAILS (cr=2 pr=0 pw=0 time=0 us cost=1 size=10 card=1)
1 INDEX UNIQUE SCAN XAK1_REF_CD_VALUE (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 308425)
289 TABLE ACCESS BY INDEX ROWID PERSON_PROFILE_NAMES (cr=127 pr=0 pw=0 time=3360 us cost=6 size=372 card=12)
289 INDEX RANGE SCAN IDX$$_D7D50001 (cr=18 pr=0 pw=0 time=672 us cost=2 size=0 card=12)(object id 311149)
200 TABLE ACCESS BY INDEX ROWID PERSON_PROFILE (cr=612 pr=0 pw=0 time=0 us cost=2 size=23 card=1)
289 INDEX UNIQUE SCAN XPK_PERSON_PROFILE (cr=323 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 308484)
200 TABLE ACCESS BY INDEX ROWID LEGAL_ENTITY (cr=239 pr=0 pw=0 time=0 us cost=2 size=27 card=1)
200 INDEX UNIQUE SCAN XPK_LEGAL_ENTITY (cr=194 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 308494)
200 TABLE ACCESS BY INDEX ROWID LEGAL_ENTITY_ADDRESSES (cr=403 pr=0 pw=0 time=0 us cost=3 size=81 card=1)
200 INDEX RANGE SCAN LENUM_CODE_ID (cr=230 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 307952)
200 TABLE ACCESS BY INDEX ROWID LEGAL_ENTITY_IDENTIFICATIONS (cr=398 pr=0 pw=0 time=0 us cost=4 size=36 card=1)
200 INDEX RANGE SCAN XIF1_LEGAL_ENTITY_ID (cr=198 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 308082)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
ges message buffer allocation 14 0.00 0.00
library cache lock 7 0.00 0.00
library cache pin 7 0.00 0.00
KJC: Wait for msg sends to complete 2 0.00 0.00
SQL*Net message to client 15 0.00 0.00
SQL*Net message from client 15 18.88 21.81
********************************************************************************There are 45411 rows with SMITH and genderid=2,Personprofilena
Whats the number of rows in table PERSONPROFILENAMES with name starting with 'SMITH' (or 'BROWN'); SMITHS are 85079 and browns 51767.
What % from customer is male 9195489 out of 16 milion records in person_profile
SMITH with genderid=3(female) takes lesser time than Male ,is still unacceptable to the client.
any joins to person_profile table is slowing down the query,like even the combination of last_name and dob is also very slow.
Regards
ASKER
Stats were collected on the recent data.Have job scheduled for stale stats also.
Thanks
Thanks
But I expect you did a select on the 'KUMAR%' before the one you showed:
now there is no 'db file sequential read' and no 'physical read' (pr=0)
so probably ORACLE has all necessary blocks in memory from a previous query.
When you (or somebody else) queries the same (limited amount of ) data twice the second query is much faster.
In sqlplus > set timed on
and get another rare name twice .
The time necessary to get the data of the table PERSON_PROFILE_NAMES once the rowid is known seems the bottleneck , but I don't know 'normal' times : I will check tomorrow when I am at work.
I wonder at the meaning of rows in the Row Source Operation listing
the number of 4923 doesn't match 85079 SMITH's,
does the 289 match the total number of 'KUMAR%' ?
If it means (anybody else?) the number of records needed to "read" until 200 records are found that match all criteria , then what could be causing the SMITH's to have so many rejected records to reach that 200 ? (legal entity changes?)
Any idea where the "SYS_ALIAS_2" comes from when referring to the PERSONPROFILE table ?
now there is no 'db file sequential read' and no 'physical read' (pr=0)
so probably ORACLE has all necessary blocks in memory from a previous query.
When you (or somebody else) queries the same (limited amount of ) data twice the second query is much faster.
In sqlplus > set timed on
and get another rare name twice .
The time necessary to get the data of the table PERSON_PROFILE_NAMES once the rowid is known seems the bottleneck , but I don't know 'normal' times : I will check tomorrow when I am at work.
I wonder at the meaning of rows in the Row Source Operation listing
the number of 4923 doesn't match 85079 SMITH's,
does the 289 match the total number of 'KUMAR%' ?
If it means (anybody else?) the number of records needed to "read" until 200 records are found that match all criteria , then what could be causing the SMITH's to have so many rejected records to reach that 200 ? (legal entity changes?)
Any idea where the "SYS_ALIAS_2" comes from when referring to the PERSONPROFILE table ?
ASKER
When you (or somebody else) queries the same (limited amount of ) data twice the second query is much faster.
Yes.
does the 289 match the total number of 'KUMAR%' ?
No.Total records with Kumar's are 3506 and with gender_id=2 its 2535.
the number of records needed to "read" until 200 records are found that match all criteria , then what could be causing the SMITH's to have so many rejected records to reach that 200 ? (legal entity changes?)
Not sure.
Any idea where the "SYS_ALIAS_2" comes from when referring to the PERSONPROFILE table ?
Could be toad generating it.
Thanks
ASKER
the number of records needed to "read" until 200 records are found that match all criteria , then what could be causing the SMITH's to have so many rejected records to reach that 200 ? (legal entity changes?)
The filtering conditions legalentityaddresses.is_de
Thanks
I did not get any relevant data to compare at work today.
Toad wil not change your query so the SYS_ALIAS_2 has te be something of Oracle.
I'm stuck : the answers don't hint me to take next steps.
For myself I would get the sql-part for the v1 of the view , add the where condition on lastname , is deleted and 200 rows and strip it to the essentials : cutting (or commenting) al joins leaving only
REF_CONTACT_DETAILS
PERSON_PROFILE_NAMES
If that performs then
add the join to
PERSON_PROFILE
etc
until you reach the step it hurts
Use other names for subsequent queries to minimize the effects of ORACLE keeping datablocks in cache.
Watch OEM for the execution plans
Toad wil not change your query so the SYS_ALIAS_2 has te be something of Oracle.
I'm stuck : the answers don't hint me to take next steps.
For myself I would get the sql-part for the v1 of the view , add the where condition on lastname , is deleted and 200 rows and strip it to the essentials : cutting (or commenting) al joins leaving only
REF_CONTACT_DETAILS
PERSON_PROFILE_NAMES
If that performs then
add the join to
PERSON_PROFILE
etc
until you reach the step it hurts
Use other names for subsequent queries to minimize the effects of ORACLE keeping datablocks in cache.
Watch OEM for the execution plans
ASKER
Simple queries joining person_profile_names and person_profile table takes 1:30 secs
even combination of LAST_NAME and DOB is also same.
not sure why even hitting directly to base tables is also slow,do you think the tables could be fragmented?
or Rebuilding indexes would help?
Thanks
PLAN_TABLE_OUTPUT
SQL_ID fhmhhma3tc4qx, child number 0
-------------------------------------
select a.first_name,a.last_name from xmatapp.person_profile_names
a,xmatapp.person_profile b where a.pp_legalentity_number=B.PP_LEGALENTIT
Y_NUMBER and a.last_name like 'SMITH%'AND b.GENDER_ID=2
Plan hash value: 31641444
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 31 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 12 | 372 | 31 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| PERSON_PROFILE_NAMES | 12 | 264 | 7 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX$$_D7D50001 | 12 | | 3 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | XPK_PERSON_PROFILE | 1 | | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | PERSON_PROFILE | 1 | 9 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."LAST_NAME" LIKE 'SMITH%')
filter("A"."LAST_NAME" LIKE 'SMITH%')
5 - access("A"."PP_LEGALENTITY_NUMBER"="B"."PP_LEGALENTITY_NUMBER")
6 - filter("B"."GENDER_ID"=2)
even combination of LAST_NAME and DOB is also same.
PLAN_TABLE_OUTPUT
SQL_ID bfa9fjdcydwv5, child number 0
-------------------------------------
select a.first_name,a.last_name from xmatapp.person_profile_names
a,xmatapp.person_profile b where a.pp_legalentity_number=b.pp_legalentit
y_number and a.last_name like 'BAKER%' and
b.dob=to_date('08-21-1955','MM-DD-YYYY')
Plan hash value: 31641444
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 31 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 12 | 468 | 31 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| PERSON_PROFILE_NAMES | 12 | 264 | 7 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX$$_D7D50001 | 12 | | 3 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | XPK_PERSON_PROFILE | 1 | | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | PERSON_PROFILE | 1 | 17 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."LAST_NAME" LIKE 'BAKER%')
filter("A"."LAST_NAME" LIKE 'BAKER%')
5 - access("A"."PP_LEGALENTITY_NUMBER"="B"."PP_LEGALENTITY_NUMBER")
6 - filter("B"."DOB"=TIMESTAMP' 1955-08-21 00:00:00')
not sure why even hitting directly to base tables is also slow,do you think the tables could be fragmented?
or Rebuilding indexes would help?
Thanks
ASKER
Its a composite index on LAST_NAME and IS_DELETED column.
It looks like 'BROWN%' and application provides it.
Thanks