Scalar subqueries to reduce PL/SQL to SQL switching

Tom Kyte has written an excellent article on the current issue (September/October) of Oracle Magazine which shows how to reduce switching between pl/sql and sql using scalar subquery.

Tom describes a scalar subquery as "What exactly is a scalar subquery? It is a subquery in a SQL statement that returns exactly one column and zero rows or one row. That single column can be a complex object type, so it can consist of many attributes, but the subquery returns a single scalar value (or NULL if the subquery returns zero records). A scalar subquery can be used anywhere a literal could have been used."

On the article there are many comparisons of using scalar subquery vs deterministic pl/sql, result cache and etc.
Below is the simplest case of performance comparison with and without scalar subquery.

1.Create a big table to query

SQL> create table bigtable as select * from all_objects nologging;

2. Create the function that will be used in this test case

create or replace function mytest(p_owner varchar2) return number is
obj_count number :=0;
begin 
 select count(*) into obj_count from bigtable bt where bt.owner = p_owner; 
 return obj_count; 
end; 
/

3. Run the first test case without scalar subquery

set timing on
variable cpu number;
begin
:cpu := dbms_utility.get_cpu_time;
end;
/

SQL> select owner,mytest(owner) from bigtable;
....
SYS                                    31328
SYS                                    31328

72232 rows selected. -- this is all the rows in the table
Elapsed: 00:05:11.01

SQL> select dbms_utility.get_cpu_time-:cpu cpu_hsecs from dual;

CPU_HSECS
---------
30931

4. Second test case with scalar subquery caching

set timing on
variable cpu number;
begin
:cpu := dbms_utility.get_cpu_time;
end;
/

SQL> select owner,(select mytest(owner) from dual) mo from bigtable;

SYS                                                    31328
SYS                                                    31328
SYS                                                    31328

72232 rows selected.  -- all the rows in the table

Elapsed: 00:00:17.05

SQL> select dbms_utility.get_cpu_time-:cpu cpu_hsecs from dual;
CPU_HSECS
---------
56

As seen from the outputs both cpu usage and elapsed time has reduced. How this happens has been explained in the above mentioned article.

ksfd: async disk io

ksfd: async disk io was appearing from time to time somewhat predominately (compared to other waits events) on standard edition 11gR1 two node cluster.

Monitoring with APConsole showed the following.


Following metalink notes are related to this wait event.

High waits for "ksfd: async disk io" [ID 1326634.1]
Bug 8660422 - "unspecified wait event" and/or "ksfd: async disk IO" after applying patches that include fix for Bug 7510766 [ID 8660422.8]

This database has the PSU 11.1.0.7.6 applied. Blog post will be updated when PSU 11.1.0.7.7 or the patch 8660422 is applied.

Update on 19th November 2011

After October 2011 PSU (11.1.0.7.9) was applied ksfd: async disk io waits disappeared. Graph (generated with ADMon)below shows the waits for two week period. Wait was happening daily on both or at least one instance (this was a two node RAC) but stopped since the applying of the patch (Patch was applied on 17th).

Removing Datafiles from a Tablespace

There are serveral ways to remove a datafile, one is with an offline drop and other is using alter tablespace. The offline drop is to be used inconjunction with drop tablespace, that is is expected the tablespace will also be dropped. If the intention is just to drop one or more datafiles that is part of a tablespace then alter tablespace drop datafile must be used which was introduced with 10gR2. There are several constraints to dropping a datafile they are (from Oracle documentation)
The following are restrictions for dropping datafiles and tempfiles:
The database must be open.

If a datafile is not empty, it cannot be dropped. If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.

You cannot drop the first or only datafile in a tablespace. This means that DROP DATAFILE cannot be used with a bigfile tablespace.

You cannot drop datafiles in a read-only tablespace.

You cannot drop datafiles in the SYSTEM tablespace.

If a datafile in a locally managed tablespace is offline, it cannot be dropped..

Situation is that there are segment(s) where extents are spread across multiple datafiles and now it is decided to move all the extents into single datafile.

SELECT x.*,  COUNT(*) FROM
  (SELECT owner, (SELECT name FROM v$datafile WHERE file#=file_id ) AS filename ,
    segment_name ,    segment_type ,    tablespace_name
  FROM dba_extents  WHERE tablespace_name='TEST'  ) x
group by owner,filename,segment_name,segment_type,tablespace_name ORDER BY 1,4 DESC,  3;

OWNER  FILENAME                                    SEGMENT_ SEGMENT_ TABLE   COUNT(*)
------ ------------------------------------------- -------- -------- ----- ----------
ASANGA +DATA/rac11g1/datafile/test.372.758295243   FULT     TABLE    TEST          19
ASANGA +FLASH/rac11g1/datafile/test.265.758295919  FULT     TABLE    TEST           1
ASANGA +FLASH/rac11g1/datafile/test.265.758295919  FULINDX  INDEX    TEST          12

For testing this could be created with step 1-4 in an earlier post.

To drop the datafile in the flash diskgroup move the segments (table and indexes) to another tablespace either through alter table .. move (there are other ways too) and alter index rebuild .. tablespace (just one way to move an index). Depending on the number of segments in the datafile that is in flash diskgroup this could take some time and if logging is used could generate lot of redo.

Once all the extents are moved out drop the datafile from the tablespace. If the datafile is not empty following error will be shown

SQL> alter tablespace test drop datafile '+FLASH/rac11g1/datafile/test.265.758295919';
alter tablespace test drop datafile '+FLASH/rac11g1/datafile/test.265.758295919'
*
ERROR at line 1:
ORA-03262: the file is non-empty

To move segments

SQL> alter table fult move tablespace users;

Table altered.

SQL> alter index fulindx rebuild online tablespace users;

Index altered.

After the move verify no extents are on the datafiles in question

SQL> SELECT x.*,  COUNT(*) FROM
  2    (SELECT owner, (SELECT name FROM v$datafile WHERE file#=file_id ) AS filename ,
  3      segment_name ,    segment_type ,    tablespace_name
  4    FROM dba_extents  WHERE tablespace_name='TEST'  ) x
  5  group by owner,filename,segment_name,segment_type,tablespace_name ORDER BY 1,4 DESC,  3;

no rows selected

Drop the datafile

SQL> alter tablespace test drop datafile '+FLASH/rac11g1/datafile/test.265.758295919';

Tablespace altered.

Move the segments back into the tablespace which now has only the desired datafile(s).

SQL> alter table fult move tablespace test;

Table altered.

SQL> alter index fulindx rebuild online tablespace test;

Index altered.

SQL> SELECT x.*,  COUNT(*) FROM
  2    (SELECT owner, (SELECT name FROM v$datafile WHERE file#=file_id ) AS filename ,
  3      segment_name ,    segment_type ,    tablespace_name
  4    FROM dba_extents  WHERE tablespace_name='TEST'  ) x
  5  group by owner,filename,segment_name,segment_type,tablespace_name ORDER BY 1,4 DESC,  3;

OWNER  FILENAME                                    SEGMENT_ SEGMENT_ TABLE   COUNT(*)
------ ------------------------------------------- -------- -------- ----- ----------
ASANGA +DATA/rac11g1/datafile/test.372.758295243   FULT     TABLE    TEST          20
ASANGA +DATA/rac11g1/datafile/test.372.758295243   FULINDX  INDEX    TEST          12