本人水平有限，帮忙优化一下sql，谢谢

如下sql：
select *
  from (select rownum rn, t.*
          from (select usr_ent_id,
                       trr_id,
                       trr_name,
                       trr_gender,
                       trr_birthday,
                       trr_company,
                       trr_education,
                       trr_id_no,
                       trr_main_teach,
                       trr_mobile,
                       trr_email,
                       trr_itm_total_count
                  from teacherResume --讲师表(大约500数据)
                  left join reguser --用户表 (大约35万数据)
                    on trr_ent_id = usr_ent_id
                 WHERE trr_ent_id in
                       (select distinct gpm_ent_id_member
                          from groupmember --用户与用户组及用户组与用户组关系表(大约100万数据)
                         where gpm_type = 'USR_PARENT_USG'--用户与用户组
                           and sysdate between gpm_start_timestamp and
                               gpm_end_timestamp
                           and gpm_ent_id_group in
                               (select grp.gpm_ent_id_member
                                  from (select gpm_ent_id_member,
                                               gpm_ent_id_group
                                          from groupmember
                                         where gpm_type = 'USG_PARENT_USG'--用户组与用户组
                                           and sysdate between
                                               gpm_start_timestamp and
                                               gpm_end_timestamp) grp
                                 start with grp.gpm_ent_id_member in
                                            (select tce_ent_id
                                              from tcTrainingCenterTargetEntity --公司与用户组表
                                              where tce_tcr_id = 9)
                                connect by prior grp.gpm_ent_id_member =
                                            grp.gpm_ent_id_group))
                   and trr_status is null
                   AND trr_type = 'INNER'
                 ORDER BY trr_comment_avg_score desc) t
         where rownum <= 10)
 where rn > 0执行计划如下：说明： 1、每个公司下面有1000个左右用户组
       2、用户组有3.6万个左右
       3、一个公司可能对应多个用户组
在线等大虾们性能优化SQL