Tag: System

Oracle Statistics Tables.

Posted on by Osama Mustafa in Database
Hi ,

I am going to talk about Some Oracle Statistics Table that will be useful for tuning your database , and gathering information about your database , these table are very useful

For example Dynamic Performance View  :
**The below query will gives you the most sql statement taking more time in the CPU .
1-Select Sql_text , executions from v$sqlstats where cpu_time > 1000 ;
**Below Query Gives you some Details about Specific Machine 
2-Select * from v$session where machine = ‘OSAMA_PC’ and where
    logon_time > sysdate -1 ;
**finally , Lock in your database 
3- select sid,ctime from v$lock where block > 0 ;

Statistics Tables are :
**Display System Wide Statistics :
V$Sysstat
V$Statname
V$Sesstat

**Display System Wide Statistics :

V$Sgastat
V$Event_name
V$system_event

 **Display Session-Related Statistics
V$statname
V$Session
V$Session _Event
V$Session_Wait

**Display Service-Related Statistics
V$Service_stats
V$Services
V$Service_event
V$Service_wait_Class


**Display Information About Wait Class :

V$Session_Wait_Class
V$System_Wait_Class
V$Service_Wait_Class

**Display Session Wait

V$Session_Event : Session Wait By event for each session had to wait .
V$Session_Wait : Session Wait By Event For current active session that are waiting .
V$System_Event : Total waits for an event (All Session Together).

Wait Event : Information About session that had to wait or must wait for Different Reasons (V$Event_name).

Some Other Useful Tables :
-V$SQL
-V$SQLAREA

Thank You
Osama Mustafa