Modern Oracle DBA

Below query collecting data from AWR report. Copy SET LINES 155 COL execs FOR 999,999,999 COL avg_etime FOR 999,999.999 COL avg_lio FOR 999,999,999.9 COL begin_interval_time FOR a30 COL node FOR 99999 BREAK ON plan_hash_value ON startup_time SKIP 1 SELECT ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, NVL(executions_delta, 0) execs, (elapsed_time_delta / DECODE(NVL(executions_delta, 0), 0, 1, executions_delta)) / 1000000 avg_etime, (buffer_gets_delta / DECODE(NVL(buffer_gets_delta, 0), 0, 1, executions_delta)) avg_lio FROM DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS WHERE ss.snap_id = S.snap_id AND ss.instance_number = S.instance_number AND sql_id = ' ' AND executions_delta > 0 ORDER BY 1, 2, 3 /  Below query use to (.sql) file Copy SET ECHO OFF SET VERIFY OFF SET FEEDBACK OFF SET HEADING ON SET TIMING OFF UNDEF sql_id PROMPT "SQL...

Long Running Query Copy SELECT fcr.request_id, fcr.parent_request_id, ftp.user_concurrent_program_name, fcu.user_name, fcu.description User_Detail, fcr.phase_code phase, floor(((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) / 3600) || ' Hrs ' || floor((((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) - floor(((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) / 3600) * 3600) / 60) || ' Mins ' || round((((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) - floor(((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) / 3600) * 3600 - (floor((((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) - floor(((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) / 3600) * 3600) / 60) * 60))) || ' Secs ' "RUNNING_SINCE", to_char(FCR.requested_start_date, ' DAY DD-MON-YY HH:MI AM') REQUESTED_START_DATE, to_char(FCR.actual_start_date, ' DAY DD-MON-YY HH:MI AM') ...

Connect the instance using User process and server process Once Basic validation completed SQL statement ready to execute Syntax Check Semantics check Shared pool check Syntax Check: The database validates the sql query syntax is correct or not. Example: SQL> select from dba_objects; select from dba_objects        * ERROR at line 1: ORA-00936: missing expression SQL> Semantics check Oracle Verify all column and table names using the dictionary, and confirm that you have permission to see the data. Shared pool Check Once syntax and semantics check done the server processer will initiate the sql query. Server process will check the library cache In the Library cache server process will check Most recently user to the least recently used algorithm for match the sql statement. If library cache matches the sql statements that’s called soft parsing If there is no match, the server process must continue with the creation...

Result cache introduce in 11g It’s area of memory either in SGA/ client application memory It’s allows results of query to be cached in SGA Possible to reduce amount of physical I/O, logical I/O, number of sorts, amount of CPU   How Result cache works When a query executes, the database searches the cache memory to determine whether the result exists in the result cache. If the result exists, then the database retrieves the result from memory instead of executing the query. If the result is not cached, then the database executes the query, returns the result as output, and stores the result in the result cache. When users execute queries and functions repeatedly, the database retrieves rows from the cache, decreasing response time. Cached results become invalid when data in dependent database objects is modified.   Result cache Type There are two types of result cache there Server Result cache Used shared memory Available to all session Client Res...

This ORA Error post will update frequently for same page  ORA-00031: session marked for kill. The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptible operation is done. No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner. Solution Find the thread in OS level select vs.sid,vs.username,vs.osuser, vs.process,vp.spid from v$session vs, v$process vp where vs.paddr = vp.addr and vs.username='<User_name>' and vs.osuser='<applmgr>'; Will get output like this SID    USERNAME     OSUSER   PROCESS   SPID 123        AP              applm...

Top Command Analysis Top command is used to show the Linux process This command shows the summary information of the system and the list of process which are currently managed by the kernel Top command we need to check two session summery session and task session Summery session System Time, Up time and user session Load average Tasks CPU usage Memory Usage System Time, Up time and user session top - 11:59:39 up 225 days, 22:31, 136 users, System time: 11:59:39 Up time: up 225 days, 22hrs:31min User session: 136 users connected. Load average Load average is the system load calculated over given period 1,5 and 15 minutes Load average have 3 sessions Load average: 4.43, 4.14, 4.03 Load average over last 1 minutes is 4.43 Load average over last 5 minutes is 4.14 Load average over last 15 minutes is 4.03 The high load average that system is overloaded many processes are waiting for CPU time Tasks Tasks session showing how many processes runni...

What is ASH report? Active session history Sampled active session for into memory buffer every second (V$ACTIVE_SESSION_HISTORY) Collect active session data only Writing to disk by AWR snapshots (DBA_HIST_ACTIVE_SESS_HISTORY) Part of oracle 10g database   How ASH work? If example oracle standard program used to run in 5-second now which taking mort hen 10-min need to check that particular sql query why taking 10-min. For AWR report never show this type of performance issue. Awr report showing only top SQL We can find this kind of issues in ASH report. Using ASH report can perform detailed analysis V$ACTIVE_SESSION_HISTORY view and DBA_HIST_ACTIVE_SESS_HISTORY view   V$ACTIVE_SESSION_HISTORY displays sampled session activity in the database. It contains snapshots of active database sessions taken once a second. For V$Activite_session_history table holed data for only one hour Whatever data in v$activie_session_history will write DBA_HIST_ACTIVE_SES...