i. Database Replay. -- captures database workload and replays. Packages: DBMS_WORKLOAD_CAPTURE and DBMS_WORKLOAD_REPLAY.
ii. SQL Performance Analyzer. -- capture specific SQL statements and analyzes against various types of changes such as initialization parameter changes, optimizer statistics, refresh, and database upgrades, and then produces a comparison report., DBMS_SQLPA.
iii. RAT (Real App testing).—both above tools are part of it.
iv. DBMS_COMPARISON. --. Allows us to compare two tables - or two views - and inform us on the differences in data. Can summarize if there any changes between the data sets. Can detail about the specific rows that are different between the two. Can converge the data sets; get on-demand replication for ordinary tables, within schemas, across schemas or even across databases.
v. SQL Test Case Builder. -- gathers of as much information as possible related to a SQL issue and then packages it. The test case script contains all the commands that are needed to be able to re-create the objects, the user, the stats and the environment. dbms_sqldiag.export_sql_testcase.
vi. OLTP Table Compression. -- The clause "compress for all operations" enables compression on all DML activities like INSERT, UPDATE, etc. The block is compressed, not the row. Compression takes place as a triggered event. Not when DMLs. So no perf impact. (Every block has a symbol table. Symbols replace data. Compression starts when block reaches a threshold.)
vii. Data Pump. Compress with parameter COMPRESSION in the expdp command line. METDATA_ONLY|DATA_ONLY| ALL| NONE. Also enc and masking.
viii. Extended Composite Partitioning.—any combinations of partitions.
ix. Reference Partitioning. -- can be partitioned by a column which is available only in the primary table.
x. Interval Partitioning. -- create table sales6 ( sales_dt date) partition by range (sales_dt) interval (numtoyminterval(1,'MONTH')) ( partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd')) ); use “partition for” while selecting.
xi. System Partitioning. -- partition by system (partition p1 tablespace users); table is physically divided into two segments. Developers decide where to insert.
xii. Partitioning on Virtual Columns.-- …Cl generated as (if ...) virtual partition by list ( a ) ( partition p_low values ('LOW'), partition p_medium values ('MEDIUM')..);
xiii. Global Partitioned Indexes: index that is partitioned using a DIFFERENT partitioning-key or partitioning strategy than the table.
xiv. Incrementally Updated Global Statistics. -- In partitioned tables, each partition can have optimizer statistics. In addition, there is a global statistic on the entire table that is not partition dependent. In 11g, we can collect the global stats incrementally from the changed partition alone, not by doing another full table scan like previous versions. dbms_stats.set_table_prefs.
xv. Partition Advisor.—part of SQL Access Advisor.
xvi. DDL Wait Option. -- alter session set ddl_lock_timeout = 10; retries for 10 secs.
xvii. Adding Columns with a Default Value. -- Oracle ll not update the existing rows unlike earlier versions. New default value is stored in the data dict and queried while selecting.
xviii. Virtual Columns.—create table a (a char generated always as (case …) virtual); indexes can be created. ( result is func based index)
xix. Invisible Indexes. – Invisible only to the optimizer.Unlike "ALTER INDEX ... UNUSABLE" which makes DML's fail, with alter index in_res_guest invisible u can work with DMLs.
xx. Read Only Tables.-- alter table TRANS read only;
xxi. Fine Grained Dependency Tracking. -- Does not set the dependent objects invalid when an object changes unless it is absolutely necessary. Only changes that directly affect an object will cause invalidation.
xxii. Oracle 11g uses Adaptive Cursor Sharing to solve bind var peeking by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values. If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement.
xxiii. The IS_BIND_SENSITIVE column is marked as 'Y', so Oracle is aware this query may require differing execution plans depending on the bind variable values, but currently the IS_BIND_AWARE column is marked as 'N', so Oracle as not acted on this yet.
xxiv. Bind sensitive & aware. -- Once oracle notices that the plan has to be regenerated frequently, it marks Y to Bind-Sensitive. Once oracle changes the plan it marks Y to Bind-Aware in V$SQL. Then best cursor ll be used.
xxv. Bind var peeking. --while no bind vars are used, oracle smartly choses weather to use the index or not based on the cardinality. Using bind variable freezes the plan of the sql at the first instance. While u query for a new value, the previous value in the bind varable peeks and plan for previous value applies here.
xxvi. Adaptive Cursors.-- uses the correct plan based on the value of the bind variable, V$SQL_CS_SELECTIVITY
xxvii. SQL Plan Baselining.—repeatable plans are stored in SQL Management Base (SMB), DBA_SQL_PLAN_BASELINES. DBMS_SPM.
xxviii. SQL PLAN MANAGEMENT. -- Oracle calls this a "conservative plan selection strategy", as the optimizer preferentially uses a tried and tested execution plan, even if a new plan looks like it might perform better. Only when the newer plan is proved to perform well will it be accepted for use.
xxix. Compound Triggers.—All f our different triggers defined as one. create trigger tr for update of col on bookings compound trigger a date before statement is begin ...end; ...after each row is ...
xxx. Ordered Execution of Trigers. -- …before update on tab for each row follows <another trigger> begin…
xxxi. Disabled Triggers. -- create or replace trigger tr after insert on t for each row disable begin…
xxxii. Continue when. -- Instead of using an if and a continue, use continue when mod(ctr,10) != 0; also continue label when cond…
xxxiii. Straight Sequences. -- trans_id:= myseq.nextval; u need to use a select stmt in prev releases.
xxxiv. When OTHERS Then Do Something. – warns for when OTHERS then NULL;
xxxv. Simple Integer. -- Values between negative 2,147,483,648 and 2,147,483,647. It does not allow nulls, but does allow overflow.
xxxvi. Intra-unit Inlining. -- Intr-unit inlining involves replacing a call to a subroutine with copy of the code of that subroutine. Recompile with the setting alter session set plsql_optimize_level = 3; or PRAGMA INLINE (calc_int, 'YES').
xxxvii. Name function parameters in sql.-- select myfunc (p_param1=>1,p_param2=>1) from dual;
xxxviii.Dynamic Cursor and REF CURSOR Interchangeability. -- DBMS_SQL.TO_REFCURSOR - Switch from DBMS_SQL to native dynamic. SQL; DBMS_SQL.TO_CURSOR_NUMBER - Switch from native dynamic SQL to DBMS_SQL.
xxxix. Regexp. -- REGEXP_COUNT to get the no. of matches, SUBEXPR parameter that limits the pattern match to a specific subexpression in the search pattern. Syntax: regexp_substr(source, pattern, position, occurrence, options, subexp).
xl. CLOB. -- EXECUTE IMMEDIATE statement; OPEN-FOR statement and DBMS_SQL.PARSE procedure all accept SQL statements in the form of CLOBs.
xli. PL/Scope. -- a tool that gathers information about user defined identifiers at compile time. Collection of PL/Scope data is controlled by the PLSCOPE_SETTINGS parameter, which has a default setting of "IDENTIFIERS: NONE". Switch this value to "IDENTIFIERS: ALL" to enable collection.
xlii. Restriction in FORALL Statements Removed. -- You can now reference the individual elements of a collection within the SET and WHERE clauses of a DML statement in a FORALL construct.
xliii. Memory Management. --Instead of setting SGA_TARGET and PGA_AGGREGATE_TARGET in 10g, you specify MEMORY_TARGET and MEMORY_MAX_TARGET. alter system set memory_max_target|memory_targe= 1G scope=spfile; V$MEMORY_DYNAMIC_COMPONENTS, V$MEMORY_TARGET_ADVICE.
xliv. Adaptive Thresholds. -- The thresholds are adaptable to a specific time based on the past behavior and are based on the AWR snapshots calculated earlier.
xlv. Pending Statistics: Gather but unpublish statistics so that u can test and then publish…dbms_stats.set_table_prefs, dbms_stats.publish_pending_stats('ARUP', 'SALGRADE'); dbms_stats.delete_pending_stats('ARUP','RES'); DBA_TAB_STATS_HISTORY.
xlvi. Extended Statistics. -- Function-Based Statistics (apply a fn while creating statistics- …method_opt => 'for all columns size skewonly for columns (upper (cust_name))’); Multi-Column Statistics (create associations between different columns (a column group) to help the optimizer make better decisions.) (dbms_stats.gather_table_stats, dbms_stats.create_extended_stats). DBA_STAT_EXTENSIONS.
xlvii. SQL Result Cache. -- When you execute a query with the HINT RESULT_CACHE, Oracle performs the operation just like any other operation but the results are stored in the SQL Result Cache. Subsequent invocations of the same query do not actually go to the table(s) but get the results from the cache. Change in the underlying table invalidates the cache, and it’s refreshed dynamically. dbms_result_cache.flush; dbms_result_cache.invalidate; dbms_result_cache.memory_report.
xlviii. PL/SQL Function Result Cache. -- create or replace function a (…) return a number RESULT_CACHE relies_on (sales_tax_rate, customers) is … exec dbms_result_cache.bypass(true); DBMS_RESULT_CACHE.
xlix. Client Query Result Cache. – param CLIENT_RESULT_CACHE_SIZE = 1G. Database client stacks that use the OCI8 drivers can use this cache.
l. PIVOT.-- select * from (
(
select 'a' value from dual union all
select 'e' value from dual union all
select 'i' value from dual union all
select 'o' value from dual union all
select 'u' value from dual
)
pivot -- use “pivot xml” to get the o/p in xml
(
count( value)
for value in ('a','e','i','o','u')
)
)
li. UNPIVOT. -- select value from
(
(
select
'a' v1,
'e' v2,
'i' v3,
'o' v4,
'u' v5
from dual
)
unpivot [INCLUDE NULLS]
(
value
for value_type in
(v1, v2,v3,v4,v5) – Also can give ANY here.
)
)
lii. SecureFiles: The New LOBs. -- LOBs can now be stored as securefiles or basicfiles. Secure File Features - alter table contracts_sec modify lob (orig_file) (deduplicate| keep_duplicates| compress high| encrypt using 'AES128’| cache), DBA_LOBS, TBS should be (ASSM) enabled., Parameter db_securefile, …lob (orig_file) store as securefile… in create table stmt. DBA_LOBS, DBA_LOB_PARTITIONS.
liii. Migration of LOBs to SecureFiles. -- create a new table, load data from the old one, and rename it. Or use dbms_redefinition.
liv. Colored SQL. -- The procedure add_colored_sql() in the package dbms_workload_repository marks the SQL as "colored", or important enough to be captured in every AWR snapshot regardless of whether the SQL is in top SQLs. WRM$_COLORED_SQL
lv. Parameter Files from Memory. -- create spfile|pfile. from memory;
lvi. Real-time SQL Monitoring. – can be seen in V$SQL_MONITOR, V$SQL_PLAN_MONITOR.
lvii. SQL Access Advisor. -- includes advice on partitioning schemes that may improve performance. The original workload manipulation has been deprecated and replaced by SQL tuning sets (Inputs for sql access advisor.).
lviii. SQL Tuning Advisor automatcally find top sqls from AWR, tests Performance, and the profiles are automatically accepted if the param ACCEPT_SQL_PROFILES is set true. DBA_SQL_PROFILES.
lix. SQL Repair Advisor. -- SRA is run after a SQL statement fails with a critical error. The advisor analyzes the statement and in many cases recommends a patch to repair the statement. If you implement the recommendation, the applied SQL patch circumvents the failure by causing the query optimizer to choose an alternate execution plan for future executions.
lx. AWR baseline. -- contains performance data from a specific time period that is preserved for comparison with other similar workload periods when performance problems occur. Never purged. In 11g Moving Window Baselines, Baseline Templates, and Baseline Metric Thresholds.
lxi. Adaptive Metric Baselines. -- In 11g, notification thresholds can be associated with a baseline, so the notification thresholds vary throughout the day in line with the baseline.
lxii. Automatic Diagnostic Repository. -- When a critical error occurs, it is assigned an incident number, and diagnostic data for the error (such as trace files) are immediately captured and tagged with this number in ADR.
lxiii. Flashback transaction. -- using DBMS_FLASHBACK.TRANSACTION_BACKOUT. View flashback_transaction_query.
lxiv. Tablespace Transport for a Single Partition thru datapump. It creates a new table with the specified partitions.
lxv. Data Masking. -- Data Pump new parameter, remap_data, for masking the actual data while exporting for QA, Test servers). Option remap_data=accounts.acc_ssn:pkg_mask.fn_mask_ssn… fn contains logic for masking.
lxvi. RMAN.-- Data Recovery Advisor - RMAN> list failure; RMAN> advise failure; RMAN> repair failure preview; RMAN> repair failure;
lxvii. RMAN Proactive Health Checks.-- RMAN> validate database;RMAN> validate tablespace users;RMAN> validate datafile 1;RMAN> validate datafile 4 block 56;
lxviii. RMAN Flashback Logs to Rescue. -- Oracle looks in the Flashback logs (instead of datafiles) to find a good copy of the past image.
lxix. RMAN New compression algorithm. -- configure compression algorithm ‘ZLIB’; or BZIP2.
lxx. RMAN Parallel Backup of the Same Datafile. --RMAN> run { allocate channel c1 type disk format '/backup1/%U'; allocate channel c2 type disk format '/backup2/%U'; backup section size 500m datafile 6; } breaks the file into chunks.
lxxi. RMAN backs up only uncommitted undo….ignores commited undo unlike prev releases…
lxxii. RMAN Virtual Private Catalog. -- RMAN> create catalog; RMAN> register database; RMAN> create virtual catalog; RMAN> import catalog rman/rman@catdb1;
lxxiii. Default Passwords. -- select * from dba_users_with_defpwd - read again.
lxxiv. Case-Sensitive Passwords. – enables on setting param SEC_CASE_SENSITIVE_LOGON
lxxv. Password Verify Function. -- improved verification logic with the new function “verify_function_11G” in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql.
lxxvi. Auditing. -- Parameter audit_trail is now set to DB by default, not NONE. More statements have been placed under audit by default. Oracle Security Manager Tool in prev versions now merged with EM.
lxxvii. Transparent Tablespace Encryption. – Everything (including transportable tablespaces, backups, and so on) in the tablespace is encrypted. Encryption of Data Pump Dumpfiles. ENCRYPTION caluse in tablespace creation command.
lxxviii. Access Control Lists for UTL_TCP/HTTP/SMTP. – control access to particular procedures in a package. dbms_network_acl_admin.create_acl, dbms_network_acl_admin.add_privilege, dbms_network_acl_admin.assign_acl.
lxxix. ASM. – New role SYSASM, Variable Extent Sizes, select name, allocation_unit_size from v$asm_diskgroup.
lxxx. ASM does not store data; the database does. The ASM instance, however, maintains metadata such as the diskgroup names, the disks in them, the directories, and so on.
lxxxi. Preferred Mirror Read. -- In an Oracle RAC database there are multiple nodes all going to the same ASM instance, now you can configure a node to read from a specific failgroup. In prev versions it reads only from primary group.
lxxxii. ASM New options. -- drop diskgroup dg7 force including contents; alter diskgroup dg7 mount restricted; alter diskgroup dg2 set attribute 'disk_repair_time'='2H';( how long the ASM instance should tolerate a disk with errors before dropping it from the diskgroup.).
lxxxiii. ASM Metadata Backup and Restore. -- $ asmcmd –p ASMCMD [+] > md_backup.
lxxxiv. Restricted Mount. -- alter diskgroup dg7 mount restricted; ASM instance is aware of the exclusivity of the operations on the underlying disks and minimizes the locking mechanism.
lxxxv. Patching. -- Online Patching- without down time, Features-Based Patching.
lxxxvi. Database Resident Connection Pooling. -- Server-side pool called Database Resident Connection Pool (DRCP). DRCP is available to all database clients that use the OCI driver including C, C++, and PHP. Execute dbms_connection_pool.start_pool; adv- one pool can serve 1000 connections.
lxxxvii.Automatic Health Monitor. –Checks for following integrities, records into Automatic Diagnostic Repository, param diagnostic_dest and view V$DIAG_INFO. AHM calls recovery advisors. If recovery fails, logs in to V$CORRUPT_XID_LIST. DB Structure, Data Block, Redo, Undo Segment, Transaction and Dictionary Integrity Check. DBMS_HM.
lxxxviii. Active Data Guard. – The Data Guard environment works by sending the redo data to the standby server by connecting to the database instance there. In 11g, you can open the physical standby database in read-only mode and restart the recovery process. This means you can continue to be in sync with primary but can use the standby for reporting. dba_logstdby_parameters, LOGSTDBY$EVENTS.
lxxxix. Standby DB types. -- A logical standby (physical struct does not match) turns redo into inserts, updates and deletes (SQLs) and applies to SB db, while a physical standby (physical struct match) directly applies redo to its datafiles.
xc. Snapshot Standby. -- To test apps in prod with Database Replay, the physical standby database can be temporarily converted into an up datatable one called Snapshot Standby Database. alter database convert to snapshot|physical standby.
xci. Easier Creation of Standby Database. -- One RMAN command that does it all on setting a few params.
xcii. Real Native Compilation. -- Native-compile without a C compiler in the server or setting the parameter. All you have to do is set a session parameter before creating or recompiling stored code: alter session set plsql_code_type = native; USER_PLSQL_OBJECT_SETTINGS.
xciii. Dimensions. -- They reference existing tables, and do not contain any data themselves - they merely add additional metadata to existing database objects. To create a product dimension, you'd first create the table that contains the data, and then create the dimension afterwards. On a product table, CREATE DIMENSION products LEVEL category IS (products.prod_category, products.prod_cat_desc) HIERARCHY prod_hier (product CHILD OF subcategory CHILD OF category) ATTRIBUTE category DETERMINES products.prod_category;
xciv. OLAP Cubes. -- Whereas MVs store pre-computed results to avoid joins and aggregations, cubes store raw data and compute most summaries on the fly. Stored in special area called an Analytic Workspace (AW) as BLOBs on tables specially named with an AW$ prefix.
xcv. Analytic Workspace Manager (AWM). – Tool to manage AW OLAP Objects.
xcvi. Cube Organized Materialized Views. -- 11g Combines the rewrite feature of MVs with the performance benefits of OLAP cubes via a new feature called Cube Organized Materialized Views.(OLAP cubes are represented as MVs).Prefixed with CB$. Select * from table(cube_table('GLOBAL.PRICE_CUBE')).
xcvii. Query Rewrite rules are more relaxed. Even if the MV and the query used inline views (or subqueries) or remote tables.
xcviii. MVs. - Refresh statement combinations (merge and delete).Removal of unnecessary refresh hint. Index creation for UNION ALL MV.
xcix. DBA_MVIEW_DETAIL_PARTITION. This view shows the partitions that have been updated and will be used in PCT (Partition change tracking) refresh later.
c. Truly Online Index Rebuild. -- The online rebuild is truly online: it does not hold an exclusive lock. The DMLs are not affected.
ci. GTT can be created with a new tablespace clause.
cii. SQL*Plus.-- Error Logging- set errorlogging on table sh.my_sperror_log;
ciii. Shrinking the Temporary Tablespace. -- alter tablespace temp1 shrink space.
civ. SQL*Plus Shows BFILE path.
cv. DBA_TEMP_FREE_SPACE. -- displays information about temporary tablespace usage. ALTER TABLESPACE temp SHRINK SPACE|TEMPFILE '..'.
cvi. ALTER SYSTEM RESET <params> - now sid clause is not needed.
cvii. DBMS_RESOURCE_MANAGER. -- Now includes a CALIBRATE_IO procedure to assess the I/O performance of the database server’s storage system by performing an I/O intensive read-only workload. Statistics in AWR.
cviii. Remote External Jobs- allows the database to schedule external jobs which run on a remote server. The remote server doesn't have to have an Oracle client or database installation, but it must have an Oracle Scheduler Agent installated. Detached Jobs, Lightweight Jobs, Support for Data Guard.
cix. Direct NFS Client. --$ORACLE_HOME/dbs/oranfstab, /etc/oranfstab, /etc/mtab
cx. Log Miner in EM.
cxi. Flashback Data Archive.—uses Flashback Recovery Area, unlike undo in prev versions. Create flashback archive near_term tablespace far_near_term retention 1 month.
cxii. The Flash Recovery Area is a specific area of disk storage that is set aside exclusively for retention of backup components such as datafile image copies, archived redo logs, and control file autobackup copies. Should be set before any Flash Backup and Recovery activity can take place.
cxiii. Online table redefinitions.—by default no longer invalidate dependent objects (PL/SQL, views, synonyms etc.), provided the redefinition does not logically affect them. An exception to this behavior is triggers, which are associated directly with a table.
cxiv. Table Compression Enhancements. --NOCOMPRESS, COMPRESS, COMPRESS FOR DIRECT_LOAD OPERATIONS, COMPRESS FOR ALL OPERATIONS.
cxv. PL/SQL Hierarchical Profiler. -- gather and analyze hierarchical profiler data for PL/SQL programs. Consists of the DBMS_HPROF package, which feels similar to the DBMS_PROFILER and DBMS_TRACE packages, and the plshprof command line utility to converts the profile information into HTML format.
cxvi. Hangman Utility – The Hang Manager (hangman) utility is to detect database bottlenecks. An extension of the dba_waiters and dba_blockers views, the hangman tables have a “hang chain” that allow the DBA to find the source of “hangs”, such as the “deadly embrace” where mutually blocking locks or latches hang a process. In 11g, the hangman utility is installed on all RAC nodes by default, allowing for easier inter-node hang diagnostics.
cxvii. PLSTIMER. -- Identifies hotspots and performance tuning opportunities in PL/SQL.
cxviii. Automatic Reporting of Corrupt Blocks.
cxix. End-to-End Redo Validation.
cxx. Oracle Audit Vault. -- is a powerful enterprise wide audit solution that efficiently consolidates, detects, monitors, alerts, and reports on audit data for security auditing and compliance.
cxxi. Oracle Configuration Manager. – creates tar to be sent to Oracle Support.
cxxii. Oracle text. -- SDATA Sections - Unlike MDATA, they can be used for range searching on numeric or date fields, as well as equality searching.
cxxiii. Oracle text. --Composite Domain Indexes, Increased Number of Partitions, New parallel Optimize Index “rebuild” mode, Online Index Recreation - CTX_DDL.RECREATE_INDEX_ONLINE, Incremental Indexing -CTX_DDL.POPULATE_PENDING.
cxxiv. Oracle text. --AUTO_LEXER can perform automatic identification of different languages, INDEX_STEMS feature now available for many more languages.
cxxv. Native Oracle XML DB Web Services. -- Web services are becoming the preferred way to expose data to client applications. Oracle 11g Database makes the conversion of existing PL/SQL code into web services easier than ever by providing Native XML DB web services. With some simple configuration, this functionality exposes PL/SQL code code as web services.
Release 2:
cxxvi. Listagg. -- select deptno, listagg( ename, '; ' ) within group (order by ename) from emp group by deptno order by deptno, for every dept no, gives the name as a ';' seperated list.
cxxvii. NTH_VALUE (a generalization of existing FIRST_VALUE and LAST_VALUE functions) gives users the functionality of retrieving an arbitrary (or nth) record in a window.
cxxviii. Recursive subquery_factoring_clause in WITH - 2 sqls should be in SFC, 1st (anchor member) shouldn't reference query name. 2nd (recursive member) should reference query name, should be combined with 1st using union all. Eg: with a (x , y) as (select 1,1 from dual union all select 2*x , y+1 from a where y<5) select x from a. o/p will be 1 2 4 8 16 5 rows.
cxxix. Edition-based Redefinition. --default edition ora$base, CREATE EDITION e2; ALTER SESSION SET EDITION = e2; Editioning views expose a different projection of each changed table into each edition to allow each to see just its own columns. Crossedition triggers propagate data changes made by the old edition into the columns of the new edition.
cxxx. Automatic Block Repair. --If a corrupt data block is discovered on a physical standby database, the server attempts to automatically repair the corruption by obtaining a copy of the block from the primary. database.Params LOG_ARCHIVE_CONFIG, LOG_ARCHIVE_DEST_n
cxxxi. Real-time query. -- A physical standby database can be open for read-only access while redo apply is active only if the Oracle Active Data Guard option is enabled. Queries can be offloaded from the primary database to a physical standby database. Param STANDBY_MAX_DATA_DELAY
cxxxii. DBMS_PARALLEL_EXECUTE. -- provides subprograms to allow a specified INSERT, UPDATE, DELETE, MERGE, or anonymous block statement to be applied in parallel chunks. Procedures CREATE_TASK, CREATE_CHUNKS_BY_ROWID, RUN_TASK, TASK_STATUS, RESUME_TASK, DROP_TASK
cxxxiii. Parallelism Enhancements. -- In-Memory Parallel Execution, Auto Degree of Parallelism, parallel_degree_policy -- decides whether or not to use direct path depending on the size of the table and the buffer cache. parallel_min_time_threshold -- only when the num_rows suggests that it will take more than nn seconds to scan the table. parallel_degree_limit -- sets a limit on the maximum degree of parallelism. parallel_force_local --prohibits “parallel parallelism”, a case where parallel queries on a RAC node are limited only to the local instance node.
cxxxiv. Deferred Segment Creation.-- Param DEFERRED_SEGMENT_CREATION, clauses SEGMENT CREATION DEFERRED and SEGMENT CREATION IMMEDIATE in CREATE TABLE..., col SEGMENT_CREATED in create table.
cxxxv. Data guard.—The redo data being transmitted across the network can be compressed.
cxxxvi. Instance Caging allows the DBA to limit the CPU usage of an Oracle instance by setting the CPU_COUNT initialization parameter and enabling CPU resource management.
cxxxvii. Standby databases that a primary database can support is increased from 9 to 30.
cxxxviii. DBMS_SCHEDULER.CREATE_FILE_WATCHER. – enables jobs to be triggered when a file arrives on a given machine.
cxxxix. Oracle Scheduler.-- e-mail notifications on any job activity.
cxl. Oracle Scheduler.-- Specify multiple destinations for a job.
cxli. EXECUTE privilege for DIRECTORY objects.
cxlii. Flashback Data Archive Support for DDLs.
cxliii. Enhance CREATE or REPLACE TYPE to Allow FORCE. - CREATE OR REPLACE TYPE t1 FORCE AS OBJECT (c varchar(20)).
cxliv. Materialized view logs on the base table contain a WITH COMMIT SCN clause.
cxlv. Column level compression -- create table tab compress for archive level=3 as select .... archive level specifies the amount of compression. Available only in Exadata storage devices.
cxlvi. Fine-Grained Dependencies for Triggers.
cxlvii. IGNORE_ROW_ON_DUPKEY_INDEX Hint for INSERT Statement.
cxlviii. Compressed Table Support in Logical Standby Databases and Oracle LogMiner.
cxlix. LogMiner.-- Enables the analysis of the contents of archived redo logs. It can be used to provide a historical view of the database without the need for point-in-time recovery. It can also be used to undo operations, allowing repair of logical corruption. DBMS_LOGMNR
cl. V$LOGMNR_CONTENTS.-- LogMiner populates a row in this view with each logical operation performed on the database, thus offering familiar, relational-based access for ad-hoc querying or custom application consumption. Each row contains a SQL UNDO statement, which can be used to rollback the change, and SQL REDO statement, which details the original operation.
cli. Stored Outlines Migration to SQL Plan Management.
clii. Zero-Size Unusable Indexes and Index Partitions.
cliii. DB Replay. -- A Replay Compare Period Report performs a high-level comparison of workload replay to its capture or to another replay of the same capture.
cliv. Compare SQL Tuning Sets.
clv. Flash Cache--Flash_cache is used for tertiary storage on solid-state disk (SSD) while KEEP pool uses volatile RAM disk. alter|create table|index objectname storage ( buffer_pool { keep | recycle | default } flash_cache { keep | none | default } );
clvi. Keep pool --The keep pool's purpose is to take small objects that should always be cached, for example Look Up Tables.
clvii. Recycle pool --The recycle pool is for larger objects.
clviii. Default pool -- The default pool is for everything else.
clix. Oracle Clusterware. -- Create Quorum Disk Groups and Quorum Disk FailGroups which cannot hold any other data than OCR or Voting Disk data.
clx. Oracle Clusterware.-- A portable cluster software that allows clustering of single servers so that they cooperate as a single system. Oracle
clx. Clusterware also provides the required infrastructure for Oracle Real Application Clusters (RAC).
clxi. The Oracle Clusterware Compenents.-- is comprised primarily of two components: the voting disk and the OCR (Oracle Cluster Registry). The voting disk is nothing but a file that contains and manages information of all the node memberships. The OCR is a file that holds cluster and database configuration information for RAC and Cluster Ready Services (CRS) such as the cluster node list, and cluster database instance to node mapping and CRS application resource profiles.
clxii. ASM Cluster File System Snapshots. -- Create up to 63 image copies of file systems as a point in time capture. These snapshots a read only and can be used to view a file system as of the point in time in the past when they were created.
clxiii. ASM Dynamic Volume Manager. -- Device driver which is integrated into the Oracle Kernel called ASM Dynamic Volume Manager (ADVM) is loaded at ASM startup and functions as a standard I/O interface for normal file systems to utilize ASM functionalities. This driver communicates with the ASM instance about ASM extent maps, rebalancing operations and I/O failure issues.
clxiv. ASM Optimal Disk Placement. -- Specify on which disk regions the ASM Disks will be placed. This enables us to make use of faster disk zones on the outer regions of disks for better I/O performance. Possible options are hot/mirrorhot and cold/mirrorcold.
clxv. Renaming Disk Groups – renamedg.
clxvi. Preprocessing Data for ORACLE_LOADER Access Driver in External Tables.
clxvii. DBFS. -- The Oracle Database File System (DBFS) is a feature that creates a standard file system interface on top of files and directories that are stored in database tables. DBFS is similar to NFS in that it provides a shared network file system that looks like a local file system. Like NFS, there is a server component and a client component.
clxviii. Partition pruning. – jus the elimination of partitioning while searching.