|
||||
|
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 […] Before to 12c, all the sql tuning features like ACS, SQL Profiles, SQL Outlines, SPM etc are for proactive tuning and plan stability. There is no way around to decide and change the optimizer decision on fly during the execution of a sql statement. Since 12c, Oracle introduced the set of capabilities called "adaptive query […] Earlier to 12c, when an partition is truncated with update global index clause the operation will take longer to complete since it has to perform the operation on index also to remove corresponding keys in index partition/index and then rebuild entire partition. But that is history, Asynchronous global index maintainence is the feature to clear […] From 12c, interval partitions has been enhanced to use reference partitioning, With this you can create a parent/child table with references in them using interval partitioning. The following is the test case. Create table EMPLOYEE ( EMPLOYEE_ID number primary key, EMPLOYEE_NAME varchar(25), JOINING_DATE date) PARTITION BY RANGE (JOINING_DATE) INTERVAL (NUMTOYMINTERVAL(1,’YEAR’)) ( PARTITION P_2013_06 VALUES LESS […] From 12c, you can truncate partitions from a referenced partition with cascade option. The following is the test case. Create table EMPLOYEE ( EMPLOYEE_ID number primary key, EMPLOYEE_NAME varchar(25), JOINING_DATE date) PARTITION BY RANGE (JOINING_DATE)( PARTITION P_2013_06 VALUES LESS THAN (to_date(’30-06-2013′,’dd-mm-yyyy’)) NOCOMPRESS, PARTITION P_2013_07 VALUES LESS THAN (to_date(’31-07-2013′,’dd-mm-yyyy’)) NOCOMPRESS, PARTITION P_2013_08 VALUES LESS THAN (to_date(’31-08-2013′,’dd-mm-yyyy’)) […] In 12c, Oracle introduced package to manage the global indexes orphan entry clean up and clearing up the online partition movement stuff. Here it is PROCEDURE CLEANUP_GIDX- To clean up the global indexes , this runs daily at 2.00AM via scheduler job automatically PROCEDURE CLEANUP_GIDX_INTERNAL – To clean up the internal tables PROCEDURE CLEANUP_ONLINE_OP […] Before to 12c, if you want to maintain partitions you will need to execute that many alter statements partition commands that many times. For example to add p7,p8 partitions, you have to use SQL> alter table test_tbl add partition p7 values less than (7) SQL> alter table test_tbl add partition p8 values less than […] Before to 12c, it is not possible to create a index for certain partitions or for the partitions you want. From 12c onwards, you can create the indexes (global or local) for specific partitions (not to all partitions) or for specific sub partitions (not to all sub partitions). This means indexes will be created […] |
||||
Oracle 23c New Features for DBA's
Follow Me!!!