- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to identify the SQL consuming more resources in Oracle?
Problem:
You want to identify the SQL statements consuming more resources in Oracle.
Solution
“V$SQLSTATS" view displays performance statistics for SQL statements that have recently executed. You can also use "V$SQL” and “V$SQLAREA" to report on SQL resource usage.
"V$SQLSTATS” is faster and retains information for a longer period of time, but contains only a subset of the columns in “V$SQL" and "V$SQLAREA”.
Example
select * from( select sql_text ,buffer_gets ,disk_reads ,sorts ,cpu_time/1000000 cpu_sec ,executions ,rows_processed from v$sqlstats order by cpu_time DESC) where rownum < 20;
Updated on 05-Dec-2020 06:30:12
- Related Questions & Answers
- How to identify SQL queries with the most waits in Oracle?
- How to limit Database Resources per Session in Oracle?
- How to identify blocked and blocking sessions in Oracle ?
- How to identify recent WAIT events in a Oracle database ?
- How to capture Oracle errors in PL/SQL?
- How to translate SQL data to XML in Oracle?
- How to determine the approximate amount of SQL work left in Oracle?
- How to monitor real time SQL execution statistics in Oracle?
- Difference between Oracle and SQL Server
- Block of PL/SQL in Oracle DBMS
- How to validate the syntax of a Oracle dynamic SQL before execution ?
- How to concatenate more than 2 fields with SQL?
- How to display a SQL execution progress along with execution plan in Oracle?
- Database Wars: MSSQL Server, Oracle PL/SQL and MySQL
- How to identify rows that are not parents of any other rows in a Hierarchy table in Oracle?
Advertisements