DBA Queries

Performance Tuning:

SUGGESTED MINIMUM SHARED POOL SIZE

SELECT cr_shared_pool_size,sum_obj_size, sum_sql_size,sum_user_size,(sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool

FROM (SELECT SUM(sharable_mem) sum_obj_size FROM v$db_object_cache where type<> 'CURSOR'),

(SELECT sum(sharable_mem) sum_sql_size FROM v$sqlarea),

(SELECT sum(250*users_opening) sum_user_size FROM v$sqlarea),

(SELECT to_Number(b.ksppstvl) cr_shared_pool_size

FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx and a.indx = c.indx AND a.ksppinm ='__shared_pool_size' );

1)

HOW TO CHECK ORACLE DATABASE FOR LONG RUNNING QUERIES?

QUERIES CURRENTLY RUNNING FOR MORE THAN 60 SECONDS. NOTE THAT IT PRINTS MULTIPLE LINES PER RUNNING QUERY IF THE SQL HAS MULTIPLE LINES.

LOOK AT THE SID,SERIAL# TO SEE WHAT BELONGS TOGETHER.

SELECT S.USERNAME,S.SID,S.SERIAL#,S.LAST_CALL_ET/60 MINS_RUNNING,Q.SQL_TEXT

    FROM V$SESSION S

        JOIN V$SQLTEXT_WITH_NEWLINES Q

            ON S.SQL_ADDRESS = Q.ADDRESS

                 WHERE STATUS='ACTIVE'

                    AND TYPE <>'BACKGROUND'  AND LAST_CALL_ET> 60

                    and username='DBO_ASIC_APR_LATEST'

ORDER BY SID,SERIAL#,Q.PIECE ;

2)

THE FOLLOWING SQL THAT IS CURRENTLY "ACTIVE":-

SELECT S.USERNAME, S.SID, S.OSUSER, T.SQL_ID, SQL_TEXT

    FROM V$SQLTEXT_WITH_NEWLINES T,V$SESSION S

        WHERE T.ADDRESS =S.SQL_ADDRESS

            AND T.HASH_VALUE = S.SQL_HASH_VALUE  AND S.STATUS = 'ACTIVE'  AND S.USERNAME <> 'SYSTEM'   and username='DBO_ASIC_FEB_DEMO'

ORDER BY S.SID,T.PIECE;

SELECT S.USERNAME, S.SID, S.OSUSER,  T.SQL_ID,A.SQL_FULLTEXT,S.LOGON_TIME ,S.STATUS

    FROM V$SQLTEXT_WITH_NEWLINES T,V$SESSION S ,v$sql a

        WHERE T.ADDRESS =S.SQL_ADDRESS

            AND T.HASH_VALUE = S.SQL_HASH_VALUE

            AND A.SQL_ID=T.SQL_ID AND S.STATUS = 'ACTIVE'  AND S.USERNAME <> 'SYSTEM' and username='DBO_ASIC_FEB_DEMO'

ORDER BY S.SID,T.PIECE;

SELECT

  SST.VALUE CPU, S.USERNAME, S.SID, S.SERIAL#, SQL_TEXT

FROM

  V$SESSION S,

  V$SQLTEXT_WITH_NEWLINES T,

  V$SESSTAT SST

WHERE S.SQL_ID IS NOT NULL

AND S.SQL_ID = T.SQL_ID

AND SST.SID = S.SID

AND SST.STATISTIC# = (SELECT STATISTIC# FROM V$STATNAME SN WHERE SN.NAME = 'CPU used by this session')

ORDER BY 1 DESC, S.SID,T.PIECE;

3)

THIS SHOWS LOCKS. SOMETIMES THINGS ARE GOING SLOW, BUT IT'S BECAUSE IT IS BLOCKED WAITING FOR A LOCK

SELECT OBJECT_NAME,OBJECT_TYPE,  SESSION_ID,  TYPE,-- TYPE OR SYSTEM/USER LOCK

        LMODE, -- LOCK MODE IN WHICH SESSION HOLDS LOCK

        REQUEST,BLOCK,CTIME                 -- TIME SINCE CURRENT MODE WAS GRANTED

            FROM  V$LOCKED_OBJECT, ALL_OBJECTS, V$LOCK

                WHERE V$LOCKED_OBJECT.OBJECT_ID = ALL_OBJECTS.OBJECT_ID AND V$LOCK.ID1 = ALL_OBJECTS.OBJECT_ID AND V$LOCK.SID = V$LOCKED_OBJECT.SESSION_ID

ORDER BY SESSION_ID, CTIME DESC, OBJECT_NAME;

4)

THIS IS A GOOD ONE FOR FINDING LONG OPERATIONS (E.G. FULL TABLE SCANS). IF IT IS BECAUSE OF LOTS OF SHORT OPERATIONS, NOTHING WILL SHOW UP.

SELECT sid, to_char(start_time,'hh24:mi:ss') stime,message,( sofar/totalwork)* 100 percent

    FROM v$session_longops

        WHERE sofar/totalwork < 1;

5)

   v$session_longops if you look for sofar != totalwork you'll see ones that haven't completed but the entries aren't removed when the operation completes so you can see a lot of history there too.

YOU CAN DETERMINE WHAT THE LONG OPERATIONS ARE BY RUNNING THE FOLLOWING QUERY:

SELECT SID,MESSAGE || '(' || TIME_REMAINING || ')' "LONG OPS"

    FROM V$SESSION_LONGOPS

        WHERE TIME_REMAINING > 600;

6)

HERE IS A QUERY THAT ONLY SHOWS QUERIES THAT ARE LONG RUNNING (WHICH MEANS TAKE MORE THAN 6 SECONDS), HAVE SOME TIME REMAINING (SO THEY ARE ACTIVE),

AND SHOWS YOU WHO IS RUNNING AND WHAT IS BEING RUN.

SELECT A.SID,(A.SOFAR/A.TOTALWORK)*100 "% DONE",TO_CHAR(A.LAST_UPDATE_TIME, 'HH24:MI:SS'),A.USERNAME,A.TIME_REMAINING "TIME LEFT",A.OPNAME ,b.status,S.SQL_TEXT

    FROM V$SESSION_LONGOPS A, V$SESSION B, V$SQLTEXT S

        WHERE A.SID =B.SID AND B.SQL_ADDRESS = S.ADDRESS AND A.SOFAR <> A.TOTALWORK

ORDER BY B.SID, LAST_UPDATE_TIME

7)

select

'alter system kill session    '||''''||SID||','||SERIAL#||''';'

from v$session where USERNAME='DBO_ASIC_APR_LATEST'  and PROGRAM ='sqlplusw.exe'

----

----alter system kill session    '19,23383';

8)

select a.sid,

to_char(a.last_update_time, 'HH24:MI:SS'),

a.username,a.time_remaining "Time Left", b.status,

s.sql_text

from v$session_longops a, v$session b, v$sqltext s

where a.sid =b.sid and b.sql_address = s.address

and a.sofar <> a.totalwork;

9)

select package_name, object_name, nvl(overload,0) "ovload",

decode(min(position),0,'Function','Procedure') "Type",

count(argument_name) "Parameters"

from all_arguments

where package_name like upper('&pkg_name') group by package_name, object_name, overload;

10)

select * from all_objects where objecT_name LIKE'%RMAN%'

11)

select * from dba_directories

12)

    Tacefile

 

select

  u_dump.value   || '/'     ||

  db_name.value  || '_ora_' ||

  v$process.spid ||

  nvl2(v$process.traceid,  '_' || v$process.traceid, null )

  || '.trc'  "Trace File"

from

             v$parameter u_dump

  cross join v$parameter db_name

  cross join v$process

        join v$session

          on v$process.addr = v$session.paddr

where

 u_dump.name   = 'user_dump_dest' and

 db_name.name  = 'db_name'        and

 v$session.audsid=sys_context('userenv','sessionid');