SQL & PL/SQL

Hi All,

I have a performance issue during with a table which is partitioned. without table being partitioned
it ran in less time but after partition it took more than double.

1) The table was created initially without any partition and the below insert took only 27 minuts.
Total Rec Inserted :- 2424233

PL/SQL procedure successfully completed.

Elapsed: 00:27:35.20


2) Now I re-created the table with partition(range yearly - below) and the same insert took 59 minuts.
Is there anyway i can achive the better performance during insert on this partitioned table?


[ similerly, I have another table with 50 Million records and the insert took 10 hrs without partition.
with partitioning the table, it took 18 hours... ]


SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4195045590

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 643K| 34M| | 12917 (3)| 00:02:36 |
|* 1 | HASH JOIN | | 643K| 34M| 2112K| 12917 (3)| 00:02:36 |
| 2 | VIEW | index$_join$_001 | 69534 | 1290K| | 529 (3)| 00:00:07 |
|* 3 | HASH JOIN | | | | | | |
| 4 | INDEX FAST FULL SCAN| PK_ACCOUNT_MASTER_BASE | 69534 | 1290K| | 181 (3)| 00:00
| 5 | INDEX FAST FULL SCAN| ACCOUNT_MASTER_BASE_IDX2 | 69534 | 1290K| | 474 (2)| 00:00

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 6 | TABLE ACCESS FULL | TB_SISADMIN_BALANCE | 2424K| 87M| | 6413 (4)| 00:01:17 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."VENDOR_ACCT_NBR"=SUBSTR("B"."ACCOUNT_NO",1,8) AND
"A"."VENDOR_CD"="B"."COMPANY_NO")
3 - access(ROWID=ROWID)



Open C1;

Loop

Fetch C1 Bulk Collect Into C_Rectype Limit 10000;

Forall I In 1..C_Rectype.Count


Insert test
(
col1,col2,col3)
Values
(
val1, val2,val3);

V_Rec := V_Rec + Nvl(C_Rectype.Count,0);

Commit;

Exit When C_Rectype.Count = 0;


C_Rectype.delete;

End Loop;

End;
/

Total Rec Inserted :- 2424233

PL/SQL procedure successfully completed.

Elapsed: 00:51:01.22



Edited by: user520824 on Jul 16, 2010 9:16 AM