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
/