OracleNotes

Author: biju.thomas

  • ODC Appreciation Day: Two Useful Parameters in 18c #ThanksODC

    ODC Appreciation Day: Two Useful Parameters in 18c #ThanksODC

    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). 

  • Exploring Autonomous Databases – Client Credentials

    Exploring Autonomous Databases – Client Credentials

    You connect to the autonomous database (ATP or ADW) by downloading the Client Credentials Zip file from the Administration console. 

    Service Console -> Administration -> Download Client Credentials

    Using SQL Developer, you can connect to the ATP or ADW database by choosing the “Connection Type” as “Cloud PDB” and provide the downloaded zip file name (wallet) as the configuration file name. A wallet contains a collection of files, including the key and other information needed to connect to your database service in the Autonomous Cloud. All communications between your computer and the database server are encrypted.

    The downloaded zip file includes the connection information for all the ATP and ADW databases you have created under the cloud account, even though the credential zip file name is “wallet_<DBName>.zip”. 

    SQL Developer Screen – Cloud PDB Connection entries from the ATP database credentials zip file

    The picture shows connection details for 4 different autonomous databases, though the wallet file name (database name) is DB201810021812. 

    When you extract the zip file, and review the tnsnames.ora file, you can find all connection entries (4 services for each ATP database, 3 services for each ADW database). The ADW connection names are <DBNAME>_high,  <DBNAME>_medium, and <DBNAME>_low. The ATP connections are the same, plus  <DBNAME>_parallel.

    The predefined service names provide different levels of performance and concurrency for Autonomous Databases. Read about the different service names here.