Hi All,
we have OACS4.6 running on Oracle 8.1.7 (with patch) currently instance is doing heavy hard parsing ,when i looked at v$sqlarea this is one of the most executed query
select ff.package_id, dca.pretty_name as community_name,
ff.forum_id, ff.name as forum_name, m.subject, m.message_id,
s.node_id as forums_node_id, s_comm.node_id as community_node_id,
m.last_child_post
from forums_forums ff, site_nodes s, site_nodes s_comm,
apm_packages p, forums_messages m,dotlrn_communities_all dca
where s.object_id = ff.package_id
and s_comm.node_id = s.parent_id
and p.package_id = s_comm.object_id
and ff.package_id in ([join $list_of_package_ids ,])
and ff.enabled_p = 't'
and dca.package_id=s_comm.object_id
and m.forum_id = ff.forum_id
and m.parent_id is null
and m.state = 'approved'
and m.last_child_post > sysdate - :days
order by lower(community_name), lower(forum_name), m.last_child_post desc
does this mean that it is beeing parsed every time ?.
i followed this thread to identify such queries
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1163635055580
is there any posibilty to avoid this operation "and ff.package_id in ([join $list_of_package_ids ,])"
the same above query will be executed several hundred times per day
can anybody throw light on that?
Thanks
Madhu
