performance - Query taking hell lot of time -
our system quite big having around 100+ tables in schema. there 1 business requirement decided handle in stored procedures. in our application have java chosen use stored procedure. in order fulfill requirement created 3 functions. these functions part of sql query fired fetch count , display records. have sql timeout set in connection set 60 seconds. when run query records of 40k, operation gets timed out , don't result. pasting here actual implementation of these 3 functions , below them query makes use of these created function.
in query below have marked function calling in bold letter.
i looking @ expert advice can tune implementation work on 100k of data. have oracle 11gr1.. implementation access web in ajax call.
do let me know if need else.
create index idx_snm_entity_name on sml_notification_main(entity_name) tablespace cm_index; create or replace function is_users_in_same_business_unit (usermasterid varchar2, ctocode varchar2) return boolean v_count number(4); begin if ctocode not null select count(*) v_count sml_user u, sml_team_member tm, sml_usrprof_cto_map ctomap, code_value cv u.status = 'active' , u.master_id null , u.id = tm.user_id , tm.profile_id = ctomap.user_profile_id , ctomap.cto_code_id = cv.id , cv.code_value = ctocode , tm.team_id in (select child_id vw_team_relation_master connect prior a.child_id = a.parent_id start a.child_id = (select child_id vw_team_relation_master c parent_id null start c.child_id = (select t.id sml_team t, sml_team_member tm t.id = tm.team_id , t.status = 'active' , t.master_id null , tm.user_id = usermasterid) connect prior c.parent_id = c.child_id)); if v_count not null , v_count > 0 return true; else return false; end if; end if; return false; end; create or replace function can_user_access_customer( notificationdesc varchar2, userid varchar2, notificationtype varchar2, userrolecode varchar2, usermasterid varchar2, ctocode varchar2, secctocode varchar2, userproftempidcommercialind varchar2, userproftempidcorporateind varchar2, userproftempstcfind varchar2, userproftempidgsamind varchar2) return boolean -- function test user can access customer regsearchcount number(2); in_same_unit boolean; count_v number(2); begin in_same_unit := is_users_in_same_business_unit(usermasterid, ctocode); -- commercial select count(*) regsearchcount dual regexp_like (ctocode,'200[3-9]|20[1-4][0-9]|2[1-6][0-6][0-9]|29[0-6][0-9]|300[1-9]|30[1-6][0-9]|3[1-9][0-6][0-9]'); if regsearchcount > 0 -- user commercial -- check secondary cto code holder select count(*) count_v dual secctocode in (select distinct code_value sml_user, sml_team_member, sml_user_profile, sml_usrprof_cto_map, code_value sml_user.id = sml_team_member.user_id , sml_team_member.profile_id = sml_user_profile.id , sml_user_profile.id = sml_usrprof_cto_map.user_profile_id , sml_usrprof_cto_map.cto_code_id = code_value.id , sml_user.login_id = userid); if count_v not null , count_v > 0 return true; end if; if notificationtype='collateral' , userrolecode not null , userrolecode='rloc' -- user rloc return true; end if; if in_same_unit , userrolecode not null , userrolecode in ('rmtl','grphd','sechd') -- user belongs gh, sh etc return true; end if; end if; -- corporate select count(*) regsearchcount dual regexp_like (ctocode,'1[0-9][0-6][0-9]'); if regsearchcount > 0 if in_same_unit , userrolecode not null , userrolecode='grphd' , notificationdesc!='0 days due expiry of collateral' -- user belongs gh return true; end if; if in_same_unit , userrolecode not null , userrolecode='rmtl' , notificationdesc='0 days due expiry of collateral' return true; end if; end if; -- stcf select count(*) regsearchcount dual regexp_like (ctocode, '20[5-6][0-9]'); if regsearchcount > 0 if in_same_unit , userrolecode not null , userrolecode = 'rmtl' return true; end if; end if; return false ; end; create or replace function is_notification_access_passed( notificationdesc varchar2, userid varchar2, notificationtype varchar2, usermasterid varchar2, userrolecode varchar2, ctocode varchar2, secctocode varchar2, userproftempidcommercialind varchar2, userproftempidcorporateind varchar2, userproftempstcfind varchar2, userproftempidgsamind varchar2) return varchar2 deterministic -- functions test, instance of notification viewable logged in user, on basis of notification type -- , other user details etc, defined type 'collateral' , 'implementation instruction' regsearchcount number(2); count_v number(2); begin if notificationtype = 'implementation instruction' if can_user_access_customer(notificationdesc,userid, notificationtype userrolecode,usermasterid ctocode,secctocode, userproftempidcommercialind, userproftempidcorporateind, userproftempstcfind, userproftempidgsamind) if userrolecode = 'ldu' select count(*) regsearchcount dual regexp_like (ctocode,'1[0-9][0-6][0-9]'); if regsearchcount >0 return 'true'; else return 'false'; end if; else return 'true'; end if; end if; end if; if notificationtype = 'collateral' if userrolecode ='wloc' return 'true'; end if; -- check primary cto code holder select count(*) count_v dual ctocode in (select distinct code_value sml_user, sml_team_member, sml_user_profile, sml_usrprof_cto_map, code_value sml_user.id = sml_team_member.user_id , sml_team_member.profile_id = sml_user_profile.id , sml_user_profile.id = sml_usrprof_cto_map.user_profile_id , sml_usrprof_cto_map.cto_code_id = code_value.id , sml_user.login_id = userid); if count_v not null , count_v > 0 return 'true'; end if; -- check other conditions if can_user_access_customer(notificationdesc,userid,notificationtype, userrolecode,usermasterid,ctocode, secctocode,userproftempidcommercialind, userproftempidcorporateind, userproftempstcfind, userproftempidgsamind) return 'true'; end if; else return 'true'; end if; return 'false'; end; the query:
select * sml_notification_main notification notification.deprecated='n' , (maker_id = 'see_rm' or to_char(cto_code) in (select distinct code_value sml_user, sml_team_member, sml_user_profile, sml_usrprof_cto_map, code_value sml_user.id = sml_team_member.user_id , sml_team_member.profile_id = sml_user_profile.id , sml_user_profile.id = sml_usrprof_cto_map.user_profile_id , sml_usrprof_cto_map.cto_code_id = code_value.id , sml_user.login_id = 'see_rm' , entity_name != 'collateral') or to_char(sec_cto_code) in (select distinct code_value sml_user, sml_team_member, sml_user_profile, sml_usrprof_cto_map, code_value sml_user.id = sml_team_member.user_id , sml_team_member.profile_id = sml_user_profile.id , sml_user_profile.id = sml_usrprof_cto_map.user_profile_id , sml_usrprof_cto_map.cto_code_id = code_value.id , entity_name != 'collateral' , sml_user.login_id ='see_rm') or role_code in (select distinct code sml_user a, code_value b a.role_id = b.id , login_id = 'see_rm') or substr(role_code, 1, 4) in (select distinct code sml_user a, code_value b a.role_id = b.id , login_id = 'see_rm') or substr(role_code, 6, 4) in (select distinct code sml_user a, code_value b entity_name = 'insurance' or entity_name = 'collateral' , a.role_id = b.id , login_id = 'see_rm') or substr(role_code, 11, 2) in (select distinct code sml_user a, code_value b a.role_id = b.id , login_id = 'see_rm') or team_lead in (select to_char(a.id) sml_user entity_name != 'collateral' , a.login_id = 'see_rm') or rm_id in (select to_char(a.id) sml_user entity_name != 'collateral' , entity_name = 'postapproval cp/covnent' , description != '30 days before due date' , a.login_id = 'see_rm') or (entity_name != 'collateral' , rm_id in (select rm_id vw_sm_rm_team_lead vw_sm_rm_team_lead.rmtl_id in (select to_char(a.id) sml_user a.login_id = 'see_rm'))) or (entity_name = 'enquiry' , rm_id in (select bca_id vw_sm_bca_team_lead vw_sm_bca_team_lead.bcatl_id in (select to_char(a.id) sml_user a.login_id = 'see_rm'))) or group_head in (select to_char(a.id) sml_user a.login_id = 'see_rm' , entity_name != 'collateral') or (entity_name != 'collateral' , sector_head ('%,'|| (select to_char(a.id) || ',' id sml_user a.login_id = 'see_rm' , status='active' , master_id null) || '%')) or (entity_name = 'collateral' , is_notification_access_passed(description, 'see_rm', entity_name, '1176', 'rm', to_char(cto_code), to_char(sec_cto_code), 'n','n','n','n') = 'true') or (rm_id in (select rm_id vw_sm_rm_team_lead rmtl_id in (select distinct su.id rmtl_id sml_user su inner join sml_team_member member on su.id=member.user_id inner join sml_team team on member.team_id=team.id , team.team_role_id = (select id code_value code='rmtl' , status='active' , master_id not null , code_set_value_id = (select id code_set code = 'team_role' , master_id not null)) inner join sml_team_child parent on team.id=parent.child_id inner join sml_team_member grop on grop.team_id=parent.parent_id inner join sml_team st on st.id=grop.team_id su.status = 'active' , st.status='active' , grop.user_id = (select to_char(a.id) sml_user a.master_id null , a.status='active' , a.login_id = 'see_rm'))) , entity_name != 'collateral' , ((entity_name = 'implementation instruction' , is_notification_access_passed(description, 'see_rm', entity_name, '1176', 'rm', to_char(cto_code), to_char(sec_cto_code), 'n','n','n','n') = 'true') or (entity_name = 'post disbursement') or (entity_name = 'postapproval cp/covnent' , description = '7 days before due date') or (entity_name = 'facility' , description != '2 days due expiry of facility') or (entity_name = 'insurance') or (entity_name = 'call report' , description = '2 days before exceeded 11 months')))) ****************************************execution plan of above query ***************** execution plan ---------------------------------------------------------- 0 select statement optimizer mode=all_rows (cost=294 card=17 k bytes=3 m) 1 0 filter 2 1 table access full uobcm.sml_notification_main (cost=294 card=37 k bytes=6 m) 3 1 filter 4 3 nested loops 5 4 nested loops (cost=47 card=1 bytes=80) 6 5 hash join (cost=23 card=24 bytes=1 k) 7 6 nested loops (cost=8 card=12 bytes=432) 8 7 hash join (cost=8 card=12 bytes=384) 9 8 table access index rowid uobcm.sml_user (cost=4 card=3 bytes=72) 10 9 index range scan uobcm.idx_usr_loginid (cost=1 card=3) 11 8 index fast full scan uobcm.idx_tmbr_tmuserpf (cost=3 card=1 k bytes=8 k) 12 7 index unique scan uobcm.pk_sml_user_profile (cost=0 card=1 bytes=4) 13 6 view uobcm.index$_join$_005 (cost=15 card=1 k bytes=14 k) 14 13 hash join 15 14 index fast full scan uobcm.idx_usrprofcto_usrprof (cost=6 card=1 k bytes=14 k) 16 14 index fast full scan uobcm.idx_usrprofcto_cto (cost=11 card=1 k bytes=14 k) 17 5 index unique scan uobcm.pk_code_value (cost=0 card=1) 18 4 table access index rowid uobcm.code_value (cost=1 card=1 bytes=36) 19 1 filter 20 19 nested loops 21 20 nested loops (cost=47 card=1 bytes=80) 22 21 hash join (cost=23 card=24 bytes=1 k) 23 22 nested loops (cost=8 card=12 bytes=432) 24 23 hash join (cost=8 card=12 bytes=384) 25 24 table access index rowid uobcm.sml_user (cost=4 card=3 bytes=72) 26 25 index range scan uobcm.idx_usr_loginid (cost=1 card=3) 27 24 index fast full scan uobcm.idx_tmbr_tmuserpf (cost=3 card=1 k bytes=8 k) 28 23 index unique scan uobcm.pk_sml_user_profile (cost=0 card=1 bytes=4) 29 22 view uobcm.index$_join$_010 (cost=15 card=1 k bytes=14 k) 30 29 hash join 31 30 index fast full scan uobcm.idx_usrprofcto_usrprof (cost=6 card=1 k bytes=14 k) 32 30 index fast full scan uobcm.idx_usrprofcto_cto (cost=11 card=1 k bytes=14 k) 33 21 index unique scan uobcm.pk_code_value (cost=0 card=1) 34 20 table access index rowid uobcm.code_value (cost=1 card=1 bytes=36) 35 1 nested loops 36 35 nested loops (cost=5 card=1 bytes=39) 37 36 table access index rowid uobcm.sml_user (cost=3 card=3 bytes=75) 38 37 index range scan uobcm.idx_usr_loginid (cost=1 card=3) 39 36 index unique scan uobcm.pk_code_value (cost=0 card=1) 40 35 table access index rowid uobcm.code_value (cost=1 card=1 bytes=14) 41 1 nested loops 42 41 nested loops (cost=5 card=1 bytes=39) 43 42 table access index rowid uobcm.sml_user (cost=3 card=3 bytes=75) 44 43 index range scan uobcm.idx_usr_loginid (cost=1 card=3) 45 42 index unique scan uobcm.pk_code_value (cost=0 card=1) 46 41 table access index rowid uobcm.code_value (cost=1 card=1 bytes=14) 47 1 nested loops (cost=9 card=3 bytes=117) 48 47 table access index rowid uobcm.code_value (cost=4 card=4 bytes=56) 49 48 index range scan uobcm.idx_cv_cdstatusid (cost=2 card=4) 50 47 table access full uobcm.sml_user (cost=5 card=2 bytes=50) 51 1 nested loops 52 51 nested loops (cost=5 card=1 bytes=39) 53 52 table access index rowid uobcm.sml_user (cost=3 card=3 bytes=75) 54 53 index range scan uobcm.idx_usr_loginid (cost=1 card=3) 55 52 index unique scan uobcm.pk_code_value (cost=0 card=1) 56 51 table access index rowid uobcm.code_value (cost=1 card=1 bytes=14) 57 1 filter 58 57 table access index rowid uobcm.sml_user (cost=4 card=1 bytes=24) 59 58 index range scan uobcm.idx_usr_loginid (cost=1 card=3) 60 1 filter 61 60 table access index rowid uobcm.sml_user (cost=4 card=1 bytes=24) 62 61 index range scan uobcm.idx_usr_loginid (cost=1 card=3) 63 1 filter 64 63 table access index rowid uobcm.sml_user (cost=4 card=1 bytes=24) 65 64 index range scan uobcm.idx_usr_loginid (cost=1 card=3) 66 1 nested loops 67 66 nested loops (cost=28 card=1 bytes=95) 68 67 hash join (cost=27 card=1 bytes=76) 69 68 nested loops 70 69 nested loops (cost=24 card=7 bytes=476) 71 70 nested loops (cost=16 card=8 bytes=472) 72 71 hash join (cost=10 card=12 bytes=612) 73 72 nested loops (cost=6 card=3 bytes=129) 74 73 table access index rowid uobcm.sml_user (cost=2 card=1 bytes=19) 75 74 index unique scan uobcm.pk_sml_user (cost=1 card=1) 76 73 table access index rowid uobcm.sml_user (cost=4 card=3 bytes=72) 77 76 index range scan uobcm.idx_usr_loginid (cost=1 card=3) 78 72 index fast full scan uobcm.idx_tmbr_tmuserpf (cost=3 card=1 k bytes=8 k) 79 71 table access index rowid uobcm.sml_team_child (cost=1 card=1 bytes=8) 80 79 index range scan uobcm.idx_tchild_team (cost=0 card=1) 81 70 index unique scan uobcm.pk_sml_team (cost=0 card=1) 82 69 table access index rowid uobcm.sml_team (cost=1 card=1 bytes=9) 83 82 table access index rowid uobcm.code_value (cost=3 card=1 bytes=35) 84 83 index range scan uobcm.idx_cv_cs (cost=1 card=19) 85 84 table access full uobcm.code_set (cost=18 card=1 bytes=38) 86 68 index fast full scan uobcm.idx_tmbr_tmuserpf (cost=3 card=4 bytes=32) 87 67 index unique scan uobcm.pk_sml_team (cost=0 card=1) 88 66 table access index rowid uobcm.sml_team (cost=1 card=1 bytes=19) 89 1 table access index rowid uobcm.sml_user (cost=4 card=1 bytes=43) 90 89 index range scan uobcm.idx_usr_loginid (cost=1 card=3) 91 1 nested loops 92 91 nested loops (cost=31 card=1 bytes=142) 93 92 nested loops (cost=30 card=1 bytes=123) 94 93 nested loops (cost=29 card=1 bytes=104) 95 94 nested loops (cost=26 card=3 bytes=288) 96 95 nested loops (cost=22 card=4 bytes=348) 97 96 hash join (cost=20 card=5 bytes=395) 98 97 nested loops 99 98 nested loops (cost=16 card=4 bytes=284) 100 99 nested loops (cost=12 card=4 bytes=208) 101 100 nested loops (cost=10 card=2 bytes=88) 102 101 nested loops (cost=7 card=3 bytes=105) 103 102 nested loops (cost=5 card=4 bytes=108) 104 103 table access index rowid uobcm.sml_user (cost=2 card=1 bytes=19) 105 104 index unique scan uobcm.pk_sml_user (cost=1 card=1) 106 103 index fast full scan uobcm.idx_tmbr_tmuserpf (cost=3 card=4 bytes=32) 107 106 table access index rowid uobcm.sml_user (cost=4 card=1 bytes=43) 108 107 index range scan uobcm.idx_usr_loginid (cost=1 card=3) 109 102 table access index rowid uobcm.sml_team_child (cost=1 card=1 bytes=8) 110 109 index range scan uobcm.idx_tchild_team (cost=0 card=1) 111 101 table access index rowid uobcm.sml_team (cost=1 card=1 bytes=9) 112 111 index unique scan uobcm.pk_sml_team (cost=0 card=1) 113 111 table access index rowid uobcm.code_value (cost=3 card=1 bytes=35) 114 113 index range scan uobcm.idx_cv_cs (cost=1 card=19) 115 114 table access full uobcm.code_set (cost=18 card=1 bytes=38) 116 100 index range scan uobcm.idx_tmbr_tmuserpf (cost=1 card=2 bytes=16) 117 99 index unique scan uobcm.pk_sml_user (cost=0 card=1) 118 98 table access index rowid uobcm.sml_user (cost=1 card=1 bytes=19) 119 97 index fast full scan uobcm.idx_tmbr_tmuserpf (cost=3 card=1 k bytes=8 k) 120 96 table access index rowid uobcm.sml_team_child (cost=1 card=1 bytes=8) 121 120 index range scan uobcm.idx_tchild_team (cost=0 card=1) 122 95 table access index rowid uobcm.sml_team (cost=1 card=1 bytes=9) 123 122 index unique scan uobcm.pk_sml_team (cost=0 card=1) 124 122 table access index rowid uobcm.code_value (cost=3 card=1 bytes=35) 125 124 index range scan uobcm.idx_cv_cs (cost=1 card=19) 126 125 table access full uobcm.code_set (cost=18 card=1 bytes=38) 127 94 index range scan uobcm.idx_tmbr_tmuserpf (cost=1 card=1 bytes=8) 128 93 table access index rowid uobcm.sml_team (cost=1 card=1 bytes=19) 129 128 index unique scan uobcm.pk_sml_team (cost=0 card=1) 130 92 index unique scan uobcm.pk_sml_team (cost=0 card=1) 131 91 table access index rowid uobcm.sml_team (cost=1 card=1 bytes=19) 132 1 nested loops 133 132 nested loops (cost=28 card=1 bytes=95) 134 133 hash join (cost=27 card=1 bytes=76) 135 134 nested loops 136 135 nested loops (cost=24 card=7 bytes=476) 137 136 nested loops (
this reasonably large query lots of sub queries, you'd need explain plan on , start chipping away @ "slow" bits. may query extract logic stored procedure , embed in query. 100,000 rows not much, should able tune lot.
Comments
Post a Comment