Смотрите, сейчас мы пытаемся побороть и оптимизировать простейшую операцию, вместо того, чтобы понять, нужна ли она вообще.
По описанию выделенным - это кеширование, что, поверьте, оракл умеет замечательно делать и без нас.
Материализованные представления - это не про кеширование, это либо предрасчет, когда масса каких-то диких по тяжести расчетов имеет значительную общую часть, либо решение вопросов транспорта, когда матвьюшка обновляется через какой-нибудь медленный дблинк, а отдает данные быстро. При этом обслуживание матвьюшек происходит не в пользовательских сессиях совсем.
Соответственно, бросайте переписывать кеширование на уровне своего кода. Если у вас эти гигантские расчеты сводятся к какому-то общему куску - сделайте этот кусок в материализованном отдельном представлении (тип обновления выбрать будет вам домашним заданием), а вместо гигантских расчетов просто берите предрасчет из матвьюшки и досчитывайте то, чем, собственно, различаются эти материализованные представления. Думаю, что это даст наибольший эффект, но и наиболее трудозатратно для архитектора.
Какой-то расчет можно класть в result_cache, да, но убедитесь, что у вас все же не 19.0.0.0, а патч не ниже 9 стоит, или, даже 13. Плохая у меня память на цифры, могу посмотреть потом, если не найдете. В Oracle result_cache достаточно тормозной, в отличие от того же MySQL, и у меня еще и сваливался в invalid достаточно беспричинно и слишком часто для продуктива. Кейс мы завели, но тут началось сами знаете что, и все зависло.
Что касается буферного кеша, то не забывайте, что его можно выделить в отдельный пул, если операция, которую вы борете, основная для вашей базы. Это делается
Код:
ALTER TABLE ... STORAGE (buffer_pool keep)
все, что с этим связано, можете почитать сами. Суть - заведение менее вымываемого кеша. Если у вас Solaris и ZFS, то поставьте filesystemio_options в async и дайте кешу операционки поработать, он там просто классный.
Кроме этого, можно кешировать табличку с помощью
Код:
ALTER TABLE .... CACHE
но на практике мне не удавалось воспользоваться этим без неприятных побочных явлений.
Не забывайте, что кеширование исследовать надо не только таблиц, но и индексов. В общем, поле для деятельности очень большое, вариантов вагоны, которые надо перебрать, исходя из общей загрузки базы.
Для начала я бы вообще взял и вот тот большой и страшный кусок скормил DBMS_SQLTUNE. Как им пользоваться, я тут где-то уже писал, поищите. Может, пару индексов добавите и не надо будет приседать по всячески и ломать голову.