{"id":69,"date":"2015-03-30T14:02:00","date_gmt":"2015-03-30T14:02:00","guid":{"rendered":"https:\/\/dbadave.co.uk\/?p=69"},"modified":"2016-02-09T23:10:57","modified_gmt":"2016-02-09T23:10:57","slug":"oracle-memory-structures-oracle-dba-101","status":"publish","type":"post","link":"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/","title":{"rendered":"Oracle Memory Structures – Oracle DBA 101"},"content":{"rendered":"

As described in the first article in this Oracle DBA 101 series (which you can read here<\/a>), an Oracle instance consists of the background processes and the Oracle memory structures used when the database is running.<\/p>\n

In this article, we’ll go into more detail about the memory structures.<\/p>\n

The Oracle Memory Structures<\/strong><\/em><\/h2>\n

There are two basic memory structures associated with an instance:<\/p>\n

    \n
  1. The System Global Area (or SGA for short)<\/li>\n
  2. The Program Global Area (or PGA for short)<\/li>\n<\/ol>\n

    System Global Area<\/h3>\n

    The SGA stores data and control information for the instance and is shared by all server and background processes.<\/p>\n

    Program Global Area<\/h3>\n

    The PGA stores\u00a0data and control information for a single server or background process.\u00a0Each server process and background process has its own PGA.<\/p>\n

    The System Global Area<\/strong><\/em><\/h2>\n

    The SGA includes the following data structures:<\/p>\n

    The Shared Pool<\/h3>\n

    The shared pool is essentially a buffer for SQL statements – it stores SQL statements and has the following components:<\/p>\n

    The Library Cache<\/h4>\n

    The library cache stores the current SQL execution plan, and also holds stored procedure and trigger code.<\/p>\n

    The Dictionary Cache<\/h4>\n

    The data dictionary is a collection of database tables and views containing metadata about the database and its users. The Oracle database server accesses the data dictionary a lot during the parsing of SQL statements<\/p>\n

    The Database Buffer Cache<\/h4>\n

    The database buffer cache hold copies of data blocks read from disk. The cache is shared between all users currently connected to the database. The buffer cache has a default buffer pool in which all data blocks are stored unless the KEEP buffer pool and\/or the RECYCLE buffer pool is configured. If either or both of these pools are configured, then the default pool is used to data blocks from any objects not explicitly assigned to one of those two pools.<\/p>\n

    The KEEP Buffer Pool<\/h5>\n

    The KEEP buffer pool is a part of the database buffer cache that can be configured to permanently keep frequently accessed data blocks in the buffer cache.<\/p>\n

    The RECYCLE Buffer Pool<\/h5>\n

    The RECYCLE buffer pool is a part of the database buffer that can be configured for rarely accessed data blocks, to prevent large objects using unnecessary space in the cache.<\/p>\n

    The nK Buffer Cache<\/h5>\n

    The default buffer pool holds data blocks of the default database block size. It is possible to have data blocks that aren’t of the default size and the nK buffer caches are used to hold these. n can be 2, 4, 8, 16 or 32 as long as it is not the same as the default block size.<\/p>\n

    The Redo Log Buffer<\/h4>\n

    The redo log buffer holds information about changes made to the database – it holds enough information to recreate any inserts, updates and delete statements as well as any changes to database objects, e.g. alter, create or drop operations. It is primarily used in database recovery.<\/p>\n

    The Large Pool<\/h4>\n

    The large pool is an optional component of the SGA. It is used for large memory operations when using a shared server configuration and also by disk I\/O server processes, as well as backup and restore operations.<\/p>\n

    The Java Pool<\/h4>\n

    The java pool is an area of memory in the SGA that is used for all session-specific Java code and data within the Java virtual machine (JVM). How exactly it is used depends on the database configuration and will be covered in a later article.<\/p>\n

    The Streams Pool<\/h4>\n

    The streams pool is used exclusively by Oracle Streams and stores buffered queue messages. It provides memory for Oracle Streams capture processes and apply processes.\u00a0If it’s not specifically configured, the size of the Streams pool starts at zero and grows dynamically as needed when Oracle Streams is used.<\/p>\n

    The Program Global Area<\/strong><\/em><\/h2>\n

    One Program Global Area (or PGA) is allocated by Oracle for each server process. It is used to store logon and other session information, as well as to process SQL statements. The collection of all the individual PGAs is known as the Instance PGA. There are basically two areas in each PGA:<\/p>\n

    Session Memory<\/h3>\n

    The session memory, as it’s name suggests, is used to hold information about a session, e.g. logon information. In a shared server configuration, the session memory is not private but is shared between sessions.<\/p>\n

    Private SQL Area<\/h3>\n

    Every session that executes a SQL statement has a private SQL area that holds information such as bind variables for the SQL statements, information about the state of execution of the SQL statements, and SQL statement execution work areas.<\/p>\n

    Summary<\/strong><\/h2>\n

    There are two basic Oracle memory structures – the SGA and the PGA. Each of these in turn consists of various elements that we’ve covered briefly. Understanding these is a necessary foundation for being a good DBA. In future articles we’ll go into more depth about all of these elements that make up the Oracle memory structures.<\/p>\n

     <\/p>\n","protected":false},"excerpt":{"rendered":"

    As described in the first article in this Oracle DBA 101 series (which you can read here), an Oracle instance consists of the background processes and the Oracle memory structures used when the database is running. In this article, we’ll go into more detail about the memory structures. The Oracle Memory Structures There are two … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":381,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[4,6],"yoast_head":"\nOracle Memory Structures - Oracle DBA 101 | DBADave<\/title>\n<meta name=\"description\" content=\"Understanding the Oracle memory structures is a vital element of being a good DBA. This article in the DBA 101 series provides a solid foundation.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle Memory Structures - Oracle DBA 101 | DBADave\" \/>\n<meta property=\"og:description\" content=\"Understanding the Oracle memory structures is a vital element of being a good DBA. This article in the DBA 101 series provides a solid foundation.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/\" \/>\n<meta property=\"og:site_name\" content=\"DBA Dave - Oracle Training\" \/>\n<meta property=\"article:published_time\" content=\"2015-03-30T14:02:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-02-09T23:10:57+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/03\/Oracle-DBA.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"770\" \/>\n\t<meta property=\"og:image:height\" content=\"340\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/\"},\"author\":{\"name\":\"admin\",\"@id\":\"https:\/\/dbadave.co.uk\/#\/schema\/person\/c51b3c9b3e2e2e9bbd141a719c024c40\"},\"headline\":\"Oracle Memory Structures – Oracle DBA 101\",\"datePublished\":\"2015-03-30T14:02:00+00:00\",\"dateModified\":\"2016-02-09T23:10:57+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/\"},\"wordCount\":824,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbadave.co.uk\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/03\/Oracle-DBA.jpg\",\"keywords\":[\"Oracle Database\",\"Oracle DBA 101\"],\"articleSection\":[\"Oracle Training\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/\",\"url\":\"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/\",\"name\":\"Oracle Memory Structures - Oracle DBA 101 | DBADave\",\"isPartOf\":{\"@id\":\"https:\/\/dbadave.co.uk\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/03\/Oracle-DBA.jpg\",\"datePublished\":\"2015-03-30T14:02:00+00:00\",\"dateModified\":\"2016-02-09T23:10:57+00:00\",\"description\":\"Understanding the Oracle memory structures is a vital element of being a good DBA. This article in the DBA 101 series provides a solid foundation.\",\"breadcrumb\":{\"@id\":\"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/#primaryimage\",\"url\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/03\/Oracle-DBA.jpg\",\"contentUrl\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/03\/Oracle-DBA.jpg\",\"width\":770,\"height\":340,\"caption\":\"Oracle Training - Oracle Memory Structures\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbadave.co.uk\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle Memory Structures – Oracle DBA 101\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbadave.co.uk\/#website\",\"url\":\"https:\/\/dbadave.co.uk\/\",\"name\":\"DBA Dave - Oracle Training\",\"description\":\"Expert DBA Tips & Advice\",\"publisher\":{\"@id\":\"https:\/\/dbadave.co.uk\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbadave.co.uk\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbadave.co.uk\/#organization\",\"name\":\"DBA Dave\",\"url\":\"https:\/\/dbadave.co.uk\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbadave.co.uk\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2016\/02\/dba-dave-logo.png\",\"contentUrl\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2016\/02\/dba-dave-logo.png\",\"width\":170,\"height\":50,\"caption\":\"DBA Dave\"},\"image\":{\"@id\":\"https:\/\/dbadave.co.uk\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbadave.co.uk\/#\/schema\/person\/c51b3c9b3e2e2e9bbd141a719c024c40\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbadave.co.uk\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/3d6ad3f291a149878281854f13654bf0?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/3d6ad3f291a149878281854f13654bf0?s=96&d=mm&r=g\",\"caption\":\"admin\"},\"url\":\"https:\/\/dbadave.co.uk\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Oracle Memory Structures - Oracle DBA 101 | DBADave","description":"Understanding the Oracle memory structures is a vital element of being a good DBA. This article in the DBA 101 series provides a solid foundation.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/","og_locale":"en_US","og_type":"article","og_title":"Oracle Memory Structures - Oracle DBA 101 | DBADave","og_description":"Understanding the Oracle memory structures is a vital element of being a good DBA. This article in the DBA 101 series provides a solid foundation.","og_url":"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/","og_site_name":"DBA Dave - Oracle Training","article_published_time":"2015-03-30T14:02:00+00:00","article_modified_time":"2016-02-09T23:10:57+00:00","og_image":[{"width":770,"height":340,"url":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/03\/Oracle-DBA.jpg","type":"image\/jpeg"}],"author":"admin","twitter_misc":{"Written by":"admin","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/#article","isPartOf":{"@id":"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/"},"author":{"name":"admin","@id":"https:\/\/dbadave.co.uk\/#\/schema\/person\/c51b3c9b3e2e2e9bbd141a719c024c40"},"headline":"Oracle Memory Structures – Oracle DBA 101","datePublished":"2015-03-30T14:02:00+00:00","dateModified":"2016-02-09T23:10:57+00:00","mainEntityOfPage":{"@id":"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/"},"wordCount":824,"commentCount":0,"publisher":{"@id":"https:\/\/dbadave.co.uk\/#organization"},"image":{"@id":"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/#primaryimage"},"thumbnailUrl":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/03\/Oracle-DBA.jpg","keywords":["Oracle Database","Oracle DBA 101"],"articleSection":["Oracle Training"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/","url":"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/","name":"Oracle Memory Structures - Oracle DBA 101 | DBADave","isPartOf":{"@id":"https:\/\/dbadave.co.uk\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/#primaryimage"},"image":{"@id":"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/#primaryimage"},"thumbnailUrl":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/03\/Oracle-DBA.jpg","datePublished":"2015-03-30T14:02:00+00:00","dateModified":"2016-02-09T23:10:57+00:00","description":"Understanding the Oracle memory structures is a vital element of being a good DBA. This article in the DBA 101 series provides a solid foundation.","breadcrumb":{"@id":"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/#primaryimage","url":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/03\/Oracle-DBA.jpg","contentUrl":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/03\/Oracle-DBA.jpg","width":770,"height":340,"caption":"Oracle Training - Oracle Memory Structures"},{"@type":"BreadcrumbList","@id":"https:\/\/dbadave.co.uk\/oracle-memory-structures-oracle-dba-101\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbadave.co.uk\/"},{"@type":"ListItem","position":2,"name":"Oracle Memory Structures – Oracle DBA 101"}]},{"@type":"WebSite","@id":"https:\/\/dbadave.co.uk\/#website","url":"https:\/\/dbadave.co.uk\/","name":"DBA Dave - Oracle Training","description":"Expert DBA Tips & Advice","publisher":{"@id":"https:\/\/dbadave.co.uk\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbadave.co.uk\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbadave.co.uk\/#organization","name":"DBA Dave","url":"https:\/\/dbadave.co.uk\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbadave.co.uk\/#\/schema\/logo\/image\/","url":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2016\/02\/dba-dave-logo.png","contentUrl":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2016\/02\/dba-dave-logo.png","width":170,"height":50,"caption":"DBA Dave"},"image":{"@id":"https:\/\/dbadave.co.uk\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbadave.co.uk\/#\/schema\/person\/c51b3c9b3e2e2e9bbd141a719c024c40","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbadave.co.uk\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/3d6ad3f291a149878281854f13654bf0?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3d6ad3f291a149878281854f13654bf0?s=96&d=mm&r=g","caption":"admin"},"url":"https:\/\/dbadave.co.uk\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/posts\/69"}],"collection":[{"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/comments?post=69"}],"version-history":[{"count":15,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/posts\/69\/revisions"}],"predecessor-version":[{"id":385,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/posts\/69\/revisions\/385"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/media\/381"}],"wp:attachment":[{"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/media?parent=69"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/categories?post=69"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/tags?post=69"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}