It's a bad query that could probably be resolved throuh
an analytic function but I don't normally delve into things
like that before having finished my 2nd coffee. You can
use hints, in particular, there is a hint to force hash join.
On 01/27/2004 06:44:25 AM, S.Sarkar wrote:
All,
i have this
Is the sql really the same query is run from a stored procedure or is it
perhaps using in place of the '%TATA.COM' a plsql variable (which is set
to %TATA.COM)?
At 04:44 AM 1/27/2004, you wrote:
All,
i have this query:
SELECT count(1)
FROM ats.emktg_members t1
WHERE NOT EXISTS ( SELECT 'x'
it is the same. '%TATA.COM' is not a variable.
sumant
--- Wolfgang Breitling [EMAIL PROTECTED] wrote:
Is the sql really the same query is run from a stored
procedure or is it
perhaps using in place of the '%TATA.COM' a plsql variable
(which is set
to %TATA.COM)?
At 04:44 AM
Well, you could try...
select view_name from all_views
where view_name like '%TEMP%';
..and see what you get. I have lots of views that give me
information about the TEMP tablespace. YMMV, but it's
a place to start.
Cheers,
Mike
-Original Message-
Sent: Wednesday, November 26, 2003
Below was the closest I got, but it still pretty much shows everything empty sort
of. Guess it's okay, I'll monitor. This is a warehouse so it's hard to say. So close
to the holidays, it's possible they aren't using a lot of temp.
set linesize 162
set pagesize 30
column tablespace_name format
I have included the following in my sqlnet.ora
trace_file_client = osstrace_timestamp_client = truetrace_level_client = 16
Am no expert on interpreting the trace file and Oracle are not being very helpful at the moment.
"M.Godlewski" [EMAIL PROTECTED] wrote:
Zabair,
Have you tried trace?
Zabair,
Have you tried trace?
Did you check trace route to see how many hops it makes to the database server?Zabair Ahmed [EMAIL PROTECTED] wrote:
I've got the follwing piece of code which takes almost 3 seconds to run when I execute it on the server itself using sqlplus.
DECLARE p_xml_in
because its not always faster to use an index. try using a hint for the index and see
which runs better.
From: David Wagoner [EMAIL PROTECTED]
Date: 2003/10/27 Mon AM 10:34:26 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Query Tuning Help
I'm trying to tune
Title: Query Tuning Help
David,
The
calculation "( SYSDATE - 35)" is not causing the problem. The
To_Date(a2.file_dts, 'mmddyyhh24miss') is.
You
said you created a function based index. I think you also need to
set:
Query_ReWrite_Integrity =
TRUSTEDQuery_ReWrite_Enabled
= TRUE
for
Title: Query Tuning Help
Tom,
Thanks
for the init.ora parameter tips, I consulted the docs and did that first
:-). It just seems that the CBO would rather use an index, even though I
know that's not always the case.
Best regards,
David B. Wagoner Database Administrator Arsenal Digital
Do you have query rewrite privilege? What is the
query_reqrite_inegrity set to? How about optimizer parameters
(optimizer_index_caching,optimizer_index_cost_adj)? Is everything
analyzed?
On 10/27/2003 10:34:26 AM, David Wagoner wrote:
I'm trying to tune the following query to use an index on
Title: Query Tuning Help
The
FILE_DTS column is VARCHAR2(12) NOT NULL and has data in the following
format: 07220301.
Best regards,
David B. Wagoner Database Administrator Arsenal Digital
Solutions Phone: 919-466-6723
Cell: 919-412-8462
Pager: [EMAIL PROTECTED]
Fax: 919-466-6783
David,
I would probably not try to tune a query to make it use an index but tune a query to
make it run faster - I have had recently a nice example, a join between a 500K row
table and two 2K row tables (returning about 2K rows too) was running faster with FTS
(followed by hash joins) on
Title: RE: Query Tuning Help
Mladen,
Thanks for your response. Comments are in-line.
Do you have query rewrite privilege?
Yes.
What is the query_reqrite_inegrity set to?
TRUSTED.
How about optimizer parameters (optimizer_index_caching,optimizer_index_cost_adj
PMFJI
How about optimizer parameters
(optimizer_index_caching,optimizer_index_cost_adj)?
optimizer_index_caching=0 Are these reasonable values?
optimizer_index_cost_adj=100
sorry for the space everyone ms outlook 101 is a class I *badly* need
like how do I reply in plain text with
asktom.oracle.com
http://asktom.oracle.com/pls/ask/f?p=4950:8:2542717627406446060::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:124812348063,
or
http://tinyurl.com/r3lk
pivot a result set
-Original Message-
Teresita Castro
I have the next query:
SELECT COMPANY, ITEM,
??
the solution:
use of TEMP tables, we use temp table to hold the values, each tikme the
user clicks next page we save the values to the temp table, and re-query
to fill in the fields if the user comes back to the same page ..
is my approach correct ??? is there another easier way to do
This means that the program needs to either be relinked or the LIB_PATH
variable is pointing to the wrong directory. This error usually turns up
when you have 32-bit and 64-bit libraries on the system and one of them
must be first in the LIB_PATH. Determine if the program was compiled with
a
So then a user without query rewrite system privg, would be able to create
a materialized view with enable query rewrite clause, but not enable query
rewrite for himself.
But this user without query rewrite system privg, would still be able to
sucessfully execute :
alter session set
Mladen , so what you are saying is that even if a user doesn't have 'query
rewrite' privg but has create materialized view privg he can create a
materialized view with query rewrite enabled and sucessfully execute 'alter
session set query_rewrite_enabled=true;' but even then query rewrite
My guess is that just because the Mat View is enabled for query rewrite
doesn't mean that it's going to happen. The Query Rewite attribute on the
Mat View probably indicates that it is eligible for rewrite.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Tuesday,
Nope. It's required to use it, not to create it. By enabling query rewrite,
you
enable the RDBMS to rewrite your query and use it to resolve your select,
even if
the view itself was not mentioned in it. You can always create material view
and do select from it. Material views are snapshots with
Tanel,
Thanks for your response.
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 26 Aug 2003 13:29:26 -0800
Hi!
In one migration of about 600GB DB I used 2 dblinks (loading different
partitions in parallel with separate
Hi!
What about several insert /*+ APPEND NOLOGGING */ commands over database
link run in parallel? (Possibly over dedicated network). This is fast and is
easier (in case you don't hit any compatibility problems). If you happen to
be running on Windows for some reason, you could try to use named
One caveat strikes my mind when considering Direct Load... Due to the fact
that the buffer is constructed and written directly, the kernel can perform
INSERTs only *above* the HWM. If the rate at which you perform Direct
INSERTs is high (i.e. multiple runs in a day), then you may have an
Imran,
Try this script.
i have used it often.
Jp.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Prem Khanna J
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web
Oops...it's Jared's script.
i was not knowing till now.
Jp.
26-08-2003 11:14:26, Prem Khanna J [EMAIL PROTECTED] wrote:
Imran,
Try this script.
i have used it often.
Jp.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Prem Khanna J
INET: [EMAIL PROTECTED]
Fat
Spooling into a pipe file concurrently SQL Loading from the same is even faster as
the Loading Starts off even while the spool is underway i.e. being built . Both spool
Load Run concurrently . Also NO Disk space is consumed.
HTH
-Original Message-
Sent: Tuesday, August 26, 2003 1:35
John - Thanks for the heads-up. My intention is to truncate all tables on
the target system beforehand, so that should reset the HWM. Then I have a
lot of tables to load, so my plan is to load multiple tables simultaneously,
trying for separate RAID sets, but use only a single insert on an
Taniel, Binley
Thanks for the excellent suggestions.
At this point we have been testing with two smaller test systems, moving
a single table at a time, but initial indications are that the performance
order is:
1. Perl dump to CSV / ftp / SQL*Loader
2. Copy across database link
3. Export/
Hi!
What is your goal? To finish data transfer in the shortest time or keep
hardware utilization maximum during transfer?
I think you should concentrate on keeping the time for doing data transfer
low.
Depending on your network - in case of gigabit (or 10 Gb) you could look at
enabling jumbo
Tanel
Thanks for the ideas. My simple mind says that by fully utilizing the
hardware I can minimize the overall time. But today we were on a conference
call with the application vendor and they were touting their utility for
handling this. Everyone around the table seemed pretty impressed, so
Tanel,
A quick question? How many open dblinks you have used at one time without
any issues? Default setting in init.ora is 4(if I am not wrong) and I never
used it more than that. If Dennis wants to use more than 4 dblinks at one
time, he should modify this param(open_dblinks) in init.ora,
You can also close the dblink to avoid having many open idle sessions on the
remote database.
alter session close database link dblink;
Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187
[EMAIL
Thanks for your input. Discussion here is to keep maximum use of dblinks for
data load/transfer from one server to another.
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 26 Aug 2003 12:49:27 -0800
You can also close the
Hi!
What do you customers care about? Usability and uptime of their app or
utilization of your server?
If you use direct exp and imp method:
1) you read data from disk (server process from oracle datafiles)
2) you write data to disk (expfile)
3) you read data from disk (ftp reading expfile)
4)
Hi!
In one migration of about 600GB DB I used 2 dblinks (loading different
partitions in parallel with separate statements), then added 3rd link after
what the bottleneck seemed to be the network. (I saw full network
utilization from perfmon on windows, wasn't too much of a wait interface
user
Imran,
Select COL1 ||','|| COL2||','||COL3 from table...
will create a comma delimited file that you can SPOOL to an OS file.
Or you could use a third patry product.
Ron
[EMAIL PROTECTED] 08/25/03 08:39AM
Hi,
Whats the best way to write the results of a SQL query to a CSV file?
Thanks.
if straight sql. spool and then just embed commas
spool myfile
select col1||','||col2
from tab;
if in pl/sql do the same thing with utl_file
From: Imran Ashraf [EMAIL PROTECTED]
Date: 2003/08/25 Mon AM 08:39:03 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Query
Hi,
do some reading in the SQL*PLUS manual.
Read about:
- SET command (many useful options, e. g. LINESIZE, HEADING, COLSEP)
- SPOOL command (spools the result)
hth and greetings,
Guido
[EMAIL PROTECTED] 25.08.2003 14.39 Uhr
Hi,
Whats the best way to write the results of a SQL query to a
Well, you might as well search akstom.oracle.com for owa_sylk
Kind Regards,
Hatzistavrou Yannis
-Original Message-
Sent: Monday, August 25, 2003 3:39 PM
To: Multiple recipients of list ORACLE-L
Hi,
Whats the best way to write the results of a SQL query to a CSV file?
Thanks.
That can be achieved by setting colsep in sql*plus as well and than select *
from table (less typing) :-)
Does not help when a text field also contains comma's
Jack
-Original Message-
Sent: Monday, August 25, 2003 3:07 PM
To: Multiple recipients of list ORACLE-L
Imran,
Select
This is probably the easiest way, if you are
doing it from SQL*Plus :-
SET COLSEP ,
-Original Message-
Sent: Monday, August 25, 2003 8:39 AM
To: Multiple recipients of list ORACLE-L
Hi,
Whats the best way to write the results of a SQL query to a CSV file?
Thanks.
http://www.cybcon.com/~jkstill/util/dump/dump.html
On Mon, 2003-08-25 at 05:39, Imran Ashraf wrote:
Hi,
Whats the best way to write the results of a SQL query to a CSV file?
Thanks.
_
Hotmail messages direct to your mobile
Jared - Thanks for posting this. At the moment, we are preparing to move
large database to a new server. Based on the advice you posted several
months ago, we have been testing SQL*Loader and as you predicted, it is
indeed fast. But also as you predicted, using SQL*Plus to create a CSV isn't
very
That's one of the reasons I use a development tool (mine is TOAD, but there are others
that can do the job). I got tired of typing queries like:
SELECT ''||ename||','||
TO_CHAR(salary)
FROM emp
-Original Message-
Sent: Monday, August 25, 2003 8:39 AM
To: Multiple recipients of
-print(bad = . lc($args{TABLE}) . .bad\n);
$parFh-close;
}
DENNIS WILLIAMS [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
08/25/2003 09:24 AM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: Query results
Hi!
Spooling from sqlplus is VERY slow.
Is the source database Oracle? Then use export/import
If not, is there an ODBC driver for source database? Then use Oracle
heterogenous services and do your transfer directly, without any
intermediate files.
Or use some very expensive software for doing
Thanks Tanel
We will undoubtedly use export/import for the many small tables. We are
looking for alternatives that will perform even faster. The insert phase
seems to be the slowest part, and that is where SQL*Loader in direct path
really shines. Now the next issue is how to produce a CSV file
If both servers are on the same network with reasonable connection speed,
- create target_table nologging as select * from [EMAIL PROTECTED]
will beat all other options as it creates the table in one step - no
writing/transfering/reading intermediate files in between, and nologging
uses the same
I've only looked quickly at this query, but why do you think it should be
using the shipto_key index? There doesn't appear to be any kind of
filtering on the cust_shipto table so I don't know why an index would help
since every record would match. Am I missing something?
Also, what explain
one place to look would be your sort_area_size. your doing summing and group
bys... if this is too small your going to do that in a temp tablespace
instead of in memory.
your using bitmap indexes right? this implies that some of this data atleast
is non-transactional correct? if its
I did a sql trace and tkprof and here is the output. It looks like 'bitmap conversion
to rowids' is the hogger. Anybody know what this implies? Should I try dropping and
recreating the index as b-tree? We don't have an identical test system here so I need
a 'warm and fuzzy' before doing that in
not necessarily.
can you post the lines immediately above those you've
provided? (call, count, cpu, elapsed, etc.)
It looks like 'bitmap conversion to rowids' is the
hogger.
Anybody know what this implies? Should I try
dropping and recreating the index as b-tree? We
don't have an identical
Title: RE: Query Tuning Question - new discovery
A Ha ...
it is refers to _B_TREE_BITMAP_PLANS variable ... it is true by default and what you see is the side effect. If you are not using BMI, set it to false.
http://tinyurl.com/e8ws for more info
Raj
has anythign changed in the table? inserts, updates, deletes? if so considering doing
a move on the table to rebuild it and possibly rebuilding the indexes in question.
have you gather statistics lately? Is it using the same plan it was using a fwe weeks
ago?
From: Meng, Dennis [EMAIL
What could be the cause of this wait?
At the risk of asking the bleeding obvious, have you tried doing a 10046
trace on the query?
Gudmundur
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gudmundur Bjarni Josepsson
INET: [EMAIL PROTECTED]
Fat City Network Services
Find it as attachmentWith Regards,Manoj Kumar JhaC-56 , Phase-2NOIDA -201305, UP(INDIA)Tata Consultancy ServicesPh No: (+91-120) 4461001 ext : 1037 (Off.)Mobile No : 9810090974A
Kesh,
I have some information on my website (http://www.optimaldba.com/internals/oraint_dual.html). It is a pretty terse discussion of the topic. I know that Cary Millsap has tested and written a paper on this as well. IIRC, there is mention of DUAL in his paper on reducing Logical I/Os. You
http://www.ixora.com.au/scripts/cache.htm
replace_dual.sql
In some cases the data block for SYS.DUAL can be a hot block in cache. This
script replaces the SYS.DUAL table with a view onto X$DUAL, thereby
eliminating the cache access entirely.
Please note: This is not a normal, supported
,
because the cost-based optimizer is required to calculate costs for
each of the concatenated subqueries.
Only thing I would suggest is to remove first Order by Clause if you
have any to remove sort step and see if it helps.
HTH
Mohammed Shakir
--- gmei [EMAIL PROTECTED] wrote:
RE: query run
an 'in' is just an 'or' so as soon as one value in your in list is found it has
accomplished its task. so if there are alot of possibilities to choose from oracle has
to search less records to find one that it wants.
From: gmei [EMAIL PROTECTED]
Date: 2003/06/06 Fri PM 02:09:44 EDT
To:
Title: RE: query run time vs IN list elements
Guang,
1. Just because you created an index doesn't necessarily mean Oracle is using it especially if using CBO and you haven't analyzed the table and index after the index creation. Try using a hint.
2. If IN isn't meeting your needs, try
Title: RE: query run time vs IN list elements
Hi:
1. I
should have mentioned that I analyzed the index after creating the index, also I
looked at the explain table in both situations:
Execution
Plan--
0 SELECT STATEMENT Optimizer
[EMAIL PROTECTED]
Date: 2003/06/06 Fri PM 03:10:24 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: query run time vs IN list elements
Guang,
1. Just because you created an index doesn't necessarily mean Oracle is using it
especially if using CBO and you haven't
ok i think i figured out what is going on. sorry for all the emails. long day...
Look at your query. You are including a count and a group by. Both are costly
operations. As you increase the values in 'IN' you are increasing the size of the
resultset.
COUNT and group by need to work harder
Subject: Re: query run time vs IN list elements
ok i think i figured out what is going on. sorry for all the
emails. long day...
Look at your query. You are including a count and a group by.
Both are costly operations. As you increase the values in
'IN' you are increasing the size
Title: RE: Query through database link gets lost
Are all these database similar versions of Oracle, or are they all different versions?
When I did something similar I had my monitoring script connect to a database, poll for the necessary information, disconnect, and then connect to another
[EMAIL PROTECTED] wrote:
The following qry takes large amt of time to retrieve data on production
database.
Reason being for a single row in psd, there are multiple records in
piar_fr_psd representing diff parties.
How to optimise this qry .
Select CUST_BAS_NO,BR_COD,CUST_NAM,BR_NAM
Try something like this:
select a.OP_FL_PLAN_KEY,
a.FLIGHT_NO,
a.ORIGIN,
a.DESTINATION,
a.SEG_DEP_DATE_TIME,
a.SEG_ARR_DATE_TIME,
a.DEP_DAY_CHG,
a.ARR_DAY_CHG,
DECODE(a.ac_type, 'JET', c.AC_TYPE, a.AC_TYPE),
a.AC_GRP, b.exp_lat_date_time,
b.imp_toa_date_time,
b.spl_code
from op_fl_plan a,
select a.OP_FL_PLAN_KEY,
a.FLIGHT_NO,
a.ORIGIN,
a.DESTINATION,
a.SEG_DEP_DATE_TIME,
a.SEG_ARR_DATE_TIME,
a.DEP_DAY_CHG,
a.ARR_DAY_CHG,
decode(a.ac_type,'JET',c.AC_TYPE,a.ac_type)
a.AC_GRP,
Ranganath,
Why? Will making the query more concise provide any advantage at all? I
frequently break out such concise queries into multiple UNION'd statements
in order to improve performance, by clarifying unnecessarily concise
(therefore convoluted) logic.
Not to be too cheeky, but in this
Tim,
Following your trunc() line, have you come
across this feature of 9.2 (and some earlier
versions) -
create index t1_i2 on t1(d1);
analyze table t1 compute statistics;
select * from t1
where trunc(sysdate) = '01-Dec-2002';
Execution plan:
---
table access (by index
Title: Re: Query rewrite help needed
See if the following works:
select a.OP_FL_PLAN_KEY
, a.FLIGHT_NO
, a.ORIGIN
, a.DESTINATION
, a.SEG_DEP_DATE_TIME
, a.SEG_ARR_DATE_TIME
, a.DEP_DAY_CHG
, a.ARR_DAY_CHG
, CASE WHEN a.AC_TYPE = 'JET'
then (select c.ac_type from op_ac_type c where
Unfortunately the example loses
a little credibility and impact because
I put:
where trunc(sysdate) = '01-Dec-2002';
rather than
where trunc(d1) = '01-Dec-2002';
I just can't seem to get the cut and paste
to work properly from my laptop to my
email machine :(
Regards
Jonathan
-
From: Jonathan Lewis [mailto:[EMAIL PROTECTED]
Sent: 10 March 2003 15:24
To: Multiple recipients of list ORACLE-L
Subject: Re: Query rewrite help needed
Tim,
Following your trunc() line, have you come
across this feature of 9.2 (and some earlier
versions) -
create index
Notes inline.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Now available One-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )
UK___March 19th
UK___April 8th
All other things being equal (and there
are a couple of pages in the performance
tuning guide outlining some things that
are not equal - like joins conditions, indexed
columns, subqueries, user-defined functions
etc.) the 'final' predicates against a single table
are evaluated from the bottom up
Title: RE: Query failing in CBO mode
Okay ... Rule Number 1.
Always convert the data type of the *variable value* to that of table column you are comparing with, and not the other way. The later part causes the problem.
Raj
Ranganath
I notice that the cost for the explain plan is identical. Keep in mind
that the explain plan is a good quick look at how Oracle intends to perform
the query, but is not an actual execution. It can be fooled, and even make
bad choices. When I encounter a query where explain plan isn't
Raj - If someone hasn't previously used tkprof, in your opinion should they
start with this utility, or with tkprof and work their way to this?
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Monday, February 03, 2003 9:40 AM
To: Multiple
Title: RE: Query takes more time when number of IN List value increases
Also see
http://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p_database_id=NOT_id=224270.1 this makes it easy ...
Raj
__
Rajendra Jamadagni MIS
Title: RE: Query takes more time when number of IN List value increases
Dennis,
I am not an expert, but I find this utility gives a (relatively) readable output compared to tkprof. I use both, but for starters I'd say, look at both and see what you like. This utility doesn't use tkprof
Go have a beer, relax and re-read it. Get him Kirti!!
;o)
Dave
-Original Message-
Sent: Thursday, January 23, 2003 7:29 AM
To: Multiple recipients of list ORACLE-L
I have the Oracle Performance Tuning 101 book and I have been reading the Performance
Tuning Guide on OTN, however, I
R - When this came up on this list recently, the consensus seemed to be that
Guy Harrison's Oracle SQL High-Performance Tuning is best.
http://www.amazon.com/exec/obidos/tg/detail/-/0130123811/qid=1043332563/sr=8
-1/ref=sr_8_1/103-9193296-9002269?v=glances=booksn=507846
Dennis Williams
DBA,
encoded content removed -- binaries not allowed by ListGuru
The previous attachment was filtered out by the ListGuru mailing
software at fatcity.com because binary attachments are not appropriate
for mailing lists. If you want a copy of the attachment which was
removed, contact the sender
Title: RE: Query optimization
Have you tried NO_EXPAND?
Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
-Original Message-
From: Krishnaswamy, Ranganath
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, January
SQL SELECT * FROM
emp;
NAME
EMP BOSS-- --
--SAMAR
10
20ASHOK
20
30ASHWINI
30
40MONIKA
11
21RASHI
21
31SMRITI
12
22SUMEET
22 32
7 rows selected.
SQL SELECT *
2 FROM emp 3
WHERE emp NOT IN ( SELECT emp
4
FROM emp
5
START WITH emp = 10 CONNECT BY
Wendy - I think the difference between using an alias or not is negligible.
My reasoning is that this would be easy to test (good idea if you have a
moment) and there are enough picky Oracle developers that if this was not
negligible, people would have been bragging about this as their secret
Wendy,
I have never heard of alias' providing either a performance gain or
reduction. One thing I haven't verified though is the impact on hints. It
has been my observation that if a table has an alias then that alias must
be used in the hint. For example:
select /*+ index(user) */ *
from
Title: RE: Query tuning with tablename alias
but if you provide hints on such statements, you better be using aliases for hints .
Aliases are used for readability ... you either use the aliases or user tablename.column but not both ... world is already confusing enough ...
Raj
Knowing about how a hint works with aliases is what made me wonder about
impacts to other areas. I have never seen it mentioned in any of the
manuals I have read, and I just thought maybe someone else had experience
with this. I am trying to figure out why the developer of these queries
took
Title: Message
I do
not think Oracle will run the sql below. Once you have an alias for a table,
only that alias could be used as an alias (not the table
name).
Example:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - ProductionWith the
Partitioning, OLAP and Oracle Data Mining
Wendy - Could this SQL have been generated by a program?
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, November 20, 2002 4:00 PM
To: Multiple recipients of list ORACLE-L
Knowing about how a hint works with aliases is what made me wonder
I am thinking that it must have been, since the previous email correctly
shows that it will not execute. I do not have access to the actual db and
code yet, just paper copies of the queries that I was given to look over.
Thanks to everyone for their feedback on this. Obviously this is not an
FWIW... Here is an interesting article by Jonathan Gennick:
http://www.onlamp.com/lpt/a/2640
- Kirti
-Original Message-
Sent: Wednesday, November 20, 2002 4:44 PM
To: Multiple recipients of list ORACLE-L
I am thinking that it must have been, since the previous email correctly
shows
] Quad/Tech International, Sussex, WI USA
-Original Message-
From: Stephane Faroult [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 18, 2002 3:14 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Query to predict failure on second extent of segment
Jesse, Rich wrote
Dick
V$session_longops has a column elapsed_seconds and a sid column
HTH
John
-Original Message-
Sent: 15 November 2002 18:59
To: Multiple recipients of list ORACLE-L
Quick question, Does anyone know of a location in the V$ tables where the
elapsed time of the current query is
V$session_longops only store info for create index, etc, not query.
Oracle use buffer_gets/500 as estimated time, you can get it at v$sql, v$sqlarea
-Original Message-
Sent: Monday, November 18, 2002 3:43 AM
To: Multiple recipients of list ORACLE-L
Dick
V$session_longops has a column
OK, once again, this isn't what I'm looking for. Comparing NEXT_EXTENT*2 to
the largest free space will only work in some cases. Consider that a
segment has NEXT_EXTENT of 30M and there are two 40M free spaces in the TS.
The segment clearly has enough room to extend twice, but comparing
1 - 100 of 197 matches
Mail list logo
