I am inserting huge volume of records from one table into another table using insert into using select statement in package. I am using multithreading in select statement.Shall I use /*+ APPEND NOLOGGING */ hint in the insert statement. Is it really improve performance and also is it good idea to create index on global temporary table in oracle?
The performance improvements of the APPEND hint can be massive for multiple reasons. Enabling direct-path writes allows Oracle to avoid writing multiple copies of the data, such as redo, undo, and archive logs. Direct-path writes can also enable compression, automatic statistics collections, and other optimizations. But beware of the important downsides of direct-path writes: the changes are not recoverable until the next backup, and the table is completely locked until a COMMIT.
If you're already using multi-threading on the reads, you might as well use multi-threaded writes with a hint like INSERT /*+ APPEND PARALLEL(8) */ .... But you might need to use ENABLE_PARALLEL_DML hint or enable parallel DML at the session level.
There's a good chance the APPEND hint won't initially improve performance because there are many limitations on direct-path writes, such as no the logging property (if your database is in archivelog mode), triggers, foreign keys, etc. (Note that LOGGING is not a hint, it is an object property.)
Look carefully at your execution plans to ensure you are getting direct-path writes. You should see an operation named LOAD AS SELECT instead of LOAD TABLE CONVENTIONAL to ensure direct-path writes are used. And you should see a PX ... operation before any operation you want parallelized.
If you're using a modern version of Oracle, the Note section of the execution plan may tell you why you're not getting direct-path writes or parallelism. And a SQL Monitor Report (generated through DBMS_SQLTUNE.REPORT_SQL_MONITOR) can help you identify problems with the degree of parallelism and other performance problems.
On many systems, you can improve INSERT performance by 100X or more, but it may take a lot of effort.