Thursday, January 28, 2010

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)

Saturday, January 16, 2010

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};

Tuesday, January 12, 2010

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
/