Índice do fórum Treinamentos Avançados Treinamento Oracle Tuning - SQL DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL

DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL

Dúvidas, dicas e atualizações sobre o Treinamento Oracle Tuning - SQL.

Mensagem Ter Jun 18, 2019 5:51 pm

Mensagens: 0
Olá Pessoal, bom dia

Estou com seguinte questão, preciso garantir que um determinado SQLID seja gravado sempre na DBA_HIST_ACTIVE_SESS_HISTORY.
Tentei fazer com o DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL, no entanto sem sucesso.

Vejam o procedimento de testes que realizei:

============================================================================================
Referencia : ref_marcar_sql_color.txt
Assunto : COMO MARCAR UM SQL PARA SEMPRE SER CAPTURADO NO AWR
Criado por : Roberto Fernandes Sobrinho
Data : 18/06/2019
Ref :
============================================================================================
01. Gere uma carga de dados banco de dados
============================================================================================
[oracle@ken01 scr]$ cd /u01/swingbench/bin
[oracle@ken01 bin]$ ./charbench -c ../configs/Stress_Test.xml -cs //sfighter-scan/maia -uc 5
============================================================================================
02. Gerar e Localizar o SQL que deseja marcar como colorido
============================================================================================
11:45:17 SYS@maia1> set autotrace on
11:49:33 SYS@maia1> select /*TESTE COLORIR SQL AWR*/ ID, ASMALLVARCHAR from SOE.STRESSTESTTABLE where id = 183522;
11:50:18 SYS@maia1> SELECT SQL_ID, HASH_VALUE FROM gv$sql WHERE upper(SQL_TEXT) LIKE '%TESTE COLORIR SQL AWR%' and upper(SQL_TEXT) not LIKE '%FROM GV$SQL%' and upper(SQL_TEXT) not LIKE '%EXPLAIN PLAN SET%';
SQL_ID |HASH_VALUE
-------------|----------
8uyjc6pwjh1f2|2031617474
============================================================================================
02. Criar um SNAPSHOT de inicio e executar o seu SQL alguma vezes, gerar SNAPSHOT de fim
============================================================================================
11:50:58 SYS@maia1> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

PL/SQL procedure successfully completed.

11:51:50 SYS@maia1> select /*TESTE COLORIR SQL AWR*/ ID, ASMALLVARCHAR from SOE.STRESSTESTTABLE where id = 183522;

11:52:22 SYS@maia1> select /*TESTE COLORIR SQL AWR*/ ID, ASMALLVARCHAR from SOE.STRESSTESTTABLE where id = 183522;

11:52:52 SYS@maia1> select /*TESTE COLORIR SQL AWR*/ ID, ASMALLVARCHAR from SOE.STRESSTESTTABLE where id = 183522;

11:54:21 SYS@maia1> select /*TESTE COLORIR SQL AWR*/ ID, ASMALLVARCHAR from SOE.STRESSTESTTABLE where id = 183522;

11:55:39 SYS@maia1> select /*TESTE COLORIR SQL AWR*/ ID, ASMALLVARCHAR from SOE.STRESSTESTTABLE where id = 183522;

11:56:05 SYS@maia1> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

PL/SQL procedure successfully completed.

11:57:39 SYS@maia1> !date
Tue Jun 18 11:58:26 EDT 2019
============================================================================================
03. Tentar localizar a execução do seu sqlid no AWR e na DBA_HIST_ACTIVE_SESS_HISTORY
============================================================================================
12:02:50 SYS@maia1> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
>> Enter value for report_type: text
>> Enter value for num_days: 1
-->LOCALIZAR A DATA [ SNAPSHOT de inicio] E SNAPSHOT de fim
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
maia1 MAIA 120 18 Jun 2019 11:46 1
121 18 Jun 2019 11:51 1 <- inicio
122 18 Jun 2019 11:56 1 <- fim
>>Enter value for begin_snap: 121
>>Enter value for end_snap: 122
>>Enter value for report_name
>>Enter value for report_name: awrrpt_no_color_sql.txt
12:13:47 SYS@maia1> @coe_sql_hist.sql
>>SQL ID = 8uyjc6pwjh1f2
>>Dias Atras (SYSDATE - ) = 1
no rows selected

Elapsed: 00:00:01.08
12:08:57 SYS@maia1> quit
[oracle@ken01 scr]$ grep -i '8uyjc6pwjh1f2' awrrpt_no_color_sql.txt
-->Não encontramos o SQLID 8uyjc6pwjh1f2 no AWR e nem na tabela DBA_HIST_ACTIVE_SESS_HISTORY
============================================================================================
04. Marcar o sqlid = 8uyjc6pwjh1f2 como colorido e
============================================================================================
12:16:46 SYS@maia1> EXEC DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL('8uyjc6pwjh1f2');

PL/SQL procedure successfully completed.

12:16:52 SYS@maia1> select DBID, SQL_ID, OWNER, to_char(CREATE_TIME,'dd/mm/yyyy hh24:mi:ss') CREATE_TIME from sys.wrm$_colored_sql;

DBID SQL_ID OWNER CREATE_TIME
---------- ------------- ---------- -------------------
1810160852 8uyjc6pwjh1f2 1 18/06/2019 12:16:51

12:17:43 SYS@maia1> select DBID, SQL_ID, to_char(CREATE_TIME,'dd/mm/yyyy hh24:mi:ss') from DBA_HIST_COLORED_SQL;

DBID SQL_ID TO_CHAR(CREATE_TIME
---------- ------------- -------------------
1810160852 8uyjc6pwjh1f2 18/06/2019 12:16:51

12:17:49 SYS@maia1>
============================================================================================
05. Garanta que a carga ainda esteja em execução e crie um SNAPSHOT de inicio, execute o seu SQL 5 vezes (uma por minuto) e gerar SNAPSHOT de fim
============================================================================================
12:19:15 SYS@maia1> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

PL/SQL procedure successfully completed.
--> 1x
12:21:15 SYS@maia1> select /*TESTE COLORIR SQL AWR*/ ID, ASMALLVARCHAR from SOE.STRESSTESTTABLE where id = 183522;
--> 2x
12:22:02 SYS@maia1> select /*TESTE COLORIR SQL AWR*/ ID, ASMALLVARCHAR from SOE.STRESSTESTTABLE where id = 183522;
--> 3x
12:23:38 SYS@maia1> select /*TESTE COLORIR SQL AWR*/ ID, ASMALLVARCHAR from SOE.STRESSTESTTABLE where id = 183522;
--> 4x
12:24:39 SYS@maia1> select /*TESTE COLORIR SQL AWR*/ ID, ASMALLVARCHAR from SOE.STRESSTESTTABLE where id = 183522;
--> 5x
12:25:38 SYS@maia1> select /*TESTE COLORIR SQL AWR*/ ID, ASMALLVARCHAR from SOE.STRESSTESTTABLE where id = 183522;

12:26:47 SYS@maia1> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

PL/SQL procedure successfully completed.

============================================================================================
06. Localizar a execução do seu sqlid no AWR e na DBA_HIST_ACTIVE_SESS_HISTORY
============================================================================================
12:28:43 SYS@maia1> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
>> Enter value for report_type: text
>> Enter value for num_days: 1
-->LOCALIZAR A DATA [ SNAPSHOT de inicio] E SNAPSHOT de fim

Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
maia1 MAIA 120 18 Jun 2019 11:46 1
121 18 Jun 2019 11:51 1
122 18 Jun 2019 11:56 1
123 18 Jun 2019 12:19 1 <- inicio
124 18 Jun 2019 12:26 1 <- fim
>> Enter value for begin_snap: 123
>> Enter value for end_snap: 124
>> Enter value for report_name: awrrpt_YES_color_sql.txt

12:33:52 SYS@maia1> @coe_sql_hist.sql
>>SQL ID = 8uyjc6pwjh1f2
>>Dias Atras (SYSDATE - ) = 1
no rows selected
12:36:42 SYS@maia1> quit

[oracle@ken01 scr]$ grep -i '8uyjc6pwjh1f2' awrrpt_YES_color_sql.txt
[oracle@ken01 scr]$

NÃO APARECEU :(


============================================================================================
ZAS

portilho Site Admin

Mensagens: 482
Post duplicado, respondido em viewtopic.php?f=9&t=587


Voltar para Treinamento Oracle Tuning - SQL

cron