from https://community.oracle.com/tech/apps-infra/discussion/4400206/hint-to-enforce-full-scan-with-direct-path-read
Kazuhiro:
Parallel hint can be used for enforcing direct path read, but there’s no hint for serial direct path read.
Serial direct path read is hard to control; it depends on several factors such as;
- FULL SCAN
- segment size and buffer cache size
- the num of blocks cached on buffer cache
Besides, the above logic is not written in manual and different from version to version.
So I would like to propose adding a hint like “FULL_DIRECT_PATH( emp )” to enforce FULL SCAN with direct path read.
This could be not only used to enforce optimizer serial direct path read but also ensuring smart scan on Exadata.
Sven W. :
So your suggestion would be to change the undocumented but mentioned in MOS parameter
_serial_direct_read
into a documented normal parameter
serial_direct_read
reply:
Thank you for the quick comment and sorry about my late response.
I read your commnet and suggested links (also MOS 1927934.1) and found very informative.
After have read all, I thought about the usefulness of the hint again.
As you mentioned, it is possible to enforce serial direct path read by changing
_serial_direct_read (hidden) parameter to TRUE/ALWAYS.
alter session set "_serial_direct_read"=always.
select * from emp;
I think there are the following benefits of having a hint;
(1) it can controls in sql level, not a session level
It will be hard to implement if you have several SQLs in a transaction.
As far as I tested, opt_param(‘_serial_direct_read’,’always’) cannot work…
(2) it can specify exactly which table should enforce direct path read
select /*+ FULL_DIRECT_PATH( emp ) FULL( dept) */ from emp, dept ...
(3) it is documented
If there is a hint, it will be documented in sql tuning guide explicitly, not as a hidden parameter written in MOS and blogs.
I’d be happy to hear more pros and cons.
Best regards,
Kazuhiro