In the previous SQL_TRACE blog, we discussed how to trace Oracle sessions and how to gather information using “trace” files.
This blog will help you to understand and interpret 10046 raw trace data.
This article discusses Deferred Segment Creation, a space saving feature of Oracle Database 11gR2, which is also known as segment creation on demand.
Problem
When we create a table, Oracle will immediately create related segments, like table segment, implicit index segment and LOB segment. And if there are lots of empty tables in the database, then they will occupy disk space before they are even used.
Solution
To handle this issue Oracle introduces Deferred Segment Creation feature using SEGMENT CREATION { IMMEDIATE | DEFERRED } clause. If you use SEGMENT CREATION IMMEDIATE clause with CREATE TABLE statement then all associated segments will be created immediately, but if you use SEGMENT CREATION DEFERRED clause with CREATE TABLE statement then all associated segments will be created only when rows are inserted in the table. So empty tables will not occupy any disk space. To use this feature you need to set DEFERRED_SEGMENT_CREATION initialization parameter, which is TRUE by default.
Example
We will turn off this parameter and create a table using regular create table statement.
Oracle Automatic Storage Management(ASM) was introduced in release 10g. ASM is Oracle’s logical volume manager, it uses OMF (Oracle Managed Files) to name and locate the database files. It can use raw disks, filesystems, or files which can be made to look like disks as long as the device is raw.
ASM uses its own database instance to manage the disks. It has its own processes and pfile or spfile and uses ASM disk groups to manage disks as one logical unit. In some ways ASM makes things more complicated – like accessing the files, copying to different locations, and viewing the time stamps of the files. It’s considered good practice to create one disk group for data (DATA) and one for archive logs and fast recovery area(FRA) per ASM instance. One disk group gets created during the cluster install and configuration (OCR). For a larger production database it’s common for it to have its own DATA and FRA disk groups.
This article will be discussing a new feature of Oracle 11g, which is invisible indexes. Invisible indexes simply means indexes that are not visible to optimizer, meaning the optimizer will not consider such index while preparing query execution plans. It is possible to force the optimizer to consider invisible indexes, but let’s cover the benefits of invisible indexes first:
Benefits
- Test the benefit of an index: Every DML statement will have an extra cost to maintain an index. To ensure that an index is actually useful for our system before dropping it, we can test it by simply making it invisible. The index will not be considered by optimizer anymore. If SQL statements that were using this index perform acceptably, then we should drop the index.
- Introduce New Index: We can introduce a new index without effecting execution plans of existing sql statements.
This article discusses Oracle Table Compression features and usage. Databases are getting bigger and bigger over time and demand more disk-space for storage. Archived data, which is mostly read-only and used for reporting in Warehouses and even on OLTP systems, is stored in compressed form as a best practice. Compressed data can increase I/O performance and reduced memory use in buffer cache; however, it can also increase CPU usage.