accept u_name prompt ‘Enter User Name (Enter NONE if want SID specific query): ‘ accept s_id prompt ‘Enter SID (Enter 0 for Userspecific Query): ‘ select a.sid, b.sql_text, a.sql_id, c.spid, b.executions, b.disk_reads, b.elapsed_time, b.cpu_time, b.buffer_gets from v$session a, v$sqlarea b, v$process c where a.sql_id = b.sql_id(+) and (a.username = upper(‘&u_name’) or a.sid = &s_id )…
********************************************* ***** (1) TOOLS TO ANALYZE TRACE FILES. ***** ********************************************* Mainly 2 tools to analyze trace files. (A) tkprof (B) trcsess (from oracle 10g). This tool has been designed to deal with the new trace facilities that allow trace to be identified based on client identifier or by a combination of service name / module…
(1) Check whether your database is registered with Enterprise Manager or not, using following command: posdbprod21:oracle> emctl status dbconsole OC4J Configuration issue. /oracle/app/oracle/product/10.1/oc4j/j2ee/OC4J_DBConsole_posdbprod21_CIF not found. posdbprod21:oracle> This means database is not registered with OEM. (2) Remember DBCA and other java interfaces use /vat/opt/oracle/oratab file to find the number of oracle instances instaled on that server….
http://www.nocoug.org/download/2004-11/Conquering_Ora_Latch_Conten_1d.pdf
http://www.ixora.com.au/q+a/library.htm
http://www.diku.dk/undervisning/2003f/729/papers/millsap.pdf
set pages 3000 set lines 151 accept param_name prompt ‘Enter Parameter Name : ‘ select a.ksppinm “Parameter”, b.ksppstvl “Session Value”, c.ksppstvl “Instance Value” from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx and ( upper(ksppinm) like ‘%¶m_name%’ or lower(ksppinm) like ‘%¶m_name%’ ) order by a.ksppinm;
/* System privileges */ grant create session to PERFSTAT; grant alter session to PERFSTAT; grant create table to PERFSTAT; grant create procedure to PERFSTAT; grant create sequence to PERFSTAT; grant create public synonym to PERFSTAT; grant drop public synonym to PERFSTAT; /* Select privileges on STATSPACK created views */ grant select on X_$KCBFWAIT to PERFSTAT;…
Read More “In Addition to previous note, following grants needed on PERFSTAT user.” »
When you are creating a procedure and you are using objects from other schemas, then the procedure owner needs related privilages exclusively on those objects of other schemas. This cannot be resolved with ROLE.
To collect CPU stats from Server following code needs to be setup. ( Step By Step ) Step-1 ====== Create a user named PERFSTAT/PERFSTAT. This is not for statspack, we only need 3 tables and package for this settings. Give appropriate default TS for same. Step-2 ====== create tables with following definitons: CREATE TABLE STATS$VMSTAT2…
Read More “Settting up get_vmstat.sh for colletinf CPU Usage.” »
