oracle sql 优化 ，请各位看看哈

Question

select e.id,
e.content,
e.write_time,
e.orig_id,
e.from_client,
e.images,
u.nickname || '(' || u.realname || ')' as writer,
a.orig_content,
b.praise_times,
c.comment_times,
d.transfer_times
from wx_essay e
left join wx_user u on e.writer_id = u.id
left join (select oe.id, oe.content orig_content from wx_essay oe) a on e.orig_id = a.id
left join (select p.essay_id, count(1) praise_times
from wx_essay e, wx_praise p
where p.essay_id = e.id
group by p.essay_id) b on e.id = b.essay_id
left join (select c.essay_id, count(1) comment_times
from wx_essay e, wx_comments c
where c.essay_id = e.id
group by c.essay_id) c on e.id = c.essay_id
left join (select ee.orig_id, count(1) transfer_times
from wx_essay ee
group by ee.orig_id) d on e.id = d.orig_id
order by e.write_time desc；

wx_essay 和 wx_user 表 数据量分别 不到20万，其他几张表都是1000左右数据，
wx_essay 表id是主键、write_time、WRITER_ID、ORIG_ID分别加了索引；
wx_user表id为主键，现在这个查询根本卡死出不来
下面是解释计划如图

Answer 1

with tmp as
(select e.id, e.content, e.write_time, e.orig_id, e.from_client, e.images
from wx_essay e where e.orig_id = a.id)
select e.*,
u.nickname || '(' || u.realname || ')' as writer,
a.orig_content,
b.praise_times,
c.comment_times,
d.transfer_times
from tmp e
left join wx_user u
on e.writer_id = u.id
left join (select p.essay_id, count(1) praise_times
from tmp e, wx_praise p
where p.essay_id = e.id
group by p.essay_id) b
on e.id = b.essay_id
left join (select c.essay_id, count(1) comment_times
from tmp e, wx_comments c
where c.essay_id = e.id
group by c.essay_id) c
on e.id = c.essay_id
left join (select ee.orig_id, count(1) transfer_times
from tmp ee
group by ee.orig_id) d
on e.id = d.orig_id
order by e.write_time desc；