A great feature introduced in the Oracle 11g database is the SQL query ‘Result Cache’. A result cache is an area of memory that stores the results of a database query or query block for reuse. The cached rows are shared across SQL statements and sessions unless they become stale. The caching mechanism is efficient and easy to use, and it relieves you of designing and developing your own legacy cache procedures and cache-management policies.
This package enables the collection of data for performance improvement or for determining code coverage for PL/SQL applications. Application developers can use code coverage data to focus their incremental testing efforts. This blog provides DBAs, developers, support analysts, and consultants with PL/SQL tuning hints and tips using profiling.
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.