At-risk schema separation
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.
Leave a Reply