please I would like to know any query that shows the queries or the top 10 queries that take more time to finish since the beginning of the instance with user and the sql sentence, I have seen several that show those that are currently running and active but can not find any that show a historical of these queries since the instance was started, it is usually very simple from Toad with ADDM/AWR Reports but the database in question is Oracle 9i and does not give that option. Thank you
Oracle 9i historical long queries since instance startup
oracleperformanceperformance-tuningtuning
Related Solutions
Oracle 12c has a Performance Hub which includes Monitored SQL in historical mode. Although I haven't tested it, and based on some open bugs I doubt it works 100% of the time.
For 11g, or when the Performance Hub is not available or not working, I have created the open source program Historical SQL Monitoring (hist_sql_mon):
Here's an example of how to use it:
select hist_sql_mon.hist_sql_mon(
p_sql_id => '2ssrz4j1m39wx',
p_start_time_filter => date '2014-09-25',
p_end_time_filter => sysdate - interval '1' day)
from dual;
The primary output is a CLOB containing an execution plan with a count and distinct count of events.
-------------------------------------------------------------
|Id | Operation | Name | Rows | Cost (%CPU)| Event (count|distinct count)
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 83031 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | HASH JOIN RIGHT OUTER| | 8116K| 83031 (4)| Cpu (25|25)
| 3 | INDEX FULL SCAN | I_USER2| 155 | 1 (0)|
| 4 | NESTED LOOPS OUTER | | 8116K| 82993 (4)| Cpu (1|1)
| 5 | NESTED LOOPS OUTER | | 8116K| 10702 (24)| Cpu (2|2)
...
The functions also print the SQL statement to DBMS_OUTPUT. The bind variables are replaced with hard-coded values so the query can run anywhere. This can help with debugging or creating your own queries.
----------------------------------
--Historical SQL Monitoring Report
----------------------------------
--Execution plans and ASH data, where there are some samples for a plan_hash_value.
select
--Add execution metadata.
case
when plan_table_output like 'Plan hash value: %' then
...
To get to the bottom of this you likely need to identify the SQL statements that are generating the REDO. Unfortunately I don't think there is any simple, completely automated way to detect REDO per SQL.
But it's usually not too difficult to track down the worst SQL statement. Since you've already got AWR, look at the SQL Statistics section, ordered by Elapsed Time. If there's a lot of REDO then one of those statements may stick out. For example, if the top statement is an UPDATE
, DELETE
, MERGE (UPDATE)
, or an INSERT
without a hint (or with a bad hint).
Possibly the top statements are all CTAS or INSERT /*+ APPEND */
. In that case, look at the explain plan for each of those top statements, for example by using select * from table(dbms_xplan.display_awr(sql_id => 'sql_id from AWR'));
If the execution plan says LOAD AS SELECT
then it's using direct path inserts and is OK. If it says LOAD TABLE CONVENTIONAL
, that's the problem statement.
Possibly they are all ok, but the REDO is generated by the indexes. The only way an index will not generate REDO is if the index is set to NOLOGGING
and if the index is built after the INSERT
. Index NOLOGGING
only applies to index DDL. You may need to drop/disable and recreate/rebuild indexes to reduce REDO.
In my experience, the most likely causes of an INSERT
statement not running in direct-path write are:
- Foreign key indexes enabled on the target table, except when reference partitioning is used.
- Bad hint syntax. For example, if there's a space before the plus sign, like this:
/* +
. Or if the hint is placed in a slightly wrong position, like thisINSERT INTO /*+ append */ ...
. - Weird data types that aren't supported. I can't remember exactly, but I think with LOBs there are some extra limitations, like you may need to have partitioning.
- Triggers on the target table.
- There are likely some other reasons I'm forgetting.
Post the entire SQL statement and execution plan and we may be able to help investigate.
If none of that helps, than maybe it is an AWR bug. 15GB per second is very high, but not impossible. I wouldn't be too surprised if an Exadata system could achieve that.
Related Question
- SQL Server – Troubleshooting High CPU Usage
- SQL Server – Framework to Identify Blocking Queries
- Oracle Database troubleshooting enq: TX – row lock contention
- Mysql – how to speed up / fix this Ubuntu Apache MySQL Server Configuration
- SQL Server Performance – Resolving High CPU Usage and RESOURCE_SEMAPHORE Waits
Best Answer
Dynamic performance views are not guaranteed to have all SQL information since instance startup because of the limited amount of memory.
Oracle 9i does not have AWR.
You can use Statspack, but it is not installed by default.
Install it as SYSDBA with:
@?/rdbms/admin/spcreate
Creating a snapshot (does not happen automatically, this needs to be scheduled manually or with
spauto.sql
):EXECUTE statspack.snap;
Creating a report:
@?/rdbms/admin/spreport
More details can be found in the documentation following the above link.