Happy to be part of the #ThanksODC campaign in 2018. Often I learn something new related to Oracle from a forum or blog or a social media post or an article written by someone who loves to help the Oracle community. The Oracle community has helped me tremendously to do my job better. Kudos to the Oracle Development Community! Keep growing!!
I like two parameters introduced in Oracle Database 18c -optimizer_ignore_hints and optimizer_ignore_parallel_hints. Maybe I have seen too many SQL statements with hints when trying to fix performance issues after upgrading to a newer release of the database!
In earlier releases the optimizer_ignore_hints parameter was an underscore parameter (_optimizer_ignore_hints). Now we can set the parameter at the database level (ALTER SYSTEM) or at the session level (ALTER SESSION) to disable hints. This parameter makes the Oracle optimizer ignore all embedded hints in the SQL statement. By default, the value for this parameter is FALSE, meaning, all embedded hints will be recognized in 18c.
The related parameter is optimizer_ignore_parallel_hints. Here the optimizer only ignores the PARALLEL hints.
Properties for both OPTIMIZER_IGNORE_HINTS and OPTIMIZER_IGNORE_PARALLEL_HINTS
My preference would be to set optimizer_ignore_parallel_hints in the init file (at the database level) for OLTP databases if the application uses many SQLs with hints embedded. If there is a need to set optimizer_ignore_hints, I would set it for the applications (or users) using a logon trigger at the session level (ALTER SESSION).
Oracle Database 18c (18.3) is available for download for windows platform since August 22, 2018. If you are new to Oracle or if you are a developer and need a database to play with, you can install an Oracle 18c database quickly on your laptop with a few clicks. Read “Oracle18c Database on your Windows laptop in 6 clicks!“. If you installed the Oracle Database 18c software, and want to create a new database swiftly, read “Create Oracle Database 18c in 3 clicks!“
If you are setting up a production system or a database for enterprise applications, you will need to go through the advanced configuration. The best option is to choose “Set Up Software Only” in Oracle Universal Installer (OUI) to complete the Oracle software installation, then use Database Configuration Assistant (DBCA) to create a new database.
OUI and DBCA have the advanced configuration. The OUI advanced configuration is not “advanced” enough, DBCA lets you customize the database configuration options even more – good for an experienced DBA. In my opinion, Oracle should remove the “Advanced Configuration” option from OUI, thus make DBAs to force use DBCA. Let’s see the “Advanced” options difference when using the OUI to create the database vs using DBCA to create the database.
Installation Type (OUI & DBCA)
OUI: Choose Advanced Install
DBCA: Choose Advanced Configuration
Database Edition & Software Owner (OUI only)
These screens are part of the Oracle Software install.
Choose if you want to install Oracle Enterprise Edition or Standard Edition 2
Choose the Windows user who will own the Oracle binaries software installation
Choosing Database Type
OUI gives only two options – both options include the data files from the template. No custom database option.
DBCA has more options. In addition to the two options with templates, you can create a custom database. Also, there is option to choose Single instance, or RAC or RAC one node.
Database Identification
OUI options are limited. Only 1 pluggable database creation and a CDB must have one PDB. Local undo tablespace for PDB is the default.
In DBCA you can specify the number of PDBs required in CDB. If more than one, the PDB name becomes PDB prefix. You can also create CDB with no PDBs. Option to choose local undo tablespace or not for PDB.
Database Storage
OUI lets you choose file system or ASM for storage.
DBCA has more options – use of OMF, redo and control file multiplexing.
OUI gives option to enable recovery, which includes configuring the fast recovery area location.
DBCA gives option to configure Fast Recovery Area including its size, and Archive log mode.
Configuration Options
OUI has enough configuration options, but DBCA has a lot more.
Memory:
OUI: Automatic memory management and for shared memory management, you only specify the total memory, OUI allocates the PGA and SGA.
DBCA: In addition to database character set, choose national character set, language, and territory.
SIZING & CONNECTION MODE (DBCA ONLY):
DBCA: Option to change block size if the template used is “Custom Database”.
DBCA: Option to configure a dedicated server or shared server mode.
Management Options
OUI: Option to configure OEM Cloud Control. Database Express is automatically configured on port 5500.
DBCA: In addition to OEM Cloud Control configuration, option to specify port for Database Express.
Schema Passwords
OUI and DBCA: Option to specify one password for admin accounts or different passwords. PDBADMIN is applicable only when PDBs are created.
OUI and DBCA: Option to specify one password for admin accounts or different passwords. PDBADMIN is applicable only when PDBs are created.
Database Creation Option – DBCA Only
DBCA has option to save the DB create script, create a DB template and customize storage locations, as well as change initialization parameters. No such options in OUI.
The Customize Storage option in DBCA gives flexibility to specify storage parameters.
DBCA gives option to configure a new listener or to register the database with an existing listener. OUI always creates a new listener and configures port 1521.
DBCA gives option to configure Oracle Database Vault and Oracle Label Security. No such option in OUI.
Summary
OUI: Summary shows only Global Setting and Database Configuration.
DBCA: Summary shows much more.
If you plan on creating a database using a GUI tool for enterprise applications, always use DBCA. DBCA gives more flexibility and options. If you are installing the software and want a database with basic default options, use the OUI starter database option, desktop class.
DBCA Database Creation Screens
All the screens that belong to DBCA are in the gallery below. You can also see the options included with the OLTP and DW templates – both are very much similar, one difference is the parameter star_transformation_enabled=TRUE in DW template. Both DW and OLTP include the pre-created data files, so the database creation would be much faster. The custom template builds the database from scratch, thus takes longer.
DBCA 1: Select Database Operation
DBCA 2: Select Database Creation Mode
DBCA 3: Select Database Deployment Type
DBCA 3a: Data Warehouse Template Options
DBCA 3b: General Purpose or Transaction Processing Template Options
