Tag: OracleCloud

A CDB in Oracle Cloud could host both ATP and ADW (pluggable) databases (see here). Let’s look at the initialization parameter differences at the PDB level for ATP and ADW.  

ATP has a minimum number of 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. 

Init Parameter	ATP	ADW
pga_aggregate_target	3000M	5100M
pga_aggregate_limit	6000M	10200M
sga_target	8000M	3400M

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. 

ATP?	PARAMETER NAME	DISPLAY VALUE	DEFAULT VALUE
	_cell_offload_vector_groupby	FALSE	TRUE
	_cloud_service_type	DWCS	NULL
	_datapump_inherit_svcname	TRUE	FALSE
	_default_pct_free	1	0
	_enable_parallel_dml	TRUE	FALSE
YES	_kd_rows_chk	FALSE	TRUE
	_ldr_io_size	33554432	262144
	_ldr_io_size2	33554432	1048576
	_max_io_size	33554432	1048576
	_optimizer_answering_ query_using_stats	TRUE	FALSE
	_optimizer_gather_stats_on_ load_all	TRUE	FALSE
	_optimizer_gather_stats_on_ load_hist	TRUE	FALSE
	_parallel_cluster_cache_ policy	ADAPTIVE	ADAPTIVE
	_pdb_inherit_cfd	TRUE	FALSE
	_pdb_lockdown_ddl_clauses	131066	0
	_pdb_max_audit_size	200M	0
	_pdb_max_diag_size	100M	0
	_px_xtgranule_size	128000	10000
YES	cpu_count	1	0
YES	db_lost_write_protect	TYPICAL	NONE
	max_idle_blocker_time	5	0
YES	nls_language	AMERICAN	AMERICAN
YES	nls_territory	AMERICA	AMERICA
YES	ofs_threads	40	4
	parallel_degree_policy	AUTO	MANUAL
YES	parallel_max_servers	12	4294967295
	parallel_min_degree	CPU	1
YES	parallel_servers_target	12	0
YES	pga_aggregate_limit	10200M	0
YES	pga_aggregate_target	5100M	0
YES	plscope_settings	identifiers:all	IDENTIFIERS:NONE
YES	plsql_optimize_level	2	2
	resource_manager_plan	FORCE:DWCS_PLAN	NULL
	result_cache_max_result	1	5
YES	result_cache_max_size	10M	1
	result_cache_mode	FORCE	MANUAL
YES	sessions	100	4294967295
YES	sga_target	3400M	0

Out of the 39 parameters set in ADW, half are underscored parameters!

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.

I am the new kid in the block, wandering around the autonomous database cloud. Let me share something I found interesting. Though on Oracle Cloud Infrastructure, the ATP (Autonomous Transaction Processing) and the ADW (Autonomous Data Warehouse) have two different higher level menu and they appear to be different, they are not.

Here are two ADW databases I created:

Here are two ATP databases created:

Each instance of the ADW and ATP  is a pluggable database. The (pluggable) databases created on Sep 10th – one ATP and one ADW – belonged to the same CDB. Similarly, the (pluggable) databases created on Oct 2 – again one ATP and one ADW – belonged to the same CDB. So, it appears that at the CDB level, both ATP and ADW have the same configuration. The differences are only in the PDB. And my guess is that Oracle creates the new instances (ATP and ADW) in a CDB until that CDB reaches its capacity threshold, then moves to the next CDB.

The database properties are same as well for ATP and ADW. The only (obviously) difference is the GLOBAL_DB_NAME value.