On Aug 2, 5:25 pm, Guy Taylor <twopotsa...@gmail.com> wrote:
> I have a situation where our UNDO TABLESPACE is filling up with data.
> Our application creates a temporary table. It then creates several
> indexes and runs dozens, if not hundreds of selects against the table.
> The table is then dropped. The entire process may take an hour or two.
>
> I am not an Oracle DBA, so I may get some of the terms wrong. I also
> find this fascinating because it is very counterintuitive.
>
> Our DBA tells me that the UNDO TABLESPACE has filled, or is close to
> filling up. He can tell me what SQL has run recently, or has an active
> or recent footprint in the UNDO TABLESPACE, but he cannot tell me how
> much space each statement, or even each transaction is consuming. It
> seems to be that this information should be available. If the
> TABLESPACE is filling up, one would think the database system would
> provide an interface to clean it up. Is this correct?

See http://www.oracle.com/technology/pro...aceMgmtEXT.pdf
There are various scripts floating about to see various things. It
kinda sounds like your dba needs more training or perhaps needs to be
able to communicate them better to you. Some older explanations of
rollback segments can make things more clear, see for example metalink
Note:1011108.6. More recent and informative google:
undo site:jonathanlewis.wordpress.com

>
> Secondly, the bulk of these queries are select statements. Why does a
> select statement need UNDO space? If Oracle is using the UNDO space to
> maintain a snapshot of the table for the query so that it has a
> consistent view of the table (as opposed to locking it), then why
> would the information remain in the UNDO TABLESPACE for hours? Once a
> query (or transaction) is committed, wouldn't the database release its
> resources from the UNDO TABLESPACE?

I would suggest reading the concepts manual before getting to Tom's
book. The information in the undo tablespace is needed to be able to
reconstruct data to present it to transactions that start at arbitrary
times. You need to get your head around the fact that much of
Oracle's architecture is dealing with how other people query your
data. And now it even has the ability to query your own data as it
looked in the past.

Tom's book is really good, especially for explaining why you need to
unlearn your bad habits.

>
> Thirdly, assuming that the database maintains information in the UNDO
> TABLESPACE (so it can rollback select statements, I guess), and I run
> up against the limit of the UNDO TABLESPACE size, is there any way, at
> the beginning of a transaction or select statement that I can ask the
> database to not use the UNDO TABLESPACE? In other words, the
> application is constructed in such a way that the selected from table
> is guaranteed not to change. Thus, there is no need to worry about
> consistency or the data changing underneath me. How do I tell the
> database to punt on the rollback infrastructure?

That's kind of like asking how to unhook your car's computers because
you think you can set timing better manually while you are driving.

>
> Thanks for any advice and I look forward to a discussion about how
> this works.

jg
--
@home.com is bogus.
$oerr opra 369
OPRA-00369, "Attempt to access overweight celebrity %s"
// *Cause: Attempt to access overweight celebrity failed because it
is necessary
// to become a credentialed paparazzi.
// *Action: If you have recently received your accredation, then wait
for it to
// become active. Otherwise contact Oprah support ho's.

On Thu, 02 Aug 2007 17:25:27 -0700, Guy Taylor <twopotsaday@gmail.com>
wrote:

>Thirdly, assuming that the database maintains information in the UNDO
>TABLESPACE (so it can rollback select statements, I guess), and I run
>up against the limit of the UNDO TABLESPACE size, is there any way, at
>the beginning of a transaction or select statement that I can ask the
>database to not use the UNDO TABLESPACE? In other words, the
>application is constructed in such a way that the selected from table
>is guaranteed not to change. Thus, there is no need to worry about
>consistency or the data changing underneath me. How do I tell the
>database to punt on the rollback infrastructure?

Shortly you can't. It would invalidate the entire transaction and read
consistency model, and it would mainly work when you are the only user
of the database, and your database is stored on punch cards.
You are in dire need of reading the Concepts Manual.
All of your assertions in this paragraph show you never read it.
UNDO tablespaces are there to
a) make it possible to rollback INSERT, UPDATE, DELERE statements
b) make it possible to reconstruct a read consistent image of a table
*prior* to the transaction.
In Oracle readers *DON'T* block writers, and even Mickeysoft's SQL
server is trying to implement this concept currently.

--


Sybrand Bakker
Senior Oracle DBA

> Do you mean a global temporary table or an imitation of aSQL Server
> trick that kills performance and scalability?
>
> You mean aSQL Servertype temporary ... stop it. This is Oracle notSQL Serverand what you are doing is both unnecessary and bad practice.
> Likely you need no intermediary table at all but should you need one
> then you should use a global temporary table which is a permanent table
> in which the data is temporary.
>
> To stop duplicatingSQL Servermethodology get copies of Tom Kyte's
> books and read the sections that relate to temporary tables and general
> concepts and architecture.
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

I have no doubt that Daniels recomendations around which oracle books
to read and which tecniques to employ are correct, his comments re SQL
Server are not.

The current SQL Server lock and transaction managers are new code and
have been for several releases, they have nothing to do with Sybase
never mind Ingres. Daniel appears to forget that SQL Server 2005
includes support for 2 snapshot based isolation levels that address
many of his out of date points about SQL Servers lock escalation
policies. And finally the VAST majority of customers I have seen using
temp tables in almost 10 years of working with SQL Server have nothing
to do with locking or transaction isolation models in terms of
reasoning. And of course when properly used in SQL Server(just as in
Oracle) use of these objects does not automatically equal performance
or scale problems. A badly designed and/or implemented database
independent of which platform it is, is the cause of poor performance
and scale, not the platform itself.

I will happily agree with Daniel that SQL Server customers use more
temp tables that Oracle customers and that the usage models do not
translate well.

On Aug 6, 12:33 am, euan.gar...@gmail.com wrote:
> > Do you mean a global temporary table or an imitation of aSQL Server
> > trick that kills performance and scalability?
>
> > You mean aSQL Servertype temporary ... stop it. This is Oracle notSQL Serverand what you are doing is both unnecessary and bad practice.
> > Likely you need no intermediary table at all but should you need one
> > then you should use a global temporary table which is a permanent table
> > in which the data is temporary.
>
> > To stop duplicatingSQL Servermethodology get copies of Tom Kyte's
> > books and read the sections that relate to temporary tables and general
> > concepts and architecture.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor...@x.washington.edu (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org
>
> I have no doubt that Daniels recomendations around which oracle books
> to read and which tecniques to employ are correct, his comments re SQL
> Server are not.
>
> The current SQL Server lock and transaction managers are new code and
> have been for several releases, they have nothing to do with Sybase
> never mind Ingres. Daniel appears to forget that SQL Server 2005
> includes support for 2 snapshot based isolation levels that address
> many of his out of date points about SQL Servers lock escalation
> policies. And finally the VAST majority of customers I have seen using
> temp tables in almost 10 years of working with SQL Server have nothing
> to do with locking or transaction isolation models in terms of
> reasoning. And of course when properly used in SQL Server(just as in
> Oracle) use of these objects does not automatically equal performance
> or scale problems. A badly designed and/or implemented database
> independent of which platform it is, is the cause of poor performance
> and scale, not the platform itself.

Well, as someone who works with db independent code every day, I have
to point out that lots of code has been around since before SQL Server
2005, and hasn't been re-written, and the new code being written has
to conform to the old code. The vast majority of the code _I_ work
with uses a lowest common denominator locking and concurrency, even
though the independent part allows one to select an Oracle style
mechanism, since the application packages sell more to SQL Server
shops.

The funny thing is, even using the silly mechanism, it still scales
better on Oracle. As long as I have the choice, I still work on
Oracle/unix.

>
> I will happily agree with Daniel that SQL Server customers use more
> temp tables that Oracle customers and that the usage models do not
> translate well

I've gotten lots of work where the temp tables are abused by
programmers who don't understand what is happening under the covers
(understandable with the extra layers of abstraction involved), and I
magically make it work better. I think it is a good thing to be able
to manipulate things totally in memory without setting off concurency
mechanisms when dealing with the magnitude of data you find in, say, a
sales order. I also think it is too much to ask to expect people to
be able to deal with two conflicting concurrency/consistency models at
the same time, so for Daniel to say something should never ever be
done in Oracle and to blast old stuff in SQL server is actually
laudable and timely.

jg
--
@home.com is bogus.
Dadgum hackers! http://www.signonsandiego.com/uniont...z1b6steel.html

joel garry wrote:

> so for Daniel to say something should never ever be
> done in Oracle and to blast old stuff in SQL server is actually
> laudable and timely.

And for anyone that doesn't believe me here's a simple lab you can
run to prove it to yourself. I run it every year for my students at
the university and I make them read it line by line.

CREATE TABLE test (
onecol NUMBER(1));

ALTER SESSION SET tracefile_identifier = 'drop_table';

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

DROP TABLE test;

ALTER SESSION SET EVENTS '10046 trace name context OFF';

EXIT

Now go to bdump/udump (while they still exit in your version of Oracle)
and find the trace file. Run it through TKPROF. Here's what it looks
like on my machine after copying the file to c:\temp.

c:\temp> tkprof c:\temp\orabase_ora_2736_drop_table.trc
c:\temp\trace_output.txt

Enjoy a very long read.

Here's the summary from 11g

************************************************** ******************************
Trace file: c:\temp\orabase_ora_2736_drop_table.trc
Trace file compatibility: 10.01.00
Sort options: default

1 session in tracefile.
62 user SQL statements in trace file.
288 internal SQL statements in trace file.
350 SQL statements in trace file.
107 unique SQL statements in trace file.
7932 lines in trace file.
4 elapsed seconds in trace file.

And that is with a single column, no synonyms, no indexes, no
constraints, no dependent materialized views, no views, no triggers,
and without any complicating technology such as replication.

If this looks like a good idea to anyone see your family physician.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org