Subscriber Count


Subscribe to Posts by Email


12c Database : Index clustering factor – Do your own math

From 12c, one can influence the clustering factor i.e set at custom level.

For those who does not know about clustering factor and how does it influence the optimizer to use index or not. Suggesting to read the following, if you already know skip the following and proceed to test case.

The clustering factor is […]

12c Database : SQL Translation Framework – Developer thing

SQL Translation framework is a new feature in 12c to give ability to the developers to migrate code without changing the underlying code.

In addition , this feature also provides the translation of code from non-oracle to oracle coding.

Further more, this feature can be a enhancement to sql profiles baselines etc. which can be […]

12c Database : Limiting rows using fetch clause

Do you want to restrict row results not to display/retrieve fully?

Until 11g, Oracle uses Top -n query method to retrieve top or bottom set of data using rownum, dense, rank etc.

But this has limitation when order is required for that data and also an inline view is required and complex to write.

For […]

12c Database : View database patches from the sql prompt

From 12c onwards, you can view the database home patch list from sql prompt itself . A new package dbms_qopatch has been introduced to accomplish this.

Few cool sub routines in this package are get_opatch_install, lsinventory, opatch_bugs etc.

SQL> set longchunksize 1000 SQL> select DBMS_QOPATCH.get_opatch_install_info() from dual DBMS_QOPATCH.GET_OPATCH_INSTALL_INFO() ———————————————————————– <oraclehome><uid>OracleHome-5958ed72-136c-4adc-a078-e3ec7081a6e8</uid> <targettypeid>oracle_home</targettypeid> <patchingmodel>oneoff</patchingmodel> <path>/u01/app/oracle/product/</path> <targettypeid>oracle_home</targettypeid> <inventorylocation>/u01/app/oraInventory</inventorylocation> […]

12c Database : Session level (private)statistics for global temporary tables

Often, when the global temporary tables are in use in batch processing, we have lot of problems regarding plan stability.

For example

A session inserting 1 row in a global temporary table based on some other table joins Another session which were apparently doing the same insert but will try to insert 1000 rows […]

12c Database : Reporting mode for statistics jobs and compare statistics jobs

Before to 12c, we have a problem to revert back to a question, how much time does the statistics operation will run?

This is common problem, as we cannot anticipate the time and its depend on the various factors that will take place at time of statistics job execution.

To accomplish this we will have […]

12c Database : Monitor Database operations using EM Express

Previous to 12c, when you want to perform an monitoring the Session you will have to turn the trace etc or trace with application/module/program/sql_id etc.

But, what if you want to monitor specific operation for that session not whole or application. Kind of set of operations you want to peform not all with in that […]

12c Database : SQL Plan Directives

From 12c onwards, Oracle optimizer maintains the directives that influence the optimizer to take appropriate decision while executing the statement or estimation.

As you read from previous posts, Optimizer maintains the execution statistics now,they can be used to create a directive for future executions. These directives can be used by optimizer and determine which optimization […]

12c Database : Automatic Reoptimization

In continuation of previous post,

Further to adaptive plans, Optimizer uses automatic reoptimization using 1) Statistics feedback (Cardinality feedback) 2) Dynamic statistics (Dynamic sampling)

Let’s take a closer look

Case 5: Adaptive reoptimization using Dynamic statistics

1) Deleted the statistics for table Product information and ORDER_ITEMS 2) Deleted few rows from the order_items […]

12c Database : Adaptive Execution Plans

If you have read the previous post on automatic query reoptimation, this post is the continuation of the same, by looking them closely.

All the examples used are from oracle white paper.

Parameters that influence optimizer to use adaptive features

SQL> show parameter adaptive NAME TYPE VALUE ———————————— ———– —————————— optimizer_adaptive_features boolean FALSE optimizer_adaptive_reporting_only boolean […]