Verificando el uso de AWR

Podemos verificar el uso de varias funcionalidades relacionadas con AWR en la vista dba_feature_usage_statistics por ejemplo, si  queremos ver si fueron creados Workload Repository Reports podemos utilizar la siguiente consulta:

column name format a30
SQL> SELECT name,
  detected_usages,
  currently_used,
  TO_CHAR(last_sample_date,'DD-MON-YYYY:HH24:MI') last_sample
FROM dba_feature_usage_statistics
WHERE name = 'AWR Report' ;

NAME                           DETECTED_USAGES CURRE LAST_SAMPLE
------------------------------ --------------- ----- -----------------
AWR Report                                  23 TRUE  21-SEP-2014:02:22

Ref:  AWR Reporting - Licensing Requirements Clarification (Doc ID 1490798.1)

Deadlocks causados por foreign keys sin indices

A continuación un interesante post de Dan Jankowski en su blog: https://perfstat.wordpress.com

Here is a step-by-step analysis of the deadlock which occurs due to unindexed foreign keys. This analysis was performed on Oracle XE 11.2.0.2 – results may vary on other versions.

A typical deadlock graph arising from an unindexed foreign key is as follows.

                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-0000508a-00000000        27     101    SX             28      12         SSX
TX-00090013-0000019b        28      12     X             27     101           S

This type of deadlock is characterised by at least one session holding a table lock of type TM (table lock), and another session waiting for the TM lock in SSX mode. To help make sense of this, I found it useful to first understand two underlying concepts; how to identify what a lock relates to, and how to trace locks.

Continuar leyendo ...