One of the best ways to isolate a poorly performing query is to have a user or developer complain about a specific SQL statement. In this situation, there is no detective work involved. You can directly pinpoint the SQL query that is in need of tuning.
However, you do not often have the luxury of a human letting you know specifically where to look when investigating performance issues.
Even Oracle 23c database utilizes machine learning for gathering the statistics to help with performance and for anticipating when query plans change and detect regressions in SQL statements.
The real-time statistics use a regression model that predicts the current statistics. This keeps the impact of gathering statistics low and the refreshing of stats fast to avoid bad optimizer query plans.
Before leveraging SQL plan management in the Oracle Database, the optimizer needs information about the data and statistics of the tables to help with tuning.
There are also performance reporting we can use to determine which SQL is consuming the most resources in a database. Here is a list of what we use to feed into the SQL plans:
• Real-time execution statistics with machine learning
• Near real-time statistics
• Oracle performance reports
Now let’s query V$SQL_MONITOR to monitor the near real-time resource consumption of SQL queries:
SQL> select * from (select a.sid session_id, a.sql_id ,a.status,a.cpu_time/1000000 cpu_sec ,a.buffer_gets, a.disk_reads ,b.sql_text sql_textfrom v$sql_monitor a ,v$sql b where a.sql_id = b.sql_id order by a.cpu_time desc) where rownum <=20;
In the SQL statement, we retrieve all records with an inline view to organize the statements by CPU_TIME, in descending order.
This query can be modified to order the results by the statistics of your choice or to display only the queries that are currently executing, for example, change the order by clause to disk_reads.
The statistics in V$SQL_MONITOR are updated every second, so you can view resource consumption as it changes.
These statistics are gathered by default if an SQL statement runs in parallel or consumes more than five seconds of CPU or I/O time.
You can also query views such as V$SQLSTATS to determine which SQL statements are consuming an inordinate amount of resources.
For example, use the following query to identify the 10 most resource-intensive queries, based on CPU time:
SQL> select * from( select s.sid, s.username, s.sql_id ,sa.elapsed_time/1000000, sa.cpu_time/1000000 ,sa.buffer_gets, sa.sql_text from v$sqlarea sa ,v$session s where s.sql_hash_value = sa.hash_value and s.sql_address = sa.address and s.username is not null order by sa.cpu_time desc) where rownum <= 10;
Note Keep in mind that V$SQLAREA contains statistics that are cumulative for the duration for a given session. If a session runs an identical query several times, the statistics for that connection will be the total for all the runs of a query. In contrast, V$SQL_MONITOR shows the statistics that have accumulated for the current run of a given sQl statement.therefore, each time a query runs, new statistics are reported for that query in V$SQL_MONITOR.
Leave a Reply