Question

Has anyone had to use hints or build stats on the APEX_COLLECTIONS view? A colleague made an excellent point yesterday that our stats are gathered at night when the collections tables are usually empty. If you have a lot of data in a Collection that joins to other tables it doesn't do an optimal execution plan.

For now he's using some hints to force the use of the APEX_COLLECTIONS view. He'd like to manually modify the stats for the wwv_flow collection tables. Has anyone had similar issues? If so what did you do?

Thank you,

Martin
http://apex-smb.blogspot.com
http://apex.stackexchange.com

Answer 1

Your best bet would probably be to drop the statistics for those tables entirely - that way Oracle will use dynamic sampling on the tables when it parses the queries, and (hopefully) get some fairly accurate stats.