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.
- 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 tofalse
:
Ensure that the output shows$ <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
EMD setproperty succeeded
. - Set the
_collectASHSQLStatement
property tofalse
:
Ensure that the output shows$ <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
EMD setproperty succeeded
. - Set the
_maskLiteralsInSQLStatement
property tofalse
:
Ensure that the output shows$ <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
EMD setproperty succeeded
. - Set the
_collectSQLStatement
property tofalse
:
Ensure that the output shows$ <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
EMD setproperty succeeded
. - Verify that the property settings from the configuration file
emd.properties
are set to false:
The command should return the following prompt with all four values set to false.$ cat <AGENT_BASE_DIR>/agent_inst/sysman/config/emd.properties | grep SQL
_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 userDBSNMP
, 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
Here, the four properties can be seen with a true, false, or empty value:$ cat <AGENT_BASE_DIR>/agent_inst/sysman/config/emd.properties | grep SQL
_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.
The command should return the following prompt with all four values set to true.$ cat <AGENT_BASE_DIR>/agent_inst/sysman/config/emd.properties | grep SQL
_collectSQLStatement=true _maskLiteralsInSQLStatement=true _collectASHSQLPlan=true _collectASHSQLStatement=true
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.