Some good articles about Oracle

1 面对一个全新的环境,作为一个Oracle DBA,首先应该了解什么?
2 Oracle GridControl 11gR1 for Linux 安装和配置指南
3 exadata x2-8的技术方向终于向正确的方向有所改进
4 Oracle Support Master Note for 10g Enterprise Manager Grid Control Agent Performance & Core Dump issues (Doc ID 1087997.1)
5 Working with Materialized Views Creation, Refresh and Monitoring
6 Starting and Stopping Oracle Enterprise Manager 11g Grid Control
7 Grid Control 11 Known Issues, Bugs and Features
8 Master Note: How to diagnose Database Performance - FAQ [ID 402983.1]
9 No Response from the Server, Does it Hang or Spin? [ID 68738.1]
10 Data Gathering for Troubleshooting RAC Issues [ID 556679.1]
11 What is enq: TX - row lock contention?
12 enq: TX - row lock contention
13 Use Batch File to Create Filename Using Date and Time
14 EMD upload error: Upload was successful but collections currently disabled - disk full
15 SQL Loader
16 Script to Detect Tablespace Fragmentation [ID 1020182.6]
17 Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2 [ID 1189783.1]
18 Troubleshooting Oracle Clusters and Oracle RAC

Finding statement/s which use lots of shared pool memory

SELECT substr(sql_text,1,40) "Stmt", 
count(*),
sum(sharable_mem) "Mem",
sum(users_opening)   "Open",
sum(executions)      "Exec"
FROM v$sql
GROUP BY substr(sql_text,1,40)
HAVING sum(sharable_mem) > ;


MEMSIZE is about 10% of the shared pool size in bytes

Checking for high version counts

"Versions" of a statement occur where the SQL is character for character identical but the underlying objects or binds or etc.. are different.
For example:
User scott has a dept table:
SQL>select * from dept
User Tom has his own dept table:
SQL>select * from dept

When Scott and Tom executes the above same SQL in terms of characters, because the base object "dept" is different, Oracle will treat the two statements as two different versions of SQL.

SELECT address, 
hash_value,
version_count,
users_opening,
users_executing,
substr(sql_text,1,40) "SQL"
FROM v$sqlarea
WHERE version_count > 10;

Finding the Library Cache hit ratio

SELECT SUM(PINS) "EXECUTIONS",SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"  

FROM V$LIBRARYCACHE;

If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses.

SQL Invalidation

Use the following query to identify cursors that are invalidated:
*****************************
set lines 300
col sql format a50
select substr(sql_text, 1, 40) "SQL", sql_id, invalidations 
from v$sqlarea 
where invalidations > &value 
order by invalidations;

Note: use the "&value" to dynamically enter a value to limit the output

Or use the following SQL to observer invalidations:

select sql_text,version_count,loads,invalidations,parse_calls
from v$sqlarea 

where sql_text like '%xxxxx%';

*****************************
What will cause cursor invalidation?
Activities like TRUNCATE, ANALYZE or DBMS_STATS.GATHER_XXX on tables or indexes, grants changes on unerlying objects.  The associated cursors will stay in the SQL area but when it will be reference next time, it should be reloaded and reparsed fully, so it will have negative impact on library cache.
****************************
Related metalink notes

1. [ID 123214.1]: Truncate - Causes Invalidations in the LIBRARY CACHE 
2. WAIT SCENARIOS REGARDING LIBRARY CACHE PIN AND LIBRARY CACHE LOAD LOCK [ID 115656.1]

Query shows SQL in the SGA where there are a large number of similar statements

The following query shows SQL in the SGA where there are a large number of similar statements:

SELECT substr(sql_text,1,40) "SQL",count(*),sum(executions) "TotalExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30
ORDER BY 2;

Note: If there is latch contention for the library cache latches the above statement may cause yet further contention problems.