Home » RDBMS Server » Performance Tuning » Slow query performance...
icon9.gif  Slow query performance... [message #159296] Fri, 17 February 2006 03:58 Go to next message
loveoracle
Messages: 41
Registered: February 2006
Location: Mumbai
Member

Dear All;

My query takes long time to execute. it takes 12 minutes to execute.
My version is 9.0.0 and os is Win 2003 server.
Here I am sending details;
table structures(DDL):
1) Ibs_work_bankdata;
-- Create table
create table IBS_WORK_BANKDATA
(
  BD_YRQTR  VARCHAR2(50) not null,
  BD_BKCODE VARCHAR2(50) not null,
  BD_FORCD  VARCHAR2(50),
  BD_ALCD   VARCHAR2(50),
  BD_TYPECD VARCHAR2(50),
  BD_CURCD  VARCHAR2(50),
  BD_COUNCD VARCHAR2(50),
  BD_SECTCD VARCHAR2(50),
  BD_MATCD  VARCHAR2(50),
  BD_C_U_CD VARCHAR2(50),
  BD_S_U_CD VARCHAR2(50),
  BD_FC_BAL NUMBER(20,6),
  BD_FC_INT NUMBER(20,6),
  BD_RS_BAL NUMBER(20,6),
  BD_RS_INT NUMBER(20,6),
  BD_TYPE   VARCHAR2(10) default 'O'
)
tablespace USERS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index BD_CURCD_IDX on IBS_WORK_BANKDATA (BD_CURCD)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index IBS_WORK_BANKDATA_IDX on IBS_WORK_BANKDATA (BD_YRQTR, BD_BKCODE)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index IBS_WORK_BANKDATA_INDX on IBS_WORK_BANKDATA (BD_ALCD)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index IBS_WORK_BANK_COUNT_IDX on IBS_WORK_BANKDATA (BD_COUNCD)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

2) ibs_org_bankdata;
-- Create table
create table IBS_ORG_BANKDATA
(
  BD_YRQTR  VARCHAR2(50) not null,
  BD_BKCODE VARCHAR2(50) not null,
  BD_FORCD  VARCHAR2(50),
  BD_ALCD   VARCHAR2(50),
  BD_TYPECD VARCHAR2(50),
  BD_CURCD  VARCHAR2(50),
  BD_COUNCD VARCHAR2(50),
  BD_SECTCD VARCHAR2(50),
  BD_MATCD  VARCHAR2(50),
  BD_C_U_CD VARCHAR2(50),
  BD_S_U_CD VARCHAR2(50),
  BD_FC_BAL NUMBER(20,6),
  BD_FC_INT NUMBER(20,6),
  BD_RS_BAL NUMBER(20,6),
  BD_RS_INT NUMBER(20,6),
  BD_TYPE   VARCHAR2(10) default 'O'
)
tablespace USERS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index IBS_ORG_BANKDATA_IDX on IBS_ORG_BANKDATA (BD_YRQTR, BD_BKCODE)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

3)ibs_currencymaster:
-- Create table
create table IBS_CURRENCYMASTER
(
  CUR_ID           VARCHAR2(50),
  CUR_CODE         VARCHAR2(50),
  CUR_NAME         VARCHAR2(50),
  CUR_ACTIVE       NUMBER(1) default 1,
  CUR_CREATEDDATE  DATE default sysdate,
  CUR_CHANGEDDATE  DATE default sysdate,
  CUR_CHANGEDBY    VARCHAR2(50),
  CUR_ACCOUNT      NUMBER(1),
  CUR_REPORTS      NUMBER,
  CUR_USD_EXCHANGE NUMBER(10,5) default 0.0
)
tablespace RBI
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

4)ibs_currencyexchnagerate:
-- Create table
create table IBS_CURRENCYEXCHANGERATE
(
  CER_ID           VARCHAR2(50) not null,
  CER_YEARQTR      NUMBER not null,
  CER_CURRENCYID   VARCHAR2(50) not null,
  CER_EXCHANGERATE NUMBER(15,6),
  CER_ACTIVE       NUMBER(1) default 1,
  CER_CREATEDDATE  DATE default sysdate,
  CER_CHANGEDDATE  DATE default sysdate,
  CER_CHANGEDBY    VARCHAR2(50)
)
tablespace RBI
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table IBS_CURRENCYEXCHANGERATE
  add constraint CER_ID primary key (CER_ID)
  using index 
  tablespace RBI
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

Records in tables:
SQL> select count(*) from ibs_work_bankdata;

  COUNT(*)
----------
    120351

SQL> select count(*) from ibs_org_bankdata;

  COUNT(*)
----------
    116367

SQL> select count(*) from ibs_currencyexchangerate;

  COUNT(*)
----------
        58

SQL> select count(*) from ibs_currencymaster;

  COUNT(*)
----------
       178



Query:

select trim(wrk.bd_alcd) as ALCD,
                wrk.bd_typecd as TypeCD,
                wrk.bd_forcd as FORCD,
                wrk.bd_curcd as CURCD,
                wrk.bd_councd as COUNCD,
                wrk.bd_sectcd as SECCD,
                wrk.bd_matcd as MATCD,
                wrk.bd_c_u_cd as C_U_CD,
                wrk.bd_s_u_cd as S_U_CD,
                0 as Org_FCBal,
                0 as ORG_Bal,
                case
                  when wrk.bd_type = 'O' then
                   wrk.bd_fc_bal
                  else
                   0
                end as Main_FCBal,
                case
                  when wrk.bd_type = 'O' then
                   (wrk.bd_fc_bal * nvl(exchg.cer_exchangerate, 1))
                  else
                   0
                end as main_Bal,
                wrk.bd_rs_int,
                wrk.bd_rs_bal,
                wrk.bd_fc_int,
                wrk.bd_fc_bal,
                ' ' as TrackChangs
  from ibs_work_bankdata wrk
 inner join ibs_org_bankdata org ON org.bd_yrqtr = wrk.bd_yrqtr
                                and org.bd_bkcode = wrk.bd_bkcode
                                and org.bd_forcd = wrk.bd_forcd
                                and wrk.BD_YRQTR = 20044
                                and wrk.BD_BKCODE = '000'
                                and wrk.BD_ALCD = '51'
                                and wrk.BD_FORCD = 'IN'
                                and wrk.BD_TYPECD = '11'
  left join ibs_currencymaster curmst on curmst.cur_code = wrk.bd_curcd
  left join ibs_currencyexchangerate exchg on exchg.cer_currencyid =
                                              curmst.cur_id
                                          and exchg.cer_yearqtr = 20051
                                          and exchg.CER_ACTIVE = 1
union
select distinct trim(wrk.bd_alcd) as ALCD,
                wrk.bd_typecd as TypeCD,
                wrk.bd_forcd as FORCD,
                wrk.bd_curcd as CURCD,
                wrk.bd_councd as COUNCD,
                wrk.bd_sectcd as SECCD,
                wrk.bd_matcd as MATCD,
                ' ' as C_U_CD,
                ' ' as S_U_CD,
                0 as Org_FCBal,
                0 as ORG_Bal,
                case
                  when wrk.bd_type = 'O' then
                   wrk.bd_fc_bal
                  else
                   0
                end as Main_FCBal,
                case
                  when wrk.bd_type = 'O' then
                   (wrk.bd_fc_bal * nvl(exchg.cer_exchangerate, 1))
                  else
                   0
                end as main_Bal,
                wrk.bd_rs_int,
                wrk.bd_rs_bal,
                wrk.bd_fc_int,
                wrk.bd_fc_bal,
                ' ' as TrackChangs
  from ibs_work_bankdata wrk
 inner join ibs_org_bankdata org ON org.bd_yrqtr = wrk.bd_yrqtr
                                and org.bd_bkcode = wrk.bd_bkcode
                                and org.bd_forcd = wrk.bd_forcd
                                and wrk.BD_YRQTR = 20044
                                and wrk.BD_BKCODE = '000'
                                and wrk.BD_ALCD = '51'
                                and wrk.BD_FORCD = 'IN'
                                and wrk.BD_TYPECD = '11'
                                and wrk.bd_rs_bal > 0
  left join ibs_currencymaster curmst on curmst.cur_code = wrk.bd_curcd
  left join ibs_currencyexchangerate exchg on exchg.cer_currencyid =
                                              curmst.cur_id
                                          and exchg.cer_yearqtr = 20051
                                          and exchg.CER_ACTIVE = 1
 order by main_FCBal;
 


Explain Plan for query:

Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name              |  Rows | Bytes|  Cost  | Pstar
--------------------------------------------------------------------------------
| SELECT STATEMENT          |                    |    42 |    6K|    137 |
|  SORT UNIQUE              |                    |    42 |    6K|    110 |
|   UNION-ALL               |                    |       |      |        |
|    TABLE ACCESS BY INDEX R|IBS_ORG_BANKDATA    |   204 |    2K|      2 |
|     NESTED LOOPS          |                    |    41 |    6K|     28 |
|      HASH JOIN OUTER      |                    |     1 |  143 |     26 |
|       HASH JOIN OUTER     |                    |     1 |   93 |     24 |
|        TABLE ACCESS BY IND|IBS_WORK_BANKDATA   |     1 |   52 |     22 |
|         INDEX SKIP SCAN   |IBS_WORK_BANKDATA_I |     1 |      |      7 |
|        TABLE ACCESS FULL  |IBS_CURRENCYMASTER  |   178 |    7K|      1 |
|       TABLE ACCESS FULL   |IBS_CURRENCYEXCHANG |    19 |  950 |      1 |
|      INDEX RANGE SCAN     |IBS_ORG_BANKDATA_ID |   204 |      |      1 |
|    TABLE ACCESS BY INDEX R|IBS_ORG_BANKDATA    |   204 |    2K|      2 |
|     NESTED LOOPS          |                    |    41 |    6K|     28 |
|      HASH JOIN OUTER      |                    |     1 |  143 |     26 |
|       HASH JOIN OUTER     |                    |     1 |   93 |     24 |
|        TABLE ACCESS BY IND|IBS_WORK_BANKDATA   |     1 |   52 |     22 |
|         INDEX SKIP SCAN   |IBS_WORK_BANKDATA_I |     1 |      |      7 |

Plan Table
--------------------------------------------------------------------------------
|        TABLE ACCESS FULL  |IBS_CURRENCYMASTER  |   178 |    7K|      1 |
|       TABLE ACCESS FULL   |IBS_CURRENCYEXCHANG |    19 |  950 |      1 |
|      INDEX RANGE SCAN     |IBS_ORG_BANKDATA_ID |   204 |      |      1 |
--------------------------------------------------------------------------------


Please help me.
Thanks In advance.
Re: Slow query performance... [message #159300 is a reply to message #159296] Fri, 17 February 2006 04:10 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
never mind; misread the code

[Updated on: Fri, 17 February 2006 04:11]

Report message to a moderator

Re: Slow query performance... [message #159303 is a reply to message #159300] Fri, 17 February 2006 04:17 Go to previous messageGo to next message
loveoracle
Messages: 41
Registered: February 2006
Location: Mumbai
Member

Hi Frank,

I don't understand what do you want to say?
Colud you explain, please.
Re: Slow query performance... [message #159306 is a reply to message #159303] Fri, 17 February 2006 04:39 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
I made a reply based on a misreading of your code. I removed the (nonrelevant) contents of my reply.
Re: Slow query performance... [message #159381 is a reply to message #159306] Fri, 17 February 2006 22:37 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
and wrk.BD_YRQTR = 20044
and wrk.BD_BKCODE = '000'


wrk.BD_YRQTR is a VARCHAR, but you are treating it like a NUMBER. This causes CASTING, and means that the column is not used in the index scan - hence you get a SKIP SCAN rather than a RANGE SCAN. Add quotes to the constant '20044'.

Some of your indexes seem like they might have low cardinality. Unless each value in the index returns less than 1% of the table, it is ususally best to get rid of it.

It is bad form to put constant predicates (col = 'VAL') in an INNER JOIN clause. Move them to the WHERE clause.

How many rows in the WRK table match the constant predicates:
and wrk.BD_YRQTR = 20044
and wrk.BD_BKCODE = '000'
and wrk.BD_ALCD = '51'
and wrk.BD_FORCD = 'IN'
and wrk.BD_TYPECD = '11'
If less than 5% of the table, you might do better with an index including all 5 columns.

Your join condition on the two bigger tables involves three columns: bd_yrqtr, bd_bkcode, and bd_forcd. You are also providing constant predicates on these three columns. It doesn't look like these three form a unique key. Say you get 100 rows from one table and 105 from the second table, your join will return 10500 rows! Is that what you want? If so, then that's probably the reason why it's slow.
The fact that you have used DISTINCT clauses further reinforces this theory - you were getting duplicates returned and used DISTINCT to get rid of them.
Unless you include more columns in the join to improve the join cardinality, you will never get this running fast.
Also, you only have two of the three join columns indexed in the ORG table. You would do better to have an index that includes all three columns.
_____________
Ross Leishman

Previous Topic: Commit time format in retention table
Next Topic: how to set DB_BLOCK_SIZE??????help meeeeeee......
Goto Forum:
  


Current Time: Thu Mar 28 20:56:41 CDT 2024