|
||||
|
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 […] 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 […] 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 […] 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-5958ed72-136c-4adc-a078-e3ec7081a6e8 oracle_home oneoff /u01/app/oracle/product/12.1.0.1/db_1 oracle_home /u01/app/oraInventory […] 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 […] 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 […] 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 […] 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 […] 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 […] 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 […] |
||||
Oracle 23c New Features for DBA's
Follow Me!!!