How can we get started with troubleshooting performance issue?
My database is not slow (according to the web application), but it seems slower than normal for a particular SQL statement. I want to know why it suddenly become slower. This could be due to the amount of data (maybe the amount of data is growing), but I also want to know if a certain resource (memory, disk, lack of performance counter, threads, etc) is making it slower than normal. Probably, an explain plan may help. I was also run another SQL statement which is very simple, but does delete a large number of rows from an un-related table, and I wonder if this is somehow causing performance issue for this other SQL statement.
The most thorough way to approach an Oracle performance problem is to do an extended SQL trace of one or more of the slow sessions, profile them, and see where they're actually spending their time. An excellent source of info on doing this is Cary Millsap's book which you can see the first chapter of here.
But a less complete way of looking at the same information is to simply query the data dictionary to see what events the slow sessions are waiting on. In cases where something is badly wrong, this will often be just as effective as a complete trace. I would start with running this query several times and see where that leads you:
select seq#, event, wait_time, seconds_in_wait, state
from v$session
where type='USER'
;
Another approach to gathering similar info is Tanel Poder's Snapper script
If things have been working great for some time, and nothing major has changed (i.e. increased usage, modified indexes, heavy reporting, analytial processing etc.) I'd start by checking on the health and performance of your hardware/physical disks.
As a DBA, we should have a monitoring system in place, which log and graph all the performance metrics. This would help us to see if we need to increase server's resource.
- http://www.dba-oracle.com/t_find_slow_oracle_sql.htm
- http://carymillsap.blogspot.com/2010/02/thinking-clearly-about-performance.html
- http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper
- http://www.dba-oracle.com/t_top_reasons_poor_performance.htm
- http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ
- http://dba.stackexchange.com/questions/96590/slow-running-oracle-query-caused-by-unnecessary-full-table-scan
- http://www.dadbm.com/oracle-slow-sql-query-against-dba_segments-solved/
- http://logicalread.solarwinds.com/oracle-explain-plan-uncover-slow-sql-queries-h01/#.VqJ_UlJQZrY
- http://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys
- http://use-the-index-luke.com/sql/where-clause/the-equals-operator/slow-indexes-part-ii
- http://stackoverflow.com/questions/316812/top-5-time-consuming-sql-queries-in-oracle
- http://www.oracle.com/technetwork/issue-archive/2012/12-jul/o42dba-1566567.html
- https://community.oracle.com/thread/503834?tstart=0
- http://dbaforums.org/oracle/index.php?showtopic=19671
- https://www.toadworld.com/products/toad-for-oracle/f/10/t/21186
- http://searchoracle.techtarget.com/feature/Oracle-SQL-and-index-internals-Histograms-and-finding-the-slow-running-SQL
- http://blog.orapub.com/20150119/Lots-Of-Oracle-Database-Server-Power-But-Slow-Bad-Performance.html
- http://blog.tanelpoder.com/2008/08/07/the-simplest-query-for-checking-whats-happening-in-a-database/
How can we optimize large delete?
- https://community.oracle.com/thread/877894?start=0&tstart=0
- http://www.dba-oracle.com/t_oracle_delete_tuning.htm
- http://stackoverflow.com/questions/14143510/how-to-optimize-delete-query-with-subquery-in-oracle
- http://stackoverflow.com/questions/9783030/optimize-delete-query-with-large-number-of-data-on-oracle
- https://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002252763.htm
- http://www.devx.com/dbzone/10MinuteSolution/22191
- http://www.devx.com/dbzone/Article/22614
- http://www.remote-dba.net/t_grid_rac_deleting_table_data.htm
- http://dba.stackexchange.com/questions/710/best-way-to-delete-very-large-recordset-in-oracle
- https://hoopercharles.wordpress.com/2009/12/29/which-is-most-efficient-when-deleting-rows-exists-in-or-a-view/
