We've got some performance issues that somebody thinks could be alleviated by
using Temp tables.

However, the DB management group has decreed that no temp tables will be used at
their installation.

I'm guessing they have a pretty good reason, but what would it be?

Memory?
-----------------------
PeteCresswell

Did you ask?

(Pete Cresswell) wrote:

--
Ron Reidy
Oracle DBA

On Mon, 22 Sep 2003 21:34:43 GMT, "(Pete Cresswell)" <x@y.z> wrote:

Have you asked them?

Are they making the distinction between dynamically-created temporary tables
(almost universally wrong) or global temporary tables created only once (not
often needed, but considerably less evil).

GTTs will use temp tablespace instead of your data tablespaces, and will
generate less redo compared with ordinary tables.

--
Andy Hassall (andy (AT) andyh (DOT) co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

RE/
This is a beeeeeg organization. We're talking minimum 2-3 day turnaround and at
least one email pissing contest to get an answer to a question like that.

I was trolling here for a "no-brainer" answer - like maybe 99% of all admins
don't like temp tables because of some near-universal downside like
unexpected/unpredictable changes in memory needs.

If there's an obvious answer, I could avoid bugging a lot of people for no good
reason.
-----------------------
PeteCresswell

RE/
The situation is that of creating reports. Bond trading application, many
desks, many users at each desk, all at each other's throats, nobody wants to
wait for the next guy - especially if they're on a competing desk.

The first choice offered was temporary tables. Applealing because it puts each
user in their own little world. If a hundred users try to run reports at the
same time, performance will degrade - but everybody waits - nobody goes to the
head of the line at the expense of others - everybody has the perception that
their reports are being run *now*, albeit sometimes slowly.

There's a second choice involving global tables, but part of the situation is
that many people may choose to run reports that use the same suite of data at
nearly the same times - yet each group of reports needs to be as "realtime" as
possible. My current understanding of the global table option is that it will
require locking out everybody except one user at a time - something about
inserting into a temp table wiping pre-existing data - but I know nothing of
P-SQL or Oracle.
-----------------------
PeteCresswell

On Mon, 22 Sep 2003 22:52:35 GMT, "(Pete Cresswell)" <x@y.z> wrote:

Sounds like you've not understood what global temporary tables are about?
Their _structure_ is fixed, but the _data_ is visible only to the session that
inserted it. Don't know where you heard about having to lock everyone out, but
it's wrong. (Perhaps unless you're pooling all the reports through a single
Oracle session, but then that's not going to allow more than one to run at a
time anyway, so it can't be that). Multiple users can use a single global
temporary table, and each has their own isolated set of data, invisible from
each other.

If they've decreed 'no dynamically created "temporary" tables using the plain
"CREATE TABLE x" syntax, followed by a "DROP TABLE x" at some point' then
they're correct. Adding and removing tables are schema changes, and you
shouldn't be changing schema except in controlled circumstances (i.e.
application upgrade).

If you create normal tables on the fly:

* if reports die unexpectedly, what cleans up the table? With global temporary
tables, Oracle cleans out the data at end of transaction or end of session
(depending on the option chosen at create time).

* you have to grant 'create table' and 'drop table' to an application user;
application users shouldn't have DDL access to the schema.

* you can't reuse any of the cached SQL, since all the object names are
different, so hard parses will go up

* the app has to decide which tablespaces to use, etc.; that shouldn't be the
job of the app, it's the DBA's job

* you get all the redo and undo overhead for the temporary data, which isn't
needed if the data itself is only temporary; global temporary tables cut these
both down

* no doubt many other points

--
Andy Hassall (andy (AT) andyh (DOT) co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

RE/
I think you've nailed it.

I was missing the "fixed structure, data-visible-to-only-inserting-session"
part.

Now it makes more sense.

I'm taking the "no schema changes except under controlled cirumstances" part on
faith. Would the prohibition be because letting somebody do a CEATE
TABLE/DROP TABLE for temp tables mean that they could do the same thing with
"real" tables?
-----------------------
PeteCresswell

(Pete Cresswell) wrote:


1. Kills performance and scalability
2. The privileges to create objects do not belong in a production schema
except during brief periods when it is being built or modified
under the direct supervision of the DBA.
3. Completely unncessary so no reason to do it.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

RE/
I'm not trying to be difficult - I'm taking everything that's been said to
heart...but I'd also like to be able to argue the point with somebody who tells
me they want to use Temp tables.

#1 makes sense - but it would be good to have a little insight into why.

#2 sounds like argument by assertion - but that's probably because I don't know
diddley.... Something about objects being created/destroyed impacting
performance would be my first guess.

#3, I'm guessing, is because of the availablity of global temp tables. True?
-----------------------
PeteCresswell

"(Pete Cresswell)" <x@y.z> wrote

Use scientific method to show that they are correct or not. Your hypothesis
is that using temp tables will increase performance and scalability. If the
DB management group has the time they should show a small test case proving
that the aforementioned hypothesis is wrong. (temp tables will cause
scalability and performance problems) Tom Kyte has a nice book out that has
some tools that he uses (scripts) to test such hypotheses. Tom shows how to
set up small benchmarks to do these type of tests. You can go to
asktom.oracle.com and search for temp tables.

Personally, I think you are going to find that the person who wants temp
tables probably is from a non-Oracle background (eg Sybase or SQLServer)
where they are used all the time for a variety of reasons. In Oracle it
would be very very rare that a temp table would get what you are asking for.
A view probably would work much better or at worst global temporary tables.
Jim