Automatic Shared Memory Management is disabled for one of your database instances.
Some SQL statements perform poorly due to excessive hard parse activity, thereby degrading performance.
What would be your next step?
Automatic Shared Memory Management is disabled for one of your database instances.
Some SQL statements perform poorly due to excessive hard parse activity, thereby degrading performance.
What would be your next step?
Given that automatic shared memory management is disabled, memory advisors for specific areas like the shared pool can't be run. The SQL statements are performing poorly due to excessive hard parsing, which suggests issues with the SQL statements themselves, such as not using bind variables properly. The appropriate approach would be to run the SQL Tuning Advisor to analyze the SQL and provide recommendations to optimize it.
B is correct
Agree with you: https://blogs.oracle.com/oraclemagazine/using-database-advisors The shared pool advisor provides information about the estimated parse time in the shared pool for different pool sizes, the buffer cache advisor provides information about physical reads and time for the cache size, and the PGA advisor provides information about cache hit percentage against PGA target memory size.
Memory advisors are available under the following conditions: AMM - Memory Advisor, ASMM - SGA Advisor & PGA Advisor, If MSMM (manual shared memory management) & Automatic PGA Memory Management - Buffer Cache Advisor & PGA Advisor. And, APMM is the default for Oracle databases. A is wrong. SQL Access Advisor is normally run after the SQL Tuning Advisor, if the latter does not give the required results. B is wrong. You cannot run the memory advisor for the shared pool only. C is correct. This is the logical first step in tuning SQL statements when no memory advisors are available to use. D is wrong. Hard parsing indicates that the statement in not cached in the shared pool, part of the SGA, and has nothing to do with the PGA. E is wrong. The SGA advisor is only available when running in ASMM mode.
i will go with B.
B is correct , no doubt about it
B. A hard parse rate of more than 100 a second indicates that there is a very high amount of hard parsing on the system. High hard parse rates cause serious performance issues and must be investigated. Usually, a high hard parse rate is accompanied by latch contention on the shared pool and library cache latches
I still have the doubt. Run the Memory Advisor for the Program Global Area
C is correct. https://docs.oracle.com/en/database/oracle/oracle-database/21/tgsql/sql-tuning-guide.pdf 1.5.1 SQL Tuning Tasks 4. Defining the scope of the problem The scope of the solution must match the scope of the problem. Consider a problem at the database level and a problem at the statement level. For example, the shared pool is too small, which causes cursors to age out quickly, which in turn causes many hard parses. Using an initialization parameter to increase the shared pool size fixes the problem at the database level and improves performance for all sessions. However, if a single SQL statement is not using a helpful index, then changing the optimizer initialization parameters for the entire database could harm overall performance. If a single SQL statement has a problem, then an appropriately scoped solution addresses just this problem with this statement.
c "excessive hard parse" means that there is bad SQL (i.e, use of literals and no bind variables when the statement structure is the same)
v$shared_pool_advice
B is correct
B is correct, it advises on memory size.
B is correct it advises on proper memory size for manual memory configuration when AMM is not in place. see memory architecture for oracle https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-memory.html#GUID-24312D78-522D-4C08-8934-820BE18D34C7
I think C because Memory advisor doesn't exists for shared pool and SQL Tuning Advisor is the only option when Manual Shared Memory management is enabled
B is correct
B,D and E are false since ASMM is turned off and we cannot run memory advisors, the correct answer is C sql tuning advisor because hard parse is due either shared pool is too small or sql statements are not using bind variables.