Not long ago, we launched a fairly large operation on one of our oracle databases.

It was necessary to logically separate two schemas that were extremely nested.

The intervention itself goes well but the next morning (during the ramp-up) we receive many alerts concerning the number of Oracle processes.

Indeed, instead of consuming 1000 (approximately), we have reached 3000. We started by increasing the parameter and even at 7000 that was not enough.

We are embarking on a major debugging phase, we see that each, user depending on the action he takes in the application, can use between 3 and 100+ processes.

With just a simple connection we have 8 consumed processes.

We are therefore embarking on SQLNET traces on the client side.

On our client, we modify the SQLNET.ora file and add the following elements:

trace_level_client = SUPPORT
trace_file_client = cli_test
trace_directory_client = C:\Temp\
trace_unique_client = on
trace_timestamp_client = on
trace_filelen_client = 100
log_file_client = cli_test
log_directory_client = C:\Temp\log
tnsping.trace_directory = C:\Temp\trace
tnsping.trace_level = admin
DIAG_ADR_ENABLED= OFF

 

We launch a connection to the application and close it directly in order to avoid a too large a trace file.

We end up with a * .trc file

From there we have two solutions:

• Use the file as it is
• Use TRCASST to make it more readable

We chose to use the file as it is because the TRCASST was crashing with the following error: TNS-04315:  java.lang.NullPointerException

I browse the file and find this during the login phase:

(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_brc: 2D 30 30 39 34 32 3A 20 |-00942:.|
(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_brc: 54 61 62 6c 65 20 6f 72 |Table.or|
(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_brc: 20 76 69 65 77 20 64 6f |.view.do|
(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_brc: 65 73 20 6e 6f 74 20 65 |es.not.e|
(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_brc: 78 69 73 74 20 20 20 20 |xist....|

 

I go up in the file and  find the select request which causes the ORA error.

(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_bsd: 61 53 45 4C 45 43 54 20 |aSELECT.|
(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_bsd: 43 4E 5F 54 59 50 45 2C |CN_TYPE,|
(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_bsd: 20 43 4E 5F 43 4F 44 45 |.CN_CODE|
(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_bsd: 2C 20 43 4E 5F 56 41 4C |,.CN_VAL|
(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_bsd: 55 45 5F 34 2C 20 43 4E |UE_4,.CN|
(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_bsd: 5F 56 41 4C 55 45 5F 31 |_VALUE_1|
(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_bsd: 2C 20 43 4E 5F 56 41 4C |,.CN_VAL|
(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_bsd: 55 45 5F 32 2C 20 43 4E |UE_2,.CN|
(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_bsd: 5F 56 41 4C 55 45 5F 33 |_VALUE_3|
(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_bsd: 20 46 52 4F 4D 2e 53 43 |.FROM.SC|
(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_bsd: 4F 54 54 2E 43 4F 4E 53 |OTT.CONS|
(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_bsd: 54 41 4E 54 53 2E 2E 2E |TANTS...|
(26448) [02-JUIL.-2020 09:15:54:810] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|

I compare my databases (before and after split) and I see that the view requested by the SELECT no longer exists.

We recreate it using our Grid, and we see the difference directly.

Gone are the hundreds of processes created, we fall back on normal values.