Hi All,
Oracle Version: 10.2.0.4.0
OS Version: SunOS 5.10
CPU load on the server use to be high occasionally for brief time and the high CPU usage sessions execute this below query.
But i found two different execution plan for the same SQL statement.
206 processes: 202 sleeping, 1 zombie, 3 on cpu
CPU states: 38.1% idle, 45.5% user, 16.4% kernel, 0.0% iowait, 0.0% swap
Memory: 16G phys mem, 6900M free mem, 32G total swap, 32G free swap
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
18187 oracle 1 0 0 6278M 6263M cpu/3 122:15 24.28% oracle
12841 oracle 1 0 0 6278M 6263M cpu/0 41:11 24.00% oracle
SQL> select c.sql_text,c.sql_id,c.child_number
from gv$process a,
gv$session b,
gv$sql c
where a.addr = b.paddr and
b.sql_hash_value = c.hash_value and
a.spid = &PID; 2 3 4 5 6 7
Enter value for pid: 18187
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID CHILD_NUMBER
------------- ------------
select overload, decode(position,0,'RETURN_VALUE',nvl(argument_name,chr(0))) name, decode(in_out,'IN',1,'IN/OUT',3,'OUT',decode(argument_name,null,6,2
),1) direction, decode(data_type, 'BFILE',1, 'BLOB',2, 'CHAR',3, 'CLOB',4, 'DATE',6, 'FLOAT',13, 'INTERVAL YEAR TO MONTH',8, 'INTERVAL DAY TO SECOND',
7, 'LONG',10, 'LONG RAW',9, 'NCHAR',11, 'NCLOB',12, 'NUMBER',13, 'NVARCHAR2',14, 'RAW',15, 'REF CURSOR',5, 'ROWID',16, 'TIMESTAMP',18, 'TIMESTAMP WITH
LOCAL TIME ZONE',19, 'TIMESTAMP WITH TIME ZONE',20, 'VARCHAR2',22,22) oracletype, decode(data_type, 'CHAR',2000, 'LONG',2147483647, 'LONG RAW',214748
3647, 'NCHAR',4000, 'NVARCHAR2',4000, 'RAW',2000, 'VARCHAR2',2000,0) length, nvl(data_precision, 255) precision, nvl(data_scale, 255) scale from all_a
rguments where data_level = 0 and data_type is not null and owner = 'ADMIN' and object_name = 'P_CATALOGGETPRIMARYKEYS' order by overload, position
ftubn3vh0wp34 0
select overload, decode(position,0,'RETURN_VALUE',nvl(argument_name,chr(0))) name, decode(in_out,'IN',1,'IN/OUT',3,'OUT',decode(argument_name,null,6,2
),1) direction, decode(data_type, 'BFILE',1, 'BLOB',2, 'CHAR',3, 'CLOB',4, 'DATE',6, 'FLOAT',13, 'INTERVAL YEAR TO MONTH',8, 'INTERVAL DAY TO SECOND',
7, 'LONG',10, 'LONG RAW',9, 'NCHAR',11, 'NCLOB',12, 'NUMBER',13, 'NVARCHAR2',14, 'RAW',15, 'REF CURSOR',5, 'ROWID',16, 'TIMESTAMP',18, 'TIMESTAMP WITH
LOCAL TIME ZONE',19, 'TIMESTAMP WITH TIME ZONE',20, 'VARCHAR2',22,22) oracletype, decode(data_type, 'CHAR',2000, 'LONG',2147483647, 'LONG RAW',214748
3647, 'NCHAR',4000, 'NVARCHAR2',4000, 'RAW',2000, 'VARCHAR2',2000,0) length, nvl(data_precision, 255) precision, nvl(data_scale, 255) scale from all_a
rguments where data_level = 0 and data_type is not null and owner = 'ADMIN' and object_name = 'P_CATALOGGETPRIMARYKEYS' order by overload, position
ftubn3vh0wp34 2
select overload, decode(position,0,'RETURN_VALUE',nvl(argument_name,chr(0))) name, decode(in_out,'IN',1,'IN/OUT',3,'OUT',decode(argument_name,null,6,2
),1) direction, decode(data_type, 'BFILE',1, 'BLOB',2, 'CHAR',3, 'CLOB',4, 'DATE',6, 'FLOAT',13, 'INTERVAL YEAR TO MONTH',8, 'INTERVAL DAY TO SECOND',
7, 'LONG',10, 'LONG RAW',9, 'NCHAR',11, 'NCLOB',12, 'NUMBER',13, 'NVARCHAR2',14, 'RAW',15, 'REF CURSOR',5, 'ROWID',16, 'TIMESTAMP',18, 'TIMESTAMP WITH
LOCAL TIME ZONE',19, 'TIMESTAMP WITH TIME ZONE',20, 'VARCHAR2',22,22) oracletype, decode(data_type, 'CHAR',2000, 'LONG',2147483647, 'LONG RAW',214748
3647, 'NCHAR',4000, 'NVARCHAR2',4000, 'RAW',2000, 'VARCHAR2',2000,0) length, nvl(data_precision, 255) precision, nvl(data_scale, 255) scale from all_a
rguments where data_level = 0 and data_type is not null and owner = 'ADMIN' and object_name = 'P_CATALOGGETPRIMARYKEYS' order by overload, position
ftubn3vh0wp34 0
select overload, decode(position,0,'RETURN_VALUE',nvl(argument_name,chr(0))) name, decode(in_out,'IN',1,'IN/OUT',3,'OUT',decode(argument_name,null,6,2
),1) direction, decode(data_type, 'BFILE',1, 'BLOB',2, 'CHAR',3, 'CLOB',4, 'DATE',6, 'FLOAT',13, 'INTERVAL YEAR TO MONTH',8, 'INTERVAL DAY TO SECOND',
7, 'LONG',10, 'LONG RAW',9, 'NCHAR',11, 'NCLOB',12, 'NUMBER',13, 'NVARCHAR2',14, 'RAW',15, 'REF CURSOR',5, 'ROWID',16, 'TIMESTAMP',18, 'TIMESTAMP WITH
LOCAL TIME ZONE',19, 'TIMESTAMP WITH TIME ZONE',20, 'VARCHAR2',22,22) oracletype, decode(data_type, 'CHAR',2000, 'LONG',2147483647, 'LONG RAW',214748
3647, 'NCHAR',4000, 'NVARCHAR2',4000, 'RAW',2000, 'VARCHAR2',2000,0) length, nvl(data_precision, 255) precision, nvl(data_scale, 255) scale from all_a
rguments where data_level = 0 and data_type is not null and owner = 'ADMIN' and object_name = 'P_CATALOGGETPRIMARYKEYS' order by overload, position
ftubn3vh0wp34 2
SQL> select * from table(dbms_xplan.display_awr('ftubn3vh0wp34',NULL,NULL, 'ALL'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID ftubn3vh0wp34
--------------------
select overload, decode(position,0,'RETURN_VALUE',nvl(argument_name,chr(0))) name,
decode(in_out,'IN',1,'IN/OUT',3,'OUT',decode(argument_name,null,6,2),1) direction,
decode(data_type, 'BFILE',1, 'BLOB',2, 'CHAR',3, 'CLOB',4, 'DATE',6, 'FLOAT',13,
'INTERVAL YEAR TO MONTH',8, 'INTERVAL DAY TO SECOND',7, 'LONG',10, 'LONG RAW',9,
'NCHAR',11, 'NCLOB',12, 'NUMBER',13, 'NVARCHAR2',14, 'RAW',15, 'REF CURSOR',5,
'ROWID',16, 'TIMESTAMP',18, 'TIMESTAMP WITH LOCAL TIME ZONE',19, 'TIMESTAMP WITH TIME
ZONE',20, 'VARCHAR2',22,22) oracletype, decode(data_type, 'CHAR',2000,
'LONG',2147483647, 'LONG RAW',2147483647, 'NCHAR',4000, 'NVARCHAR2',4000, 'RAW',2000,
'VARCHAR2',2000,0) length, nvl(data_precision, 255) precision, nvl(data_scale, 255)
scale from all_arguments where data_level = 0 and data_type is not null and owner =
'ADMIN' and object_name = 'P_CATALOGGETPRIMARYKEYS' order by overload, position
Plan hash value: 1154559427
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 427 (100)| |
| 1 | SORT ORDER BY | | 1 | 98 | 427 (16)| 00:00:06 |
| 2 | FILTER | | | | | |
| 3 | HASH JOIN | | 1 | 98 | 426 (16)| 00:00:06 |
| 4 | NESTED LOOPS | | 518 | 24346 | 161 (8)| 00:00:02 |
| 5 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 15 | 1 (0)| 00:00:01 |
| 6 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | OBJ$ | 518 | 16576 | 160 (8)| 00:00:02 |
| 8 | TABLE ACCESS FULL | ARGUMENT$ | 3620 | 180K| 265 (21)| 00:00:04 |
| 9 | FIXED TABLE FULL | X$KZSPR | 1 | 7 | 0 (0)| |
| 10 | NESTED LOOPS | | 1 | 15 | 1 (0)| 00:00:01 |
| 11 | FIXED TABLE FULL | X$KZSRO | 2 | 6 | 0 (0)| |
| 12 | INDEX RANGE SCAN | I_OBJAUTH1 | 1 | 12 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
5 - SEL$F5BB74E1 / U@SEL$2
6 - SEL$F5BB74E1 / U@SEL$2
7 - SEL$F5BB74E1 / O@SEL$2
8 - SEL$F5BB74E1 / A@SEL$2
9 - SEL$C8360722 / X$KZSPR@SEL$5
10 - SEL$0C7B6CF6
11 - SEL$0C7B6CF6 / X$KZSRO@SEL$7
12 - SEL$0C7B6CF6 / OBJAUTH$@SEL$6
SQL_ID ftubn3vh0wp34
--------------------
select overload, decode(position,0,'RETURN_VALUE',nvl(argument_name,chr(0)))
name, decode(in_out,'IN',1,'IN/OUT',3,'OUT',decode(argument_name,null,6,2),1)
direction, decode(data_type, 'BFILE',1, 'BLOB',2, 'CHAR',3, 'CLOB',4,
'DATE',6, 'FLOAT',13, 'INTERVAL YEAR TO MONTH',8, 'INTERVAL DAY TO SECOND',7,
'LONG',10, 'LONG RAW',9, 'NCHAR',11, 'NCLOB',12, 'NUMBER',13, 'NVARCHAR2',14,
'RAW',15, 'REF CURSOR',5, 'ROWID',16, 'TIMESTAMP',18, 'TIMESTAMP WITH LOCAL
TIME ZONE',19, 'TIMESTAMP WITH TIME ZONE',20, 'VARCHAR2',22,22) oracletype,
decode(data_type, 'CHAR',2000, 'LONG',2147483647, 'LONG RAW',2147483647,
'NCHAR',4000, 'NVARCHAR2',4000, 'RAW',2000, 'VARCHAR2',2000,0) length,
nvl(data_precision, 255) precision, nvl(data_scale, 255) scale from
all_arguments where data_level = 0 and data_type is not null and owner =
'ADMIN' and object_name = 'P_CATALOGGETPRIMARYKEYS' order by overload,
position
Plan hash value: 4030623783
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 423 (100)| |
| 1 | SORT ORDER BY | | 1 | 98 | 423 (17)| 00:00:06 |
| 2 | FILTER | | | | | |
| 3 | HASH JOIN | | 1 | 98 | 422 (17)| 00:00:06 |
| 4 | MERGE JOIN CARTESIAN| | 3559 | 229K| 263 (21)| 00:00:04 |
| 5 | TABLE ACCESS FULL | USER$ | 1 | 15 | 3 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 3559 | 177K| 260 (21)| 00:00:04 |
| 7 | TABLE ACCESS FULL | ARGUMENT$ | 3559 | 177K| 260 (21)| 00:00:04 |
| 8 | TABLE ACCESS FULL | OBJ$ | 52928 | 1654K| 157 (8)| 00:00:02 |
| 9 | FIXED TABLE FULL | X$KZSPR | 1 | 7 | 0 (0)| |
| 10 | NESTED LOOPS | | 1 | 15 | 1 (0)| 00:00:01 |
| 11 | FIXED TABLE FULL | X$KZSRO | 2 | 6 | 0 (0)| |
| 12 | INDEX RANGE SCAN | I_OBJAUTH1 | 1 | 12 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
5 - SEL$F5BB74E1 / U@SEL$2
7 - SEL$F5BB74E1 / A@SEL$2
8 - SEL$F5BB74E1 / O@SEL$2
9 - SEL$C8360722 / X$KZSPR@SEL$5
10 - SEL$0C7B6CF6
11 - SEL$0C7B6CF6 / X$KZSRO@SEL$7
12 - SEL$0C7B6CF6 / OBJAUTH$@SEL$6
95 rows selected.
SQL> select /*+ gather_plan_statistics */overload, decode(position,0,'RETURN_VALUE',nvl(argument_name,chr(0)))
2 name, decode(in_out,'IN',1,'IN/OUT',3,'OUT',decode(argument_name,null,6,2),1)
3 direction, decode(data_type, 'BFILE',1, 'BLOB',2, 'CHAR',3, 'CLOB',4,
'DATE',6, 'FLOAT',13, 'INTERVAL YEAR TO MONTH',8, 'INTERVAL DAY TO SECOND',7,
4 5 'LONG',10, 'LONG RAW',9, 'NCHAR',11, 'NCLOB',12, 'NUMBER',13, 'NVARCHAR2',14,
6 'RAW',15, 'REF CURSOR',5, 'ROWID',16, 'TIMESTAMP',18, 'TIMESTAMP WITH LOCAL
7 TIME ZONE',19, 'TIMESTAMP WITH TIME ZONE',20, 'VARCHAR2',22,22) oracletype,
8 decode(data_type, 'CHAR',2000, 'LONG',2147483647, 'LONG RAW',2147483647,
9 'NCHAR',4000, 'NVARCHAR2',4000, 'RAW',2000, 'VARCHAR2',2000,0) length,
10 nvl(data_precision, 255) precision, nvl(data_scale, 255) scale from
11 all_arguments where data_level = 0 and data_type is not null and owner =
12 'ADMIN' and object_name = 'P_CATALOGGETPRIMARYKEYS' order by overload,
13 position;
OVERLOAD
------------------------------------------------------------------------------------------------------------------------------------------------------
NAME DIRECTION ORACLETYPE
------------------------------------------------------------------------------------------------------------------------ ---------- ----------
LENGTH PRECISION SCALE
---------- ---------- ----------
SCHEMANAME 1 22
2000 255 255
TABLENAME 1 22
2000 255 255
TABLEID 1 13
0 255 255
COLUMNID 1 13
0 255 255
O_REF_CURSOR 2 5
0 255 255
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fsj25q8b52cb2, child number 0
-------------------------------------
select /*+ gather_plan_statistics */overload, decode(position,0,'RETURN_VALUE',nvl(argument_name,chr(0))) name,
decode(in_out,'IN',1,'IN/OUT',3,'OUT',decode(argument_name,null,6,2),1) direction, decode(data_type, 'BFILE',1, 'BLOB',2,
'CHAR',3, 'CLOB',4, 'DATE',6, 'FLOAT',13, 'INTERVAL YEAR TO MONTH',8, 'INTERVAL DAY TO SECOND',7, 'LONG',10, 'LONG RAW',9,
'NCHAR',11, 'NCLOB',12, 'NUMBER',13, 'NVARCHAR2',14, 'RAW',15, 'REF CURSOR',5, 'ROWID',16, 'TIMESTAMP',18, 'TIMESTAMP WITH
LOCAL TIME ZONE',19, 'TIMESTAMP WITH TIME ZONE',20, 'VARCHAR2',22,22) oracletype, decode(data_type, 'CHAR',2000,
'LONG',2147483647, 'LONG RAW',2147483647, 'NCHAR',4000, 'NVARCHAR2',4000, 'RAW',2000, 'VARCHAR2',2000,0) length,
nvl(data_precision, 255) precision, nvl(data_scale, 255) scale from all_arguments where data_level = 0 and data_type is
not null and owner = 'ADMIN' and object_name = 'P_CATALOGGETPRIMARYKEYS' order by overload, position
Plan hash value: 1154559427
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT ORDER BY | | 1 | 1 | 5 |00:00:00.12 | 1631 | 2048 | 2048 | 2048 (0)|
|* 2 | FILTER | | 1 | | 5 |00:00:00.11 | 1631 | | | |
|* 3 | HASH JOIN | | 1 | 1 | 5 |00:00:00.11 | 1631 | 927K| 927K| 1181K (0)|
| 4 | NESTED LOOPS | | 1 | 513 | 150 |00:00:00.01 | 676 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 6 | INDEX UNIQUE SCAN | I_USER1 | 1 | 1 | 1 |00:00:00.01 | 1 | | | |
|* 7 | TABLE ACCESS FULL | OBJ$ | 1 | 513 | 150 |00:00:00.01 | 674 | | | |
|* 8 | TABLE ACCESS FULL | ARGUMENT$ | 1 | 3620 | 71686 |00:00:00.07 | 955 | | | |
|* 9 | FIXED TABLE FULL | X$KZSPR | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
| 10 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 11 | FIXED TABLE FULL | X$KZSRO | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
|* 12 | INDEX RANGE SCAN | I_OBJAUTH1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("OWNER#"=USERENV('SCHEMAID') OR IS NOT NULL OR IS NOT NULL))
3 - access("O"."OBJ#"="A"."OBJ#")
filter(NVL("A"."PROCEDURE$","O"."NAME")='P_CATALOGGETPRIMARYKEYS')
6 - access("U"."NAME"='ADMIN')
7 - filter("OWNER#"="U"."USER#")
8 - filter(("A"."LEVEL#"=0 AND DECODE("A"."TYPE#",0,NULL,1,DECODE("A"."CHARSETFORM",2,'NVARCHAR2','VARCHAR2'),2,DECODE("
SCALE",(-127),'FLOAT','NUMBER'),3,'NATIVE INTEGER',8,'LONG',9,DECODE("A"."CHARSETFORM",2,'NCHAR
VARYING','VARCHAR'),11,'ROWID',12,'DATE',23,'RAW',24,'LONG RAW',29,'BINARY_INTEGER',69,'ROWID',96,DECODE("A"."CHARSETFORM",
2,'NCHAR','CHAR'),100,'BINARY_FLOAT',101,'BINARY_DOUBLE',102,'REF
CURSOR',104,'UROWID',105,'MLSLABEL',106,'MLSLABEL',110,'REF',111,'REF',112,DECODE("A"."CHARSETFORM",2,'NCLOB','CLOB'),113,'
BLOB',114,'BFILE',115,'CFILE',121,'OBJECT',122,'TABLE',123,'VARRAY',178,'TIME',179,'TIME WITH TIME
ZONE',180,'TIMESTAMP',181,'TIMESTAMP WITH TIME ZONE',231,'TIMESTAMP WITH LOCAL TIME ZONE',182,'INTERVAL YEAR TO
MONTH',183,'INTERVAL DAY TO SECOND',250,'PL/SQL RECORD',251,'PL/SQL TABLE',252,'PL/SQL BOOLEAN','UNDEFINED') IS NOT NULL))
9 - filter((((-"KZSPRPRV")=(-144) OR (-"KZSPRPRV")=(-141)) AND "INST_ID"=USERENV('INSTANCE')))
12 - access("OBJ#"=:B1 AND "GRANTEE#"="KZSROROL" AND "PRIVILEGE#"=12)
filter(("PRIVILEGE#"=12 AND "GRANTEE#"="KZSROROL"))
50 rows selected.
As you can see above there are two different execution plan for the same sql statement.
a. Using sql_id in dbms_xplan shows two execution plan with different plan hash value.
b. Executing same sql statement and getting execution plan using dbms_xplan for last execution shows only one plan which is not performing cartesian merge join.
This sql is causing high CPU usage on the server, hence i would like to request help for tuning this sql statement.
Let me know if you required more information.
-Yasser