已登入: Logged in as: 登入
搜尋:
Page last modified 15:28, 13 Mar 2014 by alang

SQL/Plus

    版本為 03:31, 12 Mar 2025

    到這個版本。

    返回到 版本存檔.

    查閱目前版本

    DB Parameters

    Check DB parameter

    Method 1:

    show parameters
show parameters sga
show parameters sga_target

    Method 2:

    col name for A20
col value for A15
col type for A15
col issys_modifiable for A15
SELECT name, value, type, issys_modifiable FROM v$parameter WHERE name like '%sga%';

    TIPs:

    ISSYS_MODIFIABLE
    - FALSE, need to restart DB
    - IMMEDIATE, no need to restart DB

    Change the parameter
    Example: Change SGA to 4G

    alter system set sga_max_size=4096M scope=spfile;
alter system set sga_target=4096M scope=spfile;

    Check active sessions

    -- Top 10 CPU consumers in last 5 minutes
SELECT * FROM
(
SELECT session_id, session_serial#, count(*)
FROM v$active_session_history
WHERE session_state= 'ON CPU' AND
      sample_time > sysdate - interval '5' minute
GROUP BY session_id, session_serial#
ORDER BY count(*) desc
)
WHERE rownum <= 10;


-- Top 10 waiting sessions in last 5 minutes
SELECT * FROM
(
SELECT session_id, session_serial#, count(*)
FROM v$active_session_history
WHERE session_state='WAITING' AND
      sample_time >  sysdate - interval '5' minute
GROUP BY session_id, session_serial#
ORDER BY count(*) DESC
)
WHERE rownum <= 10;

    檢查所有物件(VIEW,PACKAGE,PROCEDURE....)的 status

    reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown. (click for details)
    OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ ------------------
WINSTDB                        PROC_FDS_700982                VIEW
WINSTDB                        V_PROCDEF_FDS_700982           VIEW
WINSTDB                        PROCAVG_FDS_700982             VIEW
WINSTDB                        V_PROCDEFAVG_FDS_700982        VIEW
WINSTDB                        PROC_FDS_700984                VIEW
WINSTDB                        V_PROCDEF_FDS_700984           VIEW
WINSTDB                        PROCAVG_FDS_700984             VIEW
WINSTDB                        V_PROCDEFAVG_FDS_700984        VIEW
WINSTDB                        PROC_FDS_701631                VIEW
WINSTDB                        V_PROCDEF_FDS_701631           VIEW
WINSTDB                        PROCAVG_FDS_701631             VIEW

    Check the usage of the temporary tablespaces

    By tablespace:

    reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown. (click for details)
    TABLESPACE                        MB_TOTAL    MB_USED    MB_FREE
------------------------------- ---------- ---------- ----------
TMP_CDB                                500          3        497
TMP_STDB                              1000          0       1000
TEMP                                    75          0         75

    By session:

    reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown. (click for details)

    As you can see, there are different segment types. Most of time, SORT is the one we need to check. If you are lucky enough, or the client only have 1 SQL cursor, you will get the SQL hash value in the above result.

    reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown. (click for details)
    • sid =
    • PARSING_SCHEMA_NAME =
       HASH_VALUE      SORTS ROWS_PROCESSED/EXECUTIONS
------------- ---------- -------------------------
    887856235      30506                .000196676
   2631006892      30227                .001323276
   3490377209        632                46993.6709 <==
    Powered by MindTouch Core