Some hige-level concepts about transaction processing

I once read in a forum someone proposed an interview question for Oracle DBA. The question was like 'What kind of things Oracle do when issuing a 'COMMIT' ?'. I was very vague at that time. Now I am feeling better. I went through a chapter called "Transaction Processing" in an old Oracle University guide - Oracle 9i database: Space and Transaction Management (Student Guide). Here are some high-level concepts about transaction process, including something Oracle will do when it commits a transaction.

Sytem commit number (SCN)
- identifies the committed version of a database

Change vector
- contains the information necessary to change one block from one consistent state to another consistent state

Begin transaction

1. Undo segment binding
2. Slot allocation in the transaction table
3. Undo block allocation

Transaction Commit

1.  Finds an SCN value
2.  Updates the transaction table
3.  Puts the current undo block into the free block pool (under some condition)
4.  Creates a commit record in the redo log buffer
5.  Flushes the redo log buffer to disk (for durability)
6.  Releases locks held on rows and tables

[Dec 26,2009 Ed.]
Tom Kyte wrote a section "What Does a COMMIT Do?" in his book "Expert Oracle Database Architecture" (pp 292-98). There are two points I would like to add based on his book.

1. When Oracle issues a COMMIT, LGWR writes all of the remaining redo log entries to disk.  Considering commit after inserting one row into a table compared to after inserting 1M rows, the time for commit in these two cases is almost same, why? Because redo info is being written to the disk during the inserting process batch by batch. At COMMIT, the remaining redo entries  be flushed is thus small. The cost of COMMIT is not a function of the transaction size.

2. When COMMIT, some of the blocks the transaction modified will be visited and "cleaned out" in a fast mode if they are still in the buffer cache. Block cleanout refers to the cleaning out the transaction information stored in the database block header.

Modifying a data block

1.  Find an interested transaction list (ITL) available in the block
2.  Lock the row you are modifying
3.  Generate the redo part of data describing the changes to the data block
4.  Generate the undo part of data describing

•  The inverse operation of data block user changes
•  The undo block changes in the undo segment
•  The undo segment block changes (if necessary)

    5.   Create the redo record and apply the changes to the blocks.

Don't use PL/SQL to Do the Job of SQL

Several days ago, I was asked to review scripts from development team for an application consolidation effort. There is a particular script that uses PL/SQL, which run more than 3 hours. After I reviewed it, I believed they can be written by SQL statment.

For example, for the following PL/SQL block, it depends on the EXCEPTION condition to update a table. Though I am not an experienced PL/SQL programmer, I am suspicous this could be considered good practice in PL/SQL.

-- PL/SQL block

BEGIN
   FOR bas_rec IN bas_cur LOOP
      v_my_account_id:=bas_rec.my_account_id;

      BEGIN 
  SELECT DISTINCT ms.state_id INTO v_state_id
    FROM
      my_sch.tab_ms ms
    WHERE
      ms.account_id = v_my_account_id AND
      ms.state_id IS NOT NULL AND
      ms.svc_node_type_id  NOT IN (203,204,206,208,218,402) AND
      ms.is_pq ='N' AND
      ms.svc_status_id = 2;
     
  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      UPDATE my_sch.conv_2 bas
        SET bas.to_be_converted_status=v_exclusion_flag, 
     bas.exclusion_reason = v_exclusion_reason_multi, 
     bas.is_processed = v_processed_flag
        WHERE bas.my_account_id = v_my_account_id;
        v_is_updated:='Y';
      COMMIT;
    WHEN NO_DATA_FOUND THEN NULL; 
    WHEN OTHERS THEN 
    dbms_output.put_line('Multi State block OTHERS - v_ban:'||sqlerrm ); 
  COMMIT;
      END; 
  END LOOP;
END;

Anyway, I know that " Don't use PL/SQL to Do the Job of SQL". So I translated the above PL/SQL to the following SQL.

-- SQL code 

UPDATE my_sch.conv_2 bas
SET bas.to_be_converted_status='N', 
    bas.exclusion_reason = 'SERVICES IN MULTIPLE STATES', 
    bas.is_processed ='Y' 
WHERE bas.is_processed is null
  AND 1 < ( select count( DISTINCT ms.state_id)
      from my_ord.tab_ms ms
       WHERE ms.account_id =  bas.my_account_id
  AND ms.state_id IS NOT NULL 
  AND ms.svc_node_type_id  NOT IN (203,204,206,208,218,402) 
  AND ms.is_pq ='N' 
  AND ms.svc_status_id = 2 )
;

I also re-wrote the other part of the script with SQL. In a small scale test, original PL/SQL took 5 min. My SQL code took about 1.5 min. Sadly, developers are more comfortable with their PL/SQL code and are unwilling to do a thoroug test and verification about SQL method. So I will still use their PL/SQL code in the production implementation - just running 5 threads of them to speed up instead of 1 thread previously.

DDL for CONSTRAINTS and DEFAULTS

When using a CTAS to create a table from an existing table, the new table won't have all the constraints as the exsiting table except that the NOT NULL constraints are preserved. Below is an example showing a CHECK constraint is not presented in a table created from CTAS:

TESTDB> create table t (id number not null, val number check (val > 5));
 
Table created.
 
 
TESTDB> insert into t values(1,7);
 
1 row created.
 
TESTDB> commit;
 
Commit complete.
 
TESTDB> insert into t values (2,4);
insert into t values (2,4)
*
ERROR at line 1:
ORA-02290: check constraint (OPS$MYUSRID.SYS_C001214102) violated
 
 
TESTDB> desc t;
 Name                  Null?    Type     
 --------------------- -------- --------
 ID                    NOT NULL NUMBER
 VAL                            NUMBER
 
TESTDB> create table t1 as select * from t;
 
Table created.
 
TESTDB> desc t1;
 Name                 Null?    Type
 -------------------- -------- ----------
 ID                   NOT NULL NUMBER
 VAL                           NUMBER
 
TESTDB> insert into t1 values (1, 4);
 
1 row created.
 


TESTDB> ;
  1* select owner, constraint_name, constraint_type, table_name, search_condition from user_constraints
TESTDB> /
 
OWNER        CONSTRAINT_NAME                C TABLE_NAME SEARCH_CONDITION
------------ ------------------------------ - ---------- ------------------------------
OPS$MYUSRID  SYS_C001214102                 C T          val > 5
OPS$MYUSRID  SYS_C001214101                 C T          "ID" IS NOT NULL
OPS$MYUSRID  SYS_C001214103                 C T1         "ID" IS NOT NULL

To create the constraints in the new table, I thus faced a task to extract all the DDL about constraints from the existing table. The approach I adopted after several tests was as follows.

1. First, I created a table my_constraints to have CLOB datatype for search_conditon column since we can not use LIKE operater on a LONG type column.

create table my_constraints ( 
    owner varchar2(30)
   , constraint_name varchar2(30)
   , constraint_type varchar2(1)
   , table_name varchar2(30)
   , search_condition clob
   ) ;

   insert into my_constraints
   select owner, constraint_name, constraint_type, table_name, to_lob(search_condition)
     from dba_constraints 
   where owner =&owner ;

2. For PK, UK and check constraints ( excluding NOT NULL), I used the following command to extract the DDL:

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', t.constraint_name , t.owner ) ddl_string
  from my_constraints t
 where table_name = upper('&table_name')
  and  owner=upper('&owner')
and constraint_type in ('P', 'U','C')
and ( search_condition is null
      or ( search is not null and search_condition not like '%NOT NULL')
    )
;

3. For FK constraints, I used the following command to extract the DDL

SELECT to_char(DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', table_name, owner)) DDL
  FROM (SELECT DISTINCT b.table_name, b.owner
           from dba_constraints b
           where b.constraint_type = 'R'
           AND b.owner = upper('&owner')
           AND b.table_name = upper('&table_name')) ;

CTAS also does not preserve DEFAULT values of columns. I used the following to obtain the 'ALTER TABLE' statement for DAFAULTS:

BEGIN
        FOR r IN (
             select owner, table_name, column_name, data_type, data_default
               from dba_tab_columns
             where owner = upper('&owner')
               and table_name = upper('&table_name')
               and data_default is not null
        )
        LOOP
                DBMS_OUTPUT.PUT_LINE
                ( 'ALTER TABLE ' || r.owner || '.' || r.table_name  ||
                 ' modify  ' ||r.column_name ||
                 ' default ' || r.data_default || ';');
        END LOOP;
END;
/

The reason we need to use PL/SQL instead of SQL is that DATA_DEFAULT column is LONG type. We can not use '||' with LONG type column.

Move table to different tablespace through online redefinition

Some times ago, I tested the steps of moving two tables to different tablespace through online redefiniton. I developed the steps based on the Oracle online documentation.

The background is that we need to do an online copy of a production database with subset data to another data center. These two tables are critical and need to be copied over and would be used to build other tables. However, these two tables reside in a tablespace hosting many other tables. Thus to put them into a separate tablespace would make our copy job much easier.

Below describes the steps and commands involved for reference. TAB_MA and TAB_MS are under schema SCH_SO. The tablespace to which we want to move them is called SCH_SO_DATA_R. (Those are not real production table names)

Step:
~~~~~

1. Verify that the table is a candidate for online redefinition

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('sch_so','tab_ma',
dbms_redefinition.cons_use_pk);
END;
/


BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('sch_so','tab_ms',
dbms_redefinition.cons_use_pk);
END;
/


2. Create an interim tables

create table sch_so.tab_ma_int
tablespace sch_so_data_r
as
select * from sch_so.tab_ma
where 1=0;


create table sch_so.tab_ms_int
tablespace sch_so_data_r
as
select * from sch_so.tab_ms
where 1=0;


3. Start the redefinition process.

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
'sch_so', 'tab_ma','tab_ma_int',
)
END;
/


BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
'sch_so', 'tab_ms','tab_ms_int',
)
END;
/


Note:
After redefinition finished, checked the segment size of interim tables:

1 select owner, segment_name, tablespace_name, bytes/1024/1024/1024 size_G from dba_segments
2* where tablespace_name='SCH_SO_DATA_R'
SQL> /

OWNER SEGMENT_NAME TABLESPACE_NAME SIZE_G
---------------- ----------------------- ----------------- ----------
SCH_SO TAB_MA_INT SCH_SO_DATA_R 4.9375
SCH_SO TAB_MS_INT SCH_SO_DATA_R 9.1875

time: 20 min

4. Create any triggers, indexes and constraints on the interim tables


5. Optionally, synchronize the interim table hr.int_admin_emp.

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('sch_so', 'tab_ms', 'tab_ms_int');
END;
/

6. Complete the redefinition.

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('sch_so', 'tab_ms', 'tab_ms_int');
END;
/


BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('sch_so', 'tab_ma', 'tab_ma_int');
END;
/

Note: The table is locked in the exclusive mode only for a small window toward the end of this step. After this call the table is redefined such that it has all the attributes of the interim table.

7. Drop the interim table.

session_cached_cursors and parse

The following  test case demonstrated the effects of session_cached_cursors on whether and how the PL/SQL caches cursors. I tested with either session_cached_cursors=0 or 20 and with a static SQL or an 'execute immediate' statement in a PL/SQL procedure, in a 9.2.0.8 database and a 10.2.0.1 database, respectively.

create or replace procedure  proc_p
is
obj_no number;
begin
for i in 1..100
loop
execute immediate 'select count(*)  from all_objects where object_id = :1 ' into obj_no using i;
dbms_output.put_line('i='  i  ' count='  obj_no);
end loop;
end;
/


create or replace procedure  proc_p2
is
obj_no number;
begin
for i in 1..100
loop
select count(*) into obj_no from all_objects where object_id = i;
dbms_output.put_line('i='  i  ' count='  obj_no);
end loop;
end;
/

alter session set sql_trace=true;

-- alter session set session_cached_cursors=0;
alter session set session_cached_cursors=20;

exec proc_p;
exec proc_p2;

alter session set sql_trace=false;
exit;

I used TKPROF to analyse the trace files. Below is the summary:

----------------------------------------------------------------------------
version  session_cached_cursors    SQL          # of parse    # of execution
----------------------------------------------------------------------------
9.2.0.8      0                     exe immed    100            100
9.2.0.8      0                     static       100            100
9.2.0.8      20                    exe immed    100            100
9.2.0.8      20                    static       1              100
10.2.0.1     0                     exe immed    100            100
10.2.0.1     0                     static       100            100
10.2.0.1     20                    exe immed    1              100
10.2.0.1     20                    static       1              100
----------------------------------------------------------------------------

It can be seen that with session_cached_cursors=0, no matter in which version and with what kind of SQL, Oracle needs to at least soft parse each SQL for each execution. So session_cached_cursors=0 effectively disable the ability of PL/SQL to cache cursors. With session_cached_cursors=20 (nozero essentially),in 9.2.0.8, for the static SQL it can be achieved "parase once, execute many times"; in 10.2.0.1, for both type of SQL, same effect of "parse once, execute many times" is observed. Whenever possible, we should use static SQL in PL/SQL code; if not, we should try to use 'execute immediate' with bind variables. It seems from 10g, PL/SQL engine can cache cursors from 'execute immediate' statement also.

I continued to experiment about the session_cached_cursors and the number of SQL that is repeatedly executed. The test code looks like:

create or replace procedure  proc_p2
is
obj_no number;
obj_name all_objects.object_name%type;
begin
for i in 1..100
loop
-- sql a
select count(*) into obj_no from all_objects where object_id = i; 
-- sql b
select max(object_name) into obj_name from all_objects where rownum <= i;
-- sql c 
select min(object_name) into obj_name from all_objects where rownum <= i;
end loop;
end;
/

alter session set sql_trace=true;

-- alter session set session_cached_cursors=1;
alter session set session_cached_cursors=3;

exec proc_p2;

alter session set sql_trace=false;
exit;

I tested with session_cached_cursors from 1 to 3 and number of SQL from 1-3 as well. Here is the summary:

-------------------------------------------------------------------------
version  session_cached_    # static SQL      # of parse    # of execution
cursors
-------------------------------------------------------------------------
10.2.0.1     1                      1                1             100
10.2.0.1     1                      2   (a)          100           100
                                        (b)          100           100
10.2.0.1     2                      2   (a)          100           100
10.2.0.1                                (b)          100           100
10.2.0.1     3                      2   (a)          1             100
                                        (b)          1             100
10.2.0.1     3                      3   (a)          100           100
                                        (b)          100           100
                                        (c)          100           100
-------------------------------------------------------------------------

In the case of session_cached_cursors=3 and three static SQLs, in the above test, I repeatedly executed all three SQLs together 100 times. How about repeatedly executing each SQL 100 times, for three SQLs total?. How about picking up the next SQL to be executed randomly? Can increase session_cached_cursors really help?. Below are the test codes and some results.

Test code of excuting each static SQL 100 times, for three SQLs total:

create or replace procedure  proc_p2
is
obj_no number;
obj_name all_objects.object_name%type;
begin
for i in 1..100
loop
-- sql a
select count(*) into obj_no from all_objects where object_id = i; 
end loop;     

for i in 1..100
loop  
-- sql b
select max(object_name) into obj_name from all_objects where rownum <= i;
end loop;

for i in 1..100
loop 
-- sql c 
select min(object_name) into obj_name from all_objects where rownum <= i;
end loop;
end;
/

alter session set sql_trace=true;

-- alter session set session_cached_cursors=1;
alter session set session_cached_cursors=3;

exec proc_p2;

alter session set sql_trace=false;
exit;

Test code of picking next static SQL to be executed randomly:

create or replace procedure  proc_p2
is
obj_no number;
obj_name all_objects.object_name%type;
r number;
begin
for i in 1..300
loop
r := dbms_random.value; 
if ( r <= 0.3333 ) then
select count(*) into obj_no from all_objects where object_id = i; 
elsif ( r >= 0.6667 ) then
select max(object_name) into obj_name from all_objects where rownum <= i;
else 
select min(object_name) into obj_name from all_objects where rownum <= i;
end if;
end loop;     
end;
/
show errors;

alter session set sql_trace=true;

-- alter session set session_cached_cursors=1;
alter session set session_cached_cursors=20;

exec proc_p2;

alter session set sql_trace=false;
-- exit;

Here is the result from TKPROF for above two cases:

------------------------------------------------------------------------
Order      session_cached_    # static SQL    # of parse  # of execution
cursors
------------------------------------------------------------------------
Each SQL
100 times      3                   3   (a)         1          100
                                       (b)         1          100
                                       (c)         1          100
Random         3                   3   (a)        33           90
                                       (b)        33          104
                                       (c)        33          106
Random         20                  3   (a)         1           96
                                       (b)         1          104
                                       (c)         1          100
------------------------------------------------------------------------

DBMS_STATS mistake

In 10g I know, by default, Oracle will decide if the histogram is needed for a column. If we don't want to collect histogram, we should specify: method_opt=>'FOR ALL COLUMNS SIZE 1'.

I find I have maken a mistake in a pre-production 10g database. I omitted the 'ALL' keyword in the job for gathering table stats. So what could happen?

Here is the demonstration by a test:

SQL>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>
SQL>drop table t;
drop table t
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>create table t as select * from dba_objects;

Table created.

SQL>
SQL>begin
2 dbms_stats.gather_table_stats(user,'T',
3 estimate_percent =>30,
4 cascade => TRUE,degree => 8,
5 method_opt => 'FOR COLUMNS SIZE 1');
6 end;
7 /

PL/SQL procedure successfully completed.

SQL>
SQL>select table_name, column_name, density,num_buckets, last_analyzed
2 from dba_tab_columns where owner=user and table_name='T';

TABLE_NAME COLUMN_NAME DENSITY NUM_BUCKETS LAST_ANAL
------------------------------ ------------------------------ ---------- ----------- ---------
T OWNER
T OBJECT_NAME
T SUBOBJECT_NAME
T OBJECT_ID
T DATA_OBJECT_ID
T OBJECT_TYPE
T CREATED
T LAST_DDL_TIME
T TIMESTAMP
T STATUS
T TEMPORARY
T GENERATED
T SECONDARY

13 rows selected.

SQL>
SQL>
SQL>begin
2 dbms_stats.gather_table_stats(user,'T',
3 estimate_percent =>30,
4 cascade => TRUE,degree => 8,
5 method_opt => 'FOR ALL COLUMNS SIZE 1');
6 end;
7 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>select table_name, column_name, density,num_buckets, last_analyzed
2 from dba_tab_columns where owner=user and table_name='T';

TABLE_NAME COLUMN_NAME DENSITY NUM_BUCKETS LAST_ANAL
------------------------------ ------------------------------ ---------- ----------- ---------
T OWNER .03125 1 30-OCT-09
T OBJECT_NAME .000035967 1 30-OCT-09
T SUBOBJECT_NAME .005847953 1 30-OCT-09
T OBJECT_ID .000019563 1 30-OCT-09
T DATA_OBJECT_ID .000208203 1 30-OCT-09
T OBJECT_TYPE .027777778 1 30-OCT-09
T CREATED .000393236 1 30-OCT-09
T LAST_DDL_TIME .000394789 1 30-OCT-09
T TIMESTAMP .000388651 1 30-OCT-09
T STATUS .5 1 30-OCT-09
T TEMPORARY .5 1 30-OCT-09
T GENERATED .5 1 30-OCT-09
T SECONDARY .5 1 30-OCT-09

13 rows selected.


We can see that no column stats will be gathered if that 'ALL' is missing, though no errors thrown out. This will cause bad execution plan in some situations.

DBA makes mistake. The question is how to reduce and avoid serious one. Some are due to carelessness and some are due to lack of experinece or knowledge. I should say the above mentioned mistake belongs to the first category. ( Though, I did not realize that all column stats would be missing) . Another example, the other day, I forgot to lift the restricted session mode after I was done a maitenance job. I do have this step in my checklist, the situation was that I was distracted by a production issue and have not come back to verify every steps in my checklist. Chris Foot has written an execent blog: The Art of Being a Successful DBA – Poka-Yoke and Paranoid DBA Best Practices, which I may want to read from time to time.

Truncate a parent table

I overlooked the operations required to truncate a parent table. I thought I only need to
truncate all of its child tables first, then truncate itself. The following test demonstrated
that constraints have to be taken care of in addition:


SQL> @fk_child
Enter value for 1: z9xxx94
Enter value for 2: p
----- Children of z9xxx94 p -------

Owner Table Name Constraint Name Ref Constraint Name Status
-------------- ----------------- ------------------ ------------------------ --------
Z9xxx94 C C_FK P_PK ENABLED



SQL> truncate table c;

Table truncated.

SQL> truncate table p;
truncate table p
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


We have to disable child table FK constraint first:


SQL> alter table c modify constraint C_FK disable;

Table altered.

SQL> truncate table p;

Table truncated.

SQL> alter table c modify constraint C_FK enable;

Table altered.

SQL> select count(*) from c;

COUNT(*)
----------
0

SQL> select count(*) from p;

COUNT(*)
----------
0

A SQL with a hidden error

This test case is built from a real life DBA task.

SQL> create table t as select rownum id, object_name from dba_objects where rownum <=10;

Table created.

SQL> create table t2 as select rownum id1, object_name from dba_objects where rownum <=5;

Table created.


Our intention was to do the following SQL with a subqeury:

SQL> select * from t where id in ( select id1 from t2);

ID OBJECT_NAME
---------- ------------------------------
1 TAB$
2 I_IND1
3 I_COBJ#
4 USER$
5 I_OBJ1


Instead, we executed the following SQL, which has a typo . However, it got executed without error:

SQL> select * from t where id in ( select id from t2);

ID OBJECT_NAME
---------- ------------------------------
1 TAB$
2 I_IND1
3 I_COBJ#
4 USER$
5 I_OBJ1
6 I_PROXY_ROLE_DATA$_2
7 C_FILE#_BLOCK#
8 C_OBJ#
9 BOOTSTRAP$
10 I_ICOL1

10 rows selected.

Isn't it better Oracle can throw error for this SQL?

Use Ref Cursor in Java and Close it

In one of our production databases, I observed from v$open_cursor that some sessions from weblogic application server could have about 2000 opened cursor count for a particular ref cursor. Usually the weblogic servers are recycled every night. I can observe the count increasing from 0 to about 2000 before recycle each day.

This database has been crash every week from ORA-600 [17074]. In the trace file, the SQL of the process that crashes the database is shown to be that ref cursor. So the cursor was thought to be contributed into the crash at least. (Oracle Support suggests that the bug in the server combined with the condition in our code caused the crash). However, recently the database crash problem has mysteriously disappeared without any sound reasons.

Development team claimed that they close that cursor in their Java code, but I really doubted it as the count is still increasing every day. I am afraid that it is a bomb that will explode some day again.

I have used Java several years ago when I did some course works in school. However, since then, I have never touched Java. I assigned myself a task to simulate the cursor count increasing symptom. It took me some time to figure out how. I even asked that in some forum, but did not get what I want. I eventually found this AskTom post and this site helpful.

The test is done in the Oracle sample schema HR and in a 10G database.

First of all, I created a function in a package that returns a ref cursor. Given a Department ID, the function returns a ref cursor pointing to a list of last names and first names of the employees belonging to that department.

The function looks like:

function sfGetEmpDept
( pDeptID in EMPLOYEES.DEPARTMENT_ID%type)
return csGetResultSet is

csGetEmp csGetResultSet;

begin
open csGetEmp for
SELECT e.last_name, e.first_name, e.department_id
FROM employees e
WHERE e.department_id = pDeptID
ORDER BY e.last_name;

return csGetEmp;
end sfGetEmpDept;

Secondly, I figured out how to use this ref cursor in a Java program through JDeveloper. In the Java code, I called a method getEmpDept 10 times.

em.showOpenCnt( conn, "*** Before anything" );
for(int i = 10;  i <=100; i = i + 10 ) {        
emlist = em.getEmpDept(i);
System.out.println( "Dept ID: " + i +
" Number of employee is " + emlist.size()  );
}
em.showOpenCnt( conn, "*** After everything" );

There is a line in the method getEmpDept which controls whether close the result set or not:

rs.close();

I measure the open curosr count before and after call getEmpDept. The results are as expected:

1. Uncomment out rs.close(), i.e. we close the ref cursor after use it.

Get the Number of emplyee in a Department
*** Before anything
1 opened cursors current
-----------------------
Dept ID: 10 Number of employee is 1
Dept ID: 20 Number of employee is 2
Dept ID: 30 Number of employee is 6
Dept ID: 40 Number of employee is 1
Dept ID: 50 Number of employee is 45
Dept ID: 60 Number of employee is 5
Dept ID: 70 Number of employee is 1
Dept ID: 80 Number of employee is 34
Dept ID: 90 Number of employee is 3
Dept ID: 100 Number of employee is 6
*** After everything
1 opened cursors current
-----------------------
Process exited with exit code 0.

2. Comment out rs.close(), i.e. we don't close the ref cursor in every call
*** Before anything
1 opened cursors current
-----------------------
Dept ID: 10 Number of employee is 1
Dept ID: 20 Number of employee is 2
Dept ID: 30 Number of employee is 6
Dept ID: 40 Number of employee is 1
Dept ID: 50 Number of employee is 45
Dept ID: 60 Number of employee is 5
Dept ID: 70 Number of employee is 1
Dept ID: 80 Number of employee is 34
Dept ID: 90 Number of employee is 3
Dept ID: 100 Number of employee is 6
*** After everything
11 opened cursors current
-----------------------
Process exited with exit code 0.

It is obvious that without rs.close, every time we call the Java method, we will increase the count by 1. With this simulation, I feel a little bit confident to question the development team again as to whether that cursor is really closed

Here can be found the code for the ref cursor and Java class.

[Ed. Sep 28, 2009 -]:
Find another AskTom thread that is very related to this topic

[Updated, Feb 22, 2010 -]:
We had a near-crash siutation by same ORA-600 17074 last Monday night, database was completely hung. Development team eventually identified the problem in a packaged PL/SQL procedure, which returns three ref cursors when called by Java code , however developers only needs to use one of them in the application, so they use it and close that one, but leaving the other two open. After fix, i.e. close the other two also. I observed the max open cursor per session is not even exceeding  100.

Rename and drop table columns

If we have a relatively big table and we want to modify a column from one type to another, i.e. number to varchar2(50), we may experience very long time. Note if we just change, for example, from varchar2(10) to varchar2(50), it will not be a problem, Oracle can finish in a split of second.

This was what we faced last night during the code release. The column was acutally added in the previous release, but for some reason it has not been used and is containing no data. The problem was that we would run out of the maintenance window if we were just waiting for it to complete (obsevered it would take 4-5 hours from v$session_longops view).

In this situation, the capability of renaming a column name comes to help. We renamed the column with a suffix '_old', and added a new column with the desired data type and name. It completed in seconds.

The syntax looks like:

SQL> alter table myschema.mytab rename column MYCOL to MYCOL_old;
SQL> alter table myschema.mytab add MYCOL varchar2(50);


It is noted in the Oracle online doc that when you rename a column, Oracle updates associated data dictionary tables to ensure that function-based indexes and check constraints remain valid.

So now comes to the question what is the best way to drop those *_old columns? - There are acutully 4 tables with 8 columns were addressed in this way.

Checking the online document again, there is a section in the Admin Guide (9i) that covers some options. I did not see we can do parallel drop of a column. I did notice there is an interesting option: checkpoint, which we may want to consider as our tables are very large. For example:

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;


The doc describes:

" This option causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space."

My Collection of DBA Scripts

In this post, I intend to list some scripts that I use often for daily DBA work and for trouble-shooting. I will update the list as necessary when new scripts come into play. I guess every DBA has his or her own handy scripts for work.

My List of DBA script:

event.sql

Show the overall picture of system activities by summarizing the session wait event count from v$session_wait. Used when doing health check or trouble-shooting. Check Tanel Poder's opinion about sampling v$session_wait.

get_ddl.sql

Generate DDL SQL statement for creating various objects such as tables, indexes, view etc. When want to know the constraints of a table or find the storage properties, I often find it pretty easy by obtaining the DDL of the table through this script. Also, when I find an index name looks like system-generated, I use this script to get the index DDL, which can tell me if the index is funtion-based and what kind of function it is.

login.sql

Used to give username@SID > as SQL* Plus prompte. Placed at the SQLPATH or working directory. Also give better AUTOTRACE explain plan output.

longops.sql

Display the progress of some long operations, such as table scan if it is long enough (> 6s). Some time it is useful to estimate how soon your SQL will finish.

perf_log_switch_history_daily.sql

Display number of log switch in every hour in a tabular format. Very useful to understand the workload distribution. From Jeff Hunter.
pxses.sql

I support a reporting database, on which parallel queries are frequently executed. This script displays the parallel execution server sessions, which is useful to verify if parallel execution happens and to check the degree of parallelism.

qlocks.sql

Display blocking and blocked session. An example of using this script can be found here.

sesevt.sql

Display a session information given wait event as input.

sessid.sql

Display a session information given SID as input.

sesusr.sql

Display a session information given USERNAME as input.

sqlsid.sql

Display the current running SQL of the session. Input session id (sid). Output the sql text, address, hash value (in 10g, can add sql_id)

sw.sql

Display wait event of a give session by SID. From Tanel Poder.

tabcols.sql

Display table column CBO statistics. Very useful when doing SQL tunning.

tabix.sql

List of the indexes of a table and show on which columns and in which order the indexes are.Very usefull when tune a SQL.

tbs.sql

Show a list of data files of a tablespace.

tf.sql

Show tablespace space usage. Used with tbs.sql when adding space or resize a data file of a tablespace.


xadr.sql

Obtain execution plan from the library cache. Good for 9i (The DB in my current working environment are mostly 9i). Need to input the address of the SQL (obtainable from v$sql or v$sqlarea) and child cursor number. I usually use sesusr.sql to find the SID of a user session given the user name, then use sqlsid.sql to find out the current running SQL, next using this script to find the execution plan.

Hit Bug 5888835 on a new Sun T5240 Server and somthing about dreadful latch

I copied a production database (9.2.0.8) to a new server at another data center. The database looked OK after open resetlogs; however, whenever I flushed the shared pool or shutdown the database, I would encounter the ORA-600 [504] error, i.e.

ORA-00600: internal error code, arguments: [504], [0x38006AC18], [160], [7], [shared pool], [2], [0], [0x38006AB28]


Per Oracle support, I hit a bug (Bug 5888835/5508574 ML428226.1 ), which applies to 9.2.0.8 to 10.2.0.3.

Based on ML 428226.1, there could be two causes of this bug:

(1) The latch directory size exceeds 255 when _kgl_latch_count > 31.
(2) Even when the _kgl_latch_count is equal to 0, if the cpu_count is >=32 the bug still applies.

This is due to the default value of _kgl_latch_count is calculated as next prime number after the value returned by CPU_COUNT. So, this bug could still apply if the cpu_count=32 as the _kgl_latch_count would be calculated to the next prime number that would be 37.

So this is about the dreadful latch, which remind me of an unpleasant interview experience, in which I was blind when was asked about this topic.

I did some searching and reading to understand further, below is a Q&A section about some basics:

Q: What is _kgl_latch_count?
A: On this website ( http://www.ixora.com.au/q+a/library.htm), I found:

It sets the number of child library cache latches. The default is the least prime number greater than or equal to cpu_count. The maximum is 67. It can safely be increased to combat library cache latch contention, as long as you stick to prime numbers. However it is only effective if the activity across the existing child library cache latches is evenly distributed as shown in V$LATCH_CHILDREN


Q: What is parent latch and child latch?
A: A latch can be defined as a latch set. A latch set has a parent latch and several child latches. Parent latches are allocated statistically. Child latches are allocated dynamically. Both parent and child latches share the same latch name. The latches in a set are "chained" together to facilitate statistics gathering. Apparently, library cache latch is defined as a latch set.

Q: What is library cache ?
A: The primary function of the library cache is to store shared cursors together with their parse trees and execution plans among other objects. The literary cache is structured as a hash table that is accessible through an array of hash buckets. Each hash bucket gives access to a linked list of library object handles. Library cache objects are made of several independent memory heaps and are accessed through the object handles.

Q: What is library cache latch?
A: When a library object is accessed, first, the lock is required on the handle, then the necessary object heaps are pinned. A library cache latch is acquired before the lock request and is released after it. Multiple library cache latch exist, and each one protects a range of hash buckets and the latch covers all associated heaps.

Some 'good' news about latch are also reached to me as the result of this searching and reading, the latch in Oracle is or will be gone! See:

Library cache latches gone in Oracle 11g

Library cache latch has gone?


OK, enough for latch for now. Hopefully, I could say something about latch if I would be interviewed again about this topic. Let's return to the bug, it can be fixed by:

1. set CPU_COUNT <=60.
I tested in this case, no ORA-600 error when shutdown or flush shared pool. Obviously we have 128 CPUs or Oracle thinks so, this setting will not take advantage of it.

2. Upgrade to at least 10.2.0.4

3. Apply the patch.
This was what we finally did. I tested that the patch worked as advertised.


As side notes, the following query can be used to check the value of _kgl_latch_count:

select a.ksppinm aa, b.ksppstvl bb
from x$ksppi a, x$ksppsv b
where a.indx=b.indx
and a.ksppinm like '%latch%count%';

If asked why we have 128 CPUs, I will refer to http://www.sun.com/servers/coolthreads/t5240/. It says this server can up to 128 compute threads. Oracle sees it as 128 CPUs.

LIKE does not like bind variable

Today, I have learned this in a hard way. After the release, a query was time-out frequently. I have spent quite a lot of time to figure out what's going on. When checking the execution plan from the memory, there were always two plans, one good with nested loop join and one bad with hash join. Trying to add hint to force nested loop join, however, it did not work.

The problem is that the query has a predicate such as A.ORDER_ID LIKE 'ICN000104775%', but it is fired from application server and is using bind variable.

When checking why there were different versions of execution plan, I found out the following from the v$sql_shared_cursor in one occasion.

ADDRESS          KGLHDPAR         U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F L
---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
000000054EF09208 0000000516791558 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
0000000532A02020 0000000516791558 N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N
00000005327D3808 0000000516791558 N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N

The forth field from the right is called USER_BIND_PEEK_MISMATCH

The following test demonstrated that LIKE operator is not getting along with bind variable:

1. In the case of literal, execution plan looks perfect

SQL> SELECT
2     a.ACCOUNT_ID
3       ,a.IO_ID
4       ,a.ORDER_ID
5       ,a.IS_MASTER
6       ,a.IS_PQ
7     FROM
8        TAB_A a,
9        TAB_B b
10     WHERE
11        a.ORDER_ID LIKE  'ISPT143730263%'
12     AND a.io_id = b.io_id
13     AND (a.IS_VALID='Y' OR (a.IS_VALID='N' AND b.ORDER_STATUS_ID=6));


Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=45)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TAB_B' (Cost=3 Card=1 Bytes=10)
2    1     NESTED LOOPS (Cost=7 Card=1 Bytes=45)
3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TAB_A' (Cost=4 Card=1 Bytes=35)
4    3         INDEX (RANGE SCAN) OF 'TAB_A_IX5' (UNIQUE) (Cost=3 Card=1)
5    2       INDEX (RANGE SCAN) OF 'TAB_B_IX1' (NON-UNIQUE) (Cost=2 Card=1)

2. In the case of bind variable, totally wrong cardinality:

SQL>
SQL>
SQL> @s1
SQL> variable v varchar2(16);
SQL> exec :v :='ISPT143730263%';

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT
2     a.ACCOUNT_ID
3       ,a.IO_ID
4       ,a.ORDER_ID
5       ,a.IS_MASTER
6       ,a.IS_PQ
7     FROM
8        TAB_A a,
9        TAB_B b
10     WHERE
11        a.ORDER_ID LIKE  :v
12       AND a.io_id = b.io_id
13       AND (a.IS_VALID='Y' OR (a.IS_VALID='N' AND b.ORDER_STATUS_ID=6));


Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=227422 Card=833902 Bytes=37525590)
1    0   HASH JOIN (Cost=227422 Card=833902 Bytes=37525590)
2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TAB_A' (Cost=45900 Card=1516186 Bytes=53066510)
3    2       INDEX (RANGE SCAN) OF 'TAB_A_IX5' (UNIQUE) (Cost=1064 Card=272913)
4    1     TABLE ACCESS (FULL) OF 'TAB_B' (Cost=170305 Card=30317333 Bytes=303173330)

We have a function index based on substr(order_id, 1, 13), so the fix is to use substr(order_id, 1,13)=:v as the predicate.

Ed. Sep 23, 2009 -

I come accross a paper "A LOOK UNDER THE HOOD OF CBO: THE 10053 EVENT " by Wolfgang Breitling . I have learned the filter factor (selectivity) of LIKE operator is as follows:

predicate               filter factor
-------------           -------------
c1 like  value          c1.density
c1 like :b1             0.05 OR c1.density

In the case of like with bind variable, the filter factor depends on the hidden parameter _LIKE_WITH_BIND_AS_EQUALITY. When it is false (the default), 0.05 is chosen, otherwise c1.density. This is why I saw unrealistic high cardinality in my case above.

Update: Feb 20, 2011

Today, caught by same issue again in the same database (only now we are in 10g not 9i) . Last night during code release, a query was changed:

--------------
before release
--------------

SELECT 
a.PSEFS_ID
, b.PSEFS_STATUS_ID
, TO_CHAR(b.PSEFS_CRFBTF_DATE, 'MM/DD/YYYY HH24:MI:SS') AS "PSEFS_CRFBTF_DATE"
, b.PRESALE_ACTIVITY_ID
, b.PSEFS_TYPE_ID
, b.PSEFS_SUB_TYPE_ID
, b.WORKWITH
, b.WORKWITH_GROUP_ID 
FROM PSEFS_MBSTFR a
   , PSEFS_DFTBJLS b 
WHERE substr(a.PSEFS_ID,1,13) = substr(:b,1,13) 
AND a.INT_SUPP_PSEFS_ID = 0 
AND a.INT_PSEFS_ID = b.INT_PSEFS_ID;

---------------
After release
--------------

SELECT 
  a.PSEFS_ID
, b.PSEFS_STATUS_ID
, TO_CHAR(b.PSEFS_CRFBTF_DATE, 'MM/DD/YYYY HH24:MI:SS') AS "PSEFS_CRFBTF_DATE"
, b.PRESALE_ACTIVITY_ID
, b.PSEFS_TYPE_ID
, b.PSEFS_SUB_TYPE_ID
, b.WORKWITH
, b.WORKWITH_GROUP_ID  
FROM PSEFS_MBSTFR a
   , PSEFS_DFTBJLS b  
WHERE a.PSEFS_ID like substr(:b,1,13)||'%' 
 AND a.INT_SUPP_PSEFS_ID = 0
 AND a.INT_PSEFS_ID = b.INT_PSEFS_ID
 ORDER BY a.INT_BASE_PSEFS_ID,a.IS_PQ;

So the predicate changed from : substr(a.PSEFS_ID,1,13) = substr(:b,1,13) to: a.PSEFS_ID like substr(:b,1,13)||'%'    Guess what? Now the new query uses hash join and time-out. The 0.05 filter factor makes CBO think from the predicate (a.PSEFS_ID like substr(:b,1,13)||'%' ) there will come out 580K rows, thus hash join is the best choice :-(. Fortunately, we are in 10g now, so I have the choice to use sql profile to freeze the good plan without the code change. But the predicate change in this case makes no sense, having asked the dev team to revert back in the next available maintainence window.

Misconception about direct-path insert

I had a misconception about direct-path insert. I thought it would generate minimized redo by design. Actually, direct-patch insert can be performed with both logging (by default) and nologging mode. Oracle doc is very clear about this.

Now it comes to how to perform direct-path insert with nologging. I thought it would work by just adding 'nologging' after the table name in the SQL statement, similar to the case of CTAS. It turned out to be not true.

Here was my test to insert 1 million rows to a table and measure the redo generated in each case:

Case 1: Conventional insert show redo size about 70M

DB9i> @@mystat "redo size"
DB9i> set echo off

NAME                                VALUE
------------------------------ ----------
redo size                           63004

Elapsed: 00:00:03.06
DB9i>
DB9i> insert into t select * from big_table a where rownum <= 1000000;

1000000 rows created.

Elapsed: 00:00:57.00
DB9i> commit;

Commit complete.

Elapsed: 00:00:05.43
DB9i>
DB9i> @@mystat2
DB9i> set echo off

NAME                                    V DIFF
------------------------------ ---------- ------------------
redo size                        71015272       70,952,268

Case 2: Direct-path insert generate same amount of redo

DB9i> @@mystat "redo size"
DB9i> set echo off

NAME                                VALUE
------------------------------ ----------
redo size                       142548540

Elapsed: 00:00:06.04
DB9i>
DB9i> insert /*+append */ into t select * from big_table a where rownum <= 1000000;

1000000 rows created.

Elapsed: 00:00:17.10
DB9i> commit;

Commit complete.

Elapsed: 00:00:06.37
DB9i>
DB9i> @@mystat2
DB9i> set echo off

NAME                                    V DIFF
------------------------------ ---------- ------------------
redo size                       213379400       70,830,860

Note: By adding nologging keyword as follows, nothing changes, same amount of redo:

insert /*+append */ into t nologging select * from big_table a where rownum <= 1000000;


Case 3. To enable direct-path insert with nologging, we have to specify nologging at table level:

DB9i> truncate table t;

Table truncated.

Elapsed: 00:00:08.40
DB9i>
DB9i> alter table t nologging;

Table altered.

Elapsed: 00:00:06.29
DB9i>
DB9i> @@mystat "redo size"
DB9i> set echo off

NAME                                VALUE
------------------------------ ----------
redo size                       284863656

Elapsed: 00:00:07.37
DB9i>
DB9i> insert /*+append */ into t select * from big_table a where rownum <= 1000000;

1000000 rows created.

Elapsed: 00:00:08.17
DB9i> commit;

Commit complete.

Elapsed: 00:00:08.48
DB9i>
DB9i> @@mystat2
DB9i> set echo off

NAME                                    V DIFF
------------------------------ ---------- ------------------
redo size                       285145076          281,420

Note: Bear in mind the recovery scenario, this operation is not recoverable. After performing nologging operation, generally we should do a backup.

I should mention that the above results were obtained in a 9i database operated in ARCHIVELOG mode. If in a NOARCHIVELOG mode database, conventional inserts still generate same amount of redo. However, by just adding the hint 'append', we can see redo size dropped to around 280K. After all we do not expect a complete recovery for a NOARCHIVELOG database. So be careful about in which kind of databases the tests are being done.

An Experiment of Partitioning

I was experimenting about a special partitioning task, which involves a 16 million row table. The goal is to create two partitions based on its primary key. There is a driver table containing
about 10-20% primary key values that determines which rows should go to the smaller partition.

First of all, I need to add a new column as a paritition key.

SQL> alter table user_a.target_tab add (s char(1));

Secondly, I created a partitioned destination table structure with exact same indexes and constraints as the target table.

create table user_a.target_tab_p
partition by list (s)
(
partition p1 values('Y'),
partition p2 values(default)
)
as
select * from user_a.target_tab
where 1=0;


Then, I performed exchanging partition,it took about 30 min, with the following command:

alter table user_a.target_tab_p
exchange partition p2
with table user_a.target_tab
without validation
update global index
parallel 8 ;


Next, I drop the original table, rename the partitioned table, index and constraint:

SQL> drop table user_a.target_tab;
SQL> alter table user_a_target_tab_p rename to target_tab;

In order to be able to update the partition key, I have to enable row movement:

SQL> alter table user_a.target_tab enable row movement;

I used the following command to update the partition key, it took about 20 min:

update user_a.target_tab set s='Y'
where id in (
SELECT id FROM driver_tab;
);

After analyze, the following query verified the successful partition:

SELECT table_owner, table_name, partition_name, num_rows
FROM dba_tab_partitions
WHERE table_name = 'TARGET_TAB'
and table_owner='USER_A'
/

TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS
------------------ ----------------- -------------------- ----------
USER_A TARGET_TAB P1 1983143
USER_A TARGET_TAB P2 14124980


The whole process took less than 1 hour.


Reference: Partitioning an Existing Table using EXCHANGE PARTITION

A note about VCS in our environment

I was asked by a fellow DBA:
1. how to findout whether the server is in a cluster or not
2. If so how to find out what are the servers in the cluster

His question is general, however in our environment I know we use VCS.

I guess we can do ps -ef | grep had at least, if we see the had deamon is running, we have a good reason to believe the server is in the cluster.

e.g.

$ ps -ef | grep had

root 23555 1 0 Jul 12 console 126:03 /opt/VRTSvcs/bin/had
root 23557 1 0 Jul 12 console 0:00 /opt/VRTSvcs/bin/hashadow

There is a VCS configuration file called /etc/VRTSvcs/conf/config/main.cf. The file contains the information that defines the cluster and its systems. Unfortunately DBA does not have permission to read it. There is a command: hastatus -summary , which should be give us the summary information about the cluster. Again, we don't have permission to run it. However I found we had a log file under the VCS log directory, and it is readable by all, the contents are as follows:

ourservpd1:/var/VRTSvcs/log [ourdb] $ cat hastatus.summary

-- SYSTEM STATE
-- System               State                Frozen            

A  ourservpd1             RUNNING              0                  
A  ourservpd2             RUNNING              0                  

-- GROUP STATE
-- Group           System               Probed     AutoDisabled    State        

B  online_failover ourservpd1             Y          N               ONLINE       
B  online_failover ourservpd2             Y          N               OFFLINE      
B  report_static   ourservpd2             Y          N               ONLINE       

It is clear from this log about what servers are involved in the cluster.

NetApp DataONTAP Simulator

I am attending a 5-days training class this week about a NetApp course: DataONTAP 7.3 Fundamentals. I have never had hands-on on any storgae system before. The first good thing I have learned so for is there is a Simulator which allows us to practise many things about the storage system through NetApp DataONTAP.

You can download the Simulator from NetApp NOW site after login. To install this Simulator, you need Linux and the Simulator package itself. There is a step by step guide from NetApp , which describes how to install this Simulator on VMWare and Ubuntn Linux. However more than often like any other guides, it is slightly out-dated (dated Jan, 2007 so not a surprise), but still I followed it and got my simulator set up with a few intelligent guess.

My simulation environment is Windows XP, VMWare Server 2.0, Ubuntn Linux 9.04 Desktop edition. Here is the screenshot from the sysconfig -r command:

mysim> sysconfig -r
Aggregate aggr1 (online, raid_dp) (zoned checksums)
  Plex /aggr1/plex0 (online, normal, active)
    RAID group /aggr1/plex0/rg0 (normal)

      RAID Disk Device  HA  SHELF BAY CHAN Pool Type  RPM  Used (MB/blks)    Phys (MB/blks)
      --------- ------  ------------- ---- ---- ---- ----- --------------    --------------
      dparity   v4.19   v4    1   3   FC:B   -  FCAL  N/A  70/144384         77/158848
      parity    v4.20   v4    1   4   FC:B   -  FCAL  N/A  70/144384         77/158848
      data      v4.21   v4    1   5   FC:B   -  FCAL  N/A  70/144384         77/158848

Aggregate aggr0 (online, raid0) (zoned checksums)
  Plex /aggr0/plex0 (online, normal, active)
    RAID group /aggr0/plex0/rg0 (normal)

      RAID Disk Device  HA  SHELF BAY CHAN Pool Type  RPM  Used (MB/blks)    Phys (MB/blks)
      --------- ------  ------------- ---- ---- ---- ----- --------------    --------------
      data      v4.16   v4    1   0   FC:B   -  FCAL  N/A  120/246784        127/261248
      data      v4.17   v4    1   1   FC:B   -  FCAL  N/A  120/246784        127/261248
      data      v4.18   v4    1   2   FC:B   -  FCAL  N/A  120/246784        127/261248


Spare disks

RAID Disk       Device  HA  SHELF BAY CHAN Pool Type  RPM  Used (MB/blks)    Phys (MB/blks)
---------       ------  ------------- ---- ---- ---- ----- --------------    --------------
Spare disks for zoned checksum traditional volumes or aggregates only
spare           v4.22   v4    1   6   FC:B   -  FCAL  N/A  70/144384         77/158848
spare           v4.24   v4    1   8   FC:B   -  FCAL  N/A  70/144384         77/158848
spare           v4.25   v4    1   9   FC:B   -  FCAL  N/A  70/144384         77/158848
spare           v4.26   v4    1   10  FC:B   -  FCAL  N/A  70/144384         77/158848

I will see if I can get an Oracle database use the storage in the Simulator

DELETE from a view

Sometime, we need to do "scrub" against a big table, for example,delete (or update) some rows based on the condition in a second small table. We can probably perform this task through "delete from a view" if the second table has primary key constraint. The following test demonstrated the importance of table order if we do "delete from a view". We need to put the target big table first in the FROM list, otherwise we may end up deleting the small table.

SQL> create table t1
  2  as
  3  select rownum id,
  4      rpad('*', 50) pad
  5  from all_objects
  6  where rownum <=100;

Table created.

SQL>
SQL>
SQL> create table t2
  2  as
  3  select rownum*3 id
  4  from all_objects
  5  where rownum <=10;

Table created.

SQL>
SQL> alter table t1 add constraint t1_pk primary key(id);

Table altered.

SQL> alter table t2 add constraint t2_pk primary key(id);

Table altered.

SQL>
SQL> execute dbms_stats.gather_table_stats(user,'t1');

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_table_stats(user,'t2');

PL/SQL procedure successfully completed.

SQL>
SQL> -- Order: t1, t2
SQL> delete from plan_table;

3 rows deleted.

SQL> explain plan for
  2  delete from
  3  ( select t1.id
  4    from t1,
  5         t2
  6    where t2.id = t1.id
  7  );

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------
Plan hash value: 2755785190

-----------------------------------------------------------------------
 Id   Operation            Name   Rows   Bytes  Cost (%CPU) Time  
-----------------------------------------------------------------------
   0  DELETE STATEMENT               10     60      1   (0) 00:00:01
   1   DELETE              T1                                     
   2    NESTED LOOPS                 10     60      1   (0) 00:00:01
   3     INDEX FULL SCAN   T2_PK     10     30      1   (0) 00:00:01
*  4     INDEX UNIQUE SCAN T1_PK      1      3      0   (0) 00:00:01
------------------------------------------------------------------------

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

   4 - access("T2"."ID"="T1"."ID")

16 rows selected.

SQL>
SQL> -- Order: t2, t1
SQL>
SQL> delete from plan_table;

5 rows deleted.

SQL> explain plan for
  2  delete from
  3  ( select t1.id
  4    from t2,
  5         t1
  6    where t2.id = t1.id
  7  );

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
Plan hash value: 525412351

-----------------------------------------------------------------------
 Id   Operation            Name   Rows   Bytes  Cost (%CPU) Time  
-----------------------------------------------------------------------
   0  DELETE STATEMENT               10     60      1   (0) 00:00:01
   1   DELETE              T2                                     
   2    NESTED LOOPS                 10     60      1   (0) 00:00:01
   3     INDEX FULL SCAN   T2_PK     10     30      1   (0) 00:00:01
*  4     INDEX UNIQUE SCAN T1_PK      1      3      0   (0) 00:00:01
-------------------------------------------------------------------------

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

   4 - access("T2"."ID"="T1"."ID")

16 rows selected.

SQL> spool off

Process state dump needed to trouble shoot an ORA-600

Per Oracle Support, we need to generate a process state dump when an ORA-600 [17074] error occurs. I put together the commands in a small script called pdump.sql to do this task. This error has already crashed our production database several times.

The plan is once the ORA-600 hits, find the first trace file that causes it. At the top of the file , we should see the 'LOCK OWNERS' section, looks like:

LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
5311a3900 510a1c370 510a1c370 1 N PNS/[08]
531372200 512a23348 512a23348 1 N [00]
5233801c0 513a18980 513a18980 1 N PNS/[08]
5311c3440 51b148020 51b148020 1 N PNS/[08]
531280c00 50fa23be8 50fa23be8 1 N PNS/[08]
:
53135ef00 50ea46a08 50ea46a08 1 N PNS/[08]

We note down the session address column(third one).

Then we login as sysdba, to do:

SQL> @pdump 510a1c370
SQL> @pdump 512a23348

so on and so forth for the first few.

Here is a test output for the pdump script:

sys@TESTDB> @pdump 3b4333248
sys@TESTDB> select p.pid
2 from v$session s, v$process p
3 where s.paddr = p.addr
4 and s.saddr in (upper(lpad('&1',16,'0')))
5 ;
old 4: and s.saddr in (upper(lpad('&1',16,'0')))
new 4: and s.saddr in (upper(lpad('3b4333248',16,'0')))

PID
----------
70

sys@TESTDB>
sys@TESTDB> oradebug setorapid &OPID
Unix process pid: 13818, image: oracle@testserv2(TNS V1-V3)
sys@TESTDB> oradebug unlimit
Statement processed.
sys@TESTDB> oradebug dump processstate 266
Statement processed.
sys@TESTDB> oradebug tracefile_name
/apps/opt/oracle/admin/testdb/udump/testdb_ora_13818.trc
sys@TESTDB> set echo off

DELETE statement executed even using set autotrace traceonly explain

Normally, when you issue 'set autotrace traceonly explain', then execute the SQL statement in order to get its execution plan, the SQL won't get executed actually. However, I realized that this is not true for 'DELETE' statement. Below is a test case:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>
SQL> drop table t;

Table dropped.

SQL>
SQL> create table t
  2  as
  3  select rownum id,
  4    a.*
  5  from all_objects a where rownum <= 1000;

Table created.

SQL>
SQL> create index  t_ix on t(id);

Index created.

SQL>
SQL> execute dbms_stats.gather_table_stats(user,'t', cascade=>true);

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
      1000

SQL>
SQL>
SQL> set autotrace traceonly explain
SQL> delete from t where  id >=500;

501 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 2466302700

--------------------------------------------------------------------------
 Id   Operation          Name  Rows   Bytes  Cost (%CPU) Time   
--------------------------------------------------------------------------
   0  DELETE STATEMENT           502   2008      3   (0) 00:00:01
   1   DELETE            T                                      
*  2    INDEX RANGE SCAN T_IX    502   2008      3   (0) 00:00:01
--------------------------------------------------------------------------

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

   2 - access("ID">=500)

SQL>
SQL> set autotrace off
SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
       499

I also tested "explain plan for". We see the delete statement is not actually executed in this case.

SQL>
SQL> explain plan for
  2  delete from t;

Explained.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 2034385699

---------------------------------------------------------------------------
 Id   Operation           Name  Rows   Bytes  Cost (%CPU) Time   
---------------------------------------------------------------------------
   0  DELETE STATEMENT           1000   4000      5   (0) 00:00:01
   1   DELETE             T                                      
   2    TABLE ACCESS FULL T      1000   4000      5   (0) 00:00:01
---------------------------------------------------------------------------

9 rows selected.

SQL> select count(*) from t;

  COUNT(*)
----------
       499

Generate script to transfer data files with compression through SSH

Recently, I have used the following SQL to generate scripts for transferring data files in order to clone an open database. Some explanation about a similar task can be seen in this previous post

select case when mod(rn,10) = 0
then
'ssh srcserv ' || '''cat ' || file_name || 
' | gzip -c '' | gunzip -c > ' || file_name || ' &'
|| chr(10) ||
'wait ' || chr(10) ||
'echo "`date` rn: ' || rn || '" >> $LOGFILE'|| chr(10)
else
'ssh srcserv ' || '''cat ' || file_name || 
' | gzip -c '' | gunzip -c > ' || file_name || ' &'
end
from
( 
select file_name, row_number() over ( order by bytes desc ) rn 
from dba_data_files
where tablespace_name=upper('&tabspacename') 
)
/

pfile and spfile - more to learn

I thought I was quite knowlegable about spfile/pfile, especially after reading Tom Kyte's book: Expert Oracle Database Architecure Chapter 3 Files. However, weeks ago, I saw a pfile that has only one line for one of our production databases. This puzzled me for a while. Today I decided to have a test.

The line looks similiar to:
spfile=/path/to/spfile/spfileSID.ora

I was told our VCS cluster software uses pfile to start up the db. I can not confirm if this is necessary. But most likely this is why the DBA creates such a pfile. And I don't know we can do this, this is the point - one can always learn something new about Oracle.

In my test environment (10.2.0), I have a spfile under this location:

vmlinux1:/app/oracle/product/10.2.0/db_1/dbs/temp [DBT10G] $ ls
spfileDBT10G.ora

I have a pfile at stardand default location and it has only one line to tell oracle where the spfile is.

vmlinux1:/app/oracle/product/10.2.0/db_1/dbs [DBT10G] $ cat initDBT10G.ora
spfile='/app/oracle/product/10.2.0/db_1/dbs/temp/spfileDBT10G.ora'


Now I start the DB with pfile:

vmlinux1:/app/oracle/product/10.2.0/db_1/dbs [DBT10G] $ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 21 14:40:49 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

idle> conn / as sysdba
Connected to an idle instance.
idle> startup pfile='/app/oracle/product/10.2.0/db_1/dbs/initDBT10G.ora';
ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size 1218920 bytes
Variable Size 92276376 bytes
Database Buffers 171966464 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

When checking the parameter spfile, we can see the value is the full name of the spfile. So It looks like Oracle knows that it is started up with a spfile actually.

idle> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /app/oracle/product/10.2.0/db_1/dbs/temp/spfileDBT10G.ora

Having troubles in creating a database link

Today, I have spent quite some time to try to get a db link from a 10g db (DBWRKEV1) to a 9i db (DBWRK920) work.

At DBWRKEV1, we can see the global_name has a funny suffix.

z9xxx94@DBWRKEV1> select * from global_name;

GLOBAL_NAME
---------------------------------------------------------
DBWRKEV1.REGRESS.RDBMS.DEV.US.ORACLE.COM


Based on this post, I tried to remove it by this way:


SQL> show user;
USER is "SYS"
SQL> update GLOBAL_NAME SET GLOBAL_NAME='DBWRKEV1';

1 row updated.

SQL> commit;

Commit complete.

z9xxx94@DBWRKEV1> select * from global_name;

GLOBAL_NAME
---------------------------------------------
DBWRKEV1

Then, I tried to create the db link again:

z9xxx94@DBWRKEV1> create database link dbwrk920 connect to z9xxx94 identified by vxxx using 'dbwrk920';

Database link created.

z9xxx94@DBWRKEV1> select * from global_name@dbwrk920;
select * from global_name@dbwrk920
*
ERROR at line 1:
ORA-02085: database link DBWRK920.REGRESS.RDBMS.DEV.US.ORACLE.COM connects to DBWRK920


Still, I have not succeeded. It should be noted that the initialization parameter global_names is TRUE in the DBWRKEV1(10g)

Finally, I was able to use the db link through following approach:


At DBWRK920


z9xxx94@DBWRK920> select * from global_name;

GLOBAL_NAME
---------------------------------------------------------------------------
DBWRK920

z9xxx94@DBWRK920> alter database rename global_name to dbwrk920.world;

Database altered.

z9xxx94@DBWRK920> select * from global_name;

GLOBAL_NAME
---------------------------------------------------------------------------
DBWRK920.WORLD


At DBWRKEV1


z9xxx94@DBWRKEV1> create database link dbwrk920.world connect to z9xxx94 identified by vxxx using 'dbwrk920';

Database link created.


z9xxx94@DBWRKEV1>
z9xxx94@DBWRKEV1> select * from global_name@dbwrk920.world;

GLOBAL_NAME
--------------------------------------------------------------
DBWRK920.WORLD

Explore SQL Profiles - Part II

Explore SQL Profiles - Part I can be found here.

More than often, it turns out my task is to explore what other people have already explored. Thanks google, more than often, I can find the right experts who are willing to share their knowledge. Below are the two posts about SQL Profiles that makes this happen.

Plan stability in 10g - using existing cursors to create Stored Outlines and SQL profiles by Randolf Geist

SQL Profiles by Kerry Osborne

What I am going to describe is my personal experience about exploring SQL Profiles in order to deal with a problem SQL in the production environment.

The following query in one of our production 10g database has sub-optimal execution plan:

SELECT COUNT(SESSION_KEY) FROM RMS WHERE cust_btn='8136777776' AND
SESSION_KEY like 'BillingSummary%' AND LAST_ACCESS_DATE >= (SYSDATE-1)There are hundreds of such kind of queries that are only different in the literals are runing at any time during the day. Yeah, they do not use bind variables. This is not my concern in my position now. I rememberred once they said for some reason, they can not adopt bind variables. The table is truncated every night, and it then grows from 0 to about 200K rows prior to being truncated. There are an index on SESSION_KEY column and another index on cust_btn column. The bad plan uses the index on SESSION_KEY, while the good plan uses the index on cust_btn.

I have tried to use DBMS_SQLTUNE package to generate a SQL Profile to accept automatically, however, Oracle have not given any recommenations (first time due to stale stats, after gathering stats, no lucky either). This left me the choice to create it manually. My steps are as follows:

(1) Execute the sql with hint on good index and display the outline

SQL> SELECT /*+ index (RMS,IX_CUST_BTN_RMAS ) */
  2          COUNT(SESSION_KEY) FROM RMS WHERE cust_btn='8136777776' AND
  3      SESSION_KEY like 'BillingSummary%' AND LAST_ACCESS_DATE >= (SYSDATE-1);

COUNT(SESSION_KEY)
------------------
                 1

Elapsed: 00:00:00.00
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'OUTLINE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  b5mp8fdcxdxav, child number 0
-------------------------------------
SELECT /*+ index (RMS,IX_CUST_BTN_RMAS ) */         COUNT(SESSION_KEY) FROM
RMS WHERE cust_btn='8136777776' AND     SESSION_KEY like 'BillingSummary%'
AND LAST_ACCESS_DATE >= (SYSDATE-1)

Plan hash value: 2513418175

----------------------------------------------------------------------------------------
 Id   Operation                     Name               Rows   Bytes  Cost (%CPU) Time
----------------------------------------------------------------------------------------
   0  SELECT STATEMENT                                                   7 (100)     
   1   SORT AGGREGATE                                      1     69                  
*  2    TABLE ACCESS BY INDEX ROWID RMS      1     69      7   (0) 00:00:01
*  3     INDEX RANGE SCAN           IX_CUST_BTN_RMAS       7             1   (0) 00:00:01
-----------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "RMS"@"SEL$1" ("RMS"."CUST_BTN"))
      END_OUTLINE_DATA
  */

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

   2 - filter(("SESSION_KEY" LIKE 'BillingSummary%' AND "LAST_ACCESS_DATE">=SYSDATE@!-1))
   3 - access("CUST_BTN"='8136777776')

(2) Create a SQL Profile manually.

Note: using the outline info otained in (1) to specify the profile attribute.

begin
dbms_sqltune.import_sql_profile(
   name => 'profile_myacc_2',
   description => 'SQL profile created manually',
   category => 'TEST',
   sql_text => q'[SELECT COUNT(SESSION_KEY) FROM RMS WHERE cust_btn='8136777776' AND SESSION_KEY like 'BillingSummary%' AND LAST_ACCESS_DATE >= (SYSDATE-1)]',
   profile => sqlprof_attr(
        'IGNORE_OPTIM_EMBEDDED_HINTS',
        'ALL_ROWS',
        'OUTLINE_LEAF(@"SEL$1")',
        'INDEX_RS_ASC(@"SEL$1" "RMS"@"SEL$1" ("RMS"."CUST_BTN"))'
   ),
   replace => FALSE,
   force_match => TRUE
);
end;
/

(3) Verify the SQL profile can be used

a. Without using the profile, consistent gets= 17999

SQL> set autotrace on
SQL> SELECT COUNT(SESSION_KEY) FROM RMS WHERE cust_btn='6095884091' AND
 SESSION_KEY like 'LinkSummary%' AND LAST_ACCESS_DATE >= (SYSDATE-1)  2
  3  ;

COUNT(SESSION_KEY)
------------------
                 1


Execution Plan
----------------------------------------------------------
Plan hash value: 769759756

---------------------------------------------------------------------------------
 Id   Operation                     Name               Rows   Bytes  Cost (%CPU) Time 
----------------------------------------------------------------------------------------
   0  SELECT STATEMENT                                     1     69      4   (0) 00:00:01
   1   SORT AGGREGATE                                      1     69                   
*  2    TABLE ACCESS BY INDEX ROWID RMS                    1     69      4   (0) 00:00:01
*  3     INDEX RANGE SCAN           IX_MYACC_SUM_KEY       1             3   (0) 00:00:01
------------------------------------------------------------------------------------------

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

   2 - filter("CUST_BTN"='6095884091' AND "LAST_ACCESS_DATE">=SYSDATE@!-1)
   3 - access("SESSION_KEY" LIKE 'LinkSummary%')
       filter("SESSION_KEY" LIKE 'LinkSummary%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      17990  consistent gets
          0  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        488  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

b. With the profile, consistent gets= 7

SQL> alter session set sqltune_category=test;

Session altered.

SQL> SELECT COUNT(SESSION_KEY) FROM RMS WHERE cust_btn='6095884091' AND
 SESSION_KEY like 'LinkSummary%' AND LAST_ACCESS_DATE >= (SYSDATE-1)  2  ;

COUNT(SESSION_KEY)
------------------
                 1


Execution Plan
----------------------------------------------------------
Plan hash value: 2513418175

--------------------------------------------------------------------------------
 Id   Operation                     Name               Rows   Bytes  Cost (%CPU) Time
--------------------------------------------------------------------------------------
   0  SELECT STATEMENT                                     1     69      7   (0) 00:00:01
   1   SORT AGGREGATE                                      1     69                  
*  2    TABLE ACCESS BY INDEX ROWID RMS                    1     69      7   (0) 00:00:01
*  3     INDEX RANGE SCAN           IX_CUST_BTN_RMAS       7             1   (0) 00:00:01
----------------------------------------------------------------------------------------

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

   2 - filter("SESSION_KEY" LIKE 'LinkSummary%' AND "LAST_ACCESS_DATE">=SYSDATE@!-1)
   3 - access("CUST_BTN"='6095884091')

Note
-----
   - SQL profile "profile_myacc_2" used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        488  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Sep 16, 2013 update:

A tool called SQLT provides a script called coe_xfr_sql_profile.sql that can be used to create a sql profile in a very convenient way.

see: SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly or one that produces wrong results (Doc ID 215187.1)

Explore SQL Profiles - Part I

Suppose we identified a problem SQL whose access path is not optimal and we want to tune it, however we can not modify the text of the SQL. In this case, we can use SQL profiles (10g feature) to guide Oracle to choose optimal execution plan. In this post, I am going to describe a test case in order to be familiar with the use of SQL profile.

First of all, I created a test table (the test case in based on ML 271196.1 Automatic SQL Tuning - SQL Profiles)

create table test (n number);
declare
begin
  for i in 1 .. 20000
  loop
     insert into test values(i);
     commit;
  end loop;
end;
/
create index test_idx on test(n);
exec dbms_stats.gather_table_stats(user, 'TEST');

The problem SQL is as follows:

select /*+ no_index(test test_idx) */ * from test where n=1;

We know the best way to access the table TEST with predicate n=1 is through index scan, however due to the no_index hint we will have FTS. We are going to see with SQL profile, Oracle can choose index scan and ignore this hint.


Summary of the steps:

Step 1: Create a tuning task
Step 2: Execute the tuning task
Step 3: Report the tuning task
Step 4: Check SQL profile rationale
Step 5: Accept the SQL profile
Step 6: Verify SQL profile is used for the problem SQL
Step 7: Disable and Enable the SQL profile
Step 8: Modify category attribute of SQL profile
Step 9: Query DBA_SQL_PROFILES


Step 1: Create a tuning task

SQL>
SQL> declare
  2   my_task_name varchar2(30);
  3   my_sqltext clob;
  4  begin
  5   my_sqltext :='select /*+ no_index(test test_idx) */ * from test where n=1';
  6   my_task_name := dbms_sqltune.create_tuning_task (
  7      sql_text  => my_sqltext,
  8      user_name => 'Z983294',
  9      scope     => 'COMPREHENSIVE',
 10      time_limit => 60,
 11      task_name => 'my_sql_tuning_task_3',
 12      description => 'Task to tune a query on a specified table');
 13  end;
 14  /

PL/SQL procedure successfully completed.

Step 2: Execute the tuning task

SQL> begin
  2   dbms_sqltune.execute_tuning_task( task_name => 'my_sql_tuning_task_3');
  3  end;
  4  /

PL/SQL procedure successfully completed.

Step 3: Report the tuning task

SQL>
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_3') from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_3')
--------------------------------------------------------------------------
GENERAL INFORMATION SECTION
--------------------------------------------------------------------------
Tuning Task Name                  : my_sql_tuning_task_3
Tuning Task Owner                 : Z983294
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 06/30/2009 05:24:39
Completed at                      : 06/30/2009 05:24:40
Number of SQL Profile Findings    : 1

--------------------------------------------------------------------------
Schema Name: Z983294
SQL ID     : d4wgpc5g0s0vu
SQL Text   : select /*+ no_index(test test_idx) */ * from test where n=1

--------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
--------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 89.76%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'my_sql_tuning_task_3', replace => TRUE);

-------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 217508114

------------------------------------------------------------------------
 Id   Operation          Name  Rows   Bytes  Cost (%CPU) Time
-------------------------------------------------------------------------
*  1   TABLE ACCESS FULL TEST      1      4     10  (10) 00:00:01
-------------------------------------------------------------------------

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

   1 - filter("N"=1)

2- Using SQL Profile
--------------------
Plan hash value: 1416057887

-------------------------------------------------------------------------
 Id   Operation         Name     Rows  Bytes Cost (%CPU) Time
-------------------------------------------------------------------------
   0  SELECT STATEMENT              1      4     1   (0) 00:00:01
*  1   INDEX RANGE SCAN TEST_IDX    1      4     1   (0) 00:00:01
-------------------------------------------------------------------------

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

   1 - access("N"=1)

Step 4: Check SQL profile rationale

SQL>
SQL> select
  2   rat.attr1
  3  from
  4   sys.wri$_adv_tasks      tsk,
  5   sys.wri$_adv_rationale      rat
  6  where
  7        tsk.name = 'my_sql_tuning_task_3'
  8  and     rat.task_id   = tsk.id;

ATTR1
--------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS

Step 5: Accept the SQL profile

SQL>
SQL> -- Accept the tuning profile
SQL> -- SQLs different only in case and space are treated as same
SQL> -- in applying the SQL profile. If  force_match = TRUE is specified,
SQL> -- differences in literals are also ignored
SQL>
SQL> DECLARE
  2   my_sqlprofile_name VARCHAR2(30);
  3  BEGIN
  4    my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
  5      task_name => 'my_sql_tuning_task_3',
  6      name      => 'my_sql_profile',
  7      force_match => TRUE);
  8    END;
  9  /

PL/SQL procedure successfully completed.

Step 6: Verify SQL Profile is used for the problem SQL

SQL> -- Index scan due to sql profile
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

         N
----------
         1

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------
SQL_ID  apxdba96mcfqt, child number 0
-------------------------------------
select /*+ no_index(test test_idx) */ * from test where n=1

Plan hash value: 1416057887

-------------------------------------------------------------------------
 Id   Operation         Name     Rows  Bytes Cost (%CPU) Time
-------------------------------------------------------------------------
   0  SELECT STATEMENT                           1 (100)     
*  1   INDEX RANGE SCAN TEST_IDX     1     4     1   (0) 00:00:01
-------------------------------------------------------------------------

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

   1 - access("N"=1)

Note
-----
   - SQL profile "my_sql_profile" used for this statement


22 rows selected.

SQL>
SQL> select /*+ no_index(test test_idx) */ * from test where n=9;

         N
----------
         9

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID  7daa3xa9mxy0f, child number 0
-------------------------------------
select /*+ no_index(test test_idx) */ * from test where n=9

Plan hash value: 1416057887

-------------------------------------------------------------------------
 Id   Operation         Name      Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------
   0  SELECT STATEMENT                           1 (100)     
*  1   INDEX RANGE SCAN TEST_IDX      1    4     1   (0) 00:00:01
-------------------------------------------------------------------------

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

   1 - access("N"=9)

Note
-----
   - SQL profile "my_sql_profile" used for this statement

Step 7: Disable and Enable the SQL profile

SQL> -- disable the profile
SQL> BEGIN
  2     DBMS_SQLTUNE.ALTER_SQL_PROFILE(
  3       name     => 'my_sql_profile',
  4      attribute_name  => 'STATUS',
  5      value    => 'DISABLED');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> -- FTS due to profile is disabled
SQL> select /*+ no_index(test test_idx) */ * from test where n=9;

         N
----------
         9

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

PLAN_TABLE_OUTPUT
-------------------------------------------------
SQL_ID  7daa3xa9mxy0f, child number 0
-------------------------------------
select /*+ no_index(test test_idx) */ * from test where n=9

Plan hash value: 217508114

----------------------------------------------------------------------
 Id   Operation          Name Rows  Bytes Cost (%CPU) Time
----------------------------------------------------------------------
   0  SELECT STATEMENT                       10 (100)     
*  1   TABLE ACCESS FULL TEST     1     4    10  (10) 00:00:01
----------------------------------------------------------------------

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

   1 - filter("N"=9)


18 rows selected.

SQL>
SQL> -- enable the profile
SQL> BEGIN
  2     DBMS_SQLTUNE.ALTER_SQL_PROFILE(
  3       name     => 'my_sql_profile',
  4      attribute_name  => 'STATUS',
  5      value    => 'ENABLED');
  6  END;
  7  /

PL/SQL procedure successfully completed.

Step 8: Modify category attribute of SQL profile

SQL> -- Modify the category to TEST
SQL> --  category default value: 'DEFAULT'
SQL>
SQL> begin
  2   DBMS_SQLTUNE.ALTER_SQL_PROFILE (
  3      name     => 'my_sql_profile',
  4      attribute_name  =>  'CATEGORY',
  5      value    =>  'TEST');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> -- FTS after sql profile's category is modified
SQL> select /*+ no_index(test test_idx) */ * from test where n=9;

         N
----------
         9

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID  7daa3xa9mxy0f, child number 0
-------------------------------------
select /*+ no_index(test test_idx) */ * from test where n=9

Plan hash value: 217508114

---------------------------------------------------------------------
 Id   Operation          Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------
   0  SELECT STATEMENT                      10 (100)     
*  1   TABLE ACCESS FULL TEST     1    4    10  (10) 00:00:01
---------------------------------------------------------------------

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

   1 - filter("N"=9)


18 rows selected.

SQL>
SQL>
SQL> alter session set sqltune_category='TEST';

Session altered.

SQL>
SQL> -- Index scan after change the session's sqltune_category
SQL> select /*+ no_index(test test_idx) */ * from test where n=9;

         N
----------
         9

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
SQL_ID  7daa3xa9mxy0f, child number 1
-------------------------------------
select /*+ no_index(test test_idx) */ * from test where n=9

Plan hash value: 1416057887

-----------------------------------------------------------------------
 Id   Operation         Name     Rows Bytes Cost (%CPU)Time
-----------------------------------------------------------------------
   0  SELECT STATEMENT                          1 (100)    
*  1   INDEX RANGE SCAN TEST_IDX     1    4     1   (0)00:00:01
-----------------------------------------------------------------------

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

   1 - access("N"=9)

Note
-----
   - SQL profile "my_sql_profile" used for this statement

Step 9: Query DBA_SQL_PROFILES

SQL> -- Query dba_sql_profiles
SQL> col name format a10
SQL> col category format a10
SQL> col sql_text format a30
SQL> col force_matching format a5
SQL>
SQL> select name, category,sql_text,force_matching from dba_sql_profiles;

NAME       CATEGORY   SQL_TEXT                       FORCE
---------- ---------- ------------------------------ -----
my_sql_pro TEST       select /*+ no_index(test test_ YES
file                  idx) */ * from test where n=1

What if Oralce does not recommend a SQL Profile to the problem SQL? And we know there is a better access path? This is what I will explore next about SQL profiles.

Go to Part II