Monthly Archives: October 2011

What’s new in Oracle Linux oow2011 a summary

There were some significant new features of Oracle Linux, Announced during oracle openworld 2011. Some of them were in a single slide of a presentation done by Edward Screven, who is the chief corporate Architect at Oracle. Easy to have missed. So here are some of the details of the new features.

DTrace is now available for Oracle Linux 6

DTrace has been a favorite of Solaris users for ages. Now oracle brings the coolness and functionality of DTrace to Linux.  It provides very granular level information about cpu, memory, filesystem and network usage at each process level, in addition to system calls, arguments used etc. It is kind of sort of like strace on linux, but has very rich amount of details, with very little instrumentation overhead.

Wim Coekaerts , has written a detailed article on how to enable this functionality.

Kernel upgrades can now be done while the system is running (Ksplice available since beginning of Sep 2011)

This one was really not an openworld announcement (But was touched upon, in Edward Screven’s presentation) . It has been available since early september 2011. Oracle acquired a company named Ksplice in July 2011. Now Oracle Linux users can perform kernel updates without having to stop the system, or having to reboot the system.

Wim Coekaerts , has written a detailed article on this new functionality and the article has a link to a white paper on how to enable this functionality.

Unbreakable Enterprise Kernel version 2 now available with major new features

The Unbreakable Enterprise Kernel version 2 was released and brings with it lot of new features. Couple of the highlights are below

 Btrfs is now Production

Btrfs stands for Better file system and/or B-Tree file system. Although existing ext3 and ext4 file systems are great, it’s kind of old technology and lacks key enterprise features. Btrfs brings new features like ability to snapshot, online defragmentation, volume growth and shrinking, checksum’s of data and metadata etc.

 Linux Containers

Solaris has had zones and containers for a while that enables virtualization of physical Sun servers that run Solaris. Now oracle is bringing this functionality to Linux. The key difference between Linux Containers and Linux Virtual Machines (Like Oracle Virtual Machine) is that, Linux Containers can run instructions native to the core cpu without any interpretation mechanisms and hence provides good performance for the virtualized hosts.

There are also numerous enhancements to improve performance of oracle products on Oracle Linux, in this new release of the kernel.

Wim Coekaerts, has posted a good article on how to get started with using the Unbreakable kernel version 2.

 Simple example to enable linux containers .

 

Auto DOP and calibrate_io

I was posed the following question by a colleague recently. I am using auto DOP in 11gr2 and I am setting parallel_degree_threshold=12. But when i execute my query, my query is still running with  a parallelism of 48 (ie it seems to be ignoring my setting for parallel_degree_threshold). The problem turned out  to be that for Auto DOP to work,  you need to have dbms_workload_manager.calibrate_io run and the table resource_io_calibrate$ populated.

This requirement is explicitly stated in the oracle 11gr2 documentation at http://download.oracle.com/docs/cd/E11882_01/server.112/e25523/parallel002.htm#CIHEFJGC

 

“When PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database determines whether the statement should run in parallel based on the cost of the operations in the execution plan and the hardware characteristics. The hardware characteristics include I/O calibration statistics so these statistics must be gathered otherwise Oracle Database does not use the automatic degree policy feature.”

Automatic DOP, where oracle figures out the degree of parallelism to use for a given query, gets turned on by setting the initialization parameter PARALLEL_DEGREE_POLICY to either AUTO or LIMITED (When it is limited it does NOT turn on parallel query queuing and in memory parallel query). So the requirement to gather calibrate_io output is applicable to both the setting AUTO and LIMITED.

When you are using Auto Dop, and you want to limit the maximum parallelism that a query can get, you can use the parameter parallel_degree_limit=cpu/io/<integer>, to limit the DOP of a query.

When you create a new database using a DBCA (Database configuration assistant) template, in the new database, the calibrate_io statistics is not present. You can verify this by running  the queries below.

SQL> select * from resource_io_calibrate$;

no rows selected
SQL> select * from v$io_calibration_status;

STATUS
-------------
CALIBRATION_TIME
---------------------------------------------------------------------------
NOT AVAILABLE

Below is an example.

  • I have created a new table named sales_rk that has 58 million rows in it.
  • There are no indexes, or primary keys on this table.
  • The table is decorated with parallel degree DEFAULT
  • parallel_degree_policy=LIMITED
  • parallel_degree_limit=2
  • cpu_count=2
  • parallel_threads_per_cpu=2
  • At this time there are no rows in resource_io_calibrate$

I ran the query “select count(*) from sales_rk”

I would have expected auto dop to have kicked in and parallel_degree_limit to have limited the DOP of the query to 2.

However that is not what happened.

The query ran with a DOP of 4.

So oracle just picked the default DOP (cpu_count x parallel_threads_per_cpu).

Further evidence that Auto DOP did not kick in can be found by examining the dbms_xplan output of the statement.

 

SQL> select count(*) from sales_rk;

  COUNT(*)
----------
  58805952

SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	1bzaqj7scjp7p, child number 0
-------------------------------------
select count(*) from sales_rk

Plan hash value: 2302347944

--------------------------------------------------------------------------------------------------------
| Id  | Operation	       | Name	  | Rows  | Cost (%CPU)| Time	  |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |	  |	  | 21544 (100)|	  |	   |	  |	       |
|   1 |  SORT AGGREGATE        |	  |	1 |	       |	  |	   |	  |	       |
|   2 |   PX COORDINATOR       |	  |	  |	       |	  |	   |	  |	       |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |	1 |	       |	  |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |	  |	1 |	       |	  |  Q1,00 | PCWP |	       |
|   5 |      PX BLOCK ITERATOR |	  |    58M| 21544   (1)| 00:04:19 |  Q1,00 | PCWC |	       |
|*  6 |       TABLE ACCESS FULL| SALES_RK |    58M| 21544   (1)| 00:04:19 |  Q1,00 | PCWP |	       |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access(:Z>=:Z AND :Z)

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing

The Note section explicitly states that Auto DOP was skipped.

Now to fix the situation, i ran dbms_resource_manager.calibrate_io.

DECLARE
  lat  INTEGER;
  iops INTEGER;
  mbps INTEGER;
BEGIN
  DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat);
end;
/

 

Beware of calibrate_io generating bad numbers for io mbps. If it does, then follow instructions in MOS note 1269321.1 to delete the contents of resource_io_calibrate$ and populate the table manually.

Bounce the database.

Check whether calibrate_io worked

SQL> l
  1* select * from v$io_calibration_status
SQL> /

STATUS
-------------
CALIBRATION_TIME
---------------------------------------------------------------------------
READY
07-OCT-11 05.56.40.911 PM

 

Now re-run the same query, Auto DOP kicks in, and it executes with a DOP of 2.

Looking at the execution plan, confirms that Auto DOP did kick in.

 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID	1bzaqj7scjp7p, child number 1
-------------------------------------
select count(*) from sales_rk

Plan hash value: 2302347944

--------------------------------------------------------------------------------------------------------
| Id  | Operation	       | Name	  | Rows  | Cost (%CPU)| Time	  |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |	  |	  | 43087 (100)|	  |	   |	  |	       |
|   1 |  SORT AGGREGATE        |	  |	1 |	       |	  |	   |	  |	       |
|   2 |   PX COORDINATOR       |	  |	  |	       |	  |	   |	  |	       |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |	1 |	       |	  |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |	  |	1 |	       |	  |  Q1,00 | PCWP |	       |
|   5 |      PX BLOCK ITERATOR |	  |    58M| 43087   (1)| 00:00:08 |  Q1,00 | PCWC |	       |
|*  6 |       TABLE ACCESS FULL| SALES_RK |    58M| 43087   (1)| 00:00:08 |  Q1,00 | PCWP |	       |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access(:Z>=:Z AND :Z)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2 because of degree limit

Exadata smart flash log

The exadata development team has now released the exadata cell software version 11.2.2.4.0, which includes a new feature called the “Smart Flash Log”.

In a nutshell, this new feature speeds up redo log writes. Exadata smart flash log uses Exadata smart flash cache as a temporary storage to provide low latency redo log writes. With this new feature enabled, oracle writes both to physical disk and the flash cache simultaneously.

So if for some reason, flash cache writes are slow, the writes to the physical disk will provide the good response times. Similarly if the physical disk writes are slow, the flash cache writes will complete faster, providing the good response times.

You can use the “Create flash log”, cell command to turn this feature on. You can use the “Drop flash log” cell command to turn this feature off.

The exadata storage server software, users guide, has been updated with this information.

You have to have Bundle patch 11 (Actually it works from BP9 onwards, but BP11 is recommended) and exadata cell software 11.2.2.4.0 applied to get this functionality.

Please read the section “Exadata Smart Flash Logging : Flash for database logging”, in the oracle technical white paper, Exadata smart flash cache features and the Oracle Exadata Database Machine, for details.

Here is a video tutorial produced by Oracle Education on this topic, smart flash log.