Lets talk about an interesting sql performance problem. Finding the cause of the problem was a challenge since it is not a daily sql tunning task.
Actually the problem for the statement is not a general one. Sometimes you can run it in fractions of a second, sometimes (mostly the users) wait for the same sql for seconds.
The finding is very surprising. The statement is waiting on parsing. Table in the sql have more than 10000 partitions and a local index on the partitions.
Actually the problem can be detected using ASH. These columns “in_hard_parse“, “in_parse” indicate if the session is waiting for the parsing or not.
select count(*), in_hard_parse, in_parse from v$active_session_history where sql_id in ( 'eked0ebx3edbz', 'ebe247esp4eu4') and blocking_session_status = 'NO HOLDER' group by in_hard_parse, in_parse; 1 N N 364 Y Y
It is actually a normal event and mentioned in the My Oracle Support.
Long Parse Time and ‘row cache lock’ On Recursive Query Against SYS.INDPART$ (Doc ID 2127211.1)
It is advised to increase the shared pool.
Once the sql is parsed it starts to run as normal.
You can use the shared pool keep procedures to keep the cursors If you like.
Or cache the partition metadata table on the cache.
alter table sys.tabpart$ cache ; Table altered. alter table sys.indpart$ cache ; Table altered.
But best solution is to increase the shared pool or not to have so many partitions and local partitioned index on the table which is not possible in most of the cases.