• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

Optimize insert statement

Hello,

How can I optimize this insert statement, the select statement execute in 1s:
insert into PS_ARCH_TMP 
(select a.GROUP_BU, 
        a.GROUP_ID, 
        a.BUSINESS_UNIT, 
        a.CUST_ID, 
        a.ITEM, 
        a.ITEM_LINE, 
        a.ENTRY_TYPE, 
        a.ITEM_ACCTG_DT 
   from PS_PENDING_ITEM a, 
        PS_ITEM c 
  where a.posted_flag = 'Y' 
    and a.entry_type = 'MT' 
    and a.BUSINESS_UNIT in ('L0065', 'L0163', 'L0181') 
    and a.ACCOUNTING_DT < '01-JAN-08' 
    and c.item = a.item 
    and c.item_line = a.item_line 
    and c.item_status = 'C' 
    and not exists (select 'X' 
                      from PS_PENDING_ITEM b 
                     where a.GROUP_BU = b.GROUP_BU 
                       and a.GROUP_ID = b.GROUP_ID 
                       and a.CUST_ID = b.CUST_ID 
                       and b.ITEM_ACCTG_DT >= '01-JAN-08'))

Open in new window

PS_ARCH_TMP is a global temporary table.

Thanks

bibi
0
bibi92
Asked:
bibi92
  • 10
  • 8
1 Solution
 
slightwv (䄆 Netminder) Commented:
How much time does it take now?  We need a realistic goal.
0
 
slightwv (䄆 Netminder) Commented:
Post the results of the following from sqlplus:


explain plan for
select a.GROUP_BU,
        a.GROUP_ID,
        a.BUSINESS_UNIT,
        a.CUST_ID,
        a.ITEM,
        a.ITEM_LINE,
        a.ENTRY_TYPE,
        a.ITEM_ACCTG_DT
   from PS_PENDING_ITEM a,
        PS_ITEM c
  where a.posted_flag = 'Y'
    and a.entry_type = 'MT'
    and a.BUSINESS_UNIT in ('L0065', 'L0163', 'L0181')
    and a.ACCOUNTING_DT < '01-JAN-08'
    and c.item = a.item
    and c.item_line = a.item_line
    and c.item_status = 'C'
    and not exists (select 'X'
                      from PS_PENDING_ITEM b
                     where a.GROUP_BU = b.GROUP_BU
                       and a.GROUP_ID = b.GROUP_ID
                       and a.CUST_ID = b.CUST_ID
                       and b.ITEM_ACCTG_DT >= '01-JAN-08');

SELECT * FROM TABLE(dbms_xplan.display);
0
 
slightwv (䄆 Netminder) Commented:
Sorry.  I misread.  The select above executes in 1 second.  How long does the insert take?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bibi92Author Commented:
it doesn't finish in 10minutes.
thanks
bibi
0
 
slightwv (䄆 Netminder) Commented:
Add an append hint to the insert and try that:

Insert /*+ APPEND */ into ... -- the rest of your your code.

Also, what makes you think the select finishes in 1 second?  The blocks might be buffered in the cache and appear to run quick where there is no disk i/o where the insert needs to do disk i/o.  

I would still like to se the explain plan.
0
 
bibi92Author Commented:
Plan
Plan
SELECT STATEMENT  CHOOSECost: 20,005  Bytes: 360  Cardinality: 4  						
	9 FILTER  					
		6 TABLE ACCESS BY INDEX ROWID FS88.PS_ITEM Cost: 15  Bytes: 16  Cardinality: 1  				
			5 NESTED LOOPS  Cost: 19,985  Bytes: 360  Cardinality: 4  			
				3 INLIST ITERATOR  		
					2 TABLE ACCESS BY INDEX ROWID FS88.PS_PENDING_ITEM Cost: 17,600  Bytes: 11,766  Cardinality: 159  	
						1 INDEX RANGE SCAN NON-UNIQUE FS88.PSEPENDING_ITEM Cost: 4,551  Cardinality: 13,448  
				4 INDEX RANGE SCAN NON-UNIQUE FS88.PSCITEM Cost: 2  Cardinality: 12  		
		8 TABLE ACCESS BY INDEX ROWID FS88.PS_PENDING_ITEM Cost: 5  Bytes: 41  Cardinality: 1  				
			7 INDEX RANGE SCAN UNIQUE FS88.PS_PENDING_ITEM Cost: 4  Cardinality: 1  

Open in new window

                 
                  
0
 
slightwv (䄆 Netminder) Commented:
Is that output from sqlplus?  I like the way sqlplus defaults the output.  A sample from a select from dual is below.

You didn't answer all my questions.

From above:  "Also, what makes you think the select finishes in 1 second?"

Can you spool out all the output from that select in 1 second?  

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

Open in new window

0
 
bibi92Author Commented:
SQL> explain plan for
  2  select a.GROUP_BU,
  3          a.GROUP_ID,
  4          a.BUSINESS_UNIT,
  5          a.CUST_ID,
  6          a.ITEM,
  7          a.ITEM_LINE,
  8          a.ENTRY_TYPE,
  9          a.ITEM_ACCTG_DT
 10     from PS_PENDING_ITEM a,
 11          PS_ITEM c
 12    where a.posted_flag = 'Y'
 13      and a.entry_type = 'MT'
 14      and a.BUSINESS_UNIT in ('L0065', 'L0163', 'L0181')
 15      and a.ACCOUNTING_DT < '01-JAN-08'
 16      and c.item = a.item
 17      and c.item_line = a.item_line
 18      and c.item_status = 'C'
 19      and not exists (select 'X'
 20                        from PS_PENDING_ITEM b
 21                       where a.GROUP_BU = b.GROUP_BU
 22                         and a.GROUP_ID = b.GROUP_ID
 23                         and a.CUST_ID = b.CUST_ID
 24                         and b.ITEM_ACCTG_DT >= '01-JAN-08');

Explicité.

SQL> 
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
                                                                                
--------------------------------------------------------------------------------
----                                                                            
                                                                                
| Id  | Operation                       |  Name            | Rows  | Bytes | Cos
t  |                                                                            
                                                                                
--------------------------------------------------------------------------------
----                                                                            
                                                                                
|   0 | SELECT STATEMENT                |                  |     1 |   131 | 303

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
47 |                                                                            
                                                                                
|*  1 |  TABLE ACCESS BY INDEX ROWID    | PS_ITEM          |     1 |    16 |    
15 |                                                                            
                                                                                
|   2 |   NESTED LOOPS                  |                  |     1 |   131 | 303
47 |                                                                            
                                                                                
|   3 |    NESTED LOOPS ANTI            |                  |     1 |   115 | 303
32 |                                                                            
                                                                                

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
|   4 |     INLIST ITERATOR             |                  |       |       |    
   |                                                                            
                                                                                
|*  5 |      TABLE ACCESS BY INDEX ROWID| PS_PENDING_ITEM  |  3183 |   230K| 176
00 |                                                                            
                                                                                
|*  6 |       INDEX RANGE SCAN          | PSEPENDING_ITEM  | 13448 |       |  45
51 |                                                                            
                                                                                
|*  7 |     TABLE ACCESS BY INDEX ROWID | PS_PENDING_ITEM  |    13M|   525M|    
 4 |                                                                            

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
                                                                                
|*  8 |      INDEX RANGE SCAN           | PS_PENDING_ITEM  |     1 |       |    
 3 |                                                                            
                                                                                
|*  9 |    INDEX RANGE SCAN             | PSCITEM          |    12 |       |    
 2 |                                                                            
                                                                                
--------------------------------------------------------------------------------
----                                                                            
                                                                                
                                                                                

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - filter("C"."ITEM_LINE"="A"."ITEM_LINE")                                  
   5 - filter("A"."POSTED_FLAG"='Y' AND "A"."ACCOUNTING_DT"<'01-JAN-08')        
   6 - access(("A"."BUSINESS_UNIT"='L0065' OR "A"."BUSINESS_UNIT"='L0163' OR    
              "A"."BUSINESS_UNIT"='L0181') AND "A"."ENTRY_TYPE"='MT')           
       filter("A"."ENTRY_TYPE"='MT')                                            
   7 - filter("B"."ITEM_ACCTG_DT">='01-JAN-08')                                 
   8 - access("A"."GROUP_BU"="B"."GROUP_BU" AND "A"."GROUP_ID"="B"."GROUP_ID" AN
D                                                                               

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
                                                                                
              "A"."CUST_ID"="B"."CUST_ID")                                      
       filter("A"."CUST_ID"="B"."CUST_ID")                                      
   9 - access("C"."ITEM"="A"."ITEM" AND "C"."ITEM_STATUS"='C')                  
                                                                                
Note: cpu costing is off                                                        

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I'm hung up on the 13 Million rows and 525 Meg processed from PS_PENDING_ITEM and you say it does this in 1 second.

That is likely coming from the sub-select in the 'not exists' select.

For the rest of t he plan, nothing jumps out at me.

I'm not where I can create a real test but try looking at  the session wait events when running the insert.

Check out the examples found in:
http://www.databasejournal.com/features/oracle/article.php/3388861/True-Session-Wait-Activity-in-Oracle-10g.htm

0
 
bibi92Author Commented:
When I executed the select with sqlplus, the result is returned immediately.

Thanks

bibi
0
 
slightwv (䄆 Netminder) Commented:
I'm not online any more.  Check the wait events.  Should be a wait in the tmp table.  Did you try the append hint?

For grins from sqlplus:
Set autotrace on stat


The run your insert.
0
 
bibi92Author Commented:
Thanks. Hin append doesn't change anything.
0
 
slightwv (䄆 Netminder) Commented:
Can you post the results of the insert after setting autotrace on with the above command?
0
 
bibi92Author Commented:
it doesn't finish. I think we will change the query. thanks
0
 
slightwv (䄆 Netminder) Commented:
That one will actually run the insert statement.  It will take as long as the insert takes.  I believe you mentioned 10 minutes.

How will changing the query which runs in 1 second fix a 10 minute insert?

0
 
bibi92Author Commented:
it doesn't finish in 10minutes.
thanks
bibi
0
 
slightwv (䄆 Netminder) Commented:
oh...  I mis-read the 10 minutes last night.  Sorry.

Check for locks on PS_ARCH_TMP.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/manproc.htm#sthref764

There are a few simple script examples at:
http://psoug.org/reference/locks.html
0
 
bibi92Author Commented:
Thanks bibi
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now