{"id":55116,"date":"2023-10-01T14:19:09","date_gmt":"2023-10-01T14:19:09","guid":{"rendered":"https:\/\/dbtut.com\/?p=55116"},"modified":"2023-10-01T14:21:01","modified_gmt":"2023-10-01T14:21:01","slug":"oracle-database-memory-structure","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/","title":{"rendered":"Oracle Database Memory Structure"},"content":{"rendered":"

In today’s article, we will be examining the memory structure of the Oracle database. The memory structure of the Oracle database consists of two parts.<\/p>\n

System Global Area
\nProgram Global Area<\/p>\n

\"\"<\/p>\n

SYSTEM GLOBAL AREA<\/h3>\n

It is known as shared memory space.<\/p>\n

All sessions connected to the database use the SGA common area.<\/p>\n

Server and background processes do not reside within the SGA area but allocate memory space for it.<\/p>\n

Each database has its own SGA field. This area is allocated by the Oracle database through physical memory on the server and is not allowed to be used by any other application\/process until the database is closed.<\/p>\n

Oracle Instance= SGA + Background Processes<\/p>\n

The size of the SGA area is seen in the figure below.<\/p>\n

1. While the database is opening.<\/p>\n

SQL> shutdown immediate;\r\nDatabase closed.\r\nDatabase dismounted.\r\nORACLE instance shut down.\r\nSQL> startup;\r\nORACLE instance started.\r\n\r\nTotal System Global Area 1653518336 bytes\r\nFixed Size                  2253784 bytes\r\nVariable Size            1023413288 bytes\r\nDatabase Buffers          620756992 bytes\r\nRedo Buffers                7094272 bytes\r\nDatabase mounted.\r\nDatabase opened.\r\nSQL>\r\n<\/pre>\n
2. By running the following command in the open database;<\/p>\n
SQL> show sga;\r\n\r\nTotal System Global Area 1653518336 bytes\r\nFixed Size                  2253784 bytes\r\nVariable Size             956304424 bytes\r\nDatabase Buffers          687865856 bytes\r\nRedo Buffers                7094272 bytes\r\n<\/pre>\n
3. With the following SQL statement;<\/p>\n
SQL> select * from v$sga;\r\n\r\nNAME                      VALUE\r\n-------------------- ----------\r\nFixed Size              2253784\r\nVariable Size         956304424\r\nDatabase Buffers      687865856\r\nRedo Buffers            7094272\r\n<\/pre>\n
Here;<\/p>\n
Fixed Size: This is the area where information such as general information and database status information needed by background processes are kept.<\/p>\n
Variable Size: It is the sum of Shared Pool + Large Pool + Streams Pool + Java Pool + Free SGA Memory Areas.<\/p>\n
The SGA area and the components that make up this area can be determined by database administrators and can also be automatically managed by Oracle with 11g.<\/p>\n
In this context, there are 3 memory management options.<\/p>\n
1. Automatic Memory Management
\na. Memory Management is left to Oracle.
\nb. The maximum size that the instance can take is specified in the parameter file.
\nc. Parameters: memory_target, memory_max_target
\nd. Oracle automatically manages SGA and PGA fields according to this size.<\/p>\n
2. Automatic Shared Memory Management
\na. The total size that SGA and PGA can take is specified in the parameter file.
\nb. Parameters: sga_target, sga_max_target, pga_aggregate_target<\/p>\n
3. Manual Memory Management
\na. The size of all SGA components and PGA sizes are specified individually in the parameter file.<\/p>\n
\u2022 All components of SGA can be learned by querying the v$sgastat view.<\/p>\n
SQL> select * from v$sgastat;\r\n<\/pre>\n
PROGRAM GLOBAL AREA<\/h3>\n
\"\"<\/p>\n
PGA is non-shared memory space.<\/p>\n
When a server process is started, PGA memory space is allocated from the server’s physical memory. This memory area is used until the server process terminates. The memory is released back when the process is finished.<\/p>\n
PGA has some important components.<\/p>\n
Private SQL Area<\/h3>\n
It holds the information of the parsed SQL sentence.<\/p>\n
When a server process executes a SQL statement or PL\/SQL code, it uses a private SQL field to store bind variable values, query execution status information, and query workspaces.<\/p>\n
Private SQL Area and Shared SQL Area are different structures. The private SQL field of one or more sessions accesses a single Shared SQL field.<\/p>\n
For example, let’s assume that the customers table is queried with the same query in 5 different sessions. The SQL statement executed in all 5 sessions will have a single execution plan.<\/p>\n
SQL Working Area<\/h3>\n
It is an area used for intensive memory operations. Memory-intensive operations such as using the sort field with operations such as ORDER BY and hash joins using the hash field to create the hash table can be given as examples.<\/p>\n
Session Memory<\/h3>\n
This is the area where session-specific variables are kept.<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
In today’s article, we will be examining the memory structure of the Oracle database. The memory structure of the Oracle database consists of two parts. System Global Area Program Global Area SYSTEM GLOBAL AREA It is known as shared memory space. All sessions connected to the database use the SGA common area. Server and background …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":484,"featured_media":55119,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[4],"tags":[],"class_list":["post-55116","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":148,"today_views":0},"yoast_head":"\nOracle Database Memory Structure - Database Tutorials<\/title>\n<meta name=\"description\" content=\"We will be examining the memory structure of the Oracle database. The memory structure of the Oracle database consists of two parts.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle Database Memory Structure - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"We will be examining the memory structure of the Oracle database. The memory structure of the Oracle database consists of two parts.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2023-10-01T14:19:09+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-01T14:21:01+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/10\/Ekran-goruntusu-2023-10-01-171658.png\" \/>\n\t<meta property=\"og:image:width\" content=\"630\" \/>\n\t<meta property=\"og:image:height\" content=\"283\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Onur ARDAHANLI\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Onur ARDAHANLI\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/\"},\"author\":{\"name\":\"Onur ARDAHANLI\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/7fcd466cd0d347ec64aaa48f18f780c6\"},\"headline\":\"Oracle Database Memory Structure\",\"datePublished\":\"2023-10-01T14:19:09+00:00\",\"dateModified\":\"2023-10-01T14:21:01+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/\"},\"wordCount\":531,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/10\/Ekran-goruntusu-2023-10-01-171658.png\",\"articleSection\":[\"ORACLE\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/\",\"name\":\"Oracle Database Memory Structure - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/10\/Ekran-goruntusu-2023-10-01-171658.png\",\"datePublished\":\"2023-10-01T14:19:09+00:00\",\"dateModified\":\"2023-10-01T14:21:01+00:00\",\"description\":\"We will be examining the memory structure of the Oracle database. The memory structure of the Oracle database consists of two parts.\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/10\/Ekran-goruntusu-2023-10-01-171658.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/10\/Ekran-goruntusu-2023-10-01-171658.png\",\"width\":630,\"height\":283},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle Database Memory Structure\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/7fcd466cd0d347ec64aaa48f18f780c6\",\"name\":\"Onur ARDAHANLI\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ecd20c3e1374ced4e1aefc82101cce4cd437be8fd957d1be3d106668b8a1b990?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ecd20c3e1374ced4e1aefc82101cce4cd437be8fd957d1be3d106668b8a1b990?s=96&d=mm&r=g\",\"caption\":\"Onur ARDAHANLI\"},\"url\":\"https:\/\/dbtut.com\/index.php\/author\/onurardahanli\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Oracle Database Memory Structure - Database Tutorials","description":"We will be examining the memory structure of the Oracle database. The memory structure of the Oracle database consists of two parts.","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:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/","og_locale":"en_US","og_type":"article","og_title":"Oracle Database Memory Structure - Database Tutorials","og_description":"We will be examining the memory structure of the Oracle database. The memory structure of the Oracle database consists of two parts.","og_url":"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/","og_site_name":"Database Tutorials","article_published_time":"2023-10-01T14:19:09+00:00","article_modified_time":"2023-10-01T14:21:01+00:00","og_image":[{"width":630,"height":283,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/10\/Ekran-goruntusu-2023-10-01-171658.png","type":"image\/png"}],"author":"Onur ARDAHANLI","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Onur ARDAHANLI","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/"},"author":{"name":"Onur ARDAHANLI","@id":"https:\/\/dbtut.com\/#\/schema\/person\/7fcd466cd0d347ec64aaa48f18f780c6"},"headline":"Oracle Database Memory Structure","datePublished":"2023-10-01T14:19:09+00:00","dateModified":"2023-10-01T14:21:01+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/"},"wordCount":531,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/10\/Ekran-goruntusu-2023-10-01-171658.png","articleSection":["ORACLE"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/","url":"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/","name":"Oracle Database Memory Structure - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/10\/Ekran-goruntusu-2023-10-01-171658.png","datePublished":"2023-10-01T14:19:09+00:00","dateModified":"2023-10-01T14:21:01+00:00","description":"We will be examining the memory structure of the Oracle database. The memory structure of the Oracle database consists of two parts.","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/10\/Ekran-goruntusu-2023-10-01-171658.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/10\/Ekran-goruntusu-2023-10-01-171658.png","width":630,"height":283},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2023\/10\/01\/oracle-database-memory-structure\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Oracle Database Memory Structure"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/7fcd466cd0d347ec64aaa48f18f780c6","name":"Onur ARDAHANLI","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ecd20c3e1374ced4e1aefc82101cce4cd437be8fd957d1be3d106668b8a1b990?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ecd20c3e1374ced4e1aefc82101cce4cd437be8fd957d1be3d106668b8a1b990?s=96&d=mm&r=g","caption":"Onur ARDAHANLI"},"url":"https:\/\/dbtut.com\/index.php\/author\/onurardahanli\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/55116","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/484"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=55116"}],"version-history":[{"count":1,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/55116\/revisions"}],"predecessor-version":[{"id":55120,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/55116\/revisions\/55120"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/55119"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=55116"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=55116"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=55116"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}