Finding Database Bottlenecks

Oracle:
1. The view V$SYSTEM_WAIT_CLASS simply shows the wait times and counts across the new wait classes. The following query can be used to analyze the waits in Oracle server select WAIT_CLASS, TOTAL_WAITS, round(100 * (TOTAL_WAITS / SUM_WAITS),2) "WAITS %", TIME_WAITED, round(100 * (TIME_WAITED / SUM_TIME),2) "TIME %" from (select WAIT_CLASS, TOTAL_WAITS, TIME_WAITED from V$SYSTEM_WAIT_CLASS where WAIT_CLASS != 'Idle'), (select sum(TOTAL_WAITS) SUM_WAITS, sum(TIME_WAITED) SUM_TIME from V$SYSTEM_WAIT_CLASS where WAIT_CLASS != 'Idle') order by 5 desc; A sample output could look like this: WAIT_CLASS TOTAL_WAITS WAITS % TIME_WAITED TIME %
--------------- ----------- ------- ----------- -------------------------------------------------System I/O Commit Concurrency User I/O Configuration Other Network Application 80567 58050 50017 26845 7004 10628 1204195 1269 5.6 4.04 3.48 1.87 .49 .74 83.71 .09 56291 50071 36772 17695 10742 4217 2956 1418 31.24 27.79 20.41 9.82 5.96 2.34 1.64 .79
Finding Bottlenecks in PL/SQL procedures:

A profiler can be used to find PL/SQL bottlenecks. It will show us which line and command is called how many times as well as the time it took to run this command. Install the profiler. All the installation scripts are located in $ORACLE_HOME/rdbms/admin. In sqlplus, we can run this script by prepending an @ in front of it. After installation, first run the following script as SYS: @$ORACLE_HOME/rdbms/admin/profload.SQL Next, run the following script with each user you want to use the profiler: @$ORACLE_HOME/rdbms/admin/proftab.SQL Within a PL/SQL block, we run our procedure but wrap it using dbms_profiler. The first parameter is a comment we can use to find our profile later: BEGIN DBMS_PROFILER.start_profiler(<procedure_name>_profile'); <procedure_name>; DBMS_PROFILER.stop_profiler; END; This will make sure that all the details about our procedure will be saved in a few tables. From these tables, we can pull a lot of information. The following query shows how much time every line of our procedure needed, please note that we are using the same profile name to select our data. We can sort this by the total_time to find the biggest bottle neck or by total_occur to find a part in the code which is called (too) many times. SELECT as_.line, ppd.total_occur, ppd.total_time/1000000 total_time, as_.text FROM plsql_profiler_data ppd INNER JOIN plsql_profiler_units ppu ON ppu.runid=ppd.runid AND ppu.unit_number=ppd.unit_number INNER JOIN plsql_profiler_runs ppr ON ppr.runid=ppd.runid INNER JOIN all_source as_ ON as_.TYPE=ppu.unit_type AND as_.owner=ppu.unit_owner AND as_.name=ppu.unit_name AND as_.line=ppd.line# WHERE ppr.run_comment=<procedure_name>_profile' ORDER BY as_.line
SQL Server:
1. Use this below Query for Top 5 CPU Consuming statements SELECT TOP 5 qs.total_worker_time/qs.execution_count as [Avg CPU Time], SUBSTRING(qt.text,qs.statement_start_offset/2, (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else qs.statement_end_offset end -qs.statement_start_offset)/2) as query_text, qt.dbid, dbname=db_name(qt.dbid), qt.objectid FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY [Avg CPU Time] DESC 2. Currently executing user queries select * from sys.dm_exec_requests where session_id>50 This will show all the currently executing user queries. Look at the wait_type, last_wait_type, and wait_resource to see what the queries are waiting on. 3. Queued tasks select * from sys.dm_os_waiting_tasks where session_id>50 This will show all the queued tasks. This query also returns a wait_type column. If the CPU usage is low it gives the impression that SQL is not doing anything. Most of the time SQL will be waiting on another non-cpu related resource to be freed up. An inner look can be done with the queries above.
Tuning slow PL/SQL Loads

Step 1: Identify the Database Session using the following query SELECT sid, serial#, status, username, osuser, module, action FROM v$session
SID SERIAL# STATUS USERNAME OSUSER MODULE ACTION ---- ------- -------- -------- -------- -------- -------1 2 1 ACTIVE 1 ACTIVE oracle oracle
8 4921 INACTIVE RTHOMAS rthomas de 9 2492 INACTIVE EJOHNSON ejohnson de 45 3415 ACTIVE EDI_LOAD edi SQL*Plus validate
Step 2: Use the SID and view the Statement Being Executed. SELECT B.sql_text 2 FROM v$session A, v$sqlarea B 3 WHERE A.sid = 45 4 AND B.address = A.sql_address; The result of this query will help us in finding the bottleneck.

Finding Database Bottlenecks

Uploaded by

Copyright:

Available Formats

Finding Database Bottlenecks

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Finding Database Bottlenecks

Uploaded by

Copyright:

Available Formats

Oracle:

Finding Bottlenecks in PL/SQL procedures:

Tuning slow PL/SQL Loads

You might also like