Database report

ORCL.BNL.GOV usage between 17-Mar-2010 and 23-Mar-2010

Top usageProblemsSecuritySpaceOthers
Top 5 resource usage
By site
By user
By service
By sessions/day		Invalid objects
Broken jobs
Short connections
Bind variables missing		Passwords expiring
Schemas not used
Connection Errors		(Recycle bin usage)
Fragmented tables
Space consumption		Service list

Top sites

Top CPU usage (cpu hours)Top physical reads (GiB)
DOMAIN/HOSTCPUH
cern.ch 8,53
UNKNOWN 0,63
mara 0,28
usatlas.bnl.gov 0,01
local 0,00
DOMAIN/HOSTPREADS
cern.ch 1'860,2
mara 0,6
usatlas.bnl.gov 0,0
local 0,0
UNKNOWN 0,0
Top writes (GiB)Top logical reads (GiB)
DOMAIN/HOSTLWRITES
cern.ch 671,8
mara 0,5
UNKNOWN 0,5
usatlas.bnl.gov 0,1
local 0,0
DOMAIN/HOSTLREADS
cern.ch 16'588,8
mara 2'187,7
UNKNOWN 143,4
local 2,8
usatlas.bnl.gov 2,2

Top users

Top CPU usage (cpu hours)Top physical reads (GiB)
OS_USERHOSTUSERNAMECPUH
atlast0 atltzp1.cern.ch ATLAS_TAGS_DATA_2009 7,28
IUSR_WebServices UNKNOWN FLORBELA ,53
Elisabeth Vinek mara ATLAS_TAGS_CATALOG ,28
atlast0 atltzp1.cern.ch ATLAS_TAGS_COMM_2009 ,15
oracle itrac506.cern.ch USERMON ,12
OS_USERHOSTUSERNAMEPREADS
atlast0 atltzp1.cern.ch ATLAS_TAGS_DATA_2009 1'769,9
atlast0 atltzp1.cern.ch ATLAS_TAGS_COMM_2009 89,7
Elisabeth Vinek mara ATLAS_TAGS_CATALOG 0,6
oracle itrac506.cern.ch USERMON 0,1
apache lxvm0341.cern.ch ATLAS_TAGS_READER 0,1
Top writes (GiB)Top logical reads (GiB)
OS_USERHOSTUSERNAMELWRITES
atlast0 atltzp1.cern.ch ATLAS_TAGS_DATA_2009 340,1
atlast0 atltzp1.cern.ch ATLAS_TAGS_COMM_2009 8,8
atlast0 lxbrl2716.cern.ch ATLAS_TAGS_DATA_2009 4,6
atlast0 lxbrb0513.cern.ch ATLAS_TAGS_DATA_2009 4,5
atlast0 lxbra5503.cern.ch ATLAS_TAGS_DATA_2009 3,8
OS_USERHOSTUSERNAMELREADS
atlast0 atltzp1.cern.ch ATLAS_TAGS_DATA_2009 15'505,9
Elisabeth Vinek mara ATLAS_TAGS_CATALOG 2'175,3
atlast0 atltzp1.cern.ch ATLAS_TAGS_COMM_2009 184,7
IUSR_WebServices UNKNOWN FLORBELA 133,7
oracle itrac506.cern.ch USERMON 106,9

Connections and cluster usage per application

Tips - having a high percentage of physical reads compared to logical reads might mean the application queries are performing 'full table scan' which is considerabily heavy and slow. We recommend to check the the queries with 'autotrace' to improve the performance

Only showing users with significant activity.

USERNAMESESSIONSCPUHLREADSPREADSLWRITES
ATLAS_TAGS_CATALOG 5 0,28 2'227'483 649 (0.03%) 395
ATLAS_TAGS_COMM_2009 25 0,16 210'929 91'891 (43.56%) 9'889
ATLAS_TAGS_DATA_2009 2'939 8,22 16'619'574 1'812'721 (10.91%) 677'847
TOTAL 9'156 9,33 19'265'197 1'905'331 (9.89%) 688'942

Legend:

CPUH - CPU hours used by Oracle sessions
LREADS - logical reads - MiB read from memory
PREADS - physical reads - MiB read from disk (percentage of LREADS)
LWRITES - logical writes - MiB writen to memory (and disk)

Database usage per service

The table below reports the time spent executing DB operations, detailed per hour and service. A DB connection is executing on the DB if it is on CPU or waiting for IO, waiting for network, or other serialization events (i.e. locks). For example if service A shows 100% usage over the interval marked 10:00, it means that on average one DB operation was constantly running for that service between 9am and 10am. SYS$USERS services is an internal service accounting, among others, the DB activity of statistics gathering and scheduled jobs.

Legend:

10% < 100%101% < 200%201% < 800%> 800%
ModerateHighVery highSaturation

Time SYS$USERS TAGS
19-MAR-10 23:00 133%  
19-MAR-10 08:00   32%
19-MAR-10 07:00   29%
19-MAR-10 05:00   34%
19-MAR-10 04:00   73%
19-MAR-10 03:00   88%
19-MAR-10 02:00   57%
19-MAR-10 01:00   107%
19-MAR-10 00:00 133% 95%
18-MAR-10 23:00 108% 85%
18-MAR-10 22:00   75%
18-MAR-10 21:00   82%
18-MAR-10 20:00   147%
18-MAR-10 19:00   188%
18-MAR-10 18:00   202%
18-MAR-10 17:00   197%
18-MAR-10 16:00   127%
18-MAR-10 15:00   163%
18-MAR-10 14:00   107%
18-MAR-10 13:00   142%
18-MAR-10 12:00   171%
18-MAR-10 11:00   174%
18-MAR-10 10:00   31%
18-MAR-10 06:00   11%
17-MAR-10 23:00 42%  
Time SYS$USERS TAGS
17-MAR-10 13:00   100%
17-MAR-10 12:00   10%
Time SYS$USERS TAGS

Sessions per day per application

Showing only schemas with more than 2500 connection last week

USERNAMETOTALMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAYSUNDAY
ATLAS_TAGS_DATA_2009 2939 3 2 0 2848 70 8 8
FLORBELA 5954 1029 1650 0 1733 1542 0 0
TOTAL 9468 1051 1734 312 4594 1745 16 16

Problems

Broken jobs

Broken jobs do not run and should be removed and/or recreated.

Empty result set

Invalid objects

Tips - Invalid objects should be either validated or removed. To check for invalid object on your schema: SELECT * FROM USER_OBJECTS WHERE STATUS='INVALID'

USERNAME#OBJs
ATLAS_TAGS_3D 1

Short connections (less than 2 seconds)

Tips - There is a heavy load on the database by creating and closing sessions. When there are many connections from the same pair user/host we recommend to use persistent connections or connection pooling.

Showing only the 20 pairs user/host with most sessions

USERNAMEOS_USERNAMEUSERHOSTSESSIONS
ATLAS_TAGS_COMM 10
LFC_TESTLHCOPN root gridopn02.usatlas.bnl.gov 10
ATLAS_TAGS_COMM fjriegas 10
LFC_TESTLHCOPN root 10
ATLAS_TAGS_COMM_2009 atlast0 atltzp1.cern.ch 12
LFC_TESTLHCOPN lfcmgr 12
ATLAS_TAGS_COMM_2009 atlast0 12
LFC_TESTLHCOPN lfcmgr gridopn02.usatlas.bnl.gov 12
ATLAS_TAGS_COMM_2009 18
LFC_TESTLHCOPN 22
ATLAS_TAGS_DATA_2009 atlast0 lxbrl2720.cern.ch 48
ATLAS_TAGS_DATA_2009 atlast0 lxbrl2716.cern.ch 48
ATLAS_TAGS_DATA_2009 atlast0 lxbrl2910.cern.ch 48
ATLAS_TAGS_DATA_2009 atlast0 lxbrb0309.cern.ch 48
ATLAS_TAGS_DATA_2009 atlast0 lxbrl2917.cern.ch 48
ATLAS_TAGS_DATA_2009 atlast0 lxbrb1401.cern.ch 54
ATLAS_TAGS_DATA_2009 atlast0 lxbrb2712.cern.ch 54
ATLAS_TAGS_DATA_2009 atlast0 lxbrb0513.cern.ch 60
ATLAS_TAGS_READER apache lxvm0341.cern.ch 164
ATLAS_TAGS_READER apache servdb.usatlas.bnl.gov 186
ATLAS_TAGS_READER apache 350
ATLAS_TAGS_READER 352
FLORBELA fjriegas 738
FLORBELA fjriegas 738
ATLAS_TAGS_DATA_2009 atlast0 5834
ATLAS_TAGS_DATA_2009 5840
FLORBELA IUSR_WebServices 8888
FLORBELA IUSR_WebServices 8888
FLORBELA 9626
TOTAL 15876

Top queries not using bind variables

Tips - The table below shows the number of times a similar query (only the parameters change) exist in memory. That means they don't make use of bind variables, in which case only one copy of the query would be in memory. The usage of bind variables improves the efficiency of the queries as well as decreases the CPU used by optimizer.

Note - These are the current values in memory, taken at 00:10 24-MAR-2010

TOTALUSERNAMESQL_TEXT_WO_CONSTANTS
712 FLORBELA SELECT NVL(WAIT_CLASS,'#') WAIT_CLASS,ROUND(@*COUNT(*)/TASH.TOTAL_ACT,@) PER_ACT FROM GV$ACTIVE_SESSION_HISTORY ASH, (SELECT COUNT(*) TOTAL_ACT FROM GV$ACTIVE_SESSION_HISTORY WHERE SAMPLE_TIME>= SYSDATE-@/(@*@) AND INST_ID=SUBSTR('#',@,@)) TASH WHERE SAMPLE_TIME>= SYSDATE-@/(@*@) AND INST_ID||'#'||SESSION_ID||'#'||SESSION_SERIAL# ='#' GROUP BY INST_ID||'#'||SESSION_ID||'#'||SESSION_SERIAL#,WAIT_CLASS,TASH.TOTAL_ACT ORDER BY @ DESC

Security

Connection Errors

USERNAMECOUNTERROR
USERMON 23 Invalid username/password
USERMON 16 Account locked(timed)
CARLOS 2 Invalid username/password
SQLPLUS LFC_TESTLHCOPN 2 Invalid username/password

Password expiring

Tips - Applications should not use schema with password expiration. They should rather use _r or _w accounts.

Showing only open or expired (not used) accounts

USERNAMEEXPIRING
ATLAS_TAGS_3D 19-APR-08
BNLADMIN 14-JAN-09
ATLAS_COOL_PILOT 02-DEC-09

Not used schema

Tips - If the schema is not used maybe it's better to lock the account or even delete it.

Showing only open or expired (not locked) accounts for >6 months)

USERNAMELAST_LOGINOBJ
ATLAS_COOL_PILOT 18
ATLAS_TAGS_3D 67
ATLAS_TAGS_METADATA 272
BNLADMIN 4
JCRANSHAW
SYSTEM 469

Space usage

Fragmented tables

Showing tables with more than 80% free allocated space

Empty result set

Disk space usage per application

Showing only schemas with more than 5GB used

OWNERTABLESINDEXESTOTAL
ATLAS_TAGS_COMM 55 148 202
ATLAS_TAGS_DATA_2009 103 244 347
ATLAS_TAGS_COMM_2009 314 719 1033
OTHER 3 0 4
TOTAL 475 1111 1586

Registered services

Map of RAC services

SERVICE1 2
LFCOPNTEST X
MONTEST X
ORCL.BNL.GOV X X
ORCLXDB X X
TAGS X

Questions? PhyDB.Support@cern.ch

Took: 32.29 sec