Re: Query Performance Issue
From: Rakesh Ra <rakeshra.tr_at_gmail.com>
Date: Tue, 23 Apr 2019 17:44:43 +0530
Message-ID: <CAOGpvWoWnw8fvTD+FWEYP-3tz05v2YJOjHDjAP6cgvVzKG5dVQ_at_mail.gmail.com>
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | |
Date: Tue, 23 Apr 2019 17:44:43 +0530
Message-ID: <CAOGpvWoWnw8fvTD+FWEYP-3tz05v2YJOjHDjAP6cgvVzKG5dVQ_at_mail.gmail.com>
Sorry again...
Missed to give the the execution plan.
Plan hash value: 1838219834
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | |
| 774 (100)| |
| 1 | SORT AGGREGATE | | 1 | 68
| | |
| 2 | VIEW | VW_DAG_0 | 318 | 21624
| 774 (1)| 00:00:10 |
| 3 | HASH GROUP BY | | 318 | 33708
| 774 (1)| 00:00:10 |
| 4 | NESTED LOOPS | | 318 | 33708
| 773 (0)| 00:00:10 |
| 5 | NESTED LOOPS | | 318 | 33708
| 773 (0)| 00:00:10 |
|* 6 | TABLE ACCESS BY INDEX ROWID| USER_GROUP | 318 | 22260
| 137 (0)| 00:00:02 |
|* 7 | INDEX RANGE SCAN | UG_FK_GROUPKEY | 635 | | 10 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | XPKUSER | 1 | | 1 (0)| 00:00:01 | |* 9 | TABLE ACCESS BY INDEX ROWID | USERS | 1 | 36
| 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
6 - filter(NVL("UG"."DELETE_FLAG",'N')=:SYS_B_0)
7 - access("UG"."GROUP_TREE_KEY"=:1)
8 - access("U"."USER_KEY"="UG"."USER_KEY")
9 - filter(NVL("U"."DELETE_FLAG",'N')=:SYS_B_1)
On Tue, Apr 23, 2019 at 5:40 PM Rakesh Ra <rakeshra.tr_at_gmail.com> wrote:
> 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-lReceived on Tue Apr 23 2019 - 14:14:43 CEST