Monday, August 21, 2023

Day 2 day queries

Day 2 day queries



To Find DB/Instance Name in RAC 
=========================

sql> show parameter cluster;


col host_name for a20
select inst_id,instance_name,host_name,instance_role from gv$instance;


select instance_name from v$instance;


To check from the operating system
 
# lsnodes



To get the NLS PARAMETER details 
================================

set linesize 150
set pagesize 1000
col name for a50
col parameter for a30
col value for a30
col member for a50
col DATAFILE_NAME for a70
col TEMPFILE_NAME for a70
col CONTROLFILE_NAME for a70
col LOGFILE_MEMBER for a70
select * from NLS_DATABASE_PARAMETERS;


To check the Instance Uptime 
================================

select instance_name||', up since '|| to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') start_time from gv$instance;


To Identify Space occupied and original space utilized of a database 
=====================================================================

set numf 999999.99
select sum(bytes)/(1024*1024*1024) "Total Allocated GB",Total_Consumed_GB from dba_data_files,
(select sum(bytes)/(1024*1024*1024) Total_Consumed_GB from dba_segments) group by Total_Consumed_GB;


To get the DATABASE SCHEMA SIZE from the database 
===================================================

set linesize 150
set pagesize 5000
col owner for a15
col segment_name for a30
col segment_type for a20
col TABLESPACE_NAME for a30
clear breaks
clear computes
compute sum of SIZE_IN_GB on report
break on report
select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments group by owner order by owner;



To get the LOGFILES INFORMATION from the database 
==================================================

col member for a65
select lf.MEMBER,l.GROUP#,THREAD#,SEQUENCE#,MEMBERS,bytes/1024/1024 "BYTES IN MB",ARCHIVED,l.status from v$log l,v$logfile lf where l.GROUP#=lf.GROUP#


To get the CONTROLFILES INFORMATION from the database 
=======================================================

col name for a90
select * from v$controlfile


To get the TEMPFILES SIZE from the database 
=============================================
select tablespace_name,file_name,bytes/1024/1024 "SIZE_IN_MB" from dba_temp_files order by file_name asc;



To get the TABLESPACE SIZE from the database 
==============================================
select tablespace_name,sum(bytes)/1024/1024 "SIZE_IN_MB" from dba_data_files group by tablespace_name;


To get the datafile sizes from the database
================================================

col file_name for a70
col tablespace_name for a30
clear breaks
clear computes
compute sum of SIZE_IN_MB on report
break on report
select tablespace_name,file_name,AUTOEXTENSIBLE,INCREMENT_BY,MAXBYTES/1024/1024 "MAX in MB",bytes/1024/1024 "SIZE_IN_MB" from dba_data_files order by tablespace_name;


To get information about the database 
======================================

SET LINESIZE 150
SET PAGESIZE 50000
cOL HOST_NAME FOR A25
col LOGINS FOR A20
col STATUS for A15
col "STARTUP_TIME" FOR A30
col INSTANCE_NAME for a20
col VERSION for a20
select INSTANCE_NAME,HOST_NAME,VERSION,LOGINS,STATUS,to_char(STARTUP_TIME,'DD-MON-YYYY DAY HH24:MI:SS')
"STARTUP_TIME" FROM v$instance;

col name for a30
col "CREATED" for a25
col LOG_MODE for a15
col OPEN_MODE for a15
col DATABASE_ROLE for a15
select NAME,to_char(CREATED,'DD-MON-YYYY HH24:MI:SS') "CREATED",LOG_MODE,OPEN_MODE,DATABASE_ROLE from v$database;


############33Procedure for generating the DDL statements of the database objects. 
=========================================================================

There are two parameters used in the specified object,
  
1. Object Type        ==> eg: FUNCTION,PROCEDURE,PACKAGE,TABLE,VIEW etc.,
2. Directory Name  ==> Specify the directory name where the output to be stored.

Usage -  DS_GET_DDL_STATEMENT(<Object type>,<Directory Name>)

Objects to be created:
----------------------
 CREATE TABLE "SMF_APEXRPS"."DS_ERROR_TABLE" 
   (    "COMNAME" VARCHAR2(200 BYTE), 
    "COMDDDL" LONG
   );


Create or Replace
PROCEDURE DS_GET_DDL_STATEMENT(P_OBJECTTYPE VARCHAR2,PDIR VARCHAR2)
AS
V_DDL LONG;
V_ERRMSG VARCHAR2(500);
vInHandle  utl_file.file_type;
CURSOR C1 IS 
SELECT * FROM USER_OBJECTS WHERE object_type=P_OBJECTTYPE;
BEGIN
FOR I IN C1 LOOP
select 
dbms_metadata.get_ddl(P_OBJECTTYPE,I.OBJECT_NAME)  INTO V_DDL
from dual;
  vInHandle := utl_file.fopen(PDIR, I.OBJECT_NAME||'.txt', 'W');
  IF utl_file.is_open(vInHandle) THEN
   utl_file.put_line(vInHandle, V_DDL, FALSE);
    utl_file.fflush(vInHandle);
    utl_file.fclose_all;
 END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN
V_ERRMSG:=SQLERRM;
INSERT INTO DS_ERROR_TABLE VALUES('ERROR',V_ERRMSG);
END;



-- +==================================================================+
-- PURPOSE: Provides a report on the top segments (in bytes) grouped by Segment Type
-- +==================================================================+

SET LINESIZE 155
SET PAGESIZE 9999
SET VERIFY   OFF

BREAK ON segment_type SKIP 1
COMPUTE SUM OF bytes ON segment_type

COLUMN segment_type        FORMAT A20                HEADING 'Segment Type'
COLUMN owner               FORMAT A15                HEADING 'Owner'
COLUMN segment_name        FORMAT A30                HEADING 'Segment Name'
COLUMN partition_name      FORMAT A30                HEADING 'Partition Name'
COLUMN tablespace_name     FORMAT A20                HEADING 'Tablespace Name'
COLUMN bytes               FORMAT 9,999,999,999,999  HEADING 'Size (in bytes)'
COLUMN extents             FORMAT 999,999,999        HEADING 'Extents'

SELECT
    a.segment_type      segment_type
  , a.owner             owner
  , a.segment_name      segment_name
  , a.partition_name    partition_name
  , a.tablespace_name   tablespace_name
  , a.bytes             bytes
  , a.extents           extents
FROM
    (select
         b.segment_type
       , b.owner
       , b.segment_name
       , b.partition_name
       , b.tablespace_name
       , b.bytes
       , b.extents
     from
         dba_segments b
     order by
         b.bytes desc
    ) a
WHERE
    rownum < 101
ORDER BY
    segment_type, bytes desc, owner, segment_name
/

To check for index fragmentation 
 -- +------------------------------------------------------------------------------+
-- | PURPOSE  : To check for index fragmentation. As a rule of thumb if 10-15%  |
-- |            of the table data changes, then you should consider rebuilding the index  |
-- +-------------------------------------------------------------------------------+

ANALYZE INDEX &&index_name VALIDATE STRUCTURE; 

COL name         HEADING 'Index Name'          FORMAT a30 
COL del_lf_rows  HEADING 'Deleted|Leaf Rows'   FORMAT 99999999 
COL lf_rows_used HEADING 'Used|Leaf Rows'      FORMAT 99999999 
COL ibadness     HEADING '% Deleted|Leaf Rows' FORMAT 999.99999 

SELECT
    name
  , del_lf_rows
  , lf_rows - del_lf_rows lf_rows_used
  , TO_CHAR( del_lf_rows /(DECODE(lf_rows,0,0.01,lf_rows))*100,'999.99999') ibadness 
FROM   index_stats
/

prompt 
prompt Consider rebuilding any index if % of Deleted Leaf Rows is > 20%
prompt 

undefine index_name 


Report free space fragmentation 
-- +----------------------------------------------------------------------------+
-- | PURPOSE  : Report free space fragmentation.                                             |
-- |            THIS SCRIPT MUST BE RUN AS THE SYS USER!!!                  |
-- +----------------------------------------------------------------------------+

connect / as sysdba

CREATE OR REPLACE VIEW free_space (
    tablespace
  , pieces
  , free_bytes
  , free_blocks
  , largest_bytes
  , largest_blks
  , fsfi
  , data_file
  , file_id
  , total_blocks
)
AS
SELECT
    a.tablespace_name
  , COUNT(*)
  , SUM(a.bytes)
  , SUM(a.blocks)
  , MAX(a.bytes)
  , MAX(a.blocks)
  , SQRT(MAX(a.blocks)/SUM(a.blocks))*(100/SQRT(SQRT(count(a.blocks))))
  , UPPER(b.file_name)
  , MAX(a.file_id)
  , MAX(b.blocks)
FROM
    sys.dba_free_space  a
  , sys.dba_data_files  b
WHERE
    a.file_id = b.file_id
GROUP BY
    a.tablespace_name,  b.file_name
/

CLEAR COLUMNS
SET LINESIZE  120
SET PAGESIZE  9999
SET FEEDBACK  off
SET VERIFY    off

BREAK ON tablespace SKIP 2 ON REPORT

COMPUTE SUM OF  total_blocks  ON tablespace
COMPUTE SUM OF  free_blocks   ON tablespace
COMPUTE SUM OF  free_blocks   ON report
COMPUTE SUM OF  total_blocks  ON report

COLUMN tablespace     HEADING "Tablespace"    FORMAT a15
COLUMN file_id        HEADING File#           FORMAT 99999
COLUMN pieces         HEADING Frag            FORMAT 9999
COLUMN free_bytes     HEADING 'Free Byte'
COLUMN free_blocks    HEADING 'Free Blk'      FORMAT 999,999,999
COLUMN largest_bytes  HEADING 'Biggest Bytes'
COLUMN largest_blks   HEADING 'Biggest Blks'  FORMAT 999,999,999
COLUMN data_file      HEADING 'File Name'     FORMAT a45
COLUMN total_blocks   HEADING 'Total Blocks'  FORMAT 999,999,999


SELECT
    tablespace
  , data_file
  , pieces
  , free_blocks
  , largest_blks
  , file_id
  , total_blocks
FROM
    free_space
/


DROP VIEW free_space
/


List all currently connected user sessions ordered by current PGA size 
-- +-------------------------------------------------------------------------------------+
-- | PURPOSE  : List all currently connected user sessions ordered by current PGA size  |
-- +-------------------------------------------------------------------------------------+

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid                     FORMAT 99999          HEADING 'SID'
COLUMN serial_id               FORMAT 999999         HEADING 'Serial#'
COLUMN session_status          FORMAT a9             HEADING 'Status'          JUSTIFY right
COLUMN oracle_username         FORMAT a12            HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username             FORMAT a9             HEADING 'O/S User'        JUSTIFY right
COLUMN os_pid                  FORMAT 9999999        HEADING 'O/S PID'         JUSTIFY right
COLUMN session_program         FORMAT a18            HEADING 'Session Program' TRUNC
COLUMN session_machine         FORMAT a8             HEADING 'Machine'         JUSTIFY right TRUNC
COLUMN session_pga_memory      FORMAT 9,999,999,999  HEADING 'PGA Memory'
COLUMN session_pga_memory_max  FORMAT 9,999,999,999  HEADING 'PGA Memory Max'
COLUMN session_uga_memory      FORMAT 9,999,999,999  HEADING 'UGA Memory'
COLUMN session_uga_memory_max  FORMAT 9,999,999,999  HEADING 'UGA Memory MAX'

prompt 
prompt +----------------------------------------------------+
prompt | User Sessions Ordered by Current PGA Size          |
prompt +----------------------------------------------------+

SELECT
    s.sid                sid
  , s.serial#            serial_id
  , lpad(s.status,9)     session_status
  , lpad(s.username,12)  oracle_username
  , lpad(s.osuser,9)     os_username
  , lpad(p.spid,7)       os_pid
  , s.program            session_program
  , lpad(s.machine,8)    session_machine
  , sstat1.value         session_pga_memory
  , sstat2.value         session_pga_memory_max
  , sstat3.value         session_uga_memory
  , sstat4.value         session_uga_memory_max
FROM 
    v$process  p
  , v$session  s
  , v$sesstat  sstat1
  , v$sesstat  sstat2
  , v$sesstat  sstat3
  , v$sesstat  sstat4
  , v$statname statname1
  , v$statname statname2
  , v$statname statname3
  , v$statname statname4
WHERE
      p.addr (+)            = s.paddr
  AND s.sid                 = sstat1.sid
  AND s.sid                 = sstat2.sid
  AND s.sid                 = sstat3.sid
  AND s.sid                 = sstat4.sid
  AND statname1.statistic#  = sstat1.statistic#
  AND statname2.statistic#  = sstat2.statistic#
  AND statname3.statistic#  = sstat3.statistic#
  AND statname4.statistic#  = sstat4.statistic#
  AND statname1.name        = 'session pga memory'
  AND statname2.name        = 'session pga memory max'
  AND statname3.name        = 'session uga memory'
  AND statname4.name        = 'session uga memory max'
ORDER BY session_pga_memory DESC
/


List all currently connected user sessions ordered by Logical I/O 
-- +--------------------------------------------------------------------------------+
-- | PURPOSE  : List all currently connected user sessions ordered by Logical - I/O |
-- +--------------------------------------------------------------------------------+

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid               FORMAT 99999            HEADING 'SID'
COLUMN serial_id         FORMAT 999999           HEADING 'Serial#'
COLUMN session_status    FORMAT a9               HEADING 'Status'          JUSTIFY right
COLUMN oracle_username   FORMAT a12              HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username       FORMAT a9               HEADING 'O/S User'        JUSTIFY right
COLUMN os_pid            FORMAT 9999999          HEADING 'O/S PID'         JUSTIFY right
COLUMN session_program   FORMAT a18              HEADING 'Session Program' TRUNC
COLUMN session_machine   FORMAT a8               HEADING 'Machine'         JUSTIFY right TRUNC
COLUMN logical_io        FORMAT 999,999,999,999  HEADING 'Logical I/O'
COLUMN physical_reads    FORMAT 999,999,999,999  HEADING 'Physical Reads'
COLUMN physical_writes   FORMAT 999,999,999,999  HEADING 'Physical Writes'

prompt 
prompt +----------------------------------------------------+
prompt | User Sessions Ordered by Logical I/O               |
prompt +----------------------------------------------------+

SELECT
    s.sid                sid
  , s.serial#            serial_id
  , lpad(s.status,9)     session_status
  , lpad(s.username,12)  oracle_username
  , lpad(s.osuser,9)     os_username
  , lpad(p.spid,7)       os_pid
  , s.program            session_program
  , lpad(s.machine,8)    session_machine
  , sstat1.value
    + sstat2.value         logical_io
  , sstat3.value         physical_reads
  , sstat4.value         physical_writes
FROM 
    v$process  p
  , v$session  s
  , v$sesstat  sstat1
  , v$sesstat  sstat2
  , v$sesstat  sstat3
  , v$sesstat  sstat4
  , v$statname statname1
  , v$statname statname2
  , v$statname statname3
  , v$statname statname4
WHERE
      p.addr (+)            = s.paddr
  AND s.sid                 = sstat1.sid
  AND s.sid                 = sstat2.sid
  AND s.sid                 = sstat3.sid
  AND s.sid                 = sstat4.sid
  AND statname1.statistic#  = sstat1.statistic#
  AND statname2.statistic#  = sstat2.statistic#
  AND statname3.statistic#  = sstat3.statistic#
  AND statname4.statistic#  = sstat4.statistic#
  AND statname1.name        = 'db block gets'
  AND statname2.name        = 'consistent gets'
  AND statname3.name        = 'physical reads'
  AND statname4.name        = 'physical writes'
ORDER BY logical_io DESC
/


List all currently connected user sessions ordered by CPU time. 
-- +----------------------------------------------------------------------------+
-- | DATABASE : Oracle |
-- | PURPOSE : List all currently connected user sessions ordered by CPU time. |
-- +----------------------------------------------------------------------------+

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid FORMAT 99999 HEADING 'SID'
COLUMN serial_id FORMAT 999999 HEADING 'Serial#'
COLUMN session_status FORMAT a9 HEADING 'Status' JUSTIFY right
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN os_pid FORMAT 9999999 HEADING 'O/S PID' JUSTIFY right
COLUMN session_program FORMAT a20 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a14 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN cpu_value FORMAT 999,999,999,999 HEADING 'CPU'

prompt
prompt +----------------------------------------------------+
prompt | User Sessions Ordered by CPU |
prompt +----------------------------------------------------+

SELECT
s.sid sid
, s.serial# serial_id
, lpad(s.status,9) session_status
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, lpad(p.spid,7) os_pid
, s.program session_program
, lpad(s.machine,14) session_machine
, sstat.value cpu_value
FROM
v$process p
, v$session s
, v$sesstat sstat
, v$statname statname
WHERE
p.addr (+) = s.paddr
AND s.sid = sstat.sid
AND statname.statistic# = sstat.statistic#
AND statname.name = 'CPU used by this session'
ORDER BY cpu_value DESC
/


No comments:

Post a Comment