ALTER SYSTEM SWITCH LOGFILE and CHECKPOINT

Question: Will "alter system switch logfile" trigger checkpoint?
Answer: It will only trigger a complete checkpoint when active redo log group to be overwritten due to circular fashion. In other words, "alter system switch logfile" may trigger complete checkpoint, but not always.

SQL> alter system switch logfile;

System altered.

SQL> select first_time,first_change#,sequence#,status from v$log;

FIRST_TIME           FIRST_CHANGE#  SEQUENCE# STATUS
-------------------- ------------- ---------- ----------------
2010-FEB-07 09:42:49       2180598         37 ACTIVE
2010-FEB-07 09:46:12       2180846         38 CURRENT
2010-FEB-04 20:51:32       2156580         36 INACTIVE

SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ --------------------
           2180599 2010-FEB-07 09:42:49
           2180599 2010-FEB-07 09:42:49
           2180599 2010-FEB-07 09:42:49
           2180599 2010-FEB-07 09:42:49
           2180599 2010-FEB-07 09:42:49

SQL> alter system switch logfile;

System altered.

SQL> select first_time,first_change#,sequence#,status from v$log;

FIRST_TIME           FIRST_CHANGE#  SEQUENCE# STATUS
-------------------- ------------- ---------- ----------------
2010-FEB-07 09:42:49       2180598         37 ACTIVE
2010-FEB-07 09:46:12       2180846         38 ACTIVE
2010-FEB-07 09:46:40       2180856         39 CURRENT

SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ --------------------
           2180599 2010-FEB-07 09:42:49
           2180599 2010-FEB-07 09:42:49
           2180599 2010-FEB-07 09:42:49
           2180599 2010-FEB-07 09:42:49
           2180599 2010-FEB-07 09:42:49

Check point triggered, due to active redo group being overwritten

SQL> alter system switch logfile;

System altered.

SQL> select first_time,first_change#,sequence#,status from v$log;

FIRST_TIME           FIRST_CHANGE#  SEQUENCE# STATUS
-------------------- ------------- ---------- ----------------
2010-FEB-07 09:46:55       2180863         40 CURRENT
2010-FEB-07 09:46:12       2180846         38 INACTIVE
2010-FEB-07 09:46:40       2180856         39 INACTIVE

SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ --------------------
           2180863 2010-FEB-07 09:46:55
           2180863 2010-FEB-07 09:46:55
           2180863 2010-FEB-07 09:46:55
           2180863 2010-FEB-07 09:46:55
           2180863 2010-FEB-07 09:46:55

SQL> alter system switch logfile;

System altered.

SQL> select first_time,first_change#,sequence#,status from v$log;

FIRST_TIME           FIRST_CHANGE#  SEQUENCE# STATUS
-------------------- ------------- ---------- ----------------
2010-FEB-07 09:46:55       2180863         40 ACTIVE
2010-FEB-07 09:47:59       2181178         41 CURRENT
2010-FEB-07 09:46:40       2180856         39 INACTIVE

SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ --------------------
           2180863 2010-FEB-07 09:46:55
           2180863 2010-FEB-07 09:46:55
           2180863 2010-FEB-07 09:46:55
           2180863 2010-FEB-07 09:46:55
           2180863 2010-FEB-07 09:46:55

SQL> alter system switch logfile;

System altered.

SQL> select first_time,first_change#,sequence#,status from v$log;

FIRST_TIME           FIRST_CHANGE#  SEQUENCE# STATUS
-------------------- ------------- ---------- ----------------
2010-FEB-07 09:46:55       2180863         40 ACTIVE
2010-FEB-07 09:47:59       2181178         41 ACTIVE
2010-FEB-07 09:48:29       2181977         42 CURRENT

SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ --------------------
           2180863 2010-FEB-07 09:46:55
           2180863 2010-FEB-07 09:46:55
           2180863 2010-FEB-07 09:46:55
           2180863 2010-FEB-07 09:46:55
           2180863 2010-FEB-07 09:46:55

Check point triggered, due to active redo group being overwritten

SQL> alter system switch logfile;

System altered.

SQL> select first_time,first_change#,sequence#,status from v$log;

FIRST_TIME           FIRST_CHANGE#  SEQUENCE# STATUS
-------------------- ------------- ---------- ----------------
2010-FEB-07 09:49:28       2184453         43 CURRENT
2010-FEB-07 09:47:59       2181178         41 ACTIVE
2010-FEB-07 09:48:29       2181977         42 ACTIVE

SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ --------------------
           2181178 2010-FEB-07 09:47:59
           2181178 2010-FEB-07 09:47:59
           2181178 2010-FEB-07 09:47:59
           2181178 2010-FEB-07 09:47:59
           2181178 2010-FEB-07 09:47:59

log switch checkpoint and complete checkpoint

2010-02-07 21:21:29.530000 +08:00
Beginning log switch checkpoint up to RBA [0x31.2.10], SCN: 2242997
Thread 1 advanced to log sequence 49
  Current log# 1 seq# 49 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO01.LOG
2010-02-07 21:22:05.590000 +08:00
Beginning log switch checkpoint up to RBA [0x32.2.10], SCN: 2243020
Thread 1 advanced to log sequence 50
  Current log# 2 seq# 50 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO02.LOG
2010-02-07 21:22:15.244000 +08:00
Thread 1 cannot allocate new log, sequence 51
Checkpoint not complete
  Current log# 2 seq# 50 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO02.LOG
2010-02-07 21:22:16.766000 +08:00
Completed checkpoint up to RBA [0x31.2.10], SCN: 2242997
Beginning log switch checkpoint up to RBA [0x33.2.10], SCN: 2243027
Thread 1 advanced to log sequence 51
  Current log# 3 seq# 51 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO03.LOG
2010-02-07 21:22:27.363000 +08:00
Thread 1 cannot allocate new log, sequence 52
Checkpoint not complete
  Current log# 3 seq# 51 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO03.LOG
2010-02-07 21:22:28.647000 +08:00
Completed checkpoint up to RBA [0x32.2.10], SCN: 2243020
Beginning log switch checkpoint up to RBA [0x34.2.10], SCN: 2243034
2010-02-07 21:22:29.828000 +08:00
Thread 1 advanced to log sequence 52
  Current log# 1 seq# 52 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO01.LOG

Documentation Bug for Oracle database installation on Solaris

Bug 1:

"project.max-sem-nsems" appears in the kernel parameter requirement in following document:

"Oracle® Database Installation Guide
10g Release 2 (10.2) for Solaris Operating System (x86)
Part Number B15697-01"

But the correct setting should be "process.max-sem-nsems", and it has been corrected in the documentation on http://tahiti.oracle.com, and 11gR2 installation guide.

Bug 2:

"SUNWsprox" appears in the required packages for Solaris 10. But this package does not exist on Solaris 10.


Add-on:

project.max-sem-ids, required value 100, default value from Solaris 128
process.max-sem-nsems, required value 256, default value from Solaris 512. (This value should be "(all your processes initialization parameter) + 20")
project.max-shm-ids, required value 100, default value from Solaris 128

Thus only project.max-shm-memory is required to set to install Oracle on Solaris.

For example, to create a project for oracle, which allow 12GB used for SGA, and 1000 processes.

projadd -p 200 -K "project.max-shm-memory=(privileged,12G,deny)" -K "process.max-sem-nsems=(privileged,1024,deny)" user.oracle

Performance Tuning using Windowing Aggregate Functions to avoid table self-join

SQL> select first_name,last_name,salary,department_id
  2  from hr.employees a
  3  where a.salary=(select max(salary) from hr.employees b
  4  where a.department_id=b.department_id);

FIRST_NAME           LAST_NAME                     SALARY DEPARTMENT_ID
-------------------- ------------------------- ---------- -------------
Nancy                Greenberg                      12000           100
Den                  Raphaely                       11000            30
Michael              Hartstein                      13000            20
Hermann              Baer                           10000            70
Steven               King                           24000            90
Shelley              Higgins                        12000           110
Adam                 Fripp                           8200            50
Susan                Mavris                          6500            40
John                 Russell                        14000            80
Jennifer             Whalen                          4400            10
Alexander            Hunold                          9000            60

11 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1769107035

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     1 |    48 |     5  (20)| 00:00:01 |
|   1 |  NESTED LOOPS                |                   |       |       |            |          |
|   2 |   NESTED LOOPS               |                   |     1 |    48 |     5  (20)| 00:00:01 |
|   3 |    VIEW                      | VW_SQ_1           |     1 |    26 |     4  (25)| 00:00:01 |
|*  4 |     FILTER                   |                   |       |       |            |          |
|   5 |      HASH GROUP BY           |                   |     1 |    10 |     4  (25)| 00:00:01 |
|   6 |       TABLE ACCESS FULL      | EMPLOYEES         |   107 |  1070 |     3   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |
|*  8 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     1 |    22 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   4 - filter(MAX("SALARY")>0)
   7 - access("A"."DEPARTMENT_ID"="ITEM_1")
   8 - filter("A"."SALARY"="MAX(SALARY)")

SQL> select first_name,last_name,salary,department_id
  2  from (select max(salary) over (partition by department_id) as max_salary,
  3  first_name,last_name,salary,department_id
  4  from hr.employees) a
  5  where a.salary=max_salary and a.department_id is not null;

FIRST_NAME           LAST_NAME                     SALARY DEPARTMENT_ID
-------------------- ------------------------- ---------- -------------
Jennifer             Whalen                          4400            10
Michael              Hartstein                      13000            20
Den                  Raphaely                       11000            30
Susan                Mavris                          6500            40
Adam                 Fripp                           8200            50
Alexander            Hunold                          9000            60
Hermann              Baer                           10000            70
John                 Russell                        14000            80
Steven               King                           24000            90
Nancy                Greenberg                      12000           100
Shelley              Higgins                        12000           110

11 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 720055818

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |   106 |  6890 |     4  (25)| 00:00:01 |
|*  1 |  VIEW               |           |   106 |  6890 |     4  (25)| 00:00:01 |
|   2 |   WINDOW SORT       |           |   106 |  2332 |     4  (25)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| EMPLOYEES |   106 |  2332 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter("A"."SALARY"="MAX_SALARY")
   3 - filter("DEPARTMENT_ID" IS NOT NULL)

Optimizing Wildcard Search (LIKE '%KEYWORD%')

SETUP

create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
commit;
create index t_n1 on t(object_name);
exec dbms_stats.gather_table_stats(ownname=>'donghua',tabname=>'t',method_opt=>'for all indexed columns size 254');

Not Tuning Done

SQL> select * from t where object_name like '%EMPLOYEES%';
64 rows selected.
Elapsed: 00:00:05.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 55195 |  5444K|  4420   (1)| 00:00:54 |
|*  1 |  TABLE ACCESS FULL| T    | 55195 |  5444K|  4420   (1)| 00:00:54 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME" LIKE '%EMPLOYEES%')

Tuning 1: (If the final rows less than 5% of total records, which is usually better for Index, instead of full table scan)

SQL> select /*+ index(t,t_n1) */ * from t where object_name like '%EMPLOYEES%';
64 rows selected.
Elapsed: 00:00:04.12

Execution Plan
----------------------------------------------------------
Plan hash value: 3068238422
------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      | 55195 |  5444K| 44318   (1)| 00:08:52 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    | 55195 |  5444K| 44318   (1)| 00:08:52 |
|*  2 |   INDEX FULL SCAN           | T_N1 | 55427 |       |  5490   (1)| 00:01:06 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_NAME" LIKE '%EMPLOYEES%')

Tuning 2: (If we only select few columns, instead of all columns) (In below example, we select 3 specific columns instead of *)

SQL> create index t_n2 on t(object_name,owner,object_type);

Index created.

SQL> select owner,object_name,object_type from t where object_name like '%EMPLOYEES%';
64 rows selected.
Elapsed: 00:00:02.70

Execution Plan
----------------------------------------------------------
Plan hash value: 2376357677
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 55195 |  2156K|  2094   (1)| 00:00:26 |
|*  1 |  INDEX FAST FULL SCAN| T_N2 | 55195 |  2156K|  2094   (1)| 00:00:26 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME" LIKE '%EMPLOYEES%')

Tuning 3: Oracle Text

SQL> create index t_t1 on t(object_name) indextype is CTXSYS.CONTEXT;
Index created.
Elapsed: 00:01:13.54

SQL> select * from t where contains (object_name, 'EMPLOYEES', 1) >0;
64 rows selected.
Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2899541820
------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |   552 | 55752 |   124   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |   552 | 55752 |   124   (0)| 00:00:02 |
|*  2 |   DOMAIN INDEX              | T_T1 |       |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CTXSYS"."CONTAINS"("OBJECT_NAME",'EMPLOYEES',1)>0)

orapwd file argument, where should be the password file located

Platform: UNIX and Linux
Location: $ORACLE_HOME/dbs/orapwORACLE_SID

Example:
[oracle@rh4 ~]$ ls -l /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl
-rw-r----- 1 oracle oinstall 1536 Dec 31 08:33 /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl


Platform: Windows
Location: %ORACLE_HOME%\database\PWDORACLE_SID.ora

Example:
D:\ORACLE\product\11.1.0\db_1\database>attrib
A D:\ORACLE\product\11.1.0\db_1\database\hc_orcl.dat
A D:\ORACLE\product\11.1.0\db_1\database\oradba.exe
A D:\ORACLE\product\11.1.0\db_1\database\oradim.log
A D:\ORACLE\product\11.1.0\db_1\database\PWDorcl.ora
A D:\ORACLE\product\11.1.0\db_1\database\SPFILEORCL.ORA

Where operating system group OSDBA and OSOPER configured?

[oracle@rh4 ~]$ cat /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/config.c

/*  SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.  */
/*  Refer to the Installation and User's Guide for further information.  */

/* IMPORTANT: this file needs to be in sync with
              rdbms/src/server/osds/config.c, specifically regarding the
              number of elements in the ss_dba_grp array.
 */

#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "oinstall"
#define SS_ASM_GRP ""

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};

Retrieve session specific settings for previous "alter session" command

-- SQL> col name for a30
-- SQL> col sys_level for a20
-- SQL> col ses_level for a20
-- SQL> select p1.name,
--   2  p1.value sys_level,
--   3  p2.value ses_level
--   4  from v$system_parameter p1,
--   5  v$parameter p2
--   6  where p1.name=p2.name
--   7  and p1.value <> p2.value
--   8  order by p1.name
--   9  /
-- 
-- NAME                           SYS_LEVEL            SES_LEVEL
-- ------------------------------ -------------------- --------------------
-- sql_trace                      FALSE                TRUE


col name for a30
col sys_level for a20
col ses_level for a20
select p1.name,
p1.value sys_level,
p2.value ses_level
from v$system_parameter p1,
v$parameter p2
where p1.name=p2.name
and p1.value <> p2.value
order by p1.name
/