Ryan Gaffuri wrote:
> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1070328396.786977_at_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.
>
>
> i know what it does. Ive traced it. Ive also noticed that what it does
> causes contention for resources and affects throughput. When doing
> batch processing your often not concerned with throughput. Your
> concerned with response time. That resource contention is not much of
> a concern when you do not have to worry about alot of users. when your
> primary concern is getting the batch done quickly.
>
> Also, alot of people(including us) are moving to shared storage where
> you run multiple servers on one storage system. In this case physical
> I/Os become important. Since you may have alot of servers hitting one
> big pipe. I found that create table as nologging significantly reduces
> physical i/o over inserting into a global temporary table.
>
> I have found that tuning for throughput and tuning for response time
> can lead to somewhat different paths in some circumstances.
>
> as I have said before this process will scale up in terms of more
> data, etc... involved but will not scale for many, many concurrent
> users. If you need to balance batch processes and concurrency then
> this would not be appropriate.
And you are, I presume, using the APPEND hint.
--
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 Tue Dec 02 2003 - 14:26:21 CST
