Re: Query Performance Issue

BTW forgot to mention that the database is of version 11.2.0.4.1 running on OEL 6.3. Thanks,
Rakesh RA

On Tue, Apr 23, 2019 at 5:38 PM Rakesh Ra <rakeshra.tr_at_gmail.com> wrote:

> Hi All,
>
> I have a query where in application has a read timeout set for 20 seconds.
>
>
> SELECT COUNT (DISTINCT U.USER_KEY) FROM TMS.USERS U JOIN
> TMS.USER_GROUP UG ON U.USER_KEY = UG.USER_KEY WHERE
> UG.GROUP_TREE_KEY = 'ia744d7790000015b347a4749dd5889b8' and UG.delete_flag
> ='N' and U.delete_flag ='N';
>
> With most bind values of group_tree_key the query runs within 20 seconds.
> But with GROUP_TREE_KEY having more number of records in TMS.USER_GROUP the
> query runs for more than 20 seconds and then application fails with "Socket
> Read Timeout".
>
> Below are few details related to table stats and column stats. There is
> huge skewness in data for GROUP_TREE_KEY for the table TMS.USER_GROUP. I
> tried to collect the column stats for the table TMS.USER_GROUP for the
> column GROUP_TREE_KEY but that didn't have much effect. Is there any way we
> can optimize the query to have a better run time?
>
>
> TMS.USER_GROUP DETAILS
> ==========================
>
> OWNER TABLE_NAME
> LAST_ANALYZED NUM_ROWS
> ------------------------------ ------------------------------
> ----------------------------- ----------
> TMS USER_GROUP 01-FEB-2019
> 23:21:25 55479760
>
>
>
> OWNER TABLE_NAME COLUMN_NAME
> NUM_DISTINCT NUM_BUCKETS HISTOGRAM
> ------------------------------ ------------------------------
> ------------------------------ ------------ ----------- ---------------
> TMS USER_GROUP
> UPDATED_TIMESTAMP 2851921 1 NONE
> TMS USER_GROUP STOP_DATE
> 4166 1 NONE
> TMS USER_GROUP START_DATE
> 3301 1 NONE
> TMS USER_GROUP GROUP_TREE_KEY
> 87351 1 NONE
> TMS USER_GROUP USER_KEY
> 2112700 1 NONE
> TMS USER_GROUP DELETE_FLAG
> 2 1 NONE
>
>
>
>
> TABLE_NAME COLUMN_NAME INDEX_NAME
> COLUMN_POSITION
> --------------------------- ---------------------------
> ------------------------------------ ---------------
> USER_GROUP USER_KEY XPKUSER_GROUP
> 1
> USER_GROUP GROUP_TREE_KEY XPKUSER_GROUP
> 2
> USER_GROUP START_DATE XPKUSER_GROUP
> 3
> USER_GROUP STOP_DATE XPKUSER_GROUP
> 4
> USER_GROUP GROUP_TREE_KEY UG_FK_GROUPKEY
> 1
> USER_GROUP USER_KEY UG_USER_KEY
> 1
> USER_GROUP UPDATED_TIMESTAMP USER_GROUP_IDX
> 1
>
> 7 rows selected.
>
>
> OWNER INDEX_NAME
> LAST_ANALYZED NUM_ROWS NUM_ROWS CLUSTERING_FACTOR
> ------------------------------ ------------------------------------
> ----------------------------- ---------- ---------- -----------------
> TMS XPKUSER_GROUP 01-FEB-2019
> 23:23:32 55821380 55821380 50385340
> TMS UG_FK_GROUPKEY 01-FEB-2019
> 23:24:36 56195400 56195400 11056540
> TMS UG_USER_KEY 01-FEB-2019
> 23:25:55 55064620 55064620 45906520
> TMS USER_GROUP_IDX 01-FEB-2019
> 23:26:54 55041170 55041170 15547360
>
>
>
> TMS.USERS DETAILS
> ==================
>
> OWNER TABLE_NAME LAST_ANALYZED
> NUM_ROWS
> ------------------------------ ---------------------------
> ----------------------------- ----------
> TMS USERS 16-APR-2019
> 23:23:18 4093040
>
>
> OWNER TABLE_NAME COLUMN_NAME
> NUM_DISTINCT NUM_BUCKETS HISTOGRAM
> ------------------------------ ---------------------------
> --------------------------- ------------ ----------- ---------------
> TMS USERS ENCRYPTED_PASSWORD
> 0 0 NONE
> TMS USERS DISABLE_GRP_IPR
> 1 1 NONE
> TMS USERS NEW_USER
> 2 1 NONE
> TMS USERS IP_RESTRICTED
> 1 1 NONE
> TMS USERS REASON_CODE_KEY
> 4 1 NONE
> TMS USERS LOGIN_LIMIT
> 3 1 NONE
> TMS USERS SHARED
> 1 1 NONE
> TMS USERS USER_PWD_CLUE
> 0 0 NONE
> TMS USERS STOP_DATE
> 111803 1 NONE
> TMS USERS START_DATE
> 289426 1 NONE
> TMS USERS INACTIVE_STATE
> 1 1 NONE
> TMS USERS DOMAIN_KEY
> 1 1 NONE
> TMS USERS FAILED_LOGIN_CNT
> 52 1 NONE
> TMS USERS PWD_EXPIRED
> 1 1 NONE
> TMS USERS USER_GUID
> 4093040 1 NONE
> TMS USERS USER_CREATE_DATE
> 426536 1 NONE
> TMS USERS USER_LASTMOD_DATE
> 596781 1 NONE
> TMS USERS USER_PWD_RESPONSE
> 0 0 NONE
> TMS USERS USER_PWD_CHALLENGE
> 0 0 NONE
> TMS USERS USER_EMAIL
> 26162 1 NONE
> TMS USERS USER_ID
> 4093040 1 NONE
> TMS USERS USER_PASSWORD
> 2479152 1 NONE
> TMS USERS USER_MIDDLE_NAME
> 0 0 NONE
> TMS USERS USER_FIRST_NAME
> 11933 1 NONE
> TMS USERS USER_LAST_NAME
> 33227 1 NONE
> TMS USERS USER_KEY
> 4093040 1 NONE
> TMS USERS DELETE_FLAG
> 2 1 NONE
> TMS USERS SYS_NC00027$
> 1000 1 NONE
>
>
> TABLE_NAME COLUMN_NAME INDEX_NAME
> COLUMN_POSITION
> --------------------------- ---------------------------
> ------------------------------------ ---------------
> USERS USER_KEY XPKUSER
> 1
> USERS USER_GUID PSU_USER_GUID_IDX
> 1
> USERS USER_ID
> PSU_USER_ID_DOM_UQ_IDX 1
> USERS DOMAIN_KEY
> PSU_USER_ID_DOM_UQ_IDX 2
> USERS DOMAIN_KEY PSU_DKEY_EMAIL
> 1
> USERS USER_EMAIL PSU_DKEY_EMAIL
> 2
> USERS INACTIVE_STATE
> PSU_INACTIVE_STATE 1
> USERS REASON_CODE_KEY XIF5USER
> 1
> USERS SYS_NC00027$ MOD_IDX
> 1
>
> 9 rows selected.
>
> OWNER INDEX_NAME
> LAST_ANALYZED NUM_ROWS NUM_ROWS CLUSTERING_FACTOR
> ------------------------------ ------------------------------------
> ----------------------------- ---------- ---------- -----------------
> TMS XPKUSER 16-APR-2019
> 23:23:18 4237790 4237790 1774550
> TMS PSU_USER_GUID_IDX 16-APR-2019
> 23:23:19 4001180 4001180 1689690
> TMS PSU_USER_ID_DOM_UQ_IDX 16-APR-2019
> 23:23:19 3900400 3900400 1994620
> TMS PSU_DKEY_EMAIL 16-APR-2019
> 23:23:27 4268590 4268590 275130
> TMS PSU_INACTIVE_STATE 16-APR-2019
> 23:23:27 8694 8694 6123
> TMS XIF5USER 16-APR-2019
> 23:23:27 8840 8840 6337
> TMS MOD_IDX 16-APR-2019
> 23:23:31 3972540 3972540 3902610
>
>
> I understand all details are not supplied however, I am ready to get that
> if asked for.,
>
> Thanks,
> Rakesh RA
>
>
>

--
http://www.freelists.org/webpage/oracle-l