Disable and Re-Enable SQL Execution Plan Collections

Oracle Management Cloud automatically collects SQL Execution Plan data. By upgrading your agents to the latest version, OMC can begin collecting SQL Execution Plan data across all your databases. You can also manually disable and re-enable SQL Execution Plan data collection should your environment require this.

Collecting SQL text and plans data, across all your databases, allows you to analyze problematic SQL and then use your experience, or tools like SQL Tuning Advisor, to tune your SQL Statements. Some examples of this can be:

Fast running queries may turn slow if the new execution plan is bad
SQL query changing plans frequently can cause variations in performance
Improperly parallelized or not parallelized execution plans
Improper Optimizer configuration

Note:

If you have used a custom defined user, and you've granted privileges with a previous script, you will need to grant additional privileges to avoid the following error:

Error evaluating SQL Plan Details:30MinASHITACollection - FetchletException: SQLException encountered while extracting the ASH Data: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

. To grant these additional privileges run the following SQL grant commands:

grant select ON V_$SQLAREA_PLAN_HASH to <YOUR MONITOR ROLE>
grant select ON GV_$SQLAREA_PLAN_HASH to <YOUR MONITOR ROLE>

For CDBs you will need to add the C## prefix to <YOUR MONITOR ROLE>.

If future users are created, use the 1.49 or later grantPrivileges.sql script, located in: $ <AGENT BASE DIR>/plugins/oracle.em.sgfm.zip/<CURRENT OMC VERSION>/scripts/grantPrivileges.sql

Disable SQL Text/Plan Collection

If you need to disable SQL data collection, update the Oracle Management Cloud Agent that is monitoring the database with the following steps:

Set the _collectASHSQLPlan property to false:

$ <AGENT_BASE_DIR>/agent_inst/bin/omcli setproperty agent -allow_new -name _collectASHSQLPlan -value FALSE
Oracle Management Cloud Agent
Copyright (c) 1996, 2019 Oracle Corporation. All rights reserved. 

EMD setproperty succeeded

Ensure that the output shows EMD setproperty succeeded.

Set the _collectASHSQLStatement property to false:

$ <AGENT_BASE_DIR>/agent_inst/bin/omcli setproperty agent -allow_new -name _collectASHSQLStatement -value FALSE
Oracle Management Cloud Agent
Copyright (c) 1996, 2019 Oracle Corporation. All rights reserved. 

EMD setproperty succeeded

Ensure that the output shows EMD setproperty succeeded.

Set the _maskLiteralsInSQLStatement property to false:

$ <AGENT_BASE_DIR>/agent_inst/bin/omcli setproperty agent -allow_new -name _maskLiteralsInSQLStatement -value FALSE
Oracle Management Cloud Agent
Copyright (c) 1996, 2019 Oracle Corporation. All rights reserved. 

EMD setproperty succeeded

Ensure that the output shows EMD setproperty succeeded.

Set the _collectSQLStatement property to false:

$ <AGENT_BASE_DIR>/agent_inst/bin/omcli setproperty agent -allow_new -name _collectSQLStatement -value FALSE
Oracle Management Cloud Agent
Copyright (c) 1996, 2019 Oracle Corporation. All rights reserved. 

EMD setproperty succeeded

Ensure that the output shows EMD setproperty succeeded.

Verify that the property settings from the configuration file emd.properties are set to false:

$ cat <AGENT_BASE_DIR>/agent_inst/sysman/config/emd.properties | grep SQL

The command should return the following prompt with all four values set to false.

_collectSQLStatement=false 
_maskLiteralsInSQLStatement=false
_collectASHSQLPlan=false 
_collectASHSQLStatement=false

Re-Enable SQL Text/Plan Collection

If you need to re-enable SQL data collection, update the OMC Cloud Agent that is monitoring the database with the following steps:

Note:

To re-enable SQL Execution Plan data collection for Oracle databases, you must have a special database user with appropriate privileges to access this data. If you use the database user DBSNMP, these privileges are not required. The DBSNMP user already has all the privileges required to collect Oracle IT Analytics data.

Obtain the property settings from the configuration file: emd.properties
```
$ cat <AGENT_BASE_DIR>/agent_inst/sysman/config/emd.properties | grep SQL
```
Here, the four properties can be seen with a true, false, or empty value:
```
_collectSQLStatement= <true/false/empty> 
_maskLiteralsInSQLStatement= <true/false/empty>
_collectASHSQLPlan= <true/false/empty>
_collectASHSQLStatement= <true/false/empty>
```
Note:
When running this command some agents might see _collectSQLStatement and _maskLiteralsInSQLStatement already set as true. In this case you can skip directly to step 3.

Set the property values to true for _collectSQLStatement and _maskLiteralsInSQLStatement with these commands:

$ <AGENT_BASE_DIR>/agent_inst/bin/omcli setproperty agent -allow_new -name _collectSQLStatement -value true

Oracle Management Cloud Agent
Copyright (c) 1996, 2019 Oracle Corporation.  All rights reserved. 
EMD setproperty succeeded

$ <AGENT_BASE_DIR>/agent_inst/bin/omcli setproperty agent -allow_new -name _maskLiteralsInSQLStatement -value true

Oracle Management Cloud Agent
Copyright (c) 1996, 2019 Oracle Corporation.  All rights reserved. 
EMD setproperty succeeded

Set the property values to true for _collectASHSQLStatement and _collectASHSQLPlan with these commands:

$ <AGENT_BASE_DIR>/agent_inst/bin/omcli setproperty agent -allow_new -name _collectASHSQLStatement -value true

Oracle Management Cloud Agent
Copyright (c) 1996, 2019 Oracle Corporation.  All rights reserved. 
EMD setproperty succeeded

$ <AGENT_BASE_DIR>/agent_inst/bin/omcli setproperty agent -allow_new -name _collectASHSQLPlan -value true

Oracle Management Cloud Agent
Copyright (c) 1996, 2019 Oracle Corporation.  All rights reserved. 
EMD setproperty succeeded

Verify the property settings from the configuration file emd.properties are set to true.

$ cat <AGENT_BASE_DIR>/agent_inst/sysman/config/emd.properties | grep SQL

The command should return the following prompt with all four values set to true.

_collectSQLStatement=true 
_maskLiteralsInSQLStatement=true
_collectASHSQLPlan=true 
_collectASHSQLStatement=true

Once all four property settings are set to true, SQL Text/Plan collection is now enabled and will begin at the next execution cycle. Oracle Management Cloud will collect these plans and begin automatically populating the repository over time.

For further information on how SQL Text and SQL Plan Collections work, how to view and analyze the collected data and specific use cases see: Findings and Performance Analysis and Monitor SQL Performance Across Databases.