DBA Hub

📋Steps in this guide1/4

How to mark SQL_ID as Colored

How to mark SQL_ID as Colored Contents

oracle clusteringintermediate
by OracleDba
13 views
1

Overview

Contents 1. Overview

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
By default, only top sql_ids are captured in database snapshots.

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).

Information will be captured in each snapshot for sqls marked using DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL. 

All the information related to the specific sql (sql plan, executions statistics, etc) will be stored in the AWR tables. 

However the SQLs will not neccesarily appear in the AWR Report sections related to SQLS unless they are indeed amongst the top SQLs for that sections as determined by the setting for DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS
2

Section 2

2. Mark SQL_ID as colored select * from DBA_HIST_COLORED_SQL; 3. Unmark Colored SQL_ID

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL>
select * from DBA_HIST_COLORED_SQL;
no rows selected

SQL>
execute DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL(sql_id=>'1gyx4vasq9yv0');
PL/SQL procedure successfully completed.

SQL>
select * from DBA_HIST_COLORED_SQL;
DBID SQL_ID        CREATE_TI
---------- ------------- ---------
3554487085 1gyx4vasq9yv0 25-JUL-18
<-----
SQL>
3

Section 3

3. Unmark Colored SQL_ID select * from DBA_HIST_COLORED_SQL; <------ Caution : Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using. Caution

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
SQL>
execute DBMS_WORKLOAD_REPOSITORY.remove_COLORED_SQL(sql_id=>'1gyx4vasq9yv0');
PL/SQL procedure successfully completed.

SQL>
select * from DBA_HIST_COLORED_SQL;
no rows selected
<------
SQL>
4

Section 4

: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!