Hi,
I have a sql insert which was completing in around 15 minutes yesterday, today it is running for an hour and still not completing.
Below is the ADDM report which I believe to be relevant. What I don't understand is, what could have changed since yesterday?
Some additional information:
- This is a development database (i.e. not production)
- I created the database yesterday.
- I have been running this insert (and others) in oracle sql developer.
- Oracle sql developer has crashed twice today while attempting to run this query.
- Archive log was on, but I have turned it off to see if that helps. It doesnt appear to.
- I am new to Oracle.
ADDM Report for Task 'ADDM:2608938308_1_104'
--------------------------------------------
Analysis Period
---------------
AWR snapshot range from 103 to 104.
Time period starts at 25-APR-13 02.00.07 PM
Time period ends at 25-APR-13 03.00.18 PM
Analysis Target
---------------
Database 'RONNIE' with DB ID 2608938308.
Database version 11.2.0.1.0.
ADDM performed an analysis of instance ronnie, numbered 1 and hosted at
E520RONNIE.
Activity During the Analysis Period
-----------------------------------
Total database time was 3131 seconds.
The average number of active sessions was .87.
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
---------------------------------------- ------------------- ---------------
1 I/O Throughput .87 | 100 1
2 Top SQL Statements .84 | 96.49 2
3 Top Segments by "User I/O" and "Cluster" .14 | 16.56 1
4 Hard Parse Due to Parse Errors .05 | 5.89 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: I/O Throughput
Impact is .87 active sessions, 100% of total activity.
------------------------------------------------------
The throughput of the I/O subsystem was significantly lower than expected.
Recommendation 1: Host Configuration
Estimated benefit is .87 active sessions, 100% of total activity.
-----------------------------------------------------------------
Action
Consider increasing the throughput of the I/O subsystem. Oracle's
recommended solution is to stripe all data files using the SAME
methodology. You might also need to increase the number of disks for
better performance.
Rationale
During the analysis period, the average data files' I/O throughput was
239 K per second for reads and 42 K per second for writes. The average
response time for single block reads was 6.6 milliseconds.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is .19 active sessions, 21.97% of total activity.
Finding 2: Top SQL Statements
Impact is .84 active sessions, 96.49% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.
Recommendation 1: SQL Tuning
Estimated benefit is .77 active sessions, 89.14% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the INSERT statement with SQL_ID
"dmrfw0qv9vs2p".
Related Object
SQL statement with SQL_ID dmrfw0qv9vs2p.
INSERT INTO POSITIONPOINTS
(SEQUENCENUMBER, XPOSITION, YPOSITION, LOCATIONID)
select cs.point_order, cs.x, cs.y, io.mrid from cable_sections_csv cs
inner join identifiedobjects io
on to_char(cs.cable_section_id) = io.name
inner join location l
on l.identifiedobjectid = io.mrid
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "dmrfw0qv9vs2p" was executed 1 times and had
an average elapsed time of 2808 seconds.
Rationale
I/O and Cluster wait for TABLE "NPADDSUSER.POSITIONPOINTS" with object
ID 74157 consumed 95% of the database time spent on this SQL statement.
Recommendation 2: SQL Tuning
Estimated benefit is .06 active sessions, 7.35% of total activity.
------------------------------------------------------------------
Action
Investigate the INSERT statement with SQL_ID "02263bpunzmad" for
possible performance improvements. You can supplement the information
given here with an ASH report for this SQL_ID.
Related Object
SQL statement with SQL_ID 02263bpunzmad.
/* SQL Analyze(74,0) */ INSERT INTO POSITIONPOINTS
(SEQUENCENUMBER, XPOSITION, YPOSITION, LOCATIONID)
select cs.point_order, cs.x, cs.y, io.mrid from cable_sections_csv cs
inner join identifiedobjects io
on to_char(cs.cable_section_id) = io.name
inner join location l
on l.identifiedobjectid = io.mrid
Rationale
The SQL spent only 21% of its database time on CPU, I/O and Cluster
waits. Therefore, the SQL Tuning Advisor is not applicable in this case.
Look at performance data for the SQL to find potential improvements.
Rationale
Database time for this SQL was divided as follows: 22% for SQL
execution, 78% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "02263bpunzmad" was executed 4 times and had
an average elapsed time of 14 seconds.
Rationale
At least 3 distinct execution plans were utilized for this SQL statement
during the analysis period.
Rationale
Top level calls to execute the PL/SQL statement with SQL_ID
"04maspp34qdtf" are responsible for 100% of the database time spent on
the INSERT statement with SQL_ID "02263bpunzmad".
Related Object
SQL statement with SQL_ID 04maspp34qdtf.
BEGIN dbms_sqltune.execute_tuning_task(:1); END;
Finding 3: Top Segments by "User I/O" and "Cluster"
Impact is .14 active sessions, 16.56% of total activity.
--------------------------------------------------------
Individual database segments responsible for significant "User I/O" and
"Cluster" waits were found.
Recommendation 1: Segment Tuning
Estimated benefit is .14 active sessions, 16.56% of total activity.
-------------------------------------------------------------------
Action
Investigate application logic involving I/O on TABLE
"NPADDSUSER.POSITIONPOINTS" with object ID 74157.
Related Object
Database object with ID 74157.
Action
Look at the "Top SQL Statements" finding for SQL statements consuming
significant I/O on this segment. For example, the INSERT statement with
SQL_ID "dmrfw0qv9vs2p" is responsible for 100% of "User I/O" and
"Cluster" waits for this segment.
Rationale
The I/O usage statistics for the object are: 6 full object scans, 31916
physical reads, 13 physical writes and 0 direct reads.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is .19 active sessions, 21.97% of total activity.
Finding 4: Hard Parse Due to Parse Errors
Impact is .05 active sessions, 5.89% of total activity.
-------------------------------------------------------
Hard parsing SQL statements that encountered parse errors was consuming
significant database time.
Recommendation 1: Application Analysis
Estimated benefit is .05 active sessions, 5.89% of total activity.
------------------------------------------------------------------
Action
Investigate application logic to eliminate parse errors.
Symptoms That Led to the Finding:
---------------------------------
Hard parsing of SQL statements was consuming significant database time.
Impact is .06 active sessions, 6.54% of total activity.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class "Network" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.