Home » Server Options » RAC & Failsafe » Dbms jobs initiates many sessions (Oracle, 11.2.0.2)
Dbms jobs initiates many sessions [message #566064] Tue, 11 September 2012 04:28 Go to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Hi,

We created a job yesterday which will call the below procedure. if we start this job, it initiates 92 sessions parellely.
How it is initiating 92 sessions parelley?


procedure prc_HECTOR_CIDB_IN_PURGE
IS
      cursor CUR_PROC is
      select rowid from CUSTMODEL.HECTOR_CIDB_IN where PROCESS_FLAG in ('Y','F');

      TYPE TYP_CUR_DATA IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
      v_typ_cur_data typ_cur_data;

BEGIN
      OPEN CUR_PROC;
      LOOP
          v_typ_cur_data.DELETE;

          FETCH CUR_PROC BULK COLLECT INTO v_typ_cur_data LIMIT 10000;

          IF V_TYP_CUR_DATA.COUNT > 0
          THEN
              FORALL J in 1..V_TYP_CUR_DATA.COUNT
                  DELETE FROM custmodel.HECTOR_cidb_in WHERE ROWID=v_typ_cur_data(j);
                  COMMIT;
                  --dbms_output.put_line('Count '||V_TYP_CUR_DATA.COUNT);
              ELSE
              EXIT;
          END IF;
          END LOOP;

      CLOSE CUR_PROC;
      COMMIT;

EXCEPTION
          WHEN OTHERS
          THEN
              IF CUR_PROC%ISOPEN
              then
                  CLOSE CUR_PROC;
              end if;
END prc_hector_cidb_in_PURGE;




SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
fast_start_parallel_rollback         string      FALSE
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     960
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     TRUE
parallel_server_instances            integer     2
parallel_servers_target              integer     384
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0
Re: Dbms jobs initiates many sessions [message #566066 is a reply to message #566064] Tue, 11 September 2012 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select degree, instances from dba_tables where ... (your tables).

RAISE; is missing before the last END;

Regards
Michel
Re: Dbms jobs initiates many sessions [message #566068 is a reply to message #566066] Tue, 11 September 2012 05:11 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
one more thing I forget to tell. many sessions (92) got kicked off only when I execute the job manually like below.
But when the job got kicked off automatically through scheduler,only one session is getting initiated.

Begin
dbms_stats.run_job ('job_name');
end;
/

SQL> select degree,table_name,owner from dba_tables where table_name='HECTOR_CIDB_IN';

DEGREE     TABLE_NAME                     OWNER
---------- ------------------------------ ------------------------------
         1 HECTOR_CIDB_IN                 CUSTMODEL

[Updated on: Tue, 11 September 2012 05:22]

Report message to a moderator

Re: Dbms jobs initiates many sessions [message #566077 is a reply to message #566068] Tue, 11 September 2012 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not what I asked.

Regards
Michel
Re: Dbms jobs initiates many sessions [message #566101 is a reply to message #566077] Tue, 11 September 2012 06:48 Go to previous message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
SQL> select degree,instances from dba_tables where table_name='HECTOR_CIDB_IN';

DEGREE     INSTANCES
---------- ----------
         1          1
Previous Topic: ocr backup issue
Next Topic: JDBC connection to a RAC 10g database
Goto Forum:
  


Current Time: Thu Mar 28 10:55:06 CDT 2024