Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- 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
1K+ Views
Advertisements