Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Read CSV file using PySpark Python/PySpark
  • Flowers Resize datafiles Oracle
  • find_longsql.sql Oracle
  • find_cons.sql Oracle
  • Convert multiple rows to single column Oracle
  • Formatter Explain plan Output 1 Oracle
  • PLSQL Table Syntax 2 Oracle
  • Find Multiple levels of object dependencies : depen.sql Oracle
  • find_string_in_database.sql Oracle
  • Backup and Recovery Scenarios Oracle
  • How to hide author name in WordPress BLOG PHP/MYSQL/Wordpress
  • Sort with ASCII order and Numeric Order Linux/Unix
  • How do I get Oracle to automatically start when my server boots up? Oracle
  • create a folder in multiple places Linux/Unix
  • Roles and Stored Procs II 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

  • shr1.sql for MTS or Shared server configuration Oracle
  • To see how much time or progress of long transaction Oracle
  • In Addition to previous note, following grants needed on PERFSTAT user. Oracle
  • Find_table_size.sql Oracle
  • moving lob object to other tablespace lob_mvmt.sql Oracle
  • sqlnet.ora paramters Oracle

Leave a Reply Cancel reply

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

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (388)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (337)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • find_idle_cpu.sql Oracle
  • cold backup scripts to copy locally Linux/Unix
  • Some useful Unix Commands Linux/Unix
  • Rename Oracle Instance Name Oracle
  • Restoring a user’s original password 1051962.101 Oracle
  • Jai Shree Ram Oracle
  • OPENING A STANDBY DATABASE IN READ-ONLY MODE Oracle
  • good linux notes Linux/Unix

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme