ADD_COLORED_SQL

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

ADD_COLORED_SQL

Post by rfsobrinho » Tue Jun 18, 2019 2:52 pm

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
Posts: 482
Joined: Wed May 29, 2013 8:51 am

Re: ADD_COLORED_SQL

Post by portilho » Sun Jun 23, 2019 7:37 am

Oi.
Para uma melhor visualização, da próxima vez que colocar algum código, use a Tag Code, fica bem bonito.

De acordo com a documentação, o SQL só precisa estar na Shared Pool para ser capturado após ser marcado como COLORED:
If an SQL ID is colored, it will be captured in every snapshot, independent of its level of activities (so that it does not have to be a TOP SQL). Capture occurs if the SQL is found in the cursor cache at snapshot time.
Fonte: https://docs.oracle.com/en/database/ora ... F48F144A9B

No exemplo abaixo:
- Executo o SQL que quero monitorar;
- Confiro que ele está na Shared Pool;
- Capturo um Snapshot - neste momento o SQL já foi para o repositório;
- Espero um pouco, vejo que o SQL ainda está na Shared Pool, e capturo outro Snapshot;
- Vejo que ele está na DBA_HIST_SQL_PLAN.
- Tiro um relatório, que mostra o plano e estatísticas de execução.


Code: Select all

07:17:23 RICARDO@DEV> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;

Tabela criada.

Decorreram: 00:00:03.82
07:17:31 RICARDO@DEV> INSERT INTO T SELECT * FROM T;

66862 linhas criadas.

Decorreram: 00:00:00.66
07:17:37 RICARDO@DEV> INSERT INTO T SELECT * FROM T;

133724 linhas criadas.

Decorreram: 00:00:01.26
07:17:39 RICARDO@DEV> INSERT INTO T SELECT * FROM T;

267448 linhas criadas.

Decorreram: 00:00:05.27
07:17:45 RICARDO@DEV> INSERT INTO T SELECT * FROM T;

534896 linhas criadas.

Decorreram: 00:00:05.52
07:17:52 RICARDO@DEV> INSERT INTO T SELECT * FROM T;

1069792 linhas criadas.

Decorreram: 00:00:13.03
07:18:05 RICARDO@DEV> INSERT INTO T SELECT * FROM T;

2139584 linhas criadas.

Decorreram: 00:00:27.26
07:18:37 RICARDO@DEV> INSERT INTO T SELECT * FROM T;

4279168 linhas criadas.

Decorreram: 00:00:48.42
07:19:32 RICARDO@DEV> SELECT COUNT(OBJECT_ID) FROM T WHERE OBJECT_TYPE = 'SYNONYM';

COUNT(OBJECT_ID)
----------------
         1456768

Decorreram: 00:00:00.68
07:20:09 RICARDO@DEV> SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT COUNT(OBJECT_ID) FROM T WHERE OBJECT_TYPE%';

SQL_ID        SQL_TEXT
------------- --------------------------------------------------------------------------------
fy4tpsg8pffju SELECT COUNT(OBJECT_ID) FROM T WHERE OBJECT_TYPE = 'SYNONYM'

Decorreram: 00:00:00.20
07:20:37 RICARDO@DEV> EXEC DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL('fy4tpsg8pffju');

Procedimento de PL/SQL concluido com exito.

Decorreram: 00:00:00.23
07:20:51 RICARDO@DEV> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

Procedimento de PL/SQL concluido com exito.

Decorreram: 00:00:07.19
07:28:25 RICARDO@DEV> SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT COUNT(OBJECT_ID) FROM T WHERE OBJECT_TYPE%';

SQL_ID        SQL_TEXT
------------- --------------------------------------------------------------------------------
fy4tpsg8pffju SELECT COUNT(OBJECT_ID) FROM T WHERE OBJECT_TYPE = 'SYNONYM'

Decorreram: 00:00:00.10
07:28:27 RICARDO@DEV> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

Procedimento de PL/SQL concluido com exito.

Decorreram: 00:00:01.68
07:29:18 RICARDO@DEV> SELECT CON_ID, PLAN_HASH_VALUE FROM DBA_HIST_SQL_PLAN WHERE SQL_ID = 'fy4tpsg8pffju';

    CON_ID PLAN_HASH_VALUE
---------- ---------------
         4      2966233522
         4      2966233522
         4      2966233522

Decorreram: 00:00:00.01
07:29:38 RICARDO@DEV> @?/rdbms/admin/awrsqrpt.sql

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Introduzir valor para report_type: text

Type Specified:  text
Decorreram: 00:00:00.00


Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB
Introduzir valor para awr_location: AWR_PDB

Location of AWR Data Specified: AWR_PDB


Current Instance
~~~~~~~~~~~~~~~~
   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 3403615452 ORCL                1 ORCL




Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  3403615452     1      ORCL         ORCL         Baltazar.loc

Using 3403615452 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Introduzir valor para num_days: 1

Listing the last day's Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

ORCL         ORCL                 1  23 Jun 2019 07:21    1
                                  2  23 Jun 2019 07:28    1


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Introduzir valor para begin_snap: 1
Begin Snapshot Id specified: 1

Introduzir valor para end_snap: 2
End   Snapshot Id specified: 2


Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Introduzir valor para sql_id: fy4tpsg8pffju
SQL ID specified:  fy4tpsg8pffju

Listing all available Container DB Ids for SQL Id fy4tpsg8pffju
  Container DB Id Container Name
----------------- --------------
*      3403615452 DEV

Using Container DB Id 3403615452


Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_1_2.txt.  To use this name,
press <return> to continue, otherwise enter an alternative.

Introduzir valor para report_name:

Using the report name awrsqlrpt_1_1_2.txt


WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name         DB Id    Unique Name DB Role          Edition Release    RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
ORCL          3403615452 ORCL        PRIMARY          EE      19.0.0.0.0 NO  YES

Instance     Inst Num Startup Time
------------ -------- ---------------
ORCL                1 11-Jun-19 20:13

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:         1 23-Jun-19 07:21:00         1       9.0
  End Snap:         2 23-Jun-19 07:28:35         1       3.0
   Elapsed:                7.58 (mins)
   DB Time:                0.17 (mins)

SQL Summary                                                                             DB/Inst: ORCL/ORCL  Snaps: 1-2

                Elapsed
   SQL Id      Time (ms)
------------- ----------

 Container DB
 ------------
fy4tpsg8pffju      2,845
Module: SQL*Plus
SELECT COUNT(OBJECT_ID) FROM T WHERE OBJECT_TYPE = 'SYNONYM'
   3403615452

          -------------------------------------------------------------

SQL ID: fy4tpsg8pffju (Container DB Id: 3403615452)                                     DB/Inst: ORCL/ORCL  Snaps: 1-2
-> 1st Capture and Last Capture Snap IDs
   refer to Snapshot IDs witin the snapshot range
-> SELECT COUNT(OBJECT_ID) FROM T WHERE OBJECT_TYPE = 'SYNONYM'

    Plan Hash           Total Elapsed                 1st Capture   Last Capture
#   Value                    Time(ms)    Executions       Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1   2966233522                  2,845             2             2              2
          -------------------------------------------------------------


Plan 1(PHV: 2966233522)
-----------------------

Plan Statistics                                                                         DB/Inst: ORCL/ORCL  Snaps: 1-2
-> % Snap Total shows the % of the statistic for the SQL statement compared to the instance total

Stat Name                                Statement   Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms)                             2,845        1,422.7    28.2
CPU Time (ms)                                 2,844        1,421.8    41.2
Executions                                        2            1.0     0.0
Buffer Gets                                 394,677      197,338.5    78.7
Disk Reads                                        0            0.0     0.0
Parse Calls                                       2            1.0     0.1
Rows                                              2            1.0     N/A
User I/O Wait Time (ms)                           0            0.0     0.0
Cluster Wait Time (ms)                            0            0.0     0.0
Application Wait Time (ms)                        0            0.0     0.0
Concurrency Wait Time (ms)                        0            0.0     0.0
Invalidations                                     0            N/A     N/A
Version Count                                     1            N/A     N/A
Sharable Mem(KB)                                 23            N/A     N/A
          -------------------------------------------------------------

Execution Plan
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   358 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    10 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  1967 | 19670 |   358   (1)| 00:00:01 |
---------------------------------------------------------------------------



Full SQL Text

SQL ID        SQL Text
------------- -----------------------------------------------------------------
fy4tpsg8pffju SELECT COUNT(OBJECT_ID) FROM T WHERE OBJECT_TYPE = 'SYNONYM'


Report written to awrsqlrpt_1_1_2.txt
07:30:25 RICARDO@DEV>

Post Reply