Re: Query Performance Issue
From: Rakesh Ra <rakeshra.tr_at_gmail.com>
Date: Wed, 24 Apr 2019 11:55:16 +0530
Message-ID: <CAOGpvWrFesoNsai_ZvsJS-oE436cskZkagUx1-gW_MraTLXy5w_at_mail.gmail.com>
| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | 1 | | 1
|00:00:51.28 | 1574K| 849K| | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1
|00:00:51.28 | 1574K| 849K| | | |
| 2 | VIEW | VW_DAG_0 | 1 | 256 |
Date: Wed, 24 Apr 2019 11:55:16 +0530
Message-ID: <CAOGpvWrFesoNsai_ZvsJS-oE436cskZkagUx1-gW_MraTLXy5w_at_mail.gmail.com>
Hi Jonathan,
I tried the virtual column way .. Details as below... The query ran for 51 seconds...
alter table TMS.USERS add (not_deleted_user generated always as (case when delete_flag = 'N' then user_key end));
create index TMS.users_fbi1 on TMS.USERS(not_deleted_user);
alter table TMS.USER_GROUP add (not_deleted_group generated always as (case when delete_flag = 'N' then GROUP_TREE_KEY end));
alter table TMS.USER_GROUP add (not_deleted_user generated always as (case when delete_flag = 'N' then USER_KEY end));
The above columns are already indexed.
Collected stats
begin
dbms_stats.gather_table_stats(
ownname=> 'TMS',
tabname=> 'USER_GROUP' ,
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=> DBMS_STATS.AUTO_CASCADE,
degree=> DBMS_STATS.AUTO_DEGREE,
no_invalidate=> false,
granularity=> 'AUTO',
method_opt=> 'FOR ALL COLUMNS SIZE REPEAT');
end;
/
begin
dbms_stats.gather_table_stats(
ownname=> 'TMS',
tabname=> 'USERS' ,
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=> DBMS_STATS.AUTO_CASCADE,
degree=> DBMS_STATS.AUTO_DEGREE,
no_invalidate=> false,
granularity=> 'AUTO',
method_opt=> 'FOR ALL COLUMNS SIZE REPEAT');
end;
/
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';
COUNT(DISTINCTU.NOT_DELETED_USER)
256828
Elapsed: 00:00:51.29
Plan hash value: 250484473
| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | 1 | | 1
|00:00:51.28 | 1574K| 849K| | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1
|00:00:51.28 | 1574K| 849K| | | |
| 2 | VIEW | VW_DAG_0 | 1 | 256 |
256K|00:00:51.27 | 1574K| 849K| | | |
| 3 | HASH GROUP BY | | 1 | 256 |
256K|00:00:51.24 | 1574K| 849K| 29M| 6062K| 19M (0)|
| 4 | NESTED LOOPS | | 1 | 256 |
389K|00:00:50.92 | 1574K| 849K| | | |
|* 5 | TABLE ACCESS FULL| USER_GROUP | 1 | 256 |
389K|00:00:49.25 | 849K| 849K| | | |
|* 6 | INDEX RANGE SCAN | USERS_FBI1 | 389K| 1 |
389K|00:00:01.53 | 725K| 0 | | | | ---------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------
5 - filter(CASE "DELETE_FLAG" WHEN 'N' THEN "GROUP_TREE_KEY" END
=:SYS_B_0)
6 - access("U"."NOT_DELETED_USER"=CASE "DELETE_FLAG" WHEN 'N' THEN
"USER_KEY" END )
On Tue, Apr 23, 2019 at 9:32 PM Rakesh Ra <rakeshra.tr_at_gmail.com> wrote:
> Hi Jonathan, > > Thanks,. > > Below is the data distribution for USER_GROUP and USERS table. I am > working out on your virtual column suggestion.. > > > TMS.USER_GROUP > DE COUNT(*) > -- ---------- > N 57725516 > Y 485 > > Regards, > RRA > > > TMS.USERS > > DE COUNT(*) > -- ---------- > N 4052893 > Y 9 > > On Tue, Apr 23, 2019 at 6:54 PM Jonathan Lewis < > jonathan_at_jlcomp.demon.co.uk> wrote: > >> >> First problem - you've enabled cursor sharing: is this set to FORCE or >> SIMILAR ? The answer doesn't really matter because you don't have any >> histograms in place to encourage the optimizer into thinking it should >> investigate adaptive_cursor sharing. >> >> 2nd problem - there's nothing to help the optimizer work out the impact >> of the "delete_flag" or take advantage of it. What fraction of the >> user_group data has delete_flag = 'N' (or nvl(delete_flag,'N') = 'N' as >> you have it in the query ? What impact does the delete flag have on the >> worst cases of group_tree_key - is it possible that by the time you've >> restricted yourself to the delete_flag = 'N' rows there are a relatively >> small number of rows left for even the worst case group_tree_key. >> >> If (after allowing for the effects of the delete_flag) you still have a >> massive skew in the number of rows for a few group_tree_key values then >> you're going to have to think about either using materialized views, or >> giving the optimizer a histogram (or two) to work with and persuading it to >> use adaptive cursor sharing. >> >> Please tell us: >> 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 Wed Apr 24 2019 - 08:25:16 CEST