Home » RDBMS Server » Performance Tuning » help need in optimizing a query....
help need in optimizing a query.... [message #201901] Tue, 07 November 2006 05:10
Messages: 84
Registered: April 2005
Location: India

I have this below query which automatically gets generated in siebel.
Problem is when i execute this query in toad for "Nichole" it took 2 mins to return 30 records but when i execute this same query for "Adam" it returns more records than "Nichole" and return results in 1-2 secs. I know executing query in toad is not a proper way but this below query will get executed in same way thru siebel application. Histogram is already created on column "Name" but of no help. Composite Index(CX_AA_EVNT_RADIO_BATCH_TIME) is there on column bot_id & timestp.
Table s_org_ext has only 5 records of "Nichole" and 10 records of "Adam".

Will including Column "Name" in Above composite index can help?

Please anybody can suggest me anything in this? whatelse i can do to tune this query?

Thanks in advance
Chandan Singh

SELECT   t7.conflict_id, t7.last_upd, t7.created, t7.last_upd_by,
         t7.created_by, t7.modification_num, t7.row_id, t7.last_upd,
         t7.aa_acount_id, t7.aa_last_step, t7.accessory, t7.account_exists,
         t7.account_number, t7.actn_cd, t7.action_counter, t7.activated_by,
         t7.address, t7.address_2, t7.address_validation_status,
         t4.account_name, t7.aggregated_account_id, t7.aggregated_flag,
         t7.asset_id, t7.auto_delivery_type, t7.auto_make, t7.auto_model,
         t7.auto_year, t7.automated_approval, t7.activation_frequency,
    FROM siebel.s_prod_int t1,
         siebel.s_org_ext t2,
         siebel.s_asset t3,
         siebel.av_account t4,
         siebel.s_org_ext t5,
         siebel.s_prod_int_x t6,
         siebel.av_event t7
   WHERE t1.row_id = t6.par_row_id(+)
     AND t7.promotion_code = t1.part_num(+)
     AND t7.asset_id = t3.row_id(+)
     AND t3.owner_accnt_id = t5.par_row_id(+)
     AND t7.id = t2.par_row_id                    --- S_ORG_EXT_U3
     AND t7.id1 = t4.row_id(+)
     AND (t2.NAME = 'Nichole')
ORDER BY t7.bot_id, t7.timestp

Below is the execution plan for above query.

Execution Plan
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=399904 Card=5609601 Bytes=4930839279)
   1    0   NESTED LOOPS (OUTER) (Cost=399904 Card=5609601 Bytes=4930839279)
   2    1     NESTED LOOPS (OUTER) (Cost=343808 Card=5609601 Bytes=4824256860)
   3    2       NESTED LOOPS (OUTER) (Cost=231616 Card=5609601 Bytes=4740112845)
   4    3         NESTED LOOPS (OUTER) (Cost=175520 Card=5609601 Bytes=4482071199)
   5    4           NESTED LOOPS (OUTER) (Cost=119424 Card=5609601 Bytes=4325002371)
   6    5             NESTED LOOPS (Cost=63328 Card=5609601 Bytes=4240858356)
   7    6               TABLE ACCESS (BY INDEX ROWID) OF 'CX_AA_EVNT_RADIO' (Cost=6729 Card=5659910 Bytes=4126074390)
   8    7                 INDEX (FULL SCAN) OF 'CX_AA_EVNT_RADIO_BATCH_TIME' (NON-UNIQUE) (Cost=24559 Card=5659910)
   9    6               TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT' (Cost=1 Card=1 Bytes=27)
  10    9                 INDEX (UNIQUE SCAN) OF 'S_ORG_EXT_U3' (UNIQUE)
  11    5             TABLE ACCESS (BY INDEX ROWID) OF 'S_PROD_INT' (Cost=1 Card=1 Bytes=15)
  12   11               INDEX (RANGE SCAN) OF 'S_PROD_INT_M3' (NON-UNIQUE)
  13    4           TABLE ACCESS (BY INDEX ROWID) OF 'CX_AA_ACCNT' (Cost=1 Card=1 Bytes=28)
  14   13             INDEX (UNIQUE SCAN) OF 'CX_AA_ACCNT_P1' (UNIQUE)
  15    3         TABLE ACCESS (BY INDEX ROWID) OF 'S_ASSET' (Cost=1 Card=1 Bytes=46)
  16   15           INDEX (UNIQUE SCAN) OF 'S_ASSET_P1' (UNIQUE)
  17    2       TABLE ACCESS (BY INDEX ROWID) OF 'S_PROD_INT_X' (Cost=1 Card=1 Bytes=15)
  18   17         INDEX (RANGE SCAN) OF 'S_PROD_INT_X_U1' (UNIQUE) (Cost=1 Card=1)
  19    1     TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT' (Cost=1 Card=1 Bytes=19)
  20   19       INDEX (UNIQUE SCAN) OF 'S_ORG_EXT_U3' (UNIQUE)

Below is the No. of records in each table.

5,594,027.00	09/08/2006 03:03:02	CX_AA_ACCNT	 92,565.00
6,659,910.00	09/22/2006 16:14:06	CX_AA_EVNT_RADIO 608,292.00
15,384,080.00	09/22/2006 15:28:04	S_ASSET	         1,244,124.00
10,249,905.00	04/23/2006 23:51:44	S_ORG_EXT	 2,313,814.00
1,081.00	09/22/2006 15:26:12	S_PROD_INT	 129.00
602.00	        09/08/2006 04:39:42	S_PROD_INT_X	 10.00
Previous Topic: Query Optimization problem...
Next Topic: Index not used in outer join query
Goto Forum:

Current Time: Thu Aug 24 05:48:55 CDT 2017

Total time taken to generate the page: 0.06658 seconds