Hi Experts,
I am having an issue in tuning a query. Will be thankful for some pointers or analysis on the scenario - I tried some options that so far didnt work out. Details of table involved , their scripts, the query that has issue and the actual execution (which is very bad) are all as below. The table access_grp has about 13 million rows whereas the table access_trp_typ has just 15 rows (a look up table).
--The table access_grp has about *13 million rows* whereas the table access_trp_typ has just 15 rows (a look up table).
CREATE TABLE ACCESS_GRP
(
GRP_ID NUMBER(12) NOT NULL,
CLIENT_ID VARCHAR2(16 BYTE) NOT NULL,
GRP_NM VARCHAR2(70 CHAR) NOT NULL,
GRP_DESC VARCHAR2(100 CHAR),
GRP_TYP_ID NUMBER(12) NOT NULL,
PARENT_GROUP_OID NUMBER(12) NOT NULL,
IS_ACTIVE NUMBER(1),
IS_BUILTIN NUMBER(1) NOT NULL,
IS_HIDDEN NUMBER(1) NOT NULL,
CREATED_DATE DATE,
CREATED_BY VARCHAR2(80 BYTE),
MODIFIED_DATE DATE,
MODIFIED_BY VARCHAR2(80 BYTE),
APPLN_ID VARCHAR2(50 BYTE)
)
TABLESPACE DATA1;
CREATE UNIQUE INDEX ACS_GROUP_IDX01 ON ACCESS_GRP
(CLIENT_ID, GRP_NM, APPLN_ID)
TABLESPACE INDEX1;
CREATE INDEX ACS_GROUP_IDX02 ON ACCESS_GRP
(GRP_TYP_ID)
TABLESPACE INDEX1;
CREATE INDEX ACS_GROUP_IDX03 ON ACCESS_GRP
(PARENT_GROUP_OID)
TABLESPACE INDEX1;
CREATE UNIQUE INDEX PK_ACS_GROUP ON ACCESS_GRP
(GRP_ID)
TABLESPACE INDEX1;
ALTER TABLE ACCESS_GRP ADD (
CONSTRAINT PK_ACS_GROUP
PRIMARY KEY
(GRP_ID)
USING INDEX
TABLESPACE INDEX1);
CREATE TABLE ACCESS_GRP_TYP
(
GRP_TYP_ID NUMBER(3) NOT NULL,
TYP_NAME VARCHAR2(40 BYTE) NOT NULL,
TYP_DESC VARCHAR2(50 BYTE) NOT NULL,
MASTER_TYPE VARCHAR2(20 BYTE) NOT NULL
)
TABLESPACE DATA1;
CREATE UNIQUE INDEX ACS_GROUP_TYPE_IDX01 ON ACCESS_GRP_TYP
(TYP_NAME)
TABLESPACE INDEX1;
CREATE UNIQUE INDEX PK_ACS_GROUP_TYPE ON ACCESS_GRP_TYP
(GRP_TYP_ID)
TABLESPACE INDEX1;
ALTER TABLE ACCESS_GRP_TYP ADD (
CONSTRAINT PK_ACS_GROUP_TYPE
PRIMARY KEY
(GRP_TYP_ID)
USING INDEX
TABLESPACE INDEX1);
variable SYS_B_1 VARCHAR2(100) ;
variable SYS_B_2 VARCHAR2(100) ;
variable SYS_B_3 VARCHAR2(100) ;
variable SYS_B_0 NUMBER ;
exec :SYS_B_1 := 'G32M2PRX1KXFXJDN';
exec :SYS_B_2 := 'TAXAMOUNTS';
exec :SYS_B_3 := 'EMPLOYEE';
exec :SYS_B_0 := 1;
SELECT DISTINCT G.grp_id,
G.CLIENT_ID,
G.grp_nm,
G.grp_desc,
G.grp_typ_id,
G.IS_ACTIVE,
G.IS_BUILTIN,
G.PARENT_grp_id,
G.MODIFIED_DATE,
G.MODIFIED_BY,
G.CREATED_DATE,
G.CREATED_BY,
G.IS_HIDDEN,
G.APPLN_ID,
GT.TYP_NAME,
GT.TYP_DESC,
GT.MASTER_TYPE
FROM access_grp G INNER JOIN access_grp_TYP GT
ON G.grp_typ_id = GT.grp_typ_id
WHERE G.CLIENT_ID = :SYS_B_1 AND G.APPLN_ID = :SYS_B_2 AND G.IS_BUILTIN = :"SYS_B_0"
CONNECT BY PRIOR G.PARENT_grp_id = G.grp_id
START WITH UPPER (GT.TYP_NAME) IN (:SYS_B_3)
ORDER BY G.grp_id ASC
/
--trcf11.log
Plan hash value: 1497114050
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 17440 (100)| |
| 1 | SORT UNIQUE | | 33094 | 4977K| 6312K| 16308 (1)| 00:03:16 |
|* 2 | FILTER | | | | | | |
|* 3 | CONNECT BY WITH FILTERING (UNIQUE)| | | | | | |
| 4 | NESTED LOOPS | | | | | | |
| 5 | NESTED LOOPS | | 16547 | 2197K| | 4927 (1)| 00:01:00 |
|* 6 | TABLE ACCESS FULL | ACCESS_GRP_TYP | 1 | 40 | | 3 (0)| 00:00:
|* 7 | INDEX RANGE SCAN | ACS_GROUP_IDX02 | 110K| | | 296 (2)| 00:00:0
| 8 | TABLE ACCESS BY INDEX ROWID | ACCESS_GRP | 110K| 10M| | 4924 (1)|
|* 9 | HASH JOIN | | 16547 | 2407K| | 10249 (2)| 00:02:03 |
| 10 | TABLE ACCESS FULL | ACCESS_GRP_TYP | 15 | 600 | | 3 (0)| 00:00:0
|* 11 | HASH JOIN | | 16547 | 1761K| | 10246 (2)| 00:02:03 |
| 12 | CONNECT BY PUMP | | | | | | |
| 13 | TABLE ACCESS FULL | ACCESS_GRP | 1323K| 121M| | 5302 (2)| 00:01:
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("G"."CLIENT_ID"=:SYS_B_1 AND "G"."APPLN_ID"=:SYS_B_2 AND "G"."IS_BUILTIN"=:SYS_B_0))
3 - access("G"."GRP_ID"=PRIOR NULL)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
6 - filter(UPPER("GT"."TYP_NAME")=:SYS_B_3)
7 - access("G"."GRP_TYP_ID"="GT"."GRP_TYP_ID")
9 - access("G"."GRP_TYP_ID"="GT"."GRP_TYP_ID")
11 - access("connect$_by$_pump$_004"."PRIOR G.PARENT_grp_id "="G"."GRP_ID")
Will be thankful for help on the issue.
Thanks,