{"id":1329,"date":"2017-02-14T20:35:48","date_gmt":"2017-02-14T20:35:48","guid":{"rendered":"http:\/\/nenadnoveljic.com\/blog\/?p=1329"},"modified":"2020-08-21T12:20:31","modified_gmt":"2020-08-21T12:20:31","slug":"ora-04036-parsing","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/ora-04036-parsing\/","title":{"rendered":"Large Memory Allocations due to Query Parsing"},"content":{"rendered":"

I’ve recently been alerted because of an ORA-04036 (PGA memory allocation limit) error on a 12.1 Oracle database. As the problem originated in the test environment and hence was not classified as urgent, I decided to investigate the root-cause instead of simply increasing the pga_aggregate_limit<\/em>. It turned out to be a good decision, because the analysis revealed an underlying parsing issue.<\/p>\n

In this blog post, I’ll describe the troubleshooting methodology which is underpinned by ASH dictionary views (subject to licensing!), Jonathan Lewis’ My Session Workload<\/a> tool, DTrace<\/a> and Flame Graphs<\/a>.<\/p>\n

First, I selected the samples from v$active_session_history<\/em> and aggregated the size of the allocated pga memory based on force_matching_signature<\/em>.<\/p>\n

select a.force_matching_signature,max(pga_allocated),count(*) \n from v$active_session_history a \n where\n sample_time between\n to_date('10.02.2017 15:34:30','dd.mm.yyyy hh24:mi:ss') and\n to_date('10.02.2017 15:34:32','dd.mm.yyyy hh24:mi:ss')\n group by a.force_matching_signature \n order by max(pga_allocated) desc ;\n\nFORCE_MATCHING_SIGNATURE    MAX(PGA_ALLOCATED)\tCOUNT(*)\n7.92426005037451E18\t    4544622592\t       28<\/code><\/pre>\n
Second, I found out the SQL text of the largest PGA consumer by querying the shared pool based on force_matching_signature<\/em> obtained in the first step:<\/p>\n
select distinct to_char(sql_fulltext) from v$sql s , v$active_session_history a  \n  where s.force_matching_signature = a.force_matching_signature and\n    sample_time between\n      to_date('10.02.2017 15:34:30','dd.mm.yyyy hh24:mi:ss') and\n      to_date('10.02.2017 15:34:32','dd.mm.yyyy hh24:mi:ss')\n;\n\nselect ...<\/code><\/pre>\n
Note: I replaced the query text with select… in the entire post, because the SQL is application-specific anyway and as such wouldn’t provide any useful information for explaining the troubleshooting methodology.<\/em><\/p>\n
Third, I reproduced the problem by running the query in question:<\/p>\n
set serveroutput on size 1000000\nexec snap_my_stats.start_snap ;\nselect  ... ;\nexec snap_my_stats.end_snap ;\n\nName                            Value\n----                            -----\nDB time                         517\nsession pga memory max          275,644,416\nparse time elapsed              431<\/span><\/code><\/pre>\n
Note: the package snap_my_stats is a part of Jonathan Lewis’ My Session Workload<\/a> tool.<\/em><\/p>\n
By looking at the statistics above, we can draw following conclusions:<\/p>\n