Uncontrolled growth of Shared Pool (bug 21373473)

In this days, I have some dbs that suffering of  ORA-04031. Just for info, I report the "oerr" output for this error

[oracle]/sbRdbms/oracle-$oerr ORA 04031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause:  More shared memory is needed than was allocated in the shared
//          pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
//          DBMS_SHARED_POOL package to pin large packages,
//          reduce your use of shared memory, or increase the amount of
//          available shared memory by increasing the value of the
//          initialization parameters SHARED_POOL_RESERVED_SIZE and
//          SHARED_POOL_SIZE.
//          If the large pool is out of memory, increase the initialization
//          parameter LARGE_POOL_SIZE.
//          If the error is issued from an Oracle Streams or XStream process,
//          increase the initialization parameter STREAMS_POOL_SIZE or increase
//          the capture or apply parameter MAX_SGA_SIZE.

So. This is my simple analysis

The SGA is 32GB.

SELECT sum(value)/1024/1024/1024 sga_gbytes 

FROM v$sga;

SGA_GBYTES

----------

        32

But the shared pool is more then 29GB

SELECT component, current_size/1024/1024/1024 gbytes 

FROM v$sga_dynamic_components

ORDER BY 2 DESC 

FETCH FIRST 5 ROWS ONLY;

COMPONENT                GBYTES

-------------------- ----------

shared pool               29.25 <<<<<

DEFAULT buffer cache     1.5625

large pool                   .5

Shared IO Pool               .5

java pool                 .0625

So, I want to know which component of the shared pool is occupying more space

SELECT pool, name, bytes/1024/1024/1024 gbytes 

FROM v$sgastat 

WHERE pool='shared pool' 

ORDER BY BYTES DESC 

FETCH FIRST 5 ROWS ONLY;

POOL         NAME                           GBYTES

------------ -------------------------- ----------

shared pool  ges enqueues               14.7394131 <<<<<

shared pool  ges resource dynamic       7.26247394 <<<<<

shared pool  free memory                3.79368246

shared pool  gcs resources              .867172517

shared pool  gcs shadows                .481762514

14.739GB + 7.262GB = 22GB. So 29.25GB - 22GB = 6GB  (more or less), It's always a big Shared Pool, but much more human. I suspect that other components of the Shared Pool (here you can see "gcs resources" and "gcs shadows") could be less in size if I resolve the biggest issue.

If I see the history of the shared pool (here I report the output for just one instance), I see that the "shared pool" always grows while the "buffer cache" decreases:

   INST_ID LAST_START_TIME     COMPONENT            LAST_OPER_TYP OPER_MODE           FINAL_SIZE   OPER_CNT

---------- ------------------- -------------------- ------------- --------- -------------------- ----------

         2 17-07-2017 23:03:01 DEFAULT buffer cache SHRINK        IMMEDIATE       21,273,509,888         65

         2 18-07-2017 16:07:56 DEFAULT buffer cache GROW          DEFERRED        21,474,836,480          3

         2 18-07-2017 23:15:09 DEFAULT buffer cache SHRINK        IMMEDIATE       18,052,284,416         55

         2 19-07-2017 23:40:40 DEFAULT buffer cache SHRINK        IMMEDIATE        8,992,587,776        146

         2 20-07-2017 21:41:29 DEFAULT buffer cache SHRINK        IMMEDIATE        1,275,068,416        126

         2 20-07-2017 22:14:40 DEFAULT buffer cache GROW          DEFERRED         1,409,286,144          3

         2 21-07-2017 00:20:41 DEFAULT buffer cache GROW          DEFERRED         1,677,721,600          2

         2 17-07-2017 23:03:01 shared pool          GROW          IMMEDIATE       11,408,506,880         65

         2 18-07-2017 23:15:09 shared pool          GROW          IMMEDIATE       14,831,058,944         55

         2 19-07-2017 23:40:40 shared pool          GROW          IMMEDIATE       23,890,755,584        146

         2 20-07-2017 21:41:29 shared pool          GROW          IMMEDIATE       31,675,383,808        126

         2 20-07-2017 22:14:40 shared pool          SHRINK        DEFERRED        31,541,166,080          1

         2 21-07-2017 00:20:40 shared pool          SHRINK        DEFERRED        31,406,948,352          1

Browsing the internet (Saint Google), I found this link, where the author Marco Mischke have had the same problem.

He reports the Doc ID 2063751.1 from MOS: ORA-04031 Errors Occurring with High "ges resource dynamic" & "ges enqueues" Memory Usage In The Shared Pool

CAUSE: This is due to unpublished bug:21373473 fixed in 12.2, and occurs due to DX and BB locks being cached but not cleared out.  This fix removes the need to cache DX and BB locks and hence reduces overall shared pool memory usage in RAC instances.

SOLUTION: Download and apply the one-off patch number 21373473 (Patch:21373473) for your platform and version combination. Please note that if using 12.1.0.2, then you should also apply the related patch number 21260431 (Patch:21260431) which also impacts the shared pool memory allocations identified here. This issue can also be worked around by setting _GES_DIRECT_FREE_RES_TYPE="CTARAHDXBB" in the instances.

Update

201/07/28 - A collegue of mine sent me another Doc ID: ORA-4031 due to Huge "ges resource dynamic" and "ges enqueues" and Instance Terminates: ORA-29770: 'SGA: allocation forcing component growth' (Doc ID 1951758.1)

Export/Import on the fly: The simple way

In a my previous post, I have shown how to exporting and importing using two different pipes: one for each server. One pipe on source server and another one on the target server.

There is a more simple way to do the same thing: just using one pipe.

In this case you have to create one pipe on a target server (for example) and use it to write and read the data.

Configuring the target

[oracle@trc_hst]$ mknod /tmp/ExpImp.pipe p 
[oracle@trc_hst]$ nohup imp parfile=/tmp/Import.par \
                        >  /tmp/Import.nohup 2&>1 &

[oracle@trc_hst]$ time nohup exp parfile=/tmp/Export.par \
                             > /tmp/Export.nohup 2>&1 &

Let see the parameter files. Keep in mind that this is just an example where I exported only two tables.

[oracle@trc_hst]$ cat /tmp/Export.par

USERID=src_usr/src_pwd@src_db
FILE=/tmp/ExpImp.pipe
LOG=/tmp/Export.log
TABLES=(T1, T2)
DIRECT=Y
CONSISTENT=Y
ROWS=Y
INDEXES=N
BUFFER=4000000


[oracle@trc_hst]$ cat /tmp/Import.par

USERID=trc_usr/trc_pwd 
FILE=/tmp/ExpImp.pipe 
LOG=/tmp/Import.log
BUFFER=5000000
RECORDLENGTH=5000000
IGNORE=Y 
INDEXES=N 
FEEDBACK=10000 
TABLES=(T1, T2)

As you see, in the Export.par file the USERID is referenced using SQL*Net (@src_db).

Configuring the source

Nothing to do


Update

- 2017/July/25 -
In my example I exported (using "exp" binary, not "expdp") from oracle 12.1.0.2 and imported (using "imp" obviously) in Oracle 10.2.0.4, using the client of the 10.2.0.4. Anyway, there is a compartibility you have :to check. On MOS you can read following Doc ID: "Compatibility Matrix for Export And Import Between Different Oracle Versions [Video] (Doc ID 132904.1)".

From "Export From 11g or 12c using EXP Utility Version 9iR2 Produces Corrupt Export Dump (Doc ID 550740.1)", Doc ID:

You need to export data from 11g or 12c and import into 9iR2. For this reason, the exp utility version 9iR2 is used to extract data from 11g or 12c. Exp utility version 9iR2 internally uses the dictionary view EXU9DEFPSWITCHES to get information about some parameters like PLSQL_COMPILER_FLAGS and NLS_LENGTH_SEMANTICS. The parameter PLSQL_COMPILER_FLAGS doesn't exist in 11g or 12c any longer and so the view EXU9DEFPSWITCHES returns unhandled 0 rows.

SPM, Part 02: Adaptive Cursor Sharing

This post is a second part of two.
It's a copy and paste from several links that you find in the"Reference" section.

Adaptive Cursor Sharing

Oracle introduced the bind peeking feature in Oracle 9i. With bind peeking, the Optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This allows the optimizer to determine the selectivity of any WHERE clause condition as if literals have been used instead of bind variables, thus improving the quality of the execution plan generated for statements using bind variables.

However, there was a problem with this approach, when the column used in the WHERE clause with the bind contained a data skew. If there is data skew in the column, it is likely that a histogram has been created on this column during statistics gathering. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, it is not guaranteed that this plan will be good for all possible values for the bind variable. In other words, the plan is optimized for the peeked value of the bind variable, but not for all possible values.

In 11g, the optimizer has been enhanced to allow multiple execution plans to be used for a single statement that uses bind variables. This ensures that the best execution plan will be used depending on the bind value.

 A cursor is marked bind sensitive (means “been monitored”) if the optimizer believes the optimal plan may depend on the value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors the behavior of the cursor using different bind values, to determine if a different plan for different bind values is called for (Oracle observes the cursors for a while and sees how the values differ. If the different values can potentially alter the plan, the cursor is labeled "Bind-Sensitive" and the column IS_BIND_SENSITIVE shows "Y". After a few executions, the database knows more about the cursors and the values and decides if the cursor should be made to change plans based on the values. If that is the case, the cursor is called "Bind-Aware" and the column IS_BIND_AWARE shows "Y". In summary: Bind-Sensitive cursors are potential candidates for changed plans and Bind-Aware ones are where the plans actually change. A new view V$SQL_CS_HISTOGRAM shows how many times the SQL statement was executed, organized into three buckets for each child cursor).

This cursor was marked bind sensitive because the histogram on the "deptno" column, for example, was used to compute the selectivity of the predicate "where deptno = :deptno". Since the presence of the histogram indicates that the column is skewed, different values of the bind variable may call for different plans.

Behind the scenes during the first two executions, Oracle was monitoring the behavior of the queries, and determined that the different bind values caused the data volumes manipulated by the query to be significantly different.  Based on this difference, Oracle "adapts" its behavior so that the same plan is not always shared for this query. But if we execute the query again with a more selective bind value.

 If we look at the execution statistics again, there are three cursors now:

select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware, is_shareable from v$sql where sql_text like '%deptno = :deptno%';

The original cursor was discarded when the cursor switched to bind aware mode (means: selectivity of predicates during a soft parse determine which optimal plan to use among the ones in memory). This is a one-time overhead. Note that the cursor is marked as not shareable (is_shareable is "N"), which means that this cursor will be among the first to be aged out of the cursor cache, and that it will no longer be used.  In other words, it is just waiting to be garbage collected.

There is one other reason that you may notice additional cursors for such a query in 11g.  When a new bind value is used, the optimizer tries to find a cursor that it thinks will be a good fit, based on similarity in the bind value's selectivity.  If it cannot find such a cursor, it will create a new one (like above, when one (#1) was created for unselective "10" and one (#2) was created for highly-selective "9").  If the plan for the new cursor is the same as one of the existing cursors, the two cursors will be merged, to save space in the cursor cache.  This will result in one being left behind that is in a not shareable state.  This cursor will be aged out first if there is crowding in the cursor cache, and will not be used for future executions.

So it requires two mechanisms: one to control how to change state from “sensitive” to “aware” and another to select a plan in memory as per the values of the binds. Column V$SQL_CS_STATISTICS.ROWS_PROCESSED seems to drive this mechanism. This column is updated during a hard parse and it is a fuzzy representation of the amount of data the query manipulates during its execution. For small values of V$SQL_CS_STATISTICS.ROWS_PROCESSED we increment by one V$SQL_CS_HISTOGRAM.BICKED_ID(0). For medium values we increase by one V$SQL_CS_HISTOGRAM.BICKED_ID(1). And for large values we do V$SQL_CS_HISTOGRAM.BICKED_ID(2). Cursor Sharing histogram buckets 0-2 are updated on every execution of a bind sensitive query. They represent data volume manipulation of every execution. If there are significant variances then the query would benefit of ACS and it becomes Bind Aware.

There are a couple of scenarios where the values stored on these 3 buckets cause the cursor to become bind aware:

1. When two of the buckets have the same value, and this value is not zero
2. When the smallest and the largest buckets (0 and 2) have a value other than zero

Reference

SQL Plan Management (Part 1 of 4) Creating SQL plan baselines (Maria Colgan)
SQL Plan Management (Part 2 of 4) Creating SQL plan baselines (Maria Colgan)
SQL Plan Management (Part 3 of 4) Creating SQL plan baselines (Maria Colgan)
SQL Plan Management (Part 4 of 4) Creating SQL plan baselines (Maria Colgan)
How do adaptive cursor sharing and SQL Plan Management interact? (Unknown)
Why are there more cursors in 11g for my query containing bind variables? (Unknown)
Oracle Optimizer and SPM plan interaction (Mohamed Houri)
Stage and Fix SQL Statement (Unknown)

dbms_sqldiag (Jonathan Lewis)

SQL Patch I (Dominic Brooks)
SQL Patch II (Dominic Brooks)
SQL Patch III (Dominic Brooks)
SQL Patch IV (Dominic Brooks)
Adding and Disabling Hints Using SQL Patch (Nigel Bayliss)
Using SQL Patch to add hints to a packaged application (Unknown)

Adaptive Cursors and SQL Plan Management (Arup Nanda)
How a Cursor becomes Bind Aware? (Carlo Sierra)
Adaptive Cursor Sharing with SQL Plan Baselines (Dominic Brooks)

SPM, Part 01: SQL Patch, SQL Profile and Baseline

This post is a first part of two.
It's a copy and paste from several links that you find in the "Reference" section.

SQL Plan Management

SPM maintains, on disk, a plan history consisting of different execution plans generated for each managed SQL statement. An enhanced version of the Oracle optimizer, called SPM aware optimizer, accesses, uses, and manages this information which is stored in a repository called the SQL Management Base (SMB).

The plan history enables the SPM aware optimizer to determine whether the best-cost plan it has produced using the cost-based method is a brand new plan or not. A brand new plan represents a plan change that has potential to cause performance regression. For this reason, the SPM aware optimizer does not choose a brand new best-cost plan. Instead, it chooses from a set of accepted plans. An accepted plan is one that has been either verified to not cause performance regression or designated to have good performance. A set of accepted plans is called a SQL plan baseline, which represents a subset of the plan history.

After you create a SQL plan baseline for a statement, subsequent executions of that statement will use the SQL plan baseline. From all the plans in the SQL plan baseline, the optimizer will select the one with the best cost in the current environment (including bind values, current statistics, parameters, etc.). The optimizer will also generate the best-cost plan that it would otherwise have used without a SQL plan baseline. However, this best-cost plan will not be used but instead added to the statement's plan history for later verification. In other words, the optimizer will use a known plan from the SQL plan baseline instead of a new and hitherto unknown plan.

It is possible for a SQL statement to have a stored outline as well as a SQL plan baseline. If a stored outline exists for a SQL statement and is enabled for use, then the optimizer will use it, ignoring the SQL plan baseline. In other words, the stored outline trumps a SQL plan baseline.

Adaptive cursor sharing (ACS) may generate multiple cursors for a given bind sensitive SQL statement if it is determined that a single plan is not optimal under all conditions. Each cursor is generated by forcing a hard parse of the statement. The optimizer will normally select the plan with the best cost upon each hard parse.

When you have a SQL plan baseline for a statement, the SPM aware optimizer will select the best accepted plan as the optimal plan. This also applies for the hard parse of a bind sensitive statement. There may be multiple accepted plans, each of which is optimal for different bind sets. With SPM and ACS enabled, the SPM aware optimizer will select the best plan for the current bind set.
Thus, if a hard parse occurs, the normal SPM plan selection algorithm is used regardless of whether a statement is bind sensitive.

Remember that they are responsible for two different tasks. ACS controls whether or not a child cursor is shared on a particular execution.  For each execution of the query, ACS considers the current bind values and decides if an existing child cursor can be shared or if the optimizer should be given the chance to find a better plan for the current bind values.  SPM controls which plans the optimizer may choose. If a child cursor is bind-aware, the decision to share or not is made irrespective of whether the query is controlled by SPM. But once the query and its current bind values are sent to the optimizer for optimization, SPM constrains the optimizer's choice of plans, without regard to whether this query is being optimized due to ACS.


My Note

SQL, with bind variable, run -> Parsing is done, peeking the content -> ACS is active, so a new execution plan is chosen -> Baseline is working, so the plan is compared with the one inside the "Plan Baseline" -> If the plan exists and is accepted, it will be selected and the original plan will not run, otherwise it will be added to the plan history and the original will run (not optimized. Well in this case I tell that the orignial plan was not optimized).

Queryng the dba_sql_plan_baselines (for example):

SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;

SQL_TEXT                                 PLAN_NAME                      ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825  YES NO
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d  YES YES

The 'NO' value for the accepted column implies that the new plan is in the plan history but is not available for use until it is verified to be a good plan. The optimizer will continue to use an accepted plan until new plans are verified and added to the SQL plan baseline. If there is more than one plan in the SQL plan baseline, the optimizer will use the one with the best cost under the then-current conditions (statistics, bind values, parameter settings and so on).
When you create a SQL plan baseline for a SQL statement, the SPM aware optimizer thus guarantees that no new plans will be used other than the ones in the SQL plan baseline. This prevents unexpected plan changes that sometimes lead to performance regressions.

A SQL statement can have both a SQL profile and a SQL plan baseline. In this case, the SPM aware optimizer will use both the SQL profile and the SQL plan baseline. The SQL profile contains additional information that helps the optimizer to accurately cost each accepted plan and select the best one. The SPM aware optimizer may choose a different accepted plan when a SQL profile is present than when it is not.

When we accepted the recommended SQL profile, the SQL Tuning Advisor created a SQL profile and also changed the non-accepted plan to accepted status, thus evolving the SQL plan baseline to two plans. Note that the SQL Tuning Advisor may also find a completely new tuned plan, one that is not in the plan history. If you then accept the recommended SQL profile, the SQL Tuning Advisor will create a SQL profile and also add the tuned plan to the SQL plan baseline.

A SQL statement can also have both a SQL Patch and a SQL plan baseline.

SQL Baselines exist to reproduce a specific plan. In fact, a plan hash exists as part of the baseline. If, on application of the baseline, the Optimizer is unable to reproduce the desired plan, the baseline is rejected outright.

On the other hand, SQL Patches have been developed primarily to get the Optimizer to avoid a particular problem path in an execution plan, specifically to avoid failures due to certain access methods, join methods, etc.

Two parameters allow you to control SPM. The first, optimizer_capture_sql_plan_baselines, which is FALSE by default, allows you to automatically capture plans. The second parameter, optimizer_use_sql_plan_baselines, is TRUE by default. It allows the SPM aware optimizer to use the SQL plan baseline if available when compiling a SQL statement. If you set this parameter to FALSE, the SPM aware optimizer will be disabled and you will get the regular cost-based optimizer which will select the best plan based on estimated cost.

My Note

SQL Patch and/or SQL Profile can exist with SQL Baseline. They are different objects used for different things.

SQL run -> SQL Patch and/or Profile exist, so it is applied -> New plan is generated -> Baseline is working, so the plan is compared with the one inside the "Plan Baseline" -> If the plan exists and is accepted, it will be selected and the original plan will not run, otherwise it will be added to the plan history and the original will run (not optimized. Well in this case I tell that the orignial plan was not optimized).

Please note that SQL Patch and SQL Profile could exist together. My guess is that SQL Patch is applied before of  SQL Profile.

So the order should be something like this

SQL run -> SQL Patch exist, so it is applied -> Profile exist, so it is applied -> new plan is generated -> Baseline is working, so the plan is compared with the one inside the "Plan Baseline" -> If the plan exists and is accepted, it will be selected and the original plan will not run, otherwise it will be added to the plan history and the original will run (not optimized. Well in this case I tell that the orignial plan was not optimized).

This picture shows what I said

Reference

SQL Plan Management (Part 1 of 4) Creating SQL plan baselines (Maria Colgan)
SQL Plan Management (Part 2 of 4) Creating SQL plan baselines (Maria Colgan)
SQL Plan Management (Part 3 of 4) Creating SQL plan baselines (Maria Colgan)
SQL Plan Management (Part 4 of 4) Creating SQL plan baselines (Maria Colgan)
How do adaptive cursor sharing and SQL Plan Management interact? (Unknown)
Why are there more cursors in 11g for my query containing bind variables? (Unknown)
Oracle Optimizer and SPM plan interaction (Mohamed Houri)
Stage and Fix SQL Statement (Unknown)

dbms_sqldiag (Jonathan Lewis)

SQL Patch I (Dominic Brooks)
SQL Patch II (Dominic Brooks)
SQL Patch III (Dominic Brooks)
SQL Patch IV (Dominic Brooks)
Adding and Disabling Hints Using SQL Patch (Nigel Bayliss)
Using SQL Patch to add hints to a packaged application (Unknown)

Adaptive Cursors and SQL Plan Management (Arup Nanda)
How a Cursor becomes Bind Aware? (Carlo Sierra)
Adaptive Cursor Sharing with SQL Plan Baselines (Dominic Brooks)