Monthly Archives: October 2013

listener, network, Oracle

Oracle defining different name for SID and Database

Leave a comment

There are a ton of reasons to define a different name for Oracle SID and database, however you must ensure that your network configuration is properly setup to support this function. The key is defining the variable GLOBAL_DBNAME in your tnsnames.ora file to match your init parameter db_name, which allows various applications to include EM to resolve the database regardless of names defined.

TNSNAMES.ora for orcl on both SID and DB_NAME.


(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(SERVICE_NAME = orcl)
(ORACLE_HOME = C:\oracle\product\11.2.0\dbhome_orcl)
)
)

TNSNAMES.ora for orcl on SID and ORACLE on DB_NAME.

(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(SERVICE_NAME = orcl)
(GLOBAL_DBNAME=ORACLE)
(ORACLE_HOME = C:\oracle\product\11.2.0\dbhome_orcl)
)
)

Larry Catt OCP

Oracle, Oracle 10g

Testing Rate of Oracle inserts into a table

Leave a comment

You can use any type of table or any data to see the rate of insert you are getting out of your Oracle RDBMS.   For this demonstration, we used a test table named INSERT_TEST and repeating insert of ever changing record sets.    You can change the scripts attached to satisfy your particular needs.

 

1.  Create table to hold insert records.

create table insert_test( var_a varchar2(100), var_b number(10,1));

2.  Insert a single record into your test table.

insert into insert_test VALUES(‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’,999999999.9);

3.  Determine the size of row inserted.

SQL> select avg_row_len from dba_tab_statistics where table_name=’INSERT_TEST’; AVG_ROW_LEN

———–        

112

SQL>

4.  Edit the following script, replacing the record size with the actual size you received in step 3 above.

SET SERVEROUTPUT ON

DECLARE  

l_loops           NUMBER := 100000;

 l_start           NUMBER;

 total_time number;

v_ct  number:=0;

v_insert_ct number:=1.1;

v_var_a varchar2(100);

v_mb number;

v_rec_ct number;

record_size number:=112;  

BEGIN

   l_start := DBMS_UTILITY.get_time; 

  FOR i IN 1 .. l_loops LOOP

  v_insert_ct:=v_insert_ct+1; v_var_a:=’AAAAAAAAAAAAAAA’||v_insert_ct; insert into insert_test VALUES(v_var_a,v_insert_ct);

v_ct:=v_ct+1;

if v_ct>99

then

commit;

v_ct:=0;

 end if;

   END LOOP;   

 DBMS_OUTPUT.put_line(‘Execution Time   : ‘ ||(DBMS_UTILITY.get_time – l_start)||’  ms’); DBMS_OUTPUT.put_line(chr(10));

select round(100000/((DBMS_UTILITY.get_time – l_start)/100),2) into v_rec_ct from dual;

DBMS_OUTPUT.put_line(‘Number of Inserts per second: ‘||v_rec_ct||’ records’);

DBMS_OUTPUT.put_line(chr(10));

select round((100000/((DBMS_UTILITY.get_time – l_start)/100)*record_size)/1024/1024,2) into v_mb from dual;

DBMS_OUTPUT.put_line(‘MB of Inserts per second: ‘||v_mb|| ‘ MB’);

END; 

 /

5.  The result set below is returned, showing the insert rate achieved by your Oracle installation.   NOTE:  Multiple things will impact your insert rate, so for additional question leave a comment.

Execution Time   : 513  ms

Number of Inserts per second: 19493.18 records

MB of Inserts per second: 2.08 MB

PL/SQL procedure successfully completed.

SQL>

Larry Catt

Oracle, Oracle 10g, oracle 9i, script

Determining the record insert rate for a table

Leave a comment

Some system require the insertion of large amounts of data in as short a period of time as possible. In this cases, you must be able to measure actual physical time of insertion per give number of records or number of records inserted per second.   The following block allows you to perform this action on a give oracle operation.

We will use the following anonymous block to track our processing time:

SET SERVEROUTPUT ON  DECLARE      v_loops          NUMBER := 1000000;    v_start_time     NUMBER;    v_total_time  NUMBER;    v_ct    NUMBER :=1;    BEGIN

— Time we started our process.    v_start_time := DBMS_UTILITY.get_time;

FOR i IN 1 .. v_loops LOOP   < Oracle Action:  Insert, function call, procedure call, etc >   v_ct:=v_ct+1;   commit;   END LOOP;       –Calculate the time in seconds     v_total_time := (DBMS_UTILITY.get_time – v_start_time)/100;           DBMS_OUTPUT.put_line(‘This process took a total of : ‘ ||v_total_time||’ seconds to complete’);  END;  /     Example Execution:

1.  Connect as the user scott and create a table test_data

SQL> connect scott/tiger@orcl Connected. SQL> create table test_data(user_no number, fname varchar2(30), lname varchar2(30));

Table created.

SQL>

2.  Execute your block with < Oracle Action …> replaced with an insert statement for test data.

SET SERVEROUTPUT ON  DECLARE      v_loops          NUMBER := 1000000;    v_start_time     NUMBER;    v_total_time  NUMBER;    v_ct    NUMBER :=1234;    BEGIN

— Time we started our process.    v_start_time := DBMS_UTILITY.get_time;

FOR i IN 1 .. v_loops LOOP   insert into scott.test_data(user_no, fname, lname) values(v_ct, ‘JANE’, ‘DOE’);   v_ct:=v_ct+1;   commit;   END LOOP;       –Calculate the time in seconds     v_total_time := (DBMS_UTILITY.get_time – v_start_time)/100;           DBMS_OUTPUT.put_line(‘This process took a total of : ‘ ||v_total_time||’ seconds to complete’);  END;  /      Results:    SQL> SET SERVEROUTPUT ON SQL>  DECLARE   2   3     v_loops          NUMBER := 1000000;   4     v_start_time     NUMBER;   5     v_total_time            NUMBER;   6     v_ct                            NUMBER :=1234;   7   8   BEGIN   9  10     — Time we started our process.  11     v_start_time := DBMS_UTILITY.get_time;  12  13    FOR i IN 1 .. v_loops LOOP  14  15  insert into scott.test_data(user_no, fname, lname) values(v_ct, ‘JANE’, ‘DOE’);  16    v_ct:=v_ct+1;  17    commit;  18    END LOOP;  19  20     –Calculate the time in seconds  21      v_total_time := (DBMS_UTILITY.get_time – v_start_time)/100;  22  23      DBMS_OUTPUT.put_line(‘This process took a total of : ‘ ||v_total_time||’ seconds to complete’);  24     END;  25   / This process took a total of : 86.45 seconds to complete

PL/SQL procedure successfully completed.

SQL> SQL>

3.  The results show a insert rate of 86.45 seconds for 1 million records or 11,567.38 records per second.

Larry Catt