{"id":2319,"date":"2018-10-03T22:00:01","date_gmt":"2018-10-04T03:00:01","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=2319"},"modified":"2026-02-07T15:26:16","modified_gmt":"2026-02-07T21:26:16","slug":"exploring-autonomous-database-init-parameters","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2018\/2319\/","title":{"rendered":"Exploring Autonomous Database – Init Parameters"},"content":{"rendered":"\n

A CDB in Oracle Cloud could host both ATP and ADW (pluggable) databases (see here<\/a>). Let’s look at the initialization parameter differences at the PDB level for ATP and ADW.\u00a0\u00a0<\/p>\n\n\n\n

ATP has a minimum number of parameters configured, whereas ADW has several initialization parameters. Though my ATP and ADW databases are both 1 oCPU<\/g> \/ 1 TB size, the SGA and PGA values are different. ADW has a bigger PGA, ATP has bigger SGA.\u00a0<\/p>\n\n\n\n
Init Parameter<\/strong><\/td>ATP<\/strong><\/td>ADW<\/strong><\/td><\/tr>
pga_aggregate_target<\/td>3000M<\/td>5100M<\/td><\/tr>
pga_aggregate_limit<\/td>6000M<\/td>10200M<\/td><\/tr>
sga_target<\/td>8000M<\/td>3400M<\/td><\/tr><\/tbody><\/table>\n\n\n\n

All the parameters set in the ATP database are set in the ADW database as well. ADW has a lot more, though. Here are the parameters from ADW. The first column shows if the parameter is set in ATP or not.\u00a0<\/p>\n\n\n\n
ATP?<\/strong><\/td>PARAMETER NAME<\/strong><\/td>DISPLAY VALUE<\/strong><\/td>DEFAULT VALUE<\/strong><\/td><\/tr>
<\/td>_cell_offload_vector_groupby<\/td>FALSE<\/td>TRUE<\/td><\/tr>
<\/td>_cloud_service_type<\/td>DWCS<\/td>NULL<\/td><\/tr>
<\/td>_datapump_inherit_svcname<\/td>TRUE<\/td>FALSE<\/td><\/tr>
<\/td>_default_pct_free<\/td>1<\/td>0<\/td><\/tr>
<\/td>_enable_parallel_dml<\/td>TRUE<\/td>FALSE<\/td><\/tr>
YES<\/td>_kd_rows_chk<\/td>FALSE<\/td>TRUE<\/td><\/tr>
<\/td>_ldr_io_size<\/td>33554432<\/td>262144<\/td><\/tr>
<\/td>_ldr_io_size2<\/td>33554432<\/td>1048576<\/td><\/tr>
<\/td>_max_io_size<\/td>33554432<\/td>1048576<\/td><\/tr>
<\/td>_optimizer_answering_ query_using_stats<\/td>TRUE<\/td>FALSE<\/td><\/tr>
<\/td>_optimizer_gather_stats_on_ load_all<\/td>TRUE<\/td>FALSE<\/td><\/tr>
<\/td>_optimizer_gather_stats_on_ load_hist<\/td>TRUE<\/td>FALSE<\/td><\/tr>
<\/td>_parallel_cluster_cache_ policy<\/td>ADAPTIVE<\/td>ADAPTIVE<\/td><\/tr>
<\/td>_pdb_inherit_cfd<\/td>TRUE<\/td>FALSE<\/td><\/tr>
<\/td>_pdb_lockdown_ddl_clauses<\/td>131066<\/td>0<\/td><\/tr>
<\/td>_pdb_max_audit_size<\/td>200M<\/td>0<\/td><\/tr>
<\/td>_pdb_max_diag_size<\/td>100M<\/td>0<\/td><\/tr>
<\/td>_px_xtgranule_size<\/td>128000<\/td>10000<\/td><\/tr>
YES<\/td>cpu_count<\/td>1<\/td>0<\/td><\/tr>
YES <\/td>db_lost_write_protect<\/td>TYPICAL<\/td>NONE<\/td><\/tr>
<\/td>max_idle_blocker_time<\/td>5<\/td>0<\/td><\/tr>
YES <\/td>nls_language<\/td>AMERICAN<\/td>AMERICAN<\/td><\/tr>
YES<\/td>nls_territory<\/td>AMERICA<\/td>AMERICA<\/td><\/tr>
YES<\/td>ofs_threads<\/td>40<\/td>4<\/td><\/tr>
<\/td>parallel_degree_policy<\/td>AUTO<\/td>MANUAL<\/td><\/tr>
YES<\/td>parallel_max_servers<\/td>12<\/td>4294967295<\/td><\/tr>
<\/td>parallel_min_degree<\/td>CPU<\/td>1<\/td><\/tr>
YES<\/td>parallel_servers_target<\/td>12<\/td>0<\/td><\/tr>
YES<\/td>pga_aggregate_limit<\/td>10200M<\/td>0<\/td><\/tr>
YES<\/td>pga_aggregate_target<\/td>5100M<\/td>0<\/td><\/tr>
YES<\/td>plscope_settings<\/td>identifiers:all<\/td>IDENTIFIERS:NONE<\/td><\/tr>
YES<\/td>plsql_optimize_level<\/td>2<\/td>2<\/td><\/tr>
<\/td>resource_manager_plan<\/td>FORCE:DWCS_PLAN<\/td>NULL<\/td><\/tr>
<\/td>result_cache_max_result<\/td>1<\/td>5<\/td><\/tr>
YES<\/td>result_cache_max_size<\/td>10M<\/td>1<\/td><\/tr>
<\/td>result_cache_mode<\/td>FORCE<\/td>MANUAL<\/td><\/tr>
YES<\/td>sessions<\/td>100<\/td>4294967295<\/td><\/tr>
YES<\/td>sga_target<\/td>3400M<\/td>0<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Out of the 39 parameters set in ADW, half are underscored<\/g> parameters!<\/p>\n\n\n\n

The ATP and ADW have two different resource management plans. ADW plan name is DWCS_PLAN and ATP plan name is OLTP_PLAN. The (default) tablespaces are same for ATP and ADW.<\/p>\n","protected":false},"excerpt":{"rendered":"

ATP has a minimum number of initialization parameters configured, whereas ADW has several initialization parameters. Though my ATP and ADW databases are both 1 oCPU \/ 1 TB size, the SGA and PGA values are different. ADW has a bigger PGA, ATP has bigger SGA. <\/p>\n","protected":false},"author":1,"featured_media":2317,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[101,55],"class_list":["post-2319","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cloud-autonomous","tag-autonomous-database","tag-oracle-cloud"],"acf":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/bijoos.com\/oraclenotes\/wp-content\/uploads\/2018\/10\/oracle-autonomous-db.png?fit=550%2C318&ssl=1","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/2319","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=2319"}],"version-history":[{"count":5,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/2319\/revisions"}],"predecessor-version":[{"id":2327,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/2319\/revisions\/2327"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media\/2317"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=2319"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=2319"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=2319"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}