Home » RDBMS Server » Performance Tuning » require to tune a sql query (oracle,10.1.0.4.0,Sun solaris 5.10)
| require to tune a sql query [message #434512] |
Wed, 09 December 2009 08:30  |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
I have a sql query given below.
select a.id ,nvl(msisdn,' ') ,nvl(group_ID,' ') ,nvl(email_address,' ') ,nvl(xml_attributes,' ') ,status_collected ,
nvl(product_base,' ') ,nvl(dealer_name,' ') into :b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7
from master_lab a, product b where (((a.sid=:b8 and a.line_status='A') and
b.id=a.id) and b.status='A')
It is running 700 times per second.
The problem is we are having very high IO in master_lab table. As part of the maintanence activity, we removed the chained rows from this table.
And also we moved this table to another tablespace. But there is no any much improvement.
Can anybody help us to reduce the IO wait? and also is there any possiblity to tune this query?
Explan plan:
============
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1074 | 27 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID | product | 1 | 10 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 6 | 1074 | 27 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| master_lab | 5 | 845 | 12 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | SID_INDX | 9 | | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | ID_INDX | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("b"."STATUS"='A')
3 - filter("a"."LINE_STATUS"='A')
4 - access("a"."SID"=:B8)
5 - access("b"."ID"="a"."ID")
[added code tags]
[Updated on: Wed, 09 December 2009 08:43] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: require to tune a sql query [message #434521 is a reply to message #434512] |
Wed, 09 December 2009 08:56  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
select a.id
,nvl(msisdn,' ')
,nvl(group_ID,' ')
,nvl(email_address,' ')
,nvl(xml_attributes,' ')
,status_collected
,nvl(product_base,' ')
,nvl(dealer_name,' ')
into :b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7
from master_lab a
,product b
where a.sid=:b8
and a.line_status='A'
and b.id=a.id
and b.status='A')
Can you let us know what indexes are on the two tables, and which columns come from which tables
|
|
|
|
|
|
| Re: require to tune a sql query [message #434528 is a reply to message #434512] |
Wed, 09 December 2009 09:51 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
The index details are given below. This query is fired from LDAP process which is firing from LDAP server. Thats'y the execution is high.
INDEX_NAME COLUMN_NAME TABLE_NAME
------------------------------ ---------------------------------- -----------
ICL_RBSID_INDX RBSID MASTER_LAB
OLD_SID_0_INDX OLD_SID_0 MASTER_LAB
OLD_SID_1_INDX OLD_SID_1 MASTER_LAB
VI$1260355800668$01 LINE_STATUS MASTER_LAB
PK_ID ID MASTER_LAB
C_ID_INDX C_ID MASTER_LAB
MSISDN_INDX MSISDN MASTER_LAB
SID_INDX SID MASTER_LAB
GROUP_ID_INDX GROUP_ID MASTER_LAB
ID_INDX ID PRODUCT
BTI_EMAIL_INDX BTI_EMAIL PRODUCT
ORDER_REF_INDX ORDER_REF PRODUCT
DEALER_IDX DEALER_NAME PRODUCT
P_ID_INDX P_ID PRODUCT
PK_CLP_ID CLP_ID PRODUCT |
|
|
|
| Re: require to tune a sql query [message #434530 is a reply to message #434528] |
Wed, 09 December 2009 10:03 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
select a.id
,nvl(a.msisdn,' ')
,nvl(a.group_ID,' ')
,nvl(email_address,' ')
,nvl(xml_attributes,' ')
,status_collected
,nvl(product_base,' ')
,nvl(dealer_name,' ')
into :b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7
from master_lab a
,product b
where a.sid=:b8
and a.line_status='A'
and b.id=a.id
and b.status='A')
A step in the right direction would be to create indexes on MASTER_LAB(SID,STATUS,ID) and PRODUCT(ID,STATUS)
A further step would be to add further columns onto these two indexes such that every column that was returned by the query was in one of these two indexes - this way your query woudl never need to access the table at all.
Better yet (assuming that DML activity on the table is fairly low) would to be to create an ON COMMIT REFRESH materialized view based on this query, and create a single index on that with SID as the leading column, and all the other columns in that index.
|
|
|
|
| Re: require to tune a sql query [message #434534 is a reply to message #434512] |
Wed, 09 December 2009 10:43 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
The status column in product table and line_status column in master_lab table contains only "A" and "B" .So will there be any improvement, if we create an index in this column? because I tested this already as I created a normal index only to status column. But no improvement.
and let me know why we need to add all the other columns (given in select query) also in that index?. As for as my understanding is we need to create an index for the columns are in where clause for the better performance. As I am new to oracle,please correct me if I am wrong.
Yes. the update is considerably low in the master_lap table. should I create a meterialized view in the same database itself? I belive we can have the significant performance improvement from the meterialized view, when we create this in the remote db.
|
|
|
|
| Re: require to tune a sql query [message #434535 is a reply to message #434534] |
Wed, 09 December 2009 10:46 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
Post DDL for both tables
SELECT a.ID,
Nvl(msisdn,' '),
Nvl(group_id,' '),
Nvl(email_address,' '),
Nvl(xml_attributes,' '),
status_collected,
Nvl(product_base,' '),
Nvl(dealer_name,' ')
INTO :b0,:b1,:b2,:b3,
:b4,:b5,:b6,:b7
FROM master_lab a,
product b
WHERE (((a.sid = :b8
AND a.line_status = 'A')
AND b.ID = a.ID)
AND b.status = 'A')
|
|
|
|
| Re: require to tune a sql query [message #434542 is a reply to message #434512] |
Wed, 09 December 2009 11:24 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
The DDL for both the table is given below.
CREATE TABLE "OTD"."MASTER_LAB"
( "ID" NUMBER(38,0) NOT NULL ENABLE,
"C_ID" NUMBER(38,0) NOT NULL ENABLE,
"LINE_STATUS" VARCHAR2(1) NOT NULL ENABLE,
"STATUS_COLLECTED" VARCHAR2(1) NOT NULL ENABLE,
"START_DATE" DATE NOT NULL ENABLE,
"MSISDN" VARCHAR2(15),
"STOP_DATE" DATE,
"OLD_DN" VARCHAR2(15),
"DN_CHANGE_DATE" DATE,
"WSALE_PROD_CHANGE_DATE" DATE,
"SID" VARCHAR2(21),
"GROUP_ID" VARCHAR2(79),
"CBUK" VARCHAR2(12),
"INST_ID" VARCHAR2(4),
"PRODUCT_BASE" VARCHAR2(50),
"OLD_WHOLESALE_PRODUCT" VARCHAR2(50),
"EMAIL_ADDRESS" VARCHAR2(128),
"XML_ATTRIBUTES" VARCHAR2(256),
"FTR" NUMBER,
"USAGE_MANAGER_SUPPRESS_DATE" DATE,
"NETWORK_TYPE" VARCHAR2(4),
"RBSC_CUSTOMER_TYPE" VARCHAR2(3),
"BB_ABUSE_FLAG" VARCHAR2(1),
"BB_DEBT_FLAG" VARCHAR2(1),
"RBSID" VARCHAR2(44),
"OLD_SID_0" VARCHAR2(21),
"SID_CHANGE_0_DATE" DATE,
"OLD_SID_1" VARCHAR2(21),
"SID_CHANGE_1_DATE" DATE,
CONSTRAINT "PK_ID" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 178257920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "IND" ENABLE,
CONSTRAINT "CUST_ID_FK" FOREIGN KEY ("C_ID")
REFERENCES "OTD"."ISP_CUST" ("C_ID") ENABLE
) PCTFREE 30 PCTUSED 60 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1106247680 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA"
CREATE TABLE "OTD"."PRODUCT"
( "CLP_ID" NUMBER(38,0) NOT NULL ENABLE,
"ID" NUMBER(38,0) NOT NULL ENABLE,
"P_ID" NUMBER(38,0) NOT NULL ENABLE,
"STATUS" VARCHAR2(1) NOT NULL ENABLE,
"START_DATE" DATE NOT NULL ENABLE,
"STOP_DATE" DATE,
"PREV_CLP_ID" NUMBER(38,0),
"NEXT_CLP_ID" NUMBER(38,0),
"ORDER_REF" VARCHAR2(50),
"BTI_EMAIL" VARCHAR2(62),
"DEALER_NAME" VARCHAR2(32),
"OLD_DEALER_NAME" VARCHAR2(32),
"DEALER_CHANGE_DATE" DATE,
"DEALER_EXPIRY_DATE" DATE,
"THRESHOLD_REACHED" NUMBER(38,0),
"EMAIL_THRESHOLD_REACHED" NUMBER(38,2),
"OLD_DEALER_EXPIRY_DATE" DATE,
CONSTRAINT "PK_CLP_ID" PRIMARY KEY ("CLP_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 183500800 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "IND" ENABLE,
CONSTRAINT "ID_FK" FOREIGN KEY ("ID")
REFERENCES "OTD"."MASTER_LAB" ("ID") ENABLE,
CONSTRAINT "P_ID_FK" FOREIGN KEY ("P_ID")
REFERENCES "OTD"."ISP_PRODUCT" ("P_ID") ENABLE
) PCTFREE 30 PCTUSED 60 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1006632960 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ISPS_TAB" |
|
|
|
| Re: require to tune a sql query [message #434718 is a reply to message #434534] |
Thu, 10 December 2009 05:58 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
This is the fruustrating thing about trying to help people tune their queries - getting information out of them is like getting blood out of a stone.
Quote:The status column in product table and line_status column in master_lab table contains only "A" and "B" .So will there be any improvement, if we create an index in this column? because I tested this already as I created a normal index only to status column. But no improvement.
Quite right - if you'd mentioned that there were only two values, then my advice would have been to see what the distribution of values across the two statuses was, and if the one you were looking for had relatively few values, then generate histogram statistics on that table and try the index.
Quote:and let me know why we need to add all the other columns (given in select query) also in that index?. As for as my understanding is we need to create an index for the columns are in where clause for the better performance. As I am new to oracle,please correct me if I am wrong.
You are not wrong, but your information is incomplete.
If Oracle can fulfill all of the requirements of the query from columns available in indexes then it won't bother going to the tables - so rather than accessing the index and then accessing each required row from the table, you can get away with just index access, speeding up the query.
Quote:Yes. the update is considerably low in the master_lap table. should I create a meterialized view in the same database itself? I belive we can have the significant performance improvement from the meterialized view, when we create this in the remote db.
All of a sudden this has turned into a distributed transaction problem.
I have no idea what databases you're accessing this query from, because you felt no need to impart this information.
I'd create and index the Mview on the database that these two tables exist on.
|
|
|
|
Goto Forum:
Current Time: Sat May 18 08:20:46 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
