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
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)Callstack:
at User:Alang/IT_專案工作/Oracle_DBA_筆記/SQL//Plus
MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException: reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown.
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptVar expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Expr.DekiScriptVar.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Evaluate (MindTouch.Deki.Script.Expr.DekiScriptAccess expr, DekiScriptExpressionEvaluationState state, Boolean evaluateProperties) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptAccess expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Expr.DekiScriptAccess.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptCall expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Expr.DekiScriptCall.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptSequence expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Expr.DekiScriptSequence.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptReturnScope expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
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)Callstack:
at User:Alang/IT_專案工作/Oracle_DBA_筆記/SQL//Plus
MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException: reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown.
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptVar expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Expr.DekiScriptVar.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Evaluate (MindTouch.Deki.Script.Expr.DekiScriptAccess expr, DekiScriptExpressionEvaluationState state, Boolean evaluateProperties) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptAccess expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Expr.DekiScriptAccess.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptCall expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Expr.DekiScriptCall.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptSequence expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Expr.DekiScriptSequence.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptReturnScope expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
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)Callstack:
at User:Alang/IT_專案工作/Oracle_DBA_筆記/SQL//Plus
MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException: reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown.
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptVar expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Expr.DekiScriptVar.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Evaluate (MindTouch.Deki.Script.Expr.DekiScriptAccess expr, DekiScriptExpressionEvaluationState state, Boolean evaluateProperties) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptAccess expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Expr.DekiScriptAccess.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptCall expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Expr.DekiScriptCall.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptSequence expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Expr.DekiScriptSequence.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptReturnScope expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
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)Callstack:
at User:Alang/IT_專案工作/Oracle_DBA_筆記/SQL//Plus
MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException: reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown.
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptVar expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Expr.DekiScriptVar.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Evaluate (MindTouch.Deki.Script.Expr.DekiScriptAccess expr, DekiScriptExpressionEvaluationState state, Boolean evaluateProperties) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptAccess expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Expr.DekiScriptAccess.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptCall expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Expr.DekiScriptCall.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptSequence expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Expr.DekiScriptSequence.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptReturnScope expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0
- sid =
- PARSING_SCHEMA_NAME =
HASH_VALUE SORTS ROWS_PROCESSED/EXECUTIONS
------------- ---------- -------------------------
887856235 30506 .000196676
2631006892 30227 .001323276
3490377209 632 46993.6709 <==