RE: Help - Query running slow- URGENT (misused)

Your wish is granted... you will be working on NT for the rest of your DBA life.. :P

If there is something not working, first thing in NT.... Close the windows and open the windows again! :P

Winnie

"Thapliyal, Deepak" <DThapliyal_at_ea.com> on 05/05/2000 04:34:15 PM

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Winnie Liu/HQ/ISC)

Subject: RE: Help - Query running slow- URGENT (misused)

i got error during startup when i try to use this .. please help .. urgent :-)))

PS:
see .. thats why i always "wished" i was using NT .. that way in case of slow queries
i could say ctl-alt-del and then performance would suddenly improve ...

(god pl. dont grant this wish mine though!!)

have a good one all of ya'all!!

deepAk

-----Original Message-----
Sent: Friday, May 05, 2000 3:26 PM
To: Multiple recipients of list ORACLE-L

did u try the _make_sql_run_faster undocumented init.ora parm?

what version of the database you on, since it works different on each version.

8.0 = _make_sql_run_faster = true
8.1 = _make_sql_run_faster= #  where # is a number from 1 to 9
8.2 = _make_sql_run_faster=unlimited  absolute speed.

<SHAMELESS PLUG COMING UP>

everyone have a good week next week, for those of you going to IOUG-A, come and
see me at my logminer presentation on mon from 3:15 - 4:15

the above init.ora joke was brought to you by:

Joe, the not so kind or gentle DBA

salu Ullah wrote:

> Hello rajgopal,
>
> How can i make it run fast, since indexing on a column using function
> is not giving a good response. Any other alternate that i can use in my
> query??
>
> Thanks
>
> Salman
>
> >From: "Rajagopal Venkataramany" <rajagopalvr_at_hotmail.com>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: Re: Help - Query running slow
> >Date: Thu, 04 May 2000 18:27:11 -0800
> >
> >Salu
> >
> > The slowness in the response time is because of the RTRIM function
> > on MGMT_CD, ACCT_CD, and MRKT_CD (assuming u have an index on them).
> >
> > For your understanding, when you qualify any indexed column with
> > any functions then the optimizer would ignore those indexed columns.
> >
> > Some Observations :
> >
> >1.
> > Since you want distinct emp_id, this query MAY return more than
> > one value if your WHERE condition encounters different emp_id.
> > In such cases the query will fail with 'TOO MANY ROWS' error.
> >
> >2.
> > Since there is no EXCEPTION handling, this function may abort incase
> > it does not find any HIT.
> >
> >You may look into this.
> >
> >Thanks
> >Rajagopal Venkataramany
> >
> >----Original Message Follows----
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Date: Thu, 04 May 2000 16:07:04 -0800
> >
> >Hello,
> >
> >I'am getting a very slow response on the query that calls a function
after
> >getting a variable.
> >The query is used in pert script.
> >The function after getting the variable do a select statement & return
a
> >value to the query. The response time using the
> >fuction in a query is very slow. If i type the value & run the same
query
> >it
> >runs very fast.
> >Almost twice as fast as running with a function.
> >Cant figure out what's causing it slow...is it the rtrim iam using?????
> >I also created an index on these columns but no improvement.
> >
> >Any input will be appreciated
> >
> >Below is the funtion iam using:
> >
> >create or replace funtion e_conv(empid VARCHAR2, name varchar2)
> >RETURN VARCHAR2 IS
> > ret_empid VARCHAR2(6);
> > BEGIN
> > if name = 'MGMT' then
> > SELECT DISTINCT emp_id INTO ret_empid
> > FROM emp_code
> > WHERE rtrim(MGMT_CD) = empid;
> >
> > elsif name = 'ACCT' then
> > SELECT DISTINCT emp_id INTO ret_empid
> > FROM emp_code
> > WHERE rtrim(ACCT_CD) = empid;
> >
> > elsif name = 'MRKT' then
> > SELECT DISTINCT emp_id INTO ret_empid
> > FROM emp_code
> > WHERE rtrim(MRKT_CD) = empid;
> >
> > end if;
> > RETURN ret_empid;
> >END;
> >
> >
> >Salman
> >________________________________________________________________________
> >Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
> >
> >--
> >Author: salu Ullah
> > INET: salu_ullah_at_hotmail.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).
> >
> >________________________________________________________________________
> >Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
> >
> >--
> >Author: Rajagopal Venkataramany
> > INET: rajagopalvr_at_hotmail.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).
>
> ________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
>
> --
> Author: salu Ullah
> INET: salu_ullah_at_hotmail.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).

--
Author: Joseph S. Testa
  INET: teci_at_oracle-dba.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).
--
Author: Thapliyal, Deepak
  INET: DThapliyal_at_ea.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