OracleNotes

Blog

  • Oracle #DB12c New – PGA_AGGREGATE_LIMIT

    Program Global Area (PGA) in Oracle database is a private memory region that contains the data and control information for a server process. PGA is allocated per server process, used for storing run-time area of cursor, sorting, bitmap merge, etc.

    Automatic PGA management in Oracle Database was managed by two parameters up until 11gR2 – PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY. When WORKAREA_SIZE_POLICY is set to AUTO (the default) Automatic PGA memory management is enabled, thus no need to define individual work areas (no need to worry about SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE). PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance. If you explicitly did not set the PGA_AGGREGATE_TARGET parameter, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.

    If Automatic Memory Management is enabled (MEMORY_TARGET is set to a positive value) and PGA_AGGREGATE_TARGET is also set to a positive value, the PGA_AGGREGATE_TARGET value acts as the minimum value for the PGA size.

    The PGA_AGGREGATE_TARGET parameter behavior or its default values did not change in Oracle Database 12c from 11gR2, but we have a new parameter in 12c, PGA_AGGREGATE_LIMIT, to limit the aggregate PGA memory consumed by the instance.

    Why limit? What was happening in 11gR2 or before without the limit?

    Well, PGA_AGGREGATE_TARGET is only a target, there is no limit, hence at times PGA memory usage could exceed the target and lead to high rate of swapping, and thus performance issues. To avoid this, the PGA_AGGREGATE_LIMIT parameter sets a hard limit on the PGA size. If you do not explicitly set a limit (provide a value), Oracle sets the default by picking the biggest value from:

    • 2 GB or
    • 200% of the PGA_AGGREGATE_TARGET value or
    • 3 MB times the value of the PROCESSES parameter

    However, it will not exceed 120% of the physical memory size minus the total SGA size.

    If you manually set the PGA_AGGREGATE_LIMIT, it must be bigger than the default value derived by Oracle. You can set the parameter to 0, to have no limit. If you try to set it to a smaller value, you will get an error. For example below, the minimum value determined by Oracle is 909M, and hence we cannot set it lower.

    CDB$ROOT@ORCL> show parameter pga_aggregate_limit


NAME                         TYPE       VALUE
--------------------------- ----------- ------------
pga_aggregate_limit         big integer 2G

CDB$ROOT@ORCL> alter system set pga_aggregate_limit=200m ;

alter system set pga_aggregate_limit=200m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00093: pga_aggregate_limit must be between 909M and 100000G
CDB$ROOT@ORCL>

    Caution: Since Oracle derives a default limit for PGA, DBAs upgrading database to 12c must be aware of what happens if the process requires more memory and the total PGA exceeds PGA_AGGREGATE_LIMIT. Once the limit exceeds, the sessions that are using the most untunable memory will have their calls aborted. Then, if the total PGA memory usage is still over the limit, the sessions that are using the most untunable memory will be terminated. So, don’t be surprised if you start seeing PGA memory exceeded error messages (ORA-04036) in the alert log after upgrading to 12c!

  • Oracle Tidbits – April 2015

    “Oracle *daily* TidBits” (#oratidbit) published at https://www.facebook.com/oraclenotes on weekdays in April 2015. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well… Hope you find these helpful to learn something new or to remind you of its existence and use…

    Publish Date TidBit
    2-Apr #oratidbit #DB12c feature: Real-Time database operations monitoring tracks and reports on active and recently completed database operations. You can monitor details of the execution of a single SQL or PL/SQL statement. You can also monitor the progress of long-running operations such as a batch job, or ETL processing.
    3-Apr #oratidbit #DB12c new feature Real-Time database operations monitoring is part of Oracle Database Tuning Pack. The SQL monitoring feature is enabled by default when the STATISTICS_LEVEL initialization parameter is either set to TYPICAL (the default value) or ALL. SQL monitoring starts automatically for all long-running queries.
    6-Apr #oratidbit #oracle #db12c allows you to use inline function and procedure in SQL using WITH clause.
    7-Apr #oratidbit #db12c SQL: The SYS_CONTEXT(‘SYS_SESSION_ROLES’,'<role>’) returns TRUE if the <role> is enabled for the session. Note, the use of SYS_SESSION_ROLE instead of USERENV in SYS_CONTEXT.
    8-Apr #oratidbit the WITH_PLSQL hint is not an optimizer hint. It enables you to specify the WITH plsql_function or plsql_procedure clause within the SQL statement. This hint could help you avoid “ORA-32034: unsupported use of WITH clause”
    13-Apr #oratidbit #sqldeveloper provides wizards to perform RMAN backups and recovery as well as datapump export and import.
    14-Apr #oratidbit #sqldeveloper Cart is a convenient tool for deploying Oracle Database objects from one or more database connections to a destination connection. You drag and drop objects from the Connections navigator into the Cart window (or you can open a previously saved cart XML file), specify any desired options, and click the Deploy icon to display the Deploy Objects dialog box. After you complete the information in that dialog box, SQL Developer creates a .zip file containing scripts (including a master script) that can be used to create the objects in the schema of a desired destination connection.
    15-Apr #oratidbit #sqldeveloper worksheet supports many SQL*Plus commands excepet buffer editing commands (obviously), oradebug, startup, shutdown, recover, etc.
    16-Apr #oratidbit #sqldeveloper provides integrated support for the Subversion versioning and source control system.
    17-Apr #oratidbit #sqldeveloper has an interface for Oracle Application Express Listener Administration. It enables you to specify global settings and multiple database settings with different database connections for the Oracle Application Express Listener. You can create, retrieve, upload, and save Application Express Listener settings.
    20-Apr #oratidbit Starting with #DB12c Release 1, #Oracle supports results of SQL statements executed in a stored procedure to be returned implicitly to the client applications without the necessity to explicitly use a REF CURSOR. This feature eliminates the overhead of re-writing the client-side code.
    21-Apr #oratidbit ODBC driver in 12.1.0.1 supports the migration of third-party applications to Oracle Databases by using the SQL Translation Framework. This enables non-Oracle database SQL statements to run against Oracle Database. For using this feature with an ODBC application, you must specify the service name, which was created as part of SQL Translation Framework setup, as the ServerName= entry in the odbc.ini file.
    22-Apr #oratidbit #Oracle ODBC driver in 12.1.0.1 supports executing a stored procedure, which can return implicit results without using RefCursor. This support eases any third party ODBC application, which migrated to Oracle and wants to use this same functionality that was provided by their previous vendors.
    23-Apr #oratidbit Starting with #Oracle #DB12c, SQL Translation Framework tool translates the SQL statements of a client program from a foreign (non-Oracle) SQL dialect into the SQL dialect used by the Oracle Database SQL compiler. Various client-side applications, designed to work with non-Oracle Databases, cannot be used with Oracle Database without significant alterations. This is because SQL dialect varies among vendors of database technologies and different vendors use different syntaxes to express SQL queries and statements.
    24-Apr #oratidbit SQL*Plus has three new SHOW command options to display information about pluggable databases: SHOW CON_ID, SHOW CON_NAME and SHOW PDBS.
    27-Apr #oratidbit #developer Use INSTEAD OF CREATE ON SCHEMA trigger to prevent new objects  by a schema  http://wp.me/p3u6kA-os
    28-Apr #oratidbit #developer When a SQL query uses bind variables, the database can compile it once and store the query plan in the shared pool. If the same statement is executed again, then the database can perform a soft parse and reuse the plan. In contrast, a hard parse takes longer and uses more resources.
    29-Apr #oratidbit #developer Instrument application code: Good development practice involves adding debugging code to application. The ability to generate trace files is useful for debugging and diagnosing performance problems.
    30-Apr #oratidbit #developer Application developers can use the DBMS_APPLICATION_INFO package with Oracle Trace and the SQL trace facility to record names of executing modules or transactions in the database for later use when tracking the performance of various modules and debugging.