9-6 to 9-11

13-15. SQL Access Advisor

This advisor requires that you identify a SQL workload, which is a representative set of SQL statenments that access the schema. You can select you workload from different sources including current and recent SQL activity, a SQL repository, or a user-defined workload such as from a development environment.
The SQL Access Advisor may make recommendations such as creating indexes or materialized views to improve your query performance for the given workload.

13-9. SQL Tuning Advisor

Oracle's Tuning Advisor calls Automatic Tuning Optimizer (ATO) to perform 4 types of analysis:

• Statistics Analysis: The Automatic Tuning Optimizer checks each query object for missing or stale statistics, and makes recommendations to gather relevant statistics.
• SQL Profiling (Tune SQL plan): The ATO verifies its own estimates and collects auxiliary information to remove estimation errors. It builds a SQL profile using the auxiliary information and makes a recommendation to create it. When a SQL profile is created, it enables the query optimizer to generate a well-tuned plan.
• Access path Analysis: the ATO explores whether a new index can be used to significantly improve access to each table in the query and, when appropriate, makes recommendations to create such indexes.
• SQL Structure Analysis: The ATO tries to identify SQL statements that use bad plans and makes relevant suggestions to restructure them. The suggested changes can be syntactic as well as semantic.

It's important to choose the appropriate scope for the tuning task. If you choose the Limited option, then recommendations are based on:

• Statistics check
• Access path analysis
• SQL structure analysis

If you choose comprehensive option, then SQL profile recommendations are generated as well.

12-22. Server-generated alerts

A few key metrics that can provide early problem notifications are:

• Average File Read Time (centiseconds)
  
• Dump Area Used (%)
  
• Response Time (per transaction)
  
• SQL Response Time (%)
  
• Tablespace Used (%)
  
• Wait Time (%)

By default, the following server-generated alerts are enabled:

• Tablespace Space Usage (warnings 85%, critical 97%)
  
• Snapshot Too Old
  
• Recovery Area Low on Free Space
  
• Resumable Session Suspended

To set or edit a threshold for your whole database, select Manage Metrics in the Related Links region of the database home page.

There are two kinds of server-generated alerts:

• Threshold (stateful) alerts: they are configured by setting a warning and critical threshold values on database metrics. For example: Physical Reads per second, User commits per second, SQL Service Response Time, etc.
  Except for the Tablespace Space Usage metric, which is database related, the other metrics are instance related.
  Stateful alerts appear in DBA_OUTSTANDING_ALERTS and when cleared, go to DBA_ALERT_HISTORY.
  
• Nonthreshold (stateless) alerts: corresponds to specific database events such as Snapshot Too Old errors, Recovery Area Low On Free Space, and Resumable Session Suspended.
  Stateless alerts go directly to the history table. Clearing a stateless alert makes sense only in the Database Control environment because Database Control stores stateless alerts in its own repository.
  

12-21. The DBMS_ADVISOR Package

The DBMS_ADVISOR package contains all constants and procedure declarations for all advisor modules. You can use this package to execute tasks via command line.
To execute advisor procedures, you must be granted the ADVISOR privilege. The ADVISOR privilege permits full access to the advisor procedures and views.

Procedures:

• CREATE_TASK
• DELTE_TASK
• EXECUTE_TASK
• INTERRUPT_TASK
• GET_TASK_REPORT
• RESUME_TASK
• UPDATE_TASK_ATTRIBUTES
• SET-TASK_PARAMETER
• MARK_RECOMMENDATION
• GET_TASK_SCRIPT

12-8. Using the Optimizer Statistics

Go to the Enterprise Manager page for managing optimizer statistics by clicking Manage Optimizer Statistics on the Administration tabbed page. Note that GATHER_STATS_JOB should be enabled and for this to work, you should set STATISTICS_LEVEL initialization parameter is set to at least TYPICAL.

From Manage Optimizer Statistcs page, you can perform the following tasks on statistics:

• Gather optimizer statistics manually. this action submits the job that GATHER_STATS_JOB automatically does. This should be done in the case where a table's contents have changed so much between automatic gathering jobs that the statistics no longer represent the table accurately. Examples of this are a table that is truncated in the middle of the day and a batch job that runs and adds large amounts of data to a table.
• Restore optimizer statistics to a point in the past. The point in time chosen must be within the optimizer statistics retention period, which defaults to 30 days.
• Lock optimizer statistics to guarantee that the statistics for certain objects are never overwritten. This is useful if statistics have been calculated for a certain table at a time when well-representative data is present, and you want to always have those statistics. No fluctuation in the table affects the statistics if they are locked.
• Unlock optimizer statistics to undo the previously done lock
• Delete optimizer statistics to delete statistics.

When to re-organize a table

Notes from: All about chained rows @ Ask Tom

Before jumping into the topic, we need to first clarify some concepts:

• A row that doesn't fit into a block A needs to be "chained" with another block B.
• Now, if there was another row in the block A then it needs to be "migrated" to another block when block A fills up.
• a Full table scan example is SELECT * FROM t
• a query using an index is for example SELECT * FROM t WHERE t.col1 = 1 (it uses the index on col1 to find the row where t.col1 = 1)

Migrated rows and their impact on:

• Full table scan: are not affected by migrated rows since it picks up data while reading the disks
• Querying migrated rows with index (instead of full table scan): go through the ROWID pointer (that was left behind when a row was migrated) to find the data it needs so it adds 1 to Table Fetch Continue Row count.

Chained rows and their impact on:

• Full table scan: since we can't find the data in one place only, we will need an additional read to find all the data
• Querying chained rows with index (instead of full table scan): idem.

Conclusion
Chained rows are inevitable so there's not much we can do unless the data model could be changed so that fewer data is stored - but that's a functional problem. On the other hand, if we have lots of migrated rows that each of them would perfectly fit in one datablock, then we could potentially reorganize the table to reduce the additional disk lookup.

12-5. Optimizer Statistics

Optimizer statistics include table, column, index and system statistics. They provide the optimizer a statistically correct snapshot of data storage and distribution, which the optimizer uses to make decisions on how to access data.

The statistics that are collected include:

• Size of the table or index (in database blocks)
  SELECT blocks FROM dba_tables WHERE owner='hr' AND table_name='EMPLOYEES'
• Number of rows
  SELECT num_rows FROM dba_tables WHERE owner='hr' AND table_name='EMPLOYEES'
• Average row size and chain count (tables only)
  SELECT avg_row_len, chain_cnt FROM dba_tables WHERE owner='hr' AND table_name='EMPLOYEES'
• Height and number of deleted leaf rows (indexes only)

Optimizer statistics are collected automatically by the preconfigured GATHER_STATS_JOB, which runs during predefined maintenance windows, once per day.

A large table that experiences 10 percent growth (or reduction) within a 24-hour period is usually considered too volatile for statistics collection once per day to be sufficient. For tables that experience this level of change, Oracle recommends collecting statistics more frequently so it won't exceed 10 percent growth between collection periods.

Statistics can be manually collected by using Enterprise Manager or through the use of the DBMS_STATS package as shown here:

SQL> EXEC dbms_stats.gather_table_stats('HR', 'EMPLOYEES');

9-9. Monitoring and Administering Undo

In order to minimize the impact to the users, proactively monitor the alert log for the below errors and resize the undo tablespace accordingly:

• ORA-01650 unable to extend rollback segment error message: happens when the undo tablespace runs out of room for undo data. Make sure users commit large transactions periodically.
  
• ORA-01555 snapshot too old error message: occurs when the query needs to access undo information that has already been overwritten. This may happen in a long-running or flashback query. Configure an appropriate undo retention interval by setting UNDO_RETENTION in seconds and altering tablespace to have RETENTION GUARANTEE. This parameter UNDO_RETENTION is only used when:
  - the undo tablespace has the AUTOEXTEND option enabled or
  - you want to set undo retention for LOBS or
  - you want to guarantee retention.

The recommendation is to set the AUTOEXTEND to true until we determined the space requirements for the undo tablespace. After that, we should disable it.

8-28 to 8-31. Lock Conflicts Causes, Detection and Resolution

Causes

• Uncommited changes
  
• Long running transactions: Many applications use batch processing to perform bulk updates. Lock conflicts are common when transaction and batch processing are being performed simultaneously.
• Unnecessarily high lock levels: Some database still lock at page or table level. As a consequence, developers writing applications to be run among multiple databases use artificially high lock levels which make Oracle behave like those less capable DBMS.
  

Detection
Use Blocking Sessions page in Enterprise Manager to locate lock conflicts.
Alternatively, the Automatic Diagnostic Monitor (ADDM) also automatically detects lock conflicts and can advise you on inefficient locking trends.

Resolution
The best way: inform the user and let them complete the transaction.
In emergency: kill the session in session detail page (Enterprise Manager) or manually using SQL:

SQL> select sid, serial#, username from v$session where sid in (select blocking_session from v$session);

The above selects sessions where its SID matches those SIDs that are blocking other sessions.

SQL> alter system kill session 'sid,serial_number' immediate;

8-23. Lock Modes

Need more info on these locking modes (though we often don't use it since they are automatically obtained):

• ROW SHARE
• ROW EXCLUSIVE
• SHARE
• SHARE ROW EXCLUSIVE
• EXCLUSIVE

Explicación extensa de Oracle: http://youngcow.net/doc/oracle10g/server.102/b14220/consist.htm

Intento de explicación (cualquier cosa): http://mioracle.blogspot.com/2008/03/bloqueo-base-de-datos.html

8-20. Triggering Events

• DML: INSERT, UPDATE, DELTE
• DDL: CREATE, DROP, ALTER, GRANT, REVOKE, RENAME
• Database: LOGON, LOGOFF, STARTUP, SHUTDOWN, SERVERERROR

8-25. DML locks

Transaction 1:
UPDATE employees
SET salary=salary*1.1
WHERE employee_id=107;

Transaction 2:
UPDATE employees
SET salary=salary*1.1
WHERE employee_id=106;

Each DML transaction must obtain two locks:

• An EXCLUSIVE row lock for the row or rows being updated
• A ROW EXCLUSIVE table-level lock on the table being updated. This is to prevent another session from locking the whole table (possibly to drop or truncate it) while the change is being made.
  

8-18. Built-in Packages

• DBMS_STATS: Gather, view, and modify optimizer statistics
• DBMS_OUTPUT: Generate output from PL/SQL
  
• DBMS_SESSION: PL/SQL access to the ALTER SESSION and SET ROLE statements
  
• DBMS_RANDOM: Generate random numbers
  
• DBMS_UTILITY: Get time, CPU time, and version information; compute a hash value, and perform many other miscellaneous functionalities
  
• DBMS_SCHEDULER: Schedule functions and procedures that are callable from PL/SQL
  
• DBMS_CRYPTO: Encrypt and decrypt database data
  
• UTL_FILE: Read and write to operating system files from PL/SQL