Hi folks,
We are running benchmark and the heaviest CPU query was:
Code:
SELECT ec.tic_fk FROM t_enabled_channels ec, t_subs s
WHERE s.household_id = :1 AND s.sub_id = ec.sub_id;
The execution plan is:
Code:
SQL> SELECT ec.tic_fk FROM t_enabled_channels ec, t_subs s
2 WHERE s.household_id = 214967 and s.sub_id= ec.sub_id;
17 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5858 Card=20 Byt
es=400)
1 0 NESTED LOOPS (Cost=5858 Card=20 Bytes=400)
2 1 INDEX (RANGE SCAN) OF 'T_SUB_HOUSE_SUB_IDX' (INDEX) (Cos
t=2 Card=2 Bytes=20)
3 1 INDEX (FULL SCAN) OF 'PK_T_ENABLE_CHAN_TIC_FK_SUB_ID' (I
NDEX (UNIQUE)) (Cost=2928 Card=10 Bytes=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5858 consistent gets
0 physical reads
0 redo size
601 bytes sent via SQL*Net to client
507 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
17 rows processed
More info:
Code:
SQL> desc t_enabled_channels
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
SUB_ID NOT NULL NUMBER(20)
TIC_FK NOT NULL NUMBER(15)
SQL> desc t_subs
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
SUB_ID NOT NULL NUMBER(20)
USER_NAME VARCHAR2(30)
PASSWORD VARCHAR2(100)
F_NAME NOT NULL VARCHAR2(75)
L_NAME NOT NULL VARCHAR2(75)
OCCUPATION VARCHAR2(150)
COMPANY_NAME VARCHAR2(105)
GENDER VARCHAR2(30)
EMAIL VARCHAR2(150)
PERSONAL_ID VARCHAR2(60)
BIRTH_DATE DATE
TEL VARCHAR2(90)
PORT_PHONE VARCHAR2(90)
HOUSEHOLD_ID NOT NULL NUMBER(20)
PIN_CODE NOT NULL VARCHAR2(10)
MEMBER_TYPE NOT NULL NUMBER(1)
SMS NUMBER(1)
POINTER NUMBER(20)
CLUE_ANSWER VARCHAR2(300)
CLUE_QUESTION VARCHAR2(300)
SQL> select count (*) from t_subs;
COUNT(*)
----------
100012
SQL> select count (*) from t_enabled_channels;
COUNT(*)
----------
999987
SQL> select index_name,column_name
2 from user_ind_columns
3 where table_name=upper('t_enabled_channels');
INDEX_NAME COLUMN_NAME
------------------------------ ----------------------
PK_T_ENABLE_CHAN_TIC_FK_SUB_ID TIC_FK
PK_T_ENABLE_CHAN_TIC_FK_SUB_ID SUB_ID
SQL> select index_name,column_name
2 from user_ind_columns
3 where table_name=upper('t_subs');
INDEX_NAME COLUMN_NAME
------------------------------ ----------------------
PK_T_SUBS_SUB_ID SUB_ID
IND_SUBS_HOUSEHOLD_ID HOUSEHOLD_ID
IND_SUBS_USER_NAME USER_NAME
T_SUB_SUB_HOUSE_IDX SUB_ID
T_SUB_SUB_HOUSE_IDX HOUSEHOLD_ID
T_SUB_HOUSE_SUB_IDX HOUSEHOLD_ID
T_SUB_HOUSE_SUB_IDX SUB_ID
7 rows selected.
Is there a way to improve this query which took 56% CPU?
Thanks in advane.
Nir