Starting with Oracle 12c, the default behavior of database is to gathers statistics during bulk loads like CTAS or INSERT SELECT. However for some reason , if you want to go back to pre-12c behavior , you can do so with a new hint NO_GATHER_OPTIMIZER_STATISTICS hint.
See illustration of (more...)
TABLE ACCESS BY INDEX ROWID BATCHED is new execution plan operation that helps improve performance. It is generally used for > or < queries by selecting few ROWIDs from the index and then try to access the rows in blocks. This significantly reducing the number of times Oracle must (more...)
You will really appreciate this feature if you have called some of the cable companies to terminate your cable connection. This is the response you generally get to hear ” Please call us on so and so date; I cannot update the system now”. Hopefully some of those companies (more...)
Session Data Unit(SDU) is the session layer responsible for sending/receiving data from transport layer. See table below for OSI diagram. Generally increasing the value of SDU can result in improved performance, efficient network utilization when transmitting large amounts of data. However until Oracle 11g the SDU size was (more...)
In the past(Pre 12c), there hase been a lot of confusion regarding Global temporary tables and redo generation. Some of us initially misinterpreting documentation and concluding no redo generation for DMLs in global temporary tables. However in Oracle12c, the documentation is clear about undo being generated for DMLs (more...)
Starting with 12c, Oracle supports creating different type of indexes on same column(s). MySQL supported this kind of indexing mechanism and glad that Oracle also supports it. Generally indexing the same column is considered a performance bottleneck because it slows down inserts and updates to maintain multiple indexes but some (more...)
Identity columns generate unique numeric value for each row in a table. This option was available on DB2 since version 7 and is now available with Oracle 12c too. Hooray !!!!
You can define identify columns with ALWAYS or DEFAULT option of CREATE/ALTER TABLE command.
ALWAYS: Oracle Database always (more...)
1. Preupgrade Utility
Preupgrade utility “utlu121s.sql” is replaced with “preupgrd.sql”. The new utility provides fixup scripts “preupgrade_fixups.sql” and “postupggrade_fixups.sql” to address issues that might be present both before and after the upgrade.These fixup scripts can be executed interactively.
2. Upgrade Utility
The catupgrd.sql Upgrade (more...)
Renaming or relocating Oracle datafiles prior to Oracle 11g was not straight forward process. You had to follow 3 steps.
1. Take the tablespace offline.
2. Rename or relocate using OS
3. Issue ” ALTER DATABASE RENAME FILE” command. All that this command did was update the control files.
Until Oracle12c, Oracle on Unix/Linux is process based whereas on Windows, Oracle is service based. Starting with Oracle12c, databases on Unix/Linux systems can be configured as thread based systems. So far , it looks to me like Oracle introduced this feature to support muti-tenancy. However the benefits can (more...)