Re: Deadly sins againts database performance/scalability

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 01 Dec 2003 17:26:03 -0800
Message-ID: <1070328396.786977@yasure>

Ryan Gaffuri wrote:

> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<bq9n88$cdr$1$8300dec7_at_news.demon.co.uk>...
> 

>>Answer in-line
>>
>>--
>>Regards
>>
>>Jonathan Lewis
>>http://www.jlcomp.demon.co.uk
>>
>> The educated person is not the person
>> who can answer the questions, but the
>> person who can question the answers -- T. Schick Jr
>>
>>
>>One-day tutorials:
>>http://www.jlcomp.demon.co.uk/tutorial.html
>>
>>
>>Three-day seminar:
>>see http://www.jlcomp.demon.co.uk/seminar.html
>>____UK___November
>>
>>
>>The Co-operative Oracle Users' FAQ
>>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>>
>>
>>"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
>>news:1069957744.97573_at_yasure...
>>
>>>Niall Litchfield wrote:
>>>
>>>
>>>>Actually I'm beginning to regret that post now.
>>>>
>>>>As Joel rightly says there is a real danger in creating a list like that
>>>>one. In my case my post might just have suggested that one should always
>>
>> use
>>
>>>>bind variables (I believe you could read it that way :) ) and this is of
>>>>course untrue, one should always use them, except where they are
>>>>inappropriate.
>>>>
>>>
>>>And they are in appropriate ... when?
>>>
>>
>>DSS systems where the queries are few and
>>large, and the optimiser's need for precise inputs
>>is more important than a (relatively) minor increase
>>in CPU and latch costs on parse/optimise.

> 
> 
> I also disgree that you should never create intermediate tables and
> drop them. I think this is taken to an extreme. Now in a high
> transaction system you should not because it causes latch contention
> and affects throughput.
> 
> However, in a DSS system or for nightly batch processes, Ive found
> VERY large improvements in performance by doing create table as
> nologging in paralel. Its much faster than inserting into a global
> temp table. you dont have to worry about throughput if your looking at
> 5-10 users on the system max. Now you need to document it well(which
> most people dont do), so people know what to alter if you need to
> scale to more users. If I only have a certain window of time to get a
> series of batch processes done, Ill use temp tables like this. I find
> they are very useful when working with remote databases. You do a
> quick create table as to get just the data you need, then do your
> joins to the local table, so your not pushing data across a DB link
> for 10 different queries.
> 
> However, this will not scale to alot of users. So it needs to be well
> documented.

If you ever want to have some fun do the following:

CREATE TABLE test (testcol VARCHAR2(20)) NOLOGGING; ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; INSERT INTO test VALUES ('ABC');
DROP TABLE test;
ALTER SESSION SET sql_trace = FALSE;

Then ...

CREATE GLOBAL TEMPORARY TABLE test (testcol VARCHAR2(20)) ON COMMIT DELETE ROWS; ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; INSERT INTO test VALUES ('ABC');
COMMIT;
ALTER SESSION SET sql_trace = FALSE;

Run the trace files through TKPROF.

Take a good look at what Oracle does to drop that table. It is not exactly a thing of beauty. Necessary ... veryt expensive.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Mon Dec 01 2003 - 19:26:03 CST