Day-24 : Performance Tuning

What are the effects of using bind variables?

Reduces the memory wastage in shared pool, helps in soft parsing of sql statement

--------------------------------------------------------------------------------------------------------------------------

Why would a DBA pin packages into memory?

To improve the performance. Oracle provides a procedure called dbms_shared_pool.keep to pin a package, and packages can be unpinned with dbms_shared_pool.unkeep

Example: EXECUTE dbms_shared_pool.keep('DBMS_ALERT');

--------------------------------------------------------------------------------------------------------------------------

Explain why the parameter session_cached_cursors is used for?

SESSION_CACHED_CURSORS specifies the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.

This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as statements are re-executed by a user.

--------------------------------------------------------------------------------------------------------------------------

How can you gather statistics on a table?

1. Analyze table TABLENAME compute statistics; (or)

2. EXEC DBMS_STATS.gather_table_stats('owner','table_name', estimate_percent => 100, cascade => TRUE);

--------------------------------------------------------------------------------------------------------------------------