SQL Area Performance Monitor

Tags:

administration2 technical2 create new tag

Introduction

These queries allow monitoring of SQL as it executes. If you have a procedure with several queries and sub queries, it can show which pieces of code are sucking up huge quantities of processor time and affecting performance.

Code

--Step 1 - Run this script to get a list of modules that are executing or have recently executed.
--Examples of interesting modules might be sdbstudio.exe, APXSUIMP (ap supplier import)
--Cut the module of interest to the clipboard
SELECT DISTINCT module
FROM v$sqlarea

--Step 2 - Run this query to see all of the SQL and resources consumed/processed by a specific module
--Run this query and paste the module name from the first step into the variables box
--Also add to the variables box the length of time in minutes that the process has been running
SELECT  sql_text, first_load_time, rows_processed, executions, disk_reads, elapsed_time 
FROM v$sqlarea 
WHERE UPPER(module) = UPPER('&module_name') 
AND TO_DATE(first_load_time,'YYYY-MM-DD/HH24:MI:SS') > (SYSDATE - (&running_for_how_many_minutes/1440))
ORDER BY elapsed_time DESC

Comments

Links

• Related:

This Topic Is Referenced By These Topics:
Related Links:

---

---

Was This Topic Useful to You?

Others rated this topic as shown. Please include your rating.

Choice

Select ...Very UsefulSomewhat UsefulNo OpinionNot Useful

Somewhat Useful (1) Very Useful (1)

Disclaimer

Use at Your Own Risk: We provide the material available through this website for informational purposes only. Before you act on any information you've found on this site, you should confirm any facts that are important to your decision. IF YOU RELY ON ANY INFORMATION OR SERVICE AVAILABLE THROUGH THIS WEBSITE, YOU DO SO AT YOUR OWN RISK. YOU UNDERSTAND THAT YOU ARE SOLELY RESPONSIBLE FOR ANY DAMAGE OR LOSS YOU MAY INCUR THAT RESULTS FROM YOUR USE OF ANY SERVICE OR ANY MATERIAL AND/OR DATA DOWNLOADED FROM OR OTHERWISE PROVIDED THROUGH THIS WEBSITE.

Collaborating Authors and Reviewers: -- JimCrum