Home » RDBMS Server » Server Administration » Sessions and LGWR Locking (11.1.0.7 MS Server 2008 R1)
| Sessions and LGWR Locking [message #465365] |
Tue, 13 July 2010 15:36  |
Chaka1331
Messages: 3
Registered: July 2010
Location: Cambridge, Ontario
|
Junior Member |
|
|
Hi there!
We had an issue last week were we had a session with a very basic SQL query lock up the database, spiking the CPU at 100%. When you would kill the session, the lock would just jump to another session and so on. We finally had to restart the database since our clients were being kicked out. After the restart of the database, the LGWR ended up locking and held the CPU between 85-95%. The archive logs were switching every 5 minutes, when normally it would be every 45min. We spoke with Oracle Support, but they just ended up brushing the issue off and saying it was a hardware issue and were not able to provide any kind of backing to that.
Has anyone seen this kind of issue before? I would really like to find some kind of resolution to it. I was in a fight with oracle support since they were not helpful and ended up downgrading the priority of the issue without telling me.
Any suggestions or help would be very much appreciated. This has happened twice now in this environment and we really would like to prevent it from happening again.
Many thanks!
Chaka
|
|
|
|
|
|
| Re: Sessions and LGWR Locking [message #465521 is a reply to message #465373] |
Wed, 14 July 2010 09:11  |
Chaka1331
Messages: 3
Registered: July 2010
Location: Cambridge, Ontario
|
Junior Member |
|
|
I'm going to upload everything that I had given Oracle Support on this issue. Any help would be very much appreciated! Thanks for the reply! 
ADDM Report for Task 'ADDM:3042018287_1_5086'
---------------------------------------------
Analysis Period
---------------
AWR snapshot range from 5085 to 5086.
Time period starts at 07-JUL-10 03.01.03 PM
Time period ends at 07-JUL-10 04.00.06 PM
Analysis Target
---------------
Database 'ONASP1' with DB ID 3042018287.
Database version 11.1.0.7.0.
ADDM performed an analysis of instance onasp1, numbered 1 and hosted at
MCC-ORA.
Activity During the Analysis Period
-----------------------------------
Total database time was 1046 seconds.
The average number of active sessions was .3.
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
---------------------------- ------------------- ---------------
1 Virtual Memory Paging .3 | 100 3
2 Commits and Rollbacks .07 | 22.05 2
3 "User I/O" wait Class .06 | 20.05 0
4 Top SQL by DB Time .03 | 8.56 2
5 CPU Usage .02 | 6.05 1
6 Unusual "Other" Wait Event .01 | 3.37 1
7 Unusual "Network" Wait Event .01 | 2.96 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: Virtual Memory Paging
Impact is .3 active sessions, 100% of total activity.
-----------------------------------------------------
Significant virtual memory paging was detected on the host operating system.
Recommendation 1: Host Configuration
Estimated benefit is .3 active sessions, 100% of total activity.
----------------------------------------------------------------
Action
Host operating system was experiencing significant paging but no
particular root cause could be detected. Investigate processes that do
not belong to this instance running on the host that are consuming
significant amount of virtual memory. Also consider adding more physical
memory to the host.
Recommendation 2: Database Configuration
Estimated benefit is .3 active sessions, 100% of total activity.
----------------------------------------------------------------
Action
Consider enabling Automatic Shared Memory Management by setting the
parameter "sga_target" to control the amount of SGA consumed by this
instance.
Recommendation 3: Database Configuration
Estimated benefit is .3 active sessions, 100% of total activity.
----------------------------------------------------------------
Action
Consider enabling Automatic PGA Memory Management by setting the
parameter "pga_aggregate_target" to control the amount of PGA consumed
by this instance.
Finding 2: Commits and Rollbacks
Impact is .07 active sessions, 22.05% of total activity.
--------------------------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.
Recommendation 1: Application Analysis
Estimated benefit is .07 active sessions, 22.05% of total activity.
-------------------------------------------------------------------
Action
Investigate application logic for possible reduction in the number of
COMMIT operations by increasing the size of transactions.
Rationale
The application was performing 3729 transactions per minute with an
average redo size of 4460 bytes per transaction.
Recommendation 2: Host Configuration
Estimated benefit is .07 active sessions, 22.05% of total activity.
-------------------------------------------------------------------
Action
Investigate the possibility of improving the performance of I/O to the
online redo log files.
Rationale
The average size of writes to the online redo log files was 4 K and the
average time per write was 0 milliseconds.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Commit" was consuming significant database time.
Impact is .07 active sessions, 22.05% of total activity.
Finding 3: "User I/O" wait Class
Impact is .06 active sessions, 20.05% of total activity.
--------------------------------------------------------
Wait class "User I/O" was consuming significant database time.
The throughput of the I/O subsystem was not significantly lower than expected.
The Oracle instance memory (SGA and PGA) was adequately sized.
No recommendations are available.
Finding 4: Top SQL by DB Time
Impact is .03 active sessions, 8.56% of total activity.
-------------------------------------------------------
SQL statements consuming significant database time were found.
Recommendation 1: SQL Tuning
Estimated benefit is .02 active sessions, 6.41% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "g2hvk7mu5ba8v".
Related Object
SQL statement with SQL_ID g2hvk7mu5ba8v.
INSERT INTO LOG_ENTRY (ID, LOG_NAME, LOG_TIME, USER_INITIAL,
MESSAGE, ERROR) VALUES (:1,:2,:3,:4,:5,:6)
Rationale
SQL statement with SQL_ID "g2hvk7mu5ba8v" was executed 190926 times and
had an average elapsed time of 0.00035 seconds.
Recommendation 2: SQL Tuning
Estimated benefit is .01 active sessions, 2.01% of total activity.
------------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "6gvch1xu9ca3g". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID 6gvch1xu9ca3g.
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN
EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF
broken THEN :b := 1; ELSE :b := 0; END IF; END;
Rationale
SQL statement with SQL_ID "6gvch1xu9ca3g" was executed 59 times and had
an average elapsed time of 0.36 seconds.
Finding 5: CPU Usage
Impact is .02 active sessions, 6.05% of total activity.
-------------------------------------------------------
Time spent on the CPU by the instance was responsible for a substantial part
of database time.
Recommendation 1: SQL Tuning
Estimated benefit is .02 active sessions, 6.41% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "g2hvk7mu5ba8v".
Related Object
SQL statement with SQL_ID g2hvk7mu5ba8v.
INSERT INTO LOG_ENTRY (ID, LOG_NAME, LOG_TIME, USER_INITIAL,
MESSAGE, ERROR) VALUES (:1,:2,:3,:4,:5,:6)
Rationale
SQL statement with SQL_ID "g2hvk7mu5ba8v" was executed 190926 times and
had an average elapsed time of 0.00035 seconds.
Rationale
Average CPU used per execution was 0.00033 seconds.
Finding 6: Unusual "Other" Wait Event
Impact is .01 active sessions, 3.37% of total activity.
-------------------------------------------------------
Wait event "unspecified wait event" in wait class "Other" was consuming
significant database time.
Recommendation 1: Application Analysis
Estimated benefit is .01 active sessions, 3.37% of total activity.
------------------------------------------------------------------
Action
Investigate the cause for high "unspecified wait event" waits. Refer to
Oracle's "Database Reference" for the description of this wait event.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Other" was consuming significant database time.
Impact is .01 active sessions, 3.38% of total activity.
Finding 7: Unusual "Network" Wait Event
Impact is .01 active sessions, 2.96% of total activity.
-------------------------------------------------------
Wait event "SQL*Net more data to client" in wait class "Network" was consuming
significant database time.
Recommendation 1: Application Analysis
Estimated benefit is .01 active sessions, 2.96% of total activity.
------------------------------------------------------------------
Action
Investigate the cause for high "SQL*Net more data to client" waits.
Refer to Oracle's "Database Reference" for the description of this wait
event.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Network" was consuming significant database time.
Impact is .01 active sessions, 3.45% of total activity.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.
|
|
|
|
|
|
| Re: Sessions and LGWR Locking [message #465541 is a reply to message #465523] |
Wed, 14 July 2010 11:08 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
OK, I'll have go at trying to understand the situation.
You say "...a very basic SQL query lock up the database, spiking the CPU at 100%..." (I have to say the "lock up the database" is not a very precise term!) but in the AWR report there is no evidence of this. Your longest running statement was 17 seconds.
Next point: you say that "The archive logs were switching every 5 minutes", but according to the report you generated only 28M of redo, so that is impossible.
Are you sure that report is over the relevant time?
|
|
|
|
Goto Forum:
Current Time: Fri Nov 29 08:46:51 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
