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 Go to next message
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 #434517 is a reply to message #434512] Wed, 09 December 2009 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The question is why is it running 700 times per second?

Please carefully read the last message in Performances Tuning sticky and post accordingly.

Regards
Michel

[Updated on: Wed, 09 December 2009 08:42]

Report message to a moderator

Re: require to tune a sql query [message #434519 is a reply to message #434517] Wed, 09 December 2009 08:47 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member

It is a business requirment.
Re: require to tune a sql query [message #434521 is a reply to message #434512] Wed, 09 December 2009 08:56 Go to previous messageGo to next message
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 #434522 is a reply to message #434519] Wed, 09 December 2009 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I never heard a business requirement saying you have to execute this statement 700 times per second.

Regards
Michel
Re: require to tune a sql query [message #434528 is a reply to message #434512] Wed, 09 December 2009 09:51 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Index Partitioning local or global or both??
Next Topic: APPEND hint in Rule and Cost based optimizer
Goto Forum:
  


Current Time: Sun Sep 25 06:08:27 CDT 2016

Total time taken to generate the page: 0.09290 seconds