Exam 1z0-083 topic 1 question 25 discussion

B is correct

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.

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.

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

B is correct , no doubt about it

i will go with B.

I still have the doubt. Run the Memory Advisor for the Program Global Area