Hello, I'm an Oracle newbie and I had the following issue on Oracle 11g:
I wanted to copy a table with
INSERT INTO table1 (SELECT "column1", "column2" FROM table2);
the table has one BLOB column and a little less than 9 Million records, the process was not finished after 2,5 hours. Is that normal or do I have weird settings?
2.5 hours to insert 9 million rows does sound too long. If the blobs are huge there may be lots of data to transfer though.
To make it faster, you need to understand
why it's currently slow.
Are there triggers on the target table? If so, review what these do; removing these is likely to give a good performance boost
Are there any other uncommitted inserts adding the same primary key/unique values? If so, commit them!
If neither of these apply, review the plan for the statement to see what work it's doing.
There are several ways you can do this:
- If you're appropriately licensed (diagnostics & tuning packs), viewing the SQL monitor report will show you want the statement is doing
- If you're not licensed, you can get the execution plan for the insert with:
set serveroutput off
alter session set statistics_level = all;
<your insert>
select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
- Trace the session and parse the output with tkprof
exec DBMS_monitor.session_trace_enable ( null, null, true, true );
***your code here***
exec DBMS_monitor.session_trace_disable;
Then locate the trace file on the database server and turn it to something more readable with:
tkprof <trace_file_name> <output_file_name>
If you need help understanding the output, post it here and we'll help out.
For more on these methods see
https://blogs.oracle.com/sql/how-to-create-an-execution-plan