Skip to Main Content
  • Questions

  • Insert statement is taking lot of temp space.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kalyan.

Asked: November 24, 2015 - 2:27 pm UTC

Last updated: November 26, 2015 - 1:45 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Tom,

I am copying table partitions from another database using dblink but the insert statement is taking lot of temp space and few of the partition copies are failing with unable extend temp segment.Could you please explain me why insert statement is taking temp space.

insert statement follows like this.

insert /*+ append nologging parallel(A,16) */ into


---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | TQ/Ins |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 48M| | | |
| 1 | PX COORDINATOR | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 2147M| 192G| 48M| Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | Q1,01 | PCWP | |
| 4 | BUFFER SORT | | | | | Q1,01 | PCWC | |
| 5 | PX RECEIVE | | 2147M| 192G| 48M| Q1,01 | PCWP | |
| 6 | PX SEND ROUND-ROBIN| :TQ10000 | 2147M| 192G| 48M| | S->P | RND-ROBIN |
| 7 | REMOTE | OISVFA2_2015061515643 | 2147M| 192G| 48M| G3C1 | R->S | |
---------------------------------------------------------------------------------------------------------------



and Chris said...

The select statement on the remote database is run serially (not parallel). The insert is run in parallel however.

Oracle has to split the data up into chunks to do the parallel insert (lines 4-6). You're reading a huge amount of data (192G according to the plan). It's holding the data in the PGA while it does this.

Unless you've got a vast amount of memory, I doubt this fits in your PGA. So Oracle is writing it to temp.

Some things you could try to overcome this:

- Reduce the number of parallel servers, or remove it completely
- Parallelize the select
- DIY parallelization

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Source queries running with parallel

Kalyan Chukka, November 26, 2015 - 6:20 am UTC

But source query is also running with parallel 16 as inserts.

Here is the plan of the source select query.

SELECT /*+ OPAQUE_TRANSFORM PARALLEL ("B",16) */ <COLUMNS> FROM "BOOKER"."OISVFA2_2015052362802" "B"

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4039360790

------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 124M| 10G| 686 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 124M| 10G| 686 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 124M| 10G| 686 | 734 | 734 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| O_IP_SCOPE_VENDOR_FC_ARTIFACTS | 124M| 10G| 686 | 734 | 734 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------


Connor McDonald
November 26, 2015 - 1:45 pm UTC

It's still serializing the data when it send it over the database link:

https://asktom.oracle.com/pls/asktom/f?p=100:11:::::p11_question_id:3561967900346593583

You could still try reducing the number of parallel process, running it serially or trying DIY parallelism.

Alternatively, if this is one-off operation you could export/import the data, use transportable tablespaces or some other offline approach.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions