Website Design, Website Development, Shopping Carts, Content Management Systems, Custom Programming, Custom Applications Appsdba: Check Database Performance by these queries !!!!

Oracle Applications, Middleware, Database, DB RAC & HA

It is place where anyone grab more knowledge about Oracle Applications, middleware etc.

 
APPS R12
  • File System
  • Tech Stack
  • Admin Scripts
  • Forms Mode
  • APPS 11i
  • Know more
  • Maintenance Mode
  • Maintenance Mode(New)
  • Patching
  • APPS Scripts
  • Database Scripts
  • Protect APPS Password
  • Concurrent Manager
  • Database Refersh
  • Patchset Utility
  • Forms Data Loader
  • Web Server
  • Space Required
  • Responsibilities Of APPSDBA
  • OAS 9i/10gR2
  • Software Load Balancer
  • OAS 10gR3
  • Components
  • HTTP Server as Port 80
  • Database
  • Enable Archive
  • Clear Temp Tablespace
  • Drop DB (New in 10g)
  • Database Performance
  • Oracle Process
  • Advanced Topic
  • Database RAC

  • Do you know
  • Clusterware
  • Manage RAC Environment
  • High Availability
  • Certifications
  • EBS R12 Beta Exam
  • APPS 11i - 1z0-233
  • RAC 10gR2 - 1z0-048
  • Oracle Certifications
  • DBA Tools
  • TOAD
  • KEEP TOOL
  • ORACLE's SQL DEVELOPER
  • Spotlight on Oracle E-Business
  • DBDiff For Oracle

  • Check Database Performance by these queries !!!!
    Monday, January 28, 2008
    SELECT * FROM v$log;


    SELECT COUNT(*) FROM v$process;


    SELECT COUNT(*) FROM v$session WHERE status='ACTIVE'


    SELECT COUNT(*) FROM dba_locks WHERE blocking_others='Blocking';


    SELECT COUNT(*) FROM dba_objects WHERE status='INVALID' AND owner IN ('ABC','DEF') GROUP BY owner;


    SELECT A.Tablespace_Name,TRUNC((SUM(A.Tots)/1024/1024),3) Tot_Size,
    TRUNC(((SUM(A.Tots)/1024/1024)-(SUM(A.Sumb)/1024/1024)),3) Tot_Used
    FROM (
    SELECT Tablespace_Name,0 Tots,SUM(Bytes) Sumb,
    MAX(Bytes) Largest,COUNT(*) Chunks
    FROM Sys.Dba_Free_Space A
    GROUP BY Tablespace_Name
    UNION
    SELECT Tablespace_Name,SUM(Bytes) Tots,0,0,0
    FROM Sys.Dba_Data_Files
    GROUP BY Tablespace_Name) A, V$instance B
    GROUP BY A.Tablespace_Name
    ORDER BY A.Tablespace_Name


    SELECT (1-(SUM(DECODE(name,'physical reads', value, 0))/
    (SUM(DECODE(name,'db block gets', value, 0)) +
    SUM(DECODE(name,'consistent gets', value, 0))))) * 100 "Read Hit Ratio"
    FROM v$sysstat


    SELECT d.status "Status",
    d.tablespace_name "Name",
    d.CONTENTS "Type",
    d.extent_management "Extent Management",
    TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (M)",
    TO_CHAR(NVL(NVL(f.bytes, 0), 0)/1024/1024 ,'99G999G990D900') "Free (MB)",
    TO_CHAR(NVL((NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Free %"
    FROM sys.dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes
    FROM dba_data_files GROUP BY tablespace_name) a,
    (SELECT tablespace_name, SUM(bytes) bytes
    FROM dba_free_space GROUP BY tablespace_name) f
    WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
    AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')
    UNION ALL
    SELECT d.status "Status", d.tablespace_name "Name", d.CONTENTS "Type", d.extent_management "Extent Management",
    TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (M)",
    TO_CHAR(NVL((a.bytes-t.bytes), a.bytes)/1024/1024,'99G999G990D900') "Free (MB)",
    TO_CHAR(NVL((a.bytes-t.bytes) / a.bytes * 100, 100), '990D00') "Free %"
    FROM sys.dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes
    FROM dba_temp_files GROUP BY tablespace_name) a,
    (SELECT tablespace_name, SUM(bytes_cached) bytes
    FROM v$temp_extent_pool GROUP BY tablespace_name) t
    WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
    AND d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY'
    ORDER BY 7


    SELECT COUNT(*) FROM tab
    posted by Jaswinder Singh @ 2:04 AM  
    1 Comments:
    • At Tuesday, December 22, 2009, Anonymous multivitamine said…

      The main goal of the project is to create a distributed generic system collecting and storing various runtime metrics collections used for continuous system performance, health, quality and availability monitoring purposes. Allmon agents are designed to harvest a range of metrics values coming from many areas of monitored infrastructure. Collected data are base for quantitative and qualitative performance and availability analysis. Allmon collaborates with other analytical tools for OLAP analysis and Data Mining processing.

       
    Post a Comment
    << Home
     
    About Me

    Name: Jaswinder Singh
    Home: Bangalore, Karnataka, India
    Certifications: RAC Certified Expert, OCP 9i, 10g & 11i
    About Me: I am working as APPSDBA, have hands on experience on DB RAC, HA on OAS 10g and Oracle Applications.
    See my complete profile
    Previous Post
    Archives
    Links
    Powered by

    Free Blogger Templates

    BLOGGER

    ADVERTISEMENT
    click here
    © 2006 Appsdba .