two
functions versus one maybe
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]Sent: woensdag 5 maart 2003
7:34To: Multiple recipients of list ORACLE-LSubject:
Slow queryDear
all, Please find below the 2
queries : select dep_date from
test_date1
Which version of Oracle ?
The second query has to do a trunc() and a to_date()
on every single row. The first only has to do a to_char
on every single row. There is a CPU difference between
the two queries and if you run the execution plan through
v9 with cpu_costing, you will find a
Dear all,
Please find below the 2 queries :
select dep_date from test_date1
where to_char(dep_date,'dd-mon-') =
'12-jan-2002'
select dep_date from test_date1
where TRUNC(dep_date) = to_DATE(
'12-jan-2002','dd-mon-')
The execution plan for both the queries shows a FTS
on test_date1.
A small follow up question:
Why will oracle do a to_date() on second query for each row returned, as the
date mentioned is a literal and doesn't change for the duration of the query.
Won't it be calculated once and used for all the rows with trunc() applied to
each row?
Regards
Naveen
You would have thought to, but it
doesn't seem to be the case.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )
UK___March 19th
Dear all,
Please find below the 2 queries :
select dep_date from test_date1
where to_char(dep_date,'dd-mon-') = '12-jan-2002'
select dep_date from test_date1
where TRUNC(dep_date) = to_DATE( '12-jan-2002','dd-mon-')
The execution plan for both the queries shows a FTS on test_date1.
Hi Listers,
I have the below query which is taking almost 3 min. to execute.
Can anybody help me in rewriting the query and/or reducing the query
execution time. Please note that V_SHIP is a view created the source for
which is as as follows:
View Source
CREATE OR REPLACE VIEW V_SHIP (
The problem is generic, the specific query
isn't the point. RAC is a massive improvement
on OPS because block transfer is by wire not
disc - but it still takes a serious amount of
time to fling blocks from node to node, especially
if the blocks have been subject to very recent update
at the
Title: RE: Extremely Slow Query
Thanks everyone ...
Yes it is RAC (in fact I don't have non-rac databases anymore). Unfortunately this is not my query. TOAD runs this query when it starts-up and it seems like it hangs. As I am in process of deploying newest version of TOAD, I am pretty sure
Sorry,
I can't take credit for improving the query.
The improved time looks like an accident.
The plan now has two scans of OBJ$, rather
than one - it just that the two plans take 4 and 9
seconds - whereas the single scan in the first
plan took 370 seconds.
I guess that the first plan was a
Jonathan Lewis wrote:
The problem is generic, the specific query
isn't the point. RAC is a massive improvement
on OPS because block transfer is by wire not
disc - but it still takes a serious amount of
time to fling blocks from node to node, especially
if the blocks have been subject to
Title: Extremely Slow Query
Does any know how to speed up following query?
Select *
from dba_types
/
It is taking about 2-3 minutes on my 9202 database. I see a lot of Global Cache waits. The hammer shows following information
Jamadagni, Rajendra wrote:
Does any know how to speed up following query?
Select *
from dba_types
/
It is taking about 2-3 minutes on my 9202 database. I see a lot of
Global Cache waits. The hammer shows following information ...
Title: Extremely Slow Query
Are
you running RAC?
Waleed
-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02,
2003 3:51 PMTo: Multiple recipients of list
ORACLE-LSubject: Extremely Slow Query
Does any know how to speed up
Title: Extremely Slow Query
Doesnt seem to be a problem in my 9013 instance.
returned 240 rows in 1.26 elapsed seconds
- Original Message -
From:
Jamadagni, Rajendra
To: Multiple recipients of list ORACLE-L
Sent: Thursday, January 02, 2003 12:51
PM
Subject
RAC/OPS?
A guess: obj$ is a very popular table (and possibly
RAC-caches-wide-spread one ;) -- some its blocks
were not in the local cache. Does it take 2-3 mins
everytime you launch the query? Probably somebody
else is doing some manipulations with obj# actively
creates/alters/drops objects?
Looks like you've hit the big problem with RAC -
how many nodes do you have, how busy
are the nodes which are supposed to supply
with with CR copies across the interconnect,
and what's the latency and bandwidth of your
interconnect ? (And how did you get that many
objects into obj$ !!!)
If you
Jonathan Lewis wrote:
Looks like you've hit the big problem with RAC -
Do you think it's really the big problem? I'm just
thinking about Oracle APPS instance or something
alike. Somebody could start object recompilation
(maint. pack/adadmin), synonyms creation or whatever
like that on a node.
Joan,
Can't this be done as a series of ors instead of the union alls as this
would presumably reduce it to one full table scan of each table.
e.g.
SELECT T11.TRUNK TRUNK_FOUND
,T21.IDTARGET_ID
,T21.SSN TARGET_SSN
,T21.FULLNAME
ORACLE-L [EMAIL PROTECTED]
sfaroult@orio cc:
le.com Subject: Re: slow query help
Sent by:
[EMAIL PROTECTED]
om
18/12/2002
07
Title: RE: slow query help
Joan,
Here is a suggestion ...
if this is going to be your most used part, I'd look into Intermedia ... you'll have a lot more options to work with and they will work good.
Until then, I'd recommend replacing instr() with appropriate LIKE clause because at-least
Raj,
I thought it would only do the union all if it was able to use an index and
all the instr's look as though they'd stop that. Even then I thought it was
generally just rule that would do that unless you used the use_concat hint.
I can't see why a full table scan of each wouldn't be
Hi,
This is the query bothered us very much recently. It run at least 15
min. and sometimes crashed the temp tablespace. Do you have any idea how
to make it run better. Our developer tried used two cursors to compare
the result, but the result is not optimized. We tried used last name and
first
Joan Hsieh wrote:
Hi,
This is the query bothered us very much recently. It run at least 15
min. and sometimes crashed the temp tablespace. Do you have any idea how
to make it run better. Our developer tried used two cursors to compare
the result, but the result is not optimized. We tried
]
sfaroult@orio cc:
le.com Subject: Re: slow query help
Sent
Title: RE: slow query help
It need not be generatd by a tool, I have worked for a blood bank in one of my previous projects. When it comes to matching a donor in th records, you have to take a lot of precautions to see if you have a duplicate donor etc.
To me this seems to be logic to find
recipients of list
ORACLE-L [EMAIL PROTECTED]
sfaroult@orio cc:
le.com Subject: Re: slow query help
Sent by:
[EMAIL PROTECTED]
om
18/12/2002
-
From: Mark Richard [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 17, 2002 4:51 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: slow query help
I tend to agree with Stephane...
It looks like that query was possibly generated by a tool. If not you
should go talk
]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: Extremely slow query
Date: Sun, 16 Jun 2002 23:28:19 -0800
Set autotrace traceonly explain normally causes extra seconds for the
query.
But this query only lasts for 20 seconds in my database.
Is hash_join_enabled=y ? what is your
: Extremely slow query
I have the exact same explain plan when I run it, and it takes 2 seconds
for
me. What is this session waiting on? What are the system waits? Check
v$system_event, v$system_wait, v$session_event, and v$session_wait.
From: Bunyamin Karadeniz [EMAIL PROTECTED
* Solaris 2.6
* Oracle RDBMS v8.0.5.2.1
List:
We are (still) having difficult with a production database. (Users
experiencing severe slowness at times.) I'm constructing a series of
queries to run at intervals to check the health/status of the system.
I'm attempting to run the
respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Extremely slow query
* Solaris 2.6
* Oracle RDBMS v8.0.5.2.1
List:
We are (still) having difficult with a production database. (Users
Have you looked at System Waits, Session Waits and
or event 10046 trace data ? They hold the keys to
the kingdom on where you should proceed to diagnose
your performance issue.
-Original Message-
Sent: Friday, June 14, 2002 5:03 PM
To: Multiple recipients of list ORACLE-L
*
Hallo , I am running this query but it is awful slow.
(Embedded image moved to file: pic25554.pcx)
When I check TOAD Server Stats there is a red dot on the following:
- redo space wait ratio Value 145,0547
-parse/execute ratio Value 53,8402 high parse to exdecute ratio
-DBWGR avg scan depth
Hallo you DBAs;
How can I make a query run faster, I have some queries and they tend to be be very
slow.
Roland Sköldblom
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
Oh No, Oh No - Be nice now ... (I can see all the 'undocumented' parameters
being hauled out again ...)
(Roland, that is a bit like asking 'How do I implement world peace?' or 'How
do I understand women?' - we can't help make a query run faster per se - a
little detail would be nice - like
Chase it with a big stick
Nah just kidding
Provide some more detail like version and what you are trying to actually
do and you are going to get a better answer.
As I always understood it, to get the best performance all other things
being correct with the system is that you have to make
On Tue, 29 May 2001,Oweson Flynn scribbled on the wall in glitter crayon:
-(Roland, that is a bit like asking 'How do I implement world peace?' or 'How
-do I understand women?' - we can't help make a query run faster per se - a
you do not understand women, if you are very, very, very lucky you
38 matches
Mail list logo
