SEO

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?

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?

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?

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

On Aug 2, 8: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?
>
> 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?
>
> 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?
>
> Thanks for any advice and I look forward to a discussion about how
> this works.

You might want to purchase and read Tom Kyte's latest book. It has a
thorough discussion of UNDO tablespaces.

Guy Taylor wrote:
> I have a situation where our UNDO TABLESPACE is filling up with data.

Determined by what means and how/why is that an issue?

> Our application creates a temporary table.

Do you mean a global temporary table or an imitation of a SQL Server
trick that kills performance and scalability?

> 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.

You mean a SQL Server type temporary ... stop it. This is Oracle not
SQL Server and 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 duplicating SQL Server methodology 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
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Guy Taylor wrote:
> I have a situation where our UNDO TABLESPACE is filling up with data.
> (...)
> Thanks for any advice and I look forward to a discussion about how
> this works.
>

That mysterious book they are referring to would be:
Thomas Kyte, Expert Oracle Database Architecture ... he does have a
chapter dedicated to Redo and Undo.

You might also like to check asktom.oracle.com, as I am pretty sure he
may have answered related questions there.

Welcome to the friendly world of Oracle.
br,
Martin

"I have a situation where our UNDO TABLESPACE is filling up with
data."

Tell us how that is a problem.

For the rest of your post: your questions can't be answered without
getting
into the concepts of oracle. If you would really want to know, do
yourself
a favor and buy the mentioned book by Thomas Kyte. This outstanding
book
is aimed at bridging the gap between the DBA and the programmers.

H.

I bought the book this AM.

I know the UNDO tablespace filling up is a problem because the Oracle
DBA insists it is. If there is no downside to this tablespace filling
up, then I have been mislead. Perhaps my terminology is wrong
regarding the "temporary" table. I create a table in the schema's
tablespace, run queries against it, and then drop it when I am done.
Thus, perhaps it is not a "temporary table" as defined in the
literature.

Regardless, I hope the chapter about the UNDO tablespace in the book
can illustrate how to construct queries, or form transactions, that do
not leverage the UNDO tablespace. This should keep my DBA from adding
gigs and gigs of space to the tablespace.

Also, thanks for all the responses. They were very helpful.

Guy Taylor wrote:
> I bought the book this AM.
>
> I know the UNDO tablespace filling up is a problem because the Oracle
> DBA insists it is. If there is no downside to this tablespace filling
> up, then I have been mislead. Perhaps my terminology is wrong
> regarding the "temporary" table. I create a table in the schema's
> tablespace, run queries against it, and then drop it when I am done.
> Thus, perhaps it is not a "temporary table" as defined in the
> literature.
>
> Regardless, I hope the chapter about the UNDO tablespace in the book
> can illustrate how to construct queries, or form transactions, that do
> not leverage the UNDO tablespace. This should keep my DBA from adding
> gigs and gigs of space to the tablespace.
>
> Also, thanks for all the responses. They were very helpful.
>

Maybe that both you and your dba have to read carefully Tom's book.
Why do you say that UNDO tablespace filling up is a problem ??????
it is there for this Oracle manages UNDO space automatically (i you use
a version on this century) and that space is needed. Does it costs more
a GigaByte of disk occupied by UNDO or days of your time spent to try
not to use to much UNDO? It makes no sense, Thomas Kyte explains all
very well in his book, so please read carefully his book and make your
DBA read it and only after that came here to say us that UNDO tablespace
filling up is a problem

regards
--
Cristian Cudizio

http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com

Guy Taylor wrote:
> I bought the book this AM.
>
> I know the UNDO tablespace filling up is a problem because the Oracle
> DBA insists it is.

I wouldn't be so sure. <g>

> If there is no downside to this tablespace filling
> up, then I have been mislead.

Quite possibly.

> Perhaps my terminology is wrong
> regarding the "temporary" table. I create a table in the schema's
> tablespace, run queries against it, and then drop it when I am done.

That is what we understood and NEVER ... EVER ... do that in Oracle. It
is completely unnecessary under all conditions and kills scalability and
performance.

You do that in SQL Server as a work around for poorly designed locking
and transaction handling going back to its roots in Sybase and Ingres:
But not here.

> Thus, perhaps it is not a "temporary table" as defined in the
> literature.

Not in Oracle. It is a permanent table being built on-the-fly and it is
painful. It contains no indexes, the optimizer knows nothing about it,
and the overhead of creating and dropping it beyond your imagination.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Just to add to the other information posted in this thread...


> 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?

As has been stated, how does the DBA verify that the Undo ts has been
"filled up"? One may query DBA_SEGMENTS or DBA_FREE_SPACE and see
allocated space with no free space in the tablespace, but that does not
mean that the extents do not have free space.

Oracle will clean up any unneeded undo records automatically. There is
no interface for you to tell Oracle to clean up undo records manually.

> 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?

The SELECT statement does not actually create nor require undo records
to be in the UNDO tablespace. However, if DML has occurred on the block
that the SELECT statement accesses, the undo records are used to
generate a read-consistent image of the block.

The length of time that the undo records are kept in the undo tablespace
is determined by the UNDO_RETENTION initialization parameter. If the DBA
has defined this parameter to store hours of undo records, then the undo
will stick around for that amount of time. Once a transaction is
committed, the undo will still remain for UNDO_RETENTION seconds. This
is to support certain Flashback operations.

> 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?

If the UNDO_MANAGEMENT initialization parameter is set to AUTO, then you
cannot bypass the undo mechanism.

One thing that has not been stated is that if there is not enough space
in the Undo tablespace to store undo records for UNDO_RETENTION seconds,
then this can cause problems. If the Undo tablespace is too small, some
queries may see the Snapshot Too Old error (ORA-1555). If you are not
seeing this error, then it is highly likely that your Undo tablespace is
not too small. Things change in Oracle 10g if the DBA has defined the
Undo tablespace with RETENTION GUARANTEE in which case if the Undo ts is
too small, DML statements may not have anywhere to write undo records.
Appropriate sizing of the Undo tablespace is required.


HTH,
Brian


--
================================================== =================

Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

--
Posted via a free Usenet account from http://www.teranews.com

On Aug 3, 9:39 am, Guy Taylor <twopotsa...@gmail.com> wrote:
> I bought the book this AM.
>
> I know the UNDO tablespace filling up is a problem because the Oracle
> DBA insists it is. If there is no downside to this tablespace filling
> up, then I have been mislead. Perhaps my terminology is wrong
> regarding the "temporary" table. I create a table in the schema's
> tablespace, run queries against it, and then drop it when I am done.
> Thus, perhaps it is not a "temporary table" as defined in the
> literature.
>
> Regardless, I hope the chapter about the UNDO tablespace in the book
> can illustrate how to construct queries, or form transactions, that do
> not leverage the UNDO tablespace. This should keep my DBA from adding
> gigs and gigs of space to the tablespace.
>
> Also, thanks for all the responses. They were very helpful.

Ask your DBA to explain clearly why undo tablespace filling up is a
problem?
For example if you are running 10GR2 and have a fixed sized undo
tablespace,
then oracle automatically (by default setting) tries using up 85% of
space.

http://download.oracle.com/docs/cd/B...htm#sthref1484

As such, undo filling up is not usually an issue unless you are
encountering
out of space issues ... and/or ORA-1555's...

Anurag