Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 2567919612F0 for ; Thu, 2 Feb 2017 04:09:11 +0100 (CET) Received: from turing.freelists.org (turing.freelists.org [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTPS for ; Thu, 2 Feb 2017 04:09:11 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F21956C12A; Wed, 1 Feb 2017 22:09:09 -0500 (EST) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 02VM1Sd2x4B3; Wed, 1 Feb 2017 22:09:09 -0500 (EST) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9132C6C0EE; Wed, 1 Feb 2017 22:08:57 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 01 Feb 2017 22:07:35 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9C7B96C06D for ; Wed, 1 Feb 2017 22:07:35 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 8KzOg7bNM7PR for ; Wed, 1 Feb 2017 22:07:35 -0500 (EST) Received: from NAM03-DM3-obe.outbound.protection.outlook.com (mail-dm3nam03on0073.outbound.protection.outlook.com [104.47.41.73]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id A91206C06C for ; Wed, 1 Feb 2017 22:07:34 -0500 (EST) Received: from RO1P152MB1692.LAMP152.PROD.OUTLOOK.COM (10.171.138.144) by RO1P152MB1691.LAMP152.PROD.OUTLOOK.COM (10.171.138.143) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.874.12; Thu, 2 Feb 2017 03:07:32 +0000 Received: from RO1P152MB1692.LAMP152.PROD.OUTLOOK.COM ([10.171.138.144]) by RO1P152MB1692.LAMP152.PROD.OUTLOOK.COM ([10.171.138.144]) with mapi id 15.01.0874.021; Thu, 2 Feb 2017 03:07:32 +0000 From: Rodrigo Mufalani To: "gogala.mladen@gmail.com" CC: "oracle-l@freelists.org" Subject: Re: Tuning "INSERT as SELECT" Thread-Topic: Tuning "INSERT as SELECT" Date: Thu, 2 Feb 2017 03:07:32 +0000 Message-ID: <2D9997F0-0AA0-4F24-AE2C-977C7FA5F169@mufalani.com.br> References: ,<29c404c9-dc1c-634d-da7e-b04ad5270336@gmail.com> In-Reply-To: <29c404c9-dc1c-634d-da7e-b04ad5270336@gmail.com> Accept-Language: pt-BR, en-US Content-Language: pt-BR X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: spf=none (sender IP is ) smtp.mailfrom=rodrigo@mufalani.com.br; x-ms-exchange-messagesentrepresentingtype: 1 x-originating-ip: [187.13.249.15] x-ms-office365-filtering-correlation-id: 7f002c3f-74d8-41dd-6f08-08d44b18a177 x-microsoft-antispam: UriScan:;BCL:0;PCL:0;RULEID:(22001);SRVR:RO1P152MB1691; x-microsoft-exchange-diagnostics: 1;RO1P152MB1691;7:joYh/5/iwh4LOoZtXvY5QwX5+dWCmtG6BrPrM9bPOPvdBvfxzX9S1LKYjK8Q57fkApvGmDVAMsXbec/drj1+cXvEIzI2RWH2lgqmzety0E4YzIywG7tKa1ZoJXH03sXCsGClM3q1SJC5O85yLxqyQ2uQ/2H96IRVYQ7ddffNsKpe0O9N00Foez8R7M7izyfnHIGyxmk7aIux69ZEFSH+Kku3Cy0FTzPg6NwQRVN03s5CrCRG2l3RUdevFFEW1G+Ee0vEgoEfSvyElAph0P0ucMEGN5ixHddU29mCyuV1w6H9W9dIlCa0KnrYSSQTsuhjb7GB7mYlGNUobuku+yaW4g7Pc0DEvmwatwT/BTBq0FxG9l1tsGBxN+scbImztiiLGK1EOqRF2KB/OlMPfKwLff8Tchg7uRv0Y8OXpDnpsjQXfTgTjcpYgCBXZA/yL+YF+sDyz2zqTln8LFHXAbBS0djFTJqUgBNFyKlFbXpHzVL8TTtoJL+fh9Iw1xxL9p5gGhCfiGrnfBOIRE75Owr2lNv8Nlkty1vfIy7K0DEkpkzCJ44FqigJMTUzmJPdEyLR x-microsoft-antispam-prvs: x-exchange-antispam-report-test: UriScan:(189930954265078); x-exchange-antispam-report-cfa-test: BCL:0;PCL:0;RULEID:(6040375)(2401047)(8121501046)(5005006)(3002001)(10201501046)(6041248)(20161123564025)(20161123562025)(20161123560025)(20161123555025)(20161123558025)(2016111802025)(6072148)(6042181)(6043046);SRVR:RO1P152MB1691;BCL:0;PCL:0;RULEID:;SRVR:RO1P152MB1691; x-forefront-prvs: 02065A9E77 x-forefront-antispam-report: SFV:NSPM;SFS:(10009020)(7916002)(69234005)(24454002)(117514003)(189002)(377454003)(199003)(68736007)(74482002)(36756003)(77096006)(189998001)(54356999)(33656002)(86362001)(39060400001)(106116001)(83716003)(106356001)(6486002)(5640700003)(82746002)(105586002)(76176999)(38730400001)(8676002)(81166006)(50986999)(8936002)(6506006)(6436002)(2351001)(97736004)(229853002)(81156014)(122556002)(66066001)(4326007)(6916009)(3660700001)(6116002)(53936002)(6512007)(102836003)(2950100002)(2501003)(236005)(101416001)(2900100001)(3846002)(5660300001)(92566002)(3280700002)(110136003)(7736002)(54896002)(2906002)(42882006)(104396002);DIR:OUT;SFP:1101;SCL:1;SRVR:RO1P152MB1691;H:RO1P152MB1692.LAMP152.PROD.OUTLOOK.COM;FPR:;SPF:None;PTR:InfoNoRecords;MX:1;A:1;LANG:en; received-spf: None (protection.outlook.com: mufalani.com.br does not designate permitted sender hosts) spamdiagnosticoutput: 1:99 spamdiagnosticmetadata: NSPM Content-Type: multipart/alternative; boundary="_000_2D9997F00AA04F24AE2C977C7FA5F169mufalanicombr_" MIME-Version: 1.0 X-OriginatorOrg: mufalani.com.br X-MS-Exchange-CrossTenant-originalarrivaltime: 02 Feb 2017 03:07:32.4037 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 1fad75d0-66fc-4837-a5fd-39ad4e1a75bf X-MS-Exchange-Transport-CrossTenantHeadersStamped: RO1P152MB1691 X-archive-position: 67547 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: rodrigo@mufalani.com.br Precedence: normal Reply-To: rodrigo@mufalani.com.br List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --_000_2D9997F00AA04F24AE2C977C7FA5F169mufalanicombr_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable hello JP, Have you tried "alter session enable parallel dml;"?! You can use "force" option too. [ ]'s #mufalani Desculpe por erros! Este e-mail foi escrito do meu smartphone! Sorry for typos! This mail was written from my smartphone!!! Em 2 de fev de 2017, ?s 01:03, Mladen Gogala > escreveu: Prem, please turn on SQL tracing using DBMS_MONITOR or alter session and se= e what events you are waiting for. Everything else is pure witchcraft. Regards On 02/01/2017 07:48 PM, Prem Khanna J wrote: Friends, I have a question regarding tuning "insert .. as select" statment on 12.1. source table (paritioned) has 80million records . The above statment selects the records from source table ,does some little formatting/massagin= g and then inserts into target table which again is partitioned . Finally ,= close to 80million records will need to be inserted into the target table. we tuned the SELECT stmt and that alone gets done in 15 mins. we are okay w= ith that. But when it works along with "INSERT..as SELECT" , the INSERT hap= pens slow. Takes about 2?3 hours (Disbaled all the indexes , added nologgin= g etc.). The target table is partitoined such that all these 80M recs go in= to 1 single partition.This is 1 day's data. Every day 80m recs will go into= other partition (as it is partitioned on date). FYI : the machine has 50 C= PUs and enough CPU is available for parallel processes. Tried "insert /*+ append parallel */ hint too . But still takes 2 hrs. Chec= ked the explain plan . The final "LOAD as SELECT" line does not fall under = a "PX co-ordinator" makes me think , INSERT does not happen parllely. My qu= estion, with just one partition can append happen in parallel ? Would sub-p= arition help and make insert happen in parallel ? Or it does not even matte= r !!! I am going to try it anyway.But would like hear your expert opinion and the= best way to do it. Regards, JP -- Mladen Gogala Oracle DBA Tel: (347) 321-1217 --_000_2D9997F00AA04F24AE2C977C7FA5F169mufalanicombr_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
hello JP,

     Have you tried "alter session enable parallel= dml;"?! 

      You can use "force" option too.

[ ]'s
#mufalani

   Desculpe por erros! Este e-mail foi escrito do meu smartp= hone!

    Sorry for typos! This mail was written from my smartphon= e!!!

Em 2 de fev de 2017, às 01:03, Mladen Gogala <gogala.mladen@gmail.com> escreveu:

Prem, please turn on SQL tracing using DBMS_= MONITOR or alter session and see what events you are waiting for.  Eve= rything else is pure witchcraft.
Regards

On 02/01/2017 07:48 PM, Prem Khanna J wrote:
Friends,

I have a question regarding tuning "insert .. as select" sta= tment on 12.1.
source table (paritioned) has 80million records . The above statment&n= bsp;
selects the records from source table ,does some little formatting/mas= saging and then inserts into target t= able which again is partitioned . Finally , close to 80million records will need to be inserted into the target table.&n= bsp;

we tuned the SELECT stmt and that alone gets done in 15 mins. we are o= kay with that. But when it works alon= g with "INSERT..as SELECT" , the INSERT happens <= /span>slow. Takes about 2～3 hours (= Disbaled all the indexes , added nologging etc.). The target table is partitoined such that all these 80M recs go into 1 singl= e partition.This is 1 day's data. Every day 80m recs will go into other partition (as it is partitioned on date). FYI : the machine has 50 CPUs and enough CPU is available for parallel p= rocesses.

Tried "insert /*+ append parallel */ hint too . But still tak= es 2 hrs. Checked the explain plan . = The final "LOAD as SELECT" line does not fall under a "PX co= -ordinator" makes me think , INSERT does not happen parllely. My question, with just one partition can append happen in= parallel ? Would sub-pariti= on help and make insert happen in parallel ? Or it does not even= matter !!! 

I am going to try it anyway.But would like hear your expert opinion an= d the best way to do it.

Regards,
JP


--=20
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217
--_000_2D9997F00AA04F24AE2C977C7FA5F169mufalanicombr_-- -- http://www.freelists.org/webpage/oracle-l