Oracle Database test data insertion speed

Executeimmediate is a dynamic SQL statement. It is often used when the table name field name is a variable. In the case of input parameters, because the table name is unknown, you cannot directly write SQL statements.

Execute immediate is a dynamic SQL statement. It is often used when the table name field name is a variable. In the case of input parameters, because the table name is unknown, you cannot directly write SQL statements.

I. No optimized speed: Executed in 69.436 seconds

Drop table t purge;
Create table t (x int );
/* Clear the sharing pool. do not perform this operation in the production environment */
Alter system flush shared_pool;

Create or replace procedure proc1
As
Begin
For I in 100000
Loop
Execute immediate
'Insert into t values ('| I | ')';
Commit;
End loop;
End;
/

Next, check the execution time of the proc1 insert 100000 record.
SQL> set timing on;
SQL> exec proc1;

PL/SQL procedure successfully completed

Executed in 69.436 seconds

/* You can use the following statement to view the specific steps of the stored procedure */
Select t. SQL _text,t. SQL _id,t.parse_calls,t.exe cutions from v $ SQL t where SQL _text like '% insert into t values % ';

To facilitate viewing the preceding statements executed by PL/SQL DEVELOPER, for example:

As you can see from the above, every statement is parsed only once and executed once. A total of 0.1 million records are parsed. Maybe you will ask you that there are only 7136 rows of records above, how did you resolve it for 0.1 million times. I can tell you that it must have been resolved for 0.1 million times, because my Shared Pool space is not large and contains a large amount of 0.1 million pieces of information. According to the FIFO principle, you can see that, all the SQL statements I have found are SQL STATEMENT records starting from more than 92000. We know that these SQL statements are similar and there is no need to parse 0.1 million times, that is, each statement is parsed once. This PROC1 does not use variable binding. This is where we can optimize it. We use the Bind Variable to re-test, the following PROC2 can be parsed only once, of course, the speed will certainly increase a lot.

2. speed after optimization using bind variables: Executed in 26.505 seconds

Drop table t purge;
Create table t (x int );
/* Clear the sharing pool. do not perform this operation in the production environment */
Alter system flush shared_pool;

Create or replace procedure proc2
As
Begin
For I in 100000
Loop
Execute immediate
'Insert into t values (: x) 'using I;
Commit;
End loop;
End;
/

SQL> set timing on;
SQL> exec proc2;

PL/SQL procedure successfully completed

Executed in 26.505 seconds

From the above we can see that the time is basically halved.

/* You can use the following statement to view the specific steps of the stored procedure */
Select t. SQL _text,t. SQL _id,t.parse_calls,t.exe cutions from v $ SQL t where SQL _text like '% insert into t values % 'order by 1;

As you can see from the preceding execution, it is parsed once and executed 0.1 million times. It fully complies with our conjecture, so the speed is greatly improved.

Execute immediate is a dynamic SQL statement. It is often used when the table name field name is a variable and the input parameter is unknown. Therefore, you cannot directly write SQL statements, therefore, dynamic SQL statements are used to concatenate the table name and field name parameters into SQLSTATEMENT, and execute immediate is called for execution. However, my example can be written statically without any dynamic needs.

For more details, please continue to read the highlights on the next page: