Oracle Magazine, May/June 2019
Database Developer and DBA ETL data can often be used to detect the problematic SQL statements for that user However both Active Session History and Automatic Workload Repository are additional license options of Oracle Database Enterprise Edition so they may not be available for your performance tuning analysis If thats the case you can use the dynamic performance views to find poorly performing SQL statements in a database Marrying this information with a business users performance issue is more difficult but with a little luck the most resource intensive SQL statements will have some identifying characteristics that allow a basic level of confidence in mapping them to business functions The V SQL performance view lists SQL statements in the shared SQL area and presents numerous performance measurements alongside each as shown in Listing 1 Here are the most commonly used V SQL columns for providing performance measurement on SQL statements DISK_ READS discovers SQL statements performing large amounts of physical I O BUFFER_ GETS is a measure of logical I O which is the most common cause of high CPU costs for SQL statements EXECUTIONS determines whether a SQL statement is being run an excessive number of times ROWS_ PROCESSED can be useful for INSERT UPDATE and DELETE statements to determine ORACLE MAGAZINE MAY JUNE 2019 110 large redo costs ELAPSED_ TIME is the cumulative elapsed time across all executions for a SQL statement which maps most closely to user response time Those columns provide the performance characteristics of each SQL statement They can be combined with other V SQL columns to tie SQL statements back to their root business functions PARSING_ USER_ ID narrows the SQL statement down to the schema s your applica
You must have JavaScript enabled to view digital editions.
