Re: query taking time

From: raja rao <raja4list_at_yahoo.com>
Date: Tue, 18 Oct 2005 19:48:13 -0700 (PDT)
Message-ID: <20051019024813.73031.qmail@web31403.mail.mud.yahoo.com>

Its a 2 CPU box with 2 GB memory.
degree set to 4 for the tbale.
max_parallel_server set to 5.

SQL> l
1 select index_name, column_name from user_ind_columns 2 where index_name in (
3 select index_name from user_indexes 4* where table_name = 'UNBILLED_REPORT') SQL> /

INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
UB_FN_CASETYPE                 PATIENT_CASE_TYPE
UNBIL_FN_RECD_DT               SYS_NC00051$
UNBIL_HSID                     HOSPITAL_ID

i am not sure what is the FTS, can u pl. elaborate. (my whole time taking when selecting for max(received_date).

if this can be tuned, the rest going fine.

can u pl. suggest.

Sami Seerangan <dba.orcl_at_gmail.com> wrote: We can look at different prospective to improve the performance

parallel query - What is harware configuration -- like number of CPU? What is the degree of your tables/indexes involved in the query? What is you max_parallel_server parameter set to?
Check whether FTS is required It is very difficult to say- without knowing all the indexed column and selectivity

etc

HTH
Sami

On 10/18/05, raja rao <raja4list_at_yahoo.com> wrote:Hi All,

Can someone help me in tuning the below query.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=457 Card=1 Bytes=53) 1 0 SORT (AGGREGATE)

   2    1     HASH JOIN (SEMI) (Cost=457 Card=23 Bytes=1219) 
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'UNBILLED_REPORT' (Co
          st=451 Card=2252 Bytes=58552)
   4    3         INDEX (RANGE SCAN) OF 'UNBIL_FN_RECD_DT' (NON-UNIQUE
          ) (Cost=49 Card=15763)
   5    4           SORT (AGGREGATE)
   6    5             TABLE ACCESS (FULL) OF 'UNBILLED_REPORT' (Cost=3
          208 Card=135108 Bytes=1621296)
   7    2       VIEW OF 'VW_NSO_1' (Cost=5 Card=1 Bytes=27)
   8    7         HASH JOIN (Cost=5 Card=1 Bytes=89)
   9    8           TABLE ACCESS (FULL) OF 'PATIENT_CASE_TYPE' (Cost=2
           Card=1 Bytes=49)
  10    8           TABLE ACCESS (FULL) OF 'HIM_PATIENT_CASE_TYPE' (Co
          st=2 Card=1 Bytes=40)



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

Yahoo! Music Unlimited - Access over 1 million songs. Try it free.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 18 2005 - 21:50:29 CDT