query performance

From: Harvinder Singh <Harvinder.Singh_at_MetraTech.com>
Date: Thu, 16 Aug 2001 13:04:41 -0700
Message-ID: <F001.0036DEFA.20010816131550@fatcity.com>

Hi,

I am running the follwoing query and it is running very slow:

select * from v_mci_com_leader_summary where accountid=1011 and intervalid=1490

EXPLAIN PLAN looks like:

        ID Query Plan
---------- -----------------------------------------------------------

         0   SELECT STATEMENTCost=402437
         1     VIEW V_MCI_COM_LEADER_SUMMARY
         2       SORT
         3         HASH JOIN
         4           VIEW T_VW_AUDIOCONFERENCECALL
         5             UNION-ALL
         6               NESTED LOOPS
         7                 TABLE ACCESS T_ACC_USAGE_1
         8                 TABLE ACCESS T_PV_AUDIOCONFERENCECALL_1
         9                   INDEX PK_T_PV_AUDIOCONFERENCECALL_1
        10               NESTED LOOPS
        11                 TABLE ACCESS T_ACC_USAGE_2
        12                   INDEX IDX_T_ACC_USAGE_2
        13                 TABLE ACCESS T_PV_AUDIOCONFERENCECALL_2
        14                   INDEX PK_T_PV_AUDIOCONFERENCECALL_2
        15               NESTED LOOPS
        16                 TABLE ACCESS T_ACC_USAGE_3
        17                   INDEX IDX_T_ACC_USAGE_3
        18                 TABLE ACCESS T_PV_AUDIOCONFERENCECALL_3
        19                   INDEX PK_T_PV_AUDIOCONFERENCECALL_3
        20           VIEW V_MCI_COM_FEATURE_COUNT
        21             UNION-ALL
        22               SORT
        23                 NESTED LOOPS
        24                   NESTED LOOPS
        25                     INDEX PK_T_PV_FEATURECHARGES_1
        26                     TABLE ACCESS T_ACC_USAGE_1
        27                       INDEX PK_T_ACC_USAGE_1
        28                   INDEX PK_T_PV_AUDIOCONFERENCECALL_1
        29               SORT
        30                 NESTED LOOPS
        31                   NESTED LOOPS
        32                     INDEX PK_T_PV_FEATURECHARGES_2
        33                     TABLE ACCESS T_ACC_USAGE_2
        34                       INDEX PK_T_ACC_USAGE_2
        35                   INDEX PK_T_PV_AUDIOCONFERENCECALL_2
        36               SORT
        37                 NESTED LOOPS
        38                   NESTED LOOPS
        39                     INDEX PK_T_PV_FEATURECHARGES_3
        40                     TABLE ACCESS T_ACC_USAGE_3
        41                       INDEX PK_T_ACC_USAGE_3
        42                   INDEX PK_T_PV_AUDIOCONFERENCECALL_3

v_mci_com_leader_summary is a view based on 2 views t_vw_AudioConferenceCall , v_mci_com_feature_count                 

t_vw_AudioConferenceCall is also a view and based on tables   T_ACC_USAGE_1
  T_PV_AUDIOCONFERENCECALL_1
T_ACC_USAGE_2,T_PV_AUDIOCONFERENCECALL_2   T_ACC_USAGE_3,
  T_PV_AUDIOCONFERENCECALL_3 AND v_mci_com_feature_count is also a view and based on tables

T_PV_FEATURECHARGES_1 
T_ACC_USAGE_1 
T_PV_AUDIOCONFERENCECALL_1 
T_PV_FEATURECHARGES_2 , 
T_ACC_USAGE_2 ,   
T_PV_AUDIOCONFERENCECALL_2       
T_PV_FEATURECHARGES_3 , 
T_ACC_USAGE_3 ,   
T_PV_AUDIOCONFERENCECALL_3       

ROWS in all the tables involved in views is about 150,000 all the tables r analyzed with compute.
all the indexes r rebuild at frequent intervals. SGA size is 250M , all data on RAID 5 compaq machines..

How can i improve the performance.

Thanks
-Harvinder
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Harvinder Singh
  INET: Harvinder.Singh_at_MetraTech.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Aug 16 2001 - 15:04:41 CDT