11.2.0.1> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for HPUX: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
11.2.0.1> set linesize 120
11.2.0.1> col event format a40
11.2.0.1>
11.2.0.1> select startup_time from v$instance;
STARTUP_TIME
-------------------
07.12.2010 14:12:39
11.2.0.1>
11.2.0.1> select sql_id
2 , version_count
3 , (select count(*) from v$sql s where s.sql_id = a.sql_id) child_cnt
4 , loaded_versions
5 , trunc(sharable_mem / 1024 / 1024, 2) "Sh mem, M"
6 , trunc(sharable_mem/version_count/1024, 2) "Per version, K"
7 , executions
8 from v$sqlarea a
9 where version_count > loaded_versions * 100
10 and loaded_versions > 10;
no rows selected
11.2.0.1>
11.2.0.1> select event, total_waits, trunc(time_waited/100, 2) waited_s
2 from v$system_event
3 where event like 'library cache%'
4 order by time_waited desc;
EVENT TOTAL_WAITS WAITED_S
---------------------------------------- ----------- ----------
library cache lock 2530428 1111512.6
library cache: mutex X 269379 45639.84
library cache pin 1 39.12
library cache load lock 22 .25
11.2.0.1> select sql_id, version_count, loaded_versions from v$sqlarea where version_count>1e3;
SQL_ID VERSION_COUNT LOADED_VERSIONS
------------- ------------- ---------------
4vs91dcv7u1p6 10625 7694
f711myt0q6cma 9911 5789
|