Thursday, November 10, 2011

Oracle Statistics Tables.

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

TKPROF And Oracle Trace Analysis

What is the TKPROF ??!!

The TKPROF program :
converts Oracle trace files into a more readable form. If you have a problem query you can user TKPROF to get more information. 

But first you need to enable this tools since its unactivated in oracle  to start using it follow the below steps :

1-ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

2-we need table called PLAN_TABLE if its dosen't found create it by following steps :

@ORACLE_HOME\rdbms\admin\utlxplan.sql
CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYS.PLAN_TABLE;
GRANT SELECT, INSERT, UPDATE, DELETE ON SYS.PLAN_TABLE TO PUBLIC;
3-after doing the Previous steps , now you can use the amazing tools for example :

ALTER SESSION SET SQL_TRACE = TRUE;

SELECT COUNT(*)
FROM   dual;

ALTER SESSION SET SQL_TRACE = FALSE;
 
OR Another Way to trace file :
 
TKPROF   
explain=user/password@service table=sys.plan_table
 
Output will be like :
 
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

 SELECT COUNT(*)
 FROM   dual

call    count    cpu elapsed    disk   query current     rows
------- -----  ----- ------- ------- ------- -------  -------
Parse       1   0.02    0.02       0       0       0        0
Execute     1   0.00    0.00       0       0       0        0
Fetch       2   0.00    0.00       0       1       4        1
------- -----  ----- ------- ------- ------- -------  -------
total       4   0.02    0.02       0       1       4        1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 121  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE 
      1   TABLE ACCESS FULL DUAL 
 
Thank you 
Osama mustafa  

Create Enterprise manager for RAC (Real Application Cluster)

Hi All ,

Sometimes we need to create enterprise manager for Rac Environment , its the same way for One instance with different Command . 

We are here talk about 10g , 11g database .
you follow the below steps to create or recreate enterprise manager LET'S START :

if you already have RAC - enterprise manager and you need to recreate then start from here

**Stop Dbconsole for the both nodes :

Commands :

On Node-1
emctl stop dbconsole .
On Node 2 :
emctl stop dbconsole .

**Drop dbconsole with re posterity from node 1 :

Command :

$emca -deconfig dbcontrol db -repos drop -cluster

"you will wait for while until repos been dropped".

**recreate dbconsole with re posterity from node 1 "if you don't have EM for your Rac start from here"

Command :

$emca -config dbcontrol db -repos create -cluster 

"follow the instsruction and fill the information such as DB SID,Listener Port , Crs Name"

To know your CRS NAME :
$CRS_HOME/bin/cemutlo -n

the output will be your crs name .

Thank you
Osama mustafa