De acordo com a documentação do Oracle SQL Firewall FAQ o impacto de performance da ativação do SQL Firewall é inferior a 2% na maioria das cargas de trabalho.
Para validar o impacto sem montar um benchmark completo, serão criado dois cenários:
- Baseline: usuário sem captura ou allowlist habilitada;
- Enforcement: usuário protegido por uma allowlist com bloqueio ativo.
Para cada um dos cenários habilitarei um trace da sessão para comparar a quantidade de execução, CPU, tempo decorrido, leituras físicas e lógicas.
Preparação do ambiente
Criação e população da tabela clientes, no schema FW_DEMO_APP. Posteriormente será populada com 1 milhão de registros e por fim executada uma coleta de estatísticas.
[oracle@oracle26ai ~]$ sqlplus FW_DEMO_APP/<SENHA>@ora26ai SQL*Plus: Release 23.26.1.0.0 - Production on Wed Sep 9 20:33:39 2026 Version 23.26.1.0.0 Copyright (c) 1982, 2025, Oracle. All rights reserved. Last Successful login time: Wed Sep 09 2026 20:14:26 +00:00 Connected to: Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production Version 23.26.1.0.0 SQL> SET SERVEROUTPUT ON SQL> CREATE TABLE clientes ( id NUMBER PRIMARY KEY, nome VARCHAR2(100) NOT NULL ); 2 3 4 Table created. SQL> INSERT INTO clientes SELECT level, 'Cliente ' || level FROM dual CONNECT BY level <= 1000000; 2 3 4 1000000 rows created. SQL> COMMIT; Commit complete. SQL> SELECT COUNT(*) AS total_registros FROM clientes; 2 TOTAL_REGISTROS --------------- 1000000 SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER, 'CLIENTES'); END; / 2 3 4 PL/SQL procedure successfully completed. SQL>
Simularemos uma consulta por chave primária executada 500 mil vezes. O comentário SQLFW_PERF_TEST facilita a localização do SQL no relatório do TKPROF.
SQL> SQL> set timing on SQL> DECLARE 2 l_nome clientes.nome%TYPE; l_checksum NUMBER := 0; BEGIN FOR i IN 1 .. 500000 LOOP 3 4 5 6 SELECT /* SQLFW_PERF_TEST */ nome INTO l_nome 7 8 FROM clientes WHERE id = MOD(i - 1, 100000) + 1; l_checksum := l_checksum + LENGTH(l_nome); END LOOP; DBMS_OUTPUT.PUT_LINE('Checksum: ' || l_checksum); END; 9 10 11 12 13 14 15 16 / Checksum: 6444475 PL/SQL procedure successfully completed. Elapsed: 00:00:04.39 SQL> SQL>
Execute esse bloco uma vez antes de iniciar o primeiro trace para aquecer o buffer cache e evitar que o baseline seja prejudicado pelas primeiras leituras físicas.
Agora irei gerar um trace do processo sem que o SQL Firewall esteja ativo.
SQL> SQL> ALTER SESSION SET tracefile_identifier = 'SQLFW_PERF'; Session altered. Elapsed: 00:00:00.00 SQL> SQL> BEGIN DBMS_SESSION.SESSION_TRACE_ENABLE( waits => TRUE, binds => FALSE ); END; / 2 3 4 5 6 7 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> DECLARE 2 l_nome clientes.nome%TYPE; l_checksum NUMBER := 0; BEGIN FOR i IN 1 .. 500000 LOOP SELECT /* SQLFW_PERF_TEST */ nome INTO l_nome 3 4 5 6 7 8 FROM clientes WHERE id = MOD(i - 1, 100000) + 1; l_checksum := l_checksum + LENGTH(l_nome); 9 10 11 12 END LOOP; DBMS_OUTPUT.PUT_LINE('Checksum: ' || l_checksum); END; / 13 14 15 16 Checksum: 6444475 PL/SQL procedure successfully completed. Elapsed: 00:00:16.60 SQL> SQL> BEGIN DBMS_SESSION.SESSION_TRACE_DISABLE; END; / 2 3 4 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> SQL> COLUMN trace_file FORMAT A120 SELECT value AS trace_file FROM v$diag_info WHERE name = 'Default Trace File';SQL> SQL> 2 3 TRACE_FILE ------------------------------------------------------------------------------------------------------------------------ /u01/app/oracle/diag/rdbms/ora26ai_5sz_vcp/ora26ai/trace/ora26ai_ora_360809_SQLFW_PERF.trc Elapsed: 00:00:00.00 SQL>
Vou gerar um tkprof deste trace de baseline
[oracle@oracle26ai ~]$ tkprof /tmp/sqlfw_baseline.trc sqlfw_baseline.txt sys=yes aggregate=yes sort=exeela,fchela TKPROF: Release 23.0.0.0.0 - Development on Wed Sep 9 20:46:10 2026 Copyright (c) 1982, 2026, Oracle and/or its affiliates. All rights reserved. [oracle@oracle26ai ~]$ [oracle@oracle26ai ~]$ grep -n -A20 -B5 'SQLFW_PERF_TEST' sqlfw_baseline.txt 23-DECLARE 24- l_nome clientes.nome%TYPE; 25- l_checksum NUMBER := 0; 26-BEGIN 27- FOR i IN 1 .. 500000 LOOP 28: SELECT /* SQLFW_PERF_TEST */ nome 29- INTO l_nome 30- FROM clientes 31- WHERE id = MOD(i - 1, 100000) + 1; 32- l_checksum := l_checksum + LENGTH(l_nome); 33- END LOOP; 34- DBMS_OUTPUT.PUT_LINE('Checksum: ' || l_checksum); 35-END; 36- 37-call count cpu elapsed disk query current rows 38-------- ------ -------- ---------- ---------- ---------- ---------- ---------- 39-Parse 1 0.00 0.00 0 0 0 0 40-Execute 1 12.99 13.13 0 0 0 1 41-Fetch 0 0.00 0.00 0 0 0 0 42-------- ------ -------- ---------- ---------- ---------- ---------- ---------- 43-total 2 12.99 13.13 0 0 0 1 44- 45-Misses in library cache during parse: 0 46-Optimizer mode: ALL_ROWS 47-Parsing user id: 137 48- [oracle@oracle26ai ~]$
Capturar o workload, gerar a allowlist e ativar o modo enforcement.
Agora será ativado o processo de captura do workload para geração da allowlist, seguido da ativação do modo enforcement.
[oracle@oracle26ai ~]$ [oracle@oracle26ai ~]$ sqlplus SQL*Plus: Release 23.26.1.0.0 - Production on Wed Sep 9 20:50:29 2026 Version 23.26.1.0.0 Copyright (c) 1982, 2025, Oracle. All rights reserved. Enter user-name: / as sysdba Connected to: Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production Version 23.26.1.0.0 SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORA26AI_PDB1 READ WRITE NO SQL> SQL> alter session set container=ORA26AI_PDB1; Session altered. SQL> SQL> SELECT status FROM dba_sql_firewall_status; STATUS -------- DISABLED SQL> EXEC DBMS_SQL_FIREWALL.ENABLE; PL/SQL procedure successfully completed. SQL> BEGIN DBMS_SQL_FIREWALL.CREATE_CAPTURE( username => 'FW_DEMO_APP', top_level_only => TRUE, start_capture => TRUE); END; / 2 3 4 5 6 7 PL/SQL procedure successfully completed. SQL> ! [oracle@oracle26ai ~]$ sqlplus FW_DEMO_APP/<SENHA>@ora26ai SQL*Plus: Release 23.26.1.0.0 - Production on Wed Sep 9 20:51:42 2026 Version 23.26.1.0.0 Copyright (c) 1982, 2025, Oracle. All rights reserved. Last Successful login time: Wed Sep 09 2026 20:37:17 +00:00 Connected to: Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production Version 23.26.1.0.0 SQL> set timing on SQL> SQL> DECLARE 2 l_nome clientes.nome%TYPE; l_checksum NUMBER := 0; BEGIN FOR i IN 1 .. 500000 LOOP SELECT /* SQLFW_PERF_TEST */ nome INTO l_nome FROM clientes WHERE id = MOD(i - 1, 100000) + 1; 3 4 5 6 7 8 9 10 11 l_checksum := l_checksum + LENGTH(l_nome); 12 END LOOP; 13 14 DBMS_OUTPUT.PUT_LINE('Checksum: ' || l_checksum); END; / 15 16 PL/SQL procedure successfully completed. Elapsed: 00:00:04.41 SQL> exit Disconnected from Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production Version 23.26.1.0.0 [oracle@oracle26ai ~]$ exit exit SQL> EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE('FW_DEMO_APP'); PL/SQL procedure successfully completed. SQL> EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST('FW_DEMO_APP'); PL/SQL procedure successfully completed. SQL> SELECT top_level, sql_text FROM dba_sql_firewall_allowed_sql WHERE username = 'FW_DEMO_APP' 2 3 4 ; TOP_LEVEL --------- SQL_TEXT -------------------------------------------------------------------------------- Y SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL Y DECLARE L_NOME CLIENTES.NOME%TYPE; L_CHECKSUM NUMBER :=?; BEGIN FOR I IN ?? LOOP SELECT NOME INTO L_NOME FROM CLIENTES WHERE ID=MOD (I -?,?) +?; L_CHECKSUM :=L_ CHECKSUM +LENGTH (L_NOME); END LOOP; DBMS_OUTPUT.PUT_LINE (?||L_CHECKSUM); END; SQL> BEGIN DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST( username => 'FW_DEMO_APP', enforce => DBMS_SQL_FIREWALL.ENFORCE_SQL, block => TRUE ); END; / 2 3 4 5 6 7 8 PL/SQL procedure successfully completed. SQL>
Gerar trace com modo enforcement
Agora será feita um novo trace, desta vez com o modo enforcement ativo.
[oracle@oracle26ai ~]$ sqlplus FW_DEMO_APP/<SENHA>@ora26ai SQL*Plus: Release 23.26.1.0.0 - Production on Wed Sep 9 20:59:13 2026 Version 23.26.1.0.0 Copyright (c) 1982, 2025, Oracle. All rights reserved. Last Successful login time: Wed Sep 09 2026 20:51:42 +00:00 Connected to: Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production Version 23.26.1.0.0 SQL> set timing on SQL> ALTER SESSION SET tracefile_identifier = 'SQLFW_PERF'; Elapsed: 00:00:00.01 SQL> BEGIN DBMS_SESSION.SESSION_TRACE_ENABLE( waits => TRUE, binds => FALSE ); END; / 2 3 4 5 6 7 PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SQL> SQL> DECLARE 2 l_nome clientes.nome%TYPE; l_checksum NUMBER := 0; BEGIN FOR i IN 1 .. 500000 LOOP SELECT /* SQLFW_PERF_TEST */ nome INTO l_nome FROM clientes WHERE id = MOD(i - 1, 100000) + 1; 3 4 5 6 7 8 9 10 11 l_checksum := l_checksum + LENGTH(l_nome); 12 END LOOP; 13 DBMS_OUTPUT.PUT_LINE('Checksum: ' || l_checksum); END; / 14 15 16 PL/SQL procedure successfully completed. Elapsed: 00:00:16.39 Elapsed: 00:00:00.03 SQL> COLUMN trace_file FORMAT A120 SELECT value AS trace_file FROM v$diag_info WHERE name = 'Default Trace File';SQL> SQL> 2 3 TRACE_FILE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/ora26ai_5sz_vcp/ora26ai/trace/ora26ai_ora_2183.trc Elapsed: 00:00:00.01 SQL> exit
Apos coletar o trace, vamos gerar um novo tkprof.
oracle@oracle26ai ~]$ tkprof /tmp/sqlfw_enforcement.trc /tmp/sqlfw_enforcement.txt sys=yes aggregate=yes sort=exeela,fchela TKPROF: Release 23.0.0.0.0 - Development on Wed Sep 9 21:03:15 2026 Copyright (c) 1982, 2026, Oracle and/or its affiliates. All rights reserved. [oracle@oracle26ai ~]$ grep -n -A20 -B5 'SQLFW_PERF_TEST' /tmp/sqlfw_enforcement.txt 23-DECLARE 24- l_nome clientes.nome%TYPE; 25- l_checksum NUMBER := 0; 26-BEGIN 27- FOR i IN 1 .. 500000 LOOP 28: SELECT /* SQLFW_PERF_TEST */ nome 29- INTO l_nome 30- FROM clientes 31- WHERE id = MOD(i - 1, 100000) + 1; 32- l_checksum := l_checksum + LENGTH(l_nome); 33- END LOOP; 34- DBMS_OUTPUT.PUT_LINE('Checksum: ' || l_checksum); 35-END; 36- 37-call count cpu elapsed disk query current rows 38-------- ------ -------- ---------- ---------- ---------- ---------- ---------- 39-Parse 1 0.00 0.00 0 0 0 0 40-Execute 1 12.89 13.00 0 0 0 1 41-Fetch 0 0.00 0.00 0 0 0 0 42-------- ------ -------- ---------- ---------- ---------- ---------- ---------- 43-total 2 12.89 13.00 0 0 0 1 44- 45-Misses in library cache during parse: 0 46-Optimizer mode: ALL_ROWS 47-Parsing user id: 137 48- [oracle@oracle26ai ~]$
Comparativo
A demonstração prática utilizou uma tabela contendo 1 milhão de registros. O bloco PL/SQL executou 500 mil consultas por chave primária, acessando os IDs de 1 a 100 mil repetidamente. Sem o SQL Firewall, o processamento consumiu 12,99 segundos de CPU e 13,13 segundos de tempo total. Com a allowlist habilitada em modo de enforcement, o mesmo workload consumiu 12,89 segundos de CPU e 13,00 segundos de tempo total.
A diferença foi de -0,77% em CPU e -0,99% em elapsed. Essa pequena variação não representa ganho ou perda causado pelo SQL Firewall, mas uma oscilação normal entre execuções. Portanto, nesse teste, não foi identificado overhead mensurável provocado pelo enforcement.
| Métrica | Baseline | SQL Firewall | Diferença |
|---|---|---|---|
| CPU total | 12,99 s | 12,89 s | -0,77% |
| Elapsed total | 13,13 s | 13,00 s | -0,99% |
| CPU por consulta | 25,98 µs | 25,78 µs | -0,20 µs |
| Elapsed por consulta | 26,26 µs | 26,00 µs | -0,26 µs |
| Throughput aproximado | 38.081 consultas/s | 38.462 consultas/s | +1,00% |
Comentários
Postar um comentário