Re: Query Performance Issue
Date: Tue, 23 Apr 2019 13:24:42 +0000
Message-ID: <LO2P265MB0415065270EF84B7A4FAB03DA5230_at_LO2P265MB0415.GBRP265.PROD.OUTLOOK.COM>
a) number of users where delete_flag (or the nvl() version in the plan) = 'N', b) dittor for user_group c) if the number of rows left in in user_group is significantly reduced, show us the counts of the top 90 again after applying the predicate.
I wouldn't take the clustering strategy suggested by Mladen - too much risk of a huge increase in the size of the data segments. But I would consider creating an index on the user_group table that would allow the run-time engine to avoid visiting the table for this query - in the simplest case
user_group(group_tree_key, delete_flag, user_key) - probably compress 2, maybe compress 3. users(user_key, delete_flag)
Note that you can use the second index to cover the primary key on the users table in place of the xpkusers index Note, also, that your "foreign key index" on the user_group table on (user_key) is technically redundant as it is covered by the leading column of the primary key. Note, finally, that if you haven't enabled compression on any of your indexes you should look for cases where it will apply.
For a more complex, but potentially much more efficient, indexing strategy - if you find that a large fraction of the data is eliminated by your "delete_flag = 'N'" predicate then you could create function-based indexes (or, for easier reading, virtual columns which you index) so that you create indexes only for rows you are interested in, for example:
alter table users add not_deleted_user generated always as
case when delete_flag = 'N' then user_key end ;
create index users_fbi1 on users(not_deleted_user);
Similarly you could create a couple of virtual columns on user_group for "not_deleted_group" and "not_deleted_user", then your query becomes:
SELECT
COUNT (DISTINCT U.not_deleted_user)
FROM
TMS.USERS U
JOIN
TMS.USER_GROUP UG
ON
U.not_deleted_USER = UG.not_deleted_USER
WHERE
UG.not_deleted_GROUP = 'ia744d7790000015b347a4749dd5889b8'
;
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Rakesh Ra <rakeshra.tr_at_gmail.com> Sent: 23 April 2019 13:14:43
To: Oracle-L Freelists
Cc: Rakesh RA
Subject: Re: Query Performance Issue
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<mailto: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<mailto: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 - 15:24:42 CEST