?
Solved

Query Running Slow

Posted on 2012-08-22
10
Medium Priority
?
259 Views
Last Modified: 2012-12-18
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
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
********************************************************************************

Open in new window


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")
 

Open in new window



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;

Open in new window



PERSONPROFILENAMES-16 Milion rows
LEGALENTITYADDRESSES-34 Milion rows
PERSONPROFILE-16 Milion rows
LEGALENTITY 17 Milion rows
refcontactdetails 45 rows


Thanks
0
Comment
Question by:monto1
  • 6
  • 4
10 Comments
 
LVL 21

Accepted Solution

by:
flow01 earned 1500 total points
ID: 38320287
How is index IDX$$_D7D50001    defined ? (on lastname ?)
How does your like look when searching for BROWN  ? (  like '%BROWN%' ?)  ,
thus the end user provide the '%'  or  is the application ?
0
 

Author Comment

by:monto1
ID: 38320371
How is index IDX$$_D7D50001    defined ? (on lastname ?)

Its a composite index on LAST_NAME and IS_DELETED column.

How does your like look when searching for BROWN  ? (  like '%BROWN%' ?)  ,
thus the end user provide the '%'  or  is the application ?

It looks like 'BROWN%' and application provides it.

Thanks
0
 
LVL 21

Assisted Solution

by:flow01
flow01 earned 1500 total points
ID: 38321200
In this case(using like on the first column) the IS_DELETED part of the index is not used, but I don't think thats the problem.
The v1 and v2 subqueries can be eliminated but I don't expect that to be the problem either.

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

explain plan for
select ....
from
 CUSTOMER_IND_SEARCH_VIEW customerin0_ where (customerin0_.LAST_NAME like :var1)
  and customerin0_.genderid=:var2 and rownum<=200

or

explain plan for
select ....
from
 CUSTOMER_IND_SEARCH_VIEW customerin0_ where (customerin0_.LAST_NAME like 'SMITH%')
  and customerin0_.genderid= '2' and rownum<=200

If it came from the application can you explain the previous statement (with substituted) result and show the results

Does it also take as long with gender female ?  What % from customer is male ?. Whats the number of rows in table PERSONPROFILENAMES with name starting with  'SMITH' (or 'BROWN');

I assume gathered statistics on the table are based on recent data ?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:monto1
ID: 38321669
Thanks flow01.Here are the details.

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
********************************************************************************

Open in new window


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
********************************************************************************

Open in new window


There are 45411 rows with SMITH and genderid=2,Personprofilenames has more than 16 Milion rows

Whats the number of rows in table PERSONPROFILENAMES with name starting with  'SMITH' (or 'BROWN'); 

Open in new window


SMITHS  are 85079 and browns 51767.

What % from customer is male 

Open in new window


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
0
 

Author Comment

by:monto1
ID: 38321777
Stats were collected on the recent data.Have job scheduled for stale stats also.

Thanks
0
 
LVL 21

Expert Comment

by:flow01
ID: 38322516
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 ?
0
 

Author Comment

by:monto1
ID: 38325285
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
0
 

Author Comment

by:monto1
ID: 38325646
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_deleted =0 and refcontactdetails.VALUE = 'PRA'

Thanks
0
 
LVL 21

Expert Comment

by:flow01
ID: 38326099
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
0
 

Author Comment

by:monto1
ID: 38327933
Simple queries joining person_profile_names and person_profile table takes 1:30 secs

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)
 

Open in new window


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')
 

Open in new window


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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month16 days, 13 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question