Oracle · PL/SQL - SQL

Long Parse Time

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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s