Setting NLS_LENGTH_SEMANTICS for session via trigger

I would hope you have some session pooling going on, or at least one database connection per thread. This is not clear from your description. In a multi-threaded design, I would not close connections in the threads unless they make their own connections (which I would consider a bad design).

I would get the process running with one thread, then add an additional thread. Once you are comfortable you have that multi-threading gives you the results you want, you can scale up.

You could turn on auditing on the Oracle side to see what is really happening.

You didn't say how long the queries take to run. If they take minutes, to run you need to ensure you have keep-alive on the network connections. Otherwise, the network connection may get dropped.

EDIT: Check the resource limits applicable to the userid you are connecting with. These might get your connections closed. You may also be getting killed off due to exceeding resource limits. I would expect the reason for disconnect to be in the SQL Error text for the connection.

Given how long these queries are taking, you may want to investigate optimizing the queries. Unless you have lots of memory and processors on the database server you may find that the queries run faster when run serially. You may be forcing data out of the buffer pool with this many long queries running in parallel.

There is an exception. When you define a before insert, row-level trigger on a table and issue a single row INSERT statement, the table is mutating error will not be raised. But if you define the same kind of trigger and issue a multi-row INSERT statement, the error will be raised. Here is an example:

SQL> create table TB_TR_TEST(
  2    col1 number,
  3    col2 number
  4  )
  5  ;

Table created

SQL> create or replace trigger TR_TB_TR_TEST
  2  before insert on TB_TR_TEST
  3  for each row
  4  begin
  5    SELECT max(col1) + 1 INTO :NEW.col1
  6      FROM TB_TR_TEST;
  7    UPDATE TB_TR_TEST SET col2 = 5000;
  8  end;
  9  /

Trigger created

Here is a single-row insert statement, which won't raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2) values(1,2);

1 row inserted

SQL> insert into TB_TR_TEST(col1, col2) values(3,5);

1 row inserted

SQL> commit;

Commit complete

Here is a multi-row insert statement, which will raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2)
  2    select 1, 2
  3      from dual;

insert into TB_TR_TEST(col1, col2)
  select 1, 2
    from dual

ORA-04091: table HR.TB_TR_TEST is mutating, trigger/function may not see it
ORA-06512: at "HR.TR_TB_TR_TEST", line 2
ORA-04088: error during execution of trigger 'HR.TR_TB_TR_TEST'