Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • remove archfiles only when it is applied to DR rm_archfiles.sh Linux/Unix
  • Find sort details from Db find_sort.sql Oracle
  • How To Limit The Access To The Database So That Only One User Per Schema Are Connected (One Concurrent User Per Schema) Oracle
  • track_autoupgrade_copy_progress.sql Oracle
  • plan10g.sql good Oracle
  • Mutating Table Error while using database trigger Oracle
  • All About Trace Fils Oracle
  • move_arch_files.ksh Linux/Unix
  • MYSQL and Oracle Comparison for Oracle DBA MYSQL
  • SQLPLUS COPY command Precautions. Oracle
  • SQL Server Vs Oracle Architecture difference SQL Server
  • Rollback force for distributed transactions Oracle
  • 276434.1 Modifying the VIP or VIP Hostname of a 10g or 11g Oracle Clusterware Node Oracle
  • How to see which patches are applied. Oracle
  • alter database backup controlfile to trace Oracle

My Minimum Tuning Programs

Posted on 20-Jun-2006 By Admin No Comments on My Minimum Tuning Programs

REM ***** gsp.sql *****

REM ***** This is used to get SPID from SID.

col username format a30

col machine format a20

col program format a40

accept _sid prompt ‘Enter Oracle Session ID ->’

select a.sid, b.pid, b.spid, a.username,a.program,a.machine

from v$session a,V$process b

where a.paddr = b.addr

and a.sid = &_sid

/

REM ***** gsq.sql

REM ***** The details of a session for a given system process id (sid)

DEFINE sid=’&1′

column machine format a16

column osuser format a16

column sid format 9999

column sql_address format a10

column sql_hash_value format 9999999999

column serial# format 999999

column sql_text format a64

select a.sid,

a.serial#,

a.machine,

a.osuser,

a.sql_address,

a.sql_hash_value,

a.username

from v$session a

where sid =’&sid’

/

select sql_text from v$sqltext c,v$session a

where a.sid =’&sid’

and hash_value = a.sql_hash_value

and address = a.sql_address

order by piece

/

REM ***** gsid.sql

REM ***** Get SID from SPID

col username format a30

col machine format a20

col program format a40

accept spid prompt ‘Enter Unix Process ID ->’

select a.sid, b.pid, b.spid, a.username,a.program,a.machine

from v$session a,V$process b

where a.paddr = b.addr

and b.spid = &spid

/

REM gcu.sql

REM Get Curent USN

column start_dt format a20

set lines 132

select /*+ ALL_ROWS */ a.usn, a1.name, a.extents, a.hwmsize, a.status usn_status,

to_char(b.start_date, ‘DD-MON-RRRR:HH24:MI:SS’) start_dt, b.status tx_status,

c.sid, c.sql_id, b.used_ublk, b.used_urec,

d.sql_text

from v$rollstat a, v$rollname a1, v$transaction b, v$session c, v$sqlarea d

where a.usn = b. xidusn

and a.usn = a1.usn

and b.ses_addr = c.saddr

and c.sql_id = d.sql_id;

REM *****wait.sql

REM *****Returns the information about current wait events.

set pages 2000

set lines 132

column p1text format a18

column event format a35

column WT format 9999

column SW format 9999

select sid, event,p1text,p1raw,p1,p2,p3,seconds_in_Wait “SW”,Wait_time “WT”

from v$session_wait

where event not in(

‘KXFQ: Dequeue Range Keys – Slave’,

‘KXFQ: Dequeuing samples’,

‘KXFQ: kxfqcls – consumer closing TQ’,

‘KXFQ: kxfqdeq – dequeue from specific qref’,

‘KXFQ: kxfqdeq – normal deqeue’,

‘KXFX: Execution Message Dequeue – Slave’,

‘KXFX: Message Fragment Dequeue – Slave’,

‘KXFX: Parse Reply Dequeue – Query Coord’,

‘Null event’,

‘PL/SQL lock timer’,

‘Parallel Query Idle Wait – Slaves’,

‘Replication Dequeue’,

‘SQL*Net message from client’,

‘buffer deadlock’,

‘dispatcher timer’,

‘io done’,

‘pipe get’,

‘pmon timer’,

‘rdbms ipc message’,

‘rdbms ipc message block’,

‘rdbms ipc reply’,

‘slave wait’,

‘smon timer’,

‘virtual circuit status’)

/

REM ***** eplan10g.sql

REM ***** explain plan statement.

–truncate table plan_table;

set lines 132

set echo on

delete from plan_table where statement_id=’pvm’;

commmit;

EXPLAIN PLAN SET STATEMENT_ID = ‘pvm’ FOR

select * from browse_dataset where id=:”SYS_B_0″;

— syntax for viewing information from plan_table

SELECT LPAD(‘ ‘,2*(LEVEL-1))||operation||’ ‘||options ||’ ‘||object_name ||’ ‘|| DECODE(id, 0, ‘Cost = ‘||position)

“Query Plan”

FROM plan_table

START WITH id = 0 and statement_id = ‘pvm’

CONNECT BY PRIOR id = parent_id AND statement_id =’pvm’;

select * from table(dbms_xplan.display());

Oracle, SQL scripts

Post navigation

Previous Post: How to find who is using which Rollback segment and how many rows or blocks in that rollback segments,
Next Post: sess_server.sql

Related Posts

  • switchover for primary database Oracle
  • To check whether standby is recovering properly or not?? Oracle
  • before_trunc.sql Before Truncate table needs to execute following: Oracle
  • Creating never expiring DB user accounts in Oracle Oracle
  • V$transaction notes for finding XID composition. Oracle
  • How do I get Oracle to automatically start when my server boots up? Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AMC10 (0)
  • Ansible (0)
  • AWS (2)
  • Azure (1)
  • Django (0)
  • GIT (1)
  • Linux/Unix (150)
  • MYSQL (5)
  • Oracle (403)
  • PHP/MYSQL/Wordpress (10)
  • POSTGRESQL (1)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (18)
  • rman-dataguard (26)
  • shell (151)
  • SQL scripts (349)
  • SQL Server (6)
  • Uncategorized (9)
  • Videos (0)

Recent Posts

  • Tutornet.US — Source of excellent learning14-Oct-2026
  • OCI Infrastructure Patching14-Oct-2026
  • Free API credits for AI models14-Oct-2026
  • AMC1020-Sep-2026
  • Key Management in Oracle: The Core Issue: Missing Master Key12-May-2026
  • SAT Mathematics 10 questions and answer at the end.30-Apr-2026
  • top 10 AI news today30-Apr-2026
  • runon_allpdbs_show_conname.sh23-Apr-2026
  • runon_allcdbs_find_pdbs.sh23-Apr-2026
  • Running PDB on single node in RAC09-Apr-2026

Archives

  • 2026
  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • PHP code to add WordPress posts in bulk programmatically PHP/MYSQL/Wordpress
  • Drop database in Oracle 10g Oracle
  • longtx.sql with the flag whether session is blocking any DML locks or not. Oracle
  • cold backup scripts to copy locally Linux/Unix
  • findinfo.sql (SQL for getting CPU and Active session info) Oracle
  • mutex in Oracle 10.2.0.2 or Oracle 10g Oracle
  • Oracle Release Explaination Oracle
  • JSON/XML Types in Oracle Oracle

Copyright © 2026 pvmehta.com.

Powered by PressBook News WordPress theme