Analyzing Basic Performance Issues

Analyzing Basic Performance Issues 

Whenever any performance issue it is important that DBA and Developers should work together when facing a database/application issue.

When DBAs are reported a performance issue, first step is to get as much information as possible that is related to the issue. 

You can ask below questions to users/developer to collect the first level of information..

 1. What operations/program are executed?

 2. Is it Oracle seeded or custom program?

 3. How much time it used to take earlier?

 4. Is the run time increased over time or you are seeing sudden increase in run time?

 5. Was there any recent code change/migration?

 6. Is it always slow or for certain time of the day only?

 7. Is it slow for all parameters or for some specific parameters?

 8. How much data is getting processed?

 9. What is the frequency of job execution? Was there any change in frequency?

 10. Does the problem happens on both their test and production systems?

Asking above kind of questions will help you in deciding what part of system you should target.

-Target the whole system

-Target a single session

-Target a single SQL statement

 

Your goal should be to answer below three questions:

Where is time spent?

You need to identify where the time is getting consumed. Is it the code or network latency or disk I/O bottlenecks or the CPU/Memory crunch

How is time spent?

You need to find out how the time is spent and how much time is spend in each layer – database, application, network, disk etc .

How to reduce the time spent?

Finally, based on above information see where the major chunk of time is spent and how to reduce it.

 --> High level steps for System level performance issues

Use vmstat top/prstat to identify system wide CPU/Memory consumption.

Use iostat to verify if disks are the bottleneck

Use netstat/tnsping etc to verify if network is issue.

Verify if any other resource intensive processes are running on server.

Verify filesystem space.

Check alert logs, application logs, traces etc.

Check database locks

Generate AWR reports to see what is eating up resources.

Check if increasing application/database memory/redo/undo/temp/SGA will help.

 

--> High level steps for Session level performance issues

Find the Top SQLs executing under session.

Apply SQL optimization techniques on top SQLs.

Verify locking at session level

Generating AWR/ASH for that duration may help in providing useful information.

 

--> High level steps for SQL level performance issues

Apply trace, Generate TKPROF file and analyze for top resource consuming SQL.

Avoid full table scans on large tables

Possible indexes on columns contained in the WHERE clause

Use AWR/ASH reports to get collective information

Also use SQLTRPT/SQLT

Verify is statistics are current

Verify if indexes are valid and are adequate.

Verify is parallelism/materialized views/Baselines/SQL Profiles will help

Monitor V$SESSION_LONGOPS to detect long running operations

Decide on using Hints if those are helping.

Table partitioning can be thought of as an option based on kind and size of tables. 

ASH (Active Session History) Analysis

How To Generate ASH (Active Session History) Report

To generate ASH report:

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

The report provides below areas:

1. Top User Events

2. Top Service/Module

3. Top SQL Command Types

4. Top Sessions

5. Top Blocking Sessions

6. Top DB Objects

7. Top Phases of Execution

8. Top PL/SQL Procedures

9. Top SQL With Top Row Sources

11. Complete list of SQL text

12. Activity Over Time

How To Generate Explain Plan In Oracle

How To Generate Explain Plan In Oracle

1.Generating explain plan for a sql query:

We will generate the explain plan for the query "select * from test.qader_t1;"

LOADING THE EXPLAIN PLAN TO PLAN_TABLE

SQL> explain plan for select * from test.qader_t1;

 DISPLAYING THE EXPLAIN PLAN

SQL> select * from table(dbms_xplan.display);

2. Explain plan for a sql_id from cursor

set lines 2000

set pagesize 2000

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

3. Explain plan of a sql_id from AWR:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

Above will display the explain plan for all the plan_hash_value in AWR. If you wish to see the plan for a particular plan_hash_value.

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id',&plan_hash_value));

 

How to Stop and Start Oracle RAC Services on Single Node

How to Stop and Start Oracle RAC Services on Single Node

1) Disable the automatic startup of the Oracle Clusterware software:

crsctl disable has

2) Stop the Oracle clusterware stack:

crsctl stop has

3) Once clusterware Services has been stopped we will restart those Services:

crsctl enable has

4) Enable the automatic startup of the Oracle Clusterware software:

crsctl start has

Check the status:

ps -ef|grep d.bin|wc -l

ps -ef|grep pmon

5) Start the Oracle clusterware stack:

crsctl start crs

6)Check the status of Oracle Clusterware Resources:

crsctl stat res -t

Long Running Sessions in Oracle

Long Running Sessions in Oracle

SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

(or)

set lines 300

col TARGET for a40

col SQL_ID for a20

select SID,TARGET||OPNAME TARGET, TOTALWORK, SOFAR,TIME_REMAINING/60 Mins_Remaining,ELAPSED_SECONDS,SQL_ID from v$session_longops where TIME_REMAINING>0 order by TIME_REMAINING; 

Above output you can further check the sql_id, sql_text and the wait event for which query is waiting

TO find out sql_id for the above sid:

SQL> select sql_id from v$session where sid='&SID';

To find sql text for the above sql_id:

SQL> select sql_fulltext from V$sql where sql_id='1uksqt2vzxbz5';

To find wait event of the query for which it is waiting for:

SQL>select sql_id, state, last_call_et, event, program, osuser from v$session where sql_id='&sql_id';

Flashback Query (AS OF) in Oracle Database

Flashback Query (AS OF) in Oracle Database

Create a test table with no data and check the current SCN and time.

create table flashback_mqm_test (id  NUMBER(10));

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM

----------- -------------------

584777 2020-09-11 23:55:06

Add a row to the test table and commit the change.

INSERT INTO flashback_mqm_test (id) VALUES (1);

COMMIT;

If we check the contents of the table, we can see there is a single row.

SELECT COUNT(*) FROM flashback_mqm_test;

  COUNT(*)

----------

         1

The following two examples use the AS OF clause to query the table using the timestamp and SCN we captured before the row was inserted.

select count(*) from flashback_mqm_test AS OF TIMESTAMP TO_TIMESTAMP('2020-09-11 23:55:06', 'YYYY-MM-DD HH24:MI:SS');

  COUNT(*)

----------

         0

select count(*) from flashback_mqm_test AS OF SCN 584777;

  COUNT(*)

----------

         0

As we can see, the data in the table reflect the point in time specified in the AS OF clause.

There are a few things to consider when using flashback query.

-Flashback query is based on UNDO. As a result, the amount of time you can flashback is dependent on how long undo information is retained, as specified by the UNDO_RETENTION parameter.

-The flashback operation is performed by winding back the data using the undo information, like a consistent get. The time it takes to wind back the data depends on the number of changes that have happened, so flashback queries of volatile data that go back a long time can be slow.

-Flashback Data Archive (FDA) was introduced in Oracle 11g to allow you to protect UNDO information, allowing guaranteed flashback queries.

-Each table in a query can reference a different point in time.

-The AS OF clause can be included in DML and DDL statements.