In Oracle 11G Release 2 segment allocation has been changed. When new table is created, the table segment creation is postponed till first row insert.

This functionality is enabled by default with new parameter DEFERRED_SEGMENT_CREATION set as default to TRUE

DEFERRED_SEGMENT_CREATION = TRUE | FALSE

This new feature allows to save initially a lot of space for newly created objects especially when you have thousands of partitions. However you need to remeber about careful space planing.

Continue reading →

New interesting features are available in 11G related to statistics collection

• New statistics preferences
  • PUBLISH
  • INCREMENTAL
  • STALE_PERCENT
• Hash based sampling for column statistics
• Multicolumn statistics
• Expression statistics

Continue reading →

With 11g release 1 Oracle has introduced new package DBMS_HPROF. It’s very useful for DBAs and developers to analyze execution of PL/SQL code. It enables to gather, store and organize a lot of information about executed code.

Informations gathered by this package are supported by Oracle sqldeveloper which is another great news :).

The PL/SQL hierarchical profiler features:

• easy to install and use
• collect execution times for SQL and PL/SQL code
• keeps results in database tables

• Provides subprogram-level execution summary information, such as:
  • Number of calls to the subprogram
  • Time spent in the subprogram itself
  • Time spent in the subprogram itself and in its descendent’s subprograms
  • Detailed parent-children information, for example:
    • All callers of a given subprogram (parents)
    • All subprograms that a given subprogram called (children)
    • How much time was spent in subprogram x when called from y
    • How many calls to subprogram x were from y

Continue reading →

It’s no new feature in 11g but it’s worth to describe it. Package DBMS_TRACE enables to trace execution of PL/SQL code in database.Oracle collects the trace data as the program executes and writes it to database tables.

Continue reading →

New enhancements are added in 11g for dynamic SQL in PL/SQL :

• SQL statements that are larger than 32 KB are now allowed in native dynamic SQL
• CLOB can be used for DBMS_SQL.PARSE
• DBMS_SQL cursor can be converted to REF CURSOR and vice versa
• Mored data types(including object types, collections) are supported in DBMS_SQL

Continue reading →

New parameters and parallel behaviour are introduced in Oracle 11g that simplifies management of parallel DOP calculations just before execution of a query in database.

New parameters

PARALLEL_MIN_TIME_THRESHOLD - any number > 0, AUTO(default)
PARALLEL_DEGREE_POLICY - MANUAL(default),LIMITED,AUTO
PARALLEL_DEGREE_LIMIT - any number > 1, CPU(default), IO, AUTO
PARALLEL_FORCE_LOCAL - TRUE,FALSE(default)

Changed parameters:

PARALLEL_IO_CAP_ENABLED  - is deprecated and remapped 
to PARALLEL_DEGREE_LIMIT set to IO
PARALLEL_THREADS_PER_CPU - is now hidden parameter
 _PARALLEL_THREADS_PER_CPU
PARALLEL_AUTOMATIC_TUNING - is deprecated and should not 
be used. It means should have default value FALSE.

Continue reading →