oracle 单条查询 60ms,oracle thread bootstrap 等待和Shared Pool Latches

admin管理员组

文章数量:1530288

ADDM Report for Task 'TASK_12080'

---------------------------------

Analysis Period

---------------

AWR snapshot range from 5584 to 5590.

Time period starts at 06-FEB-18 09.00.12 PM

Time period ends at 07-FEB-18 03.00.58 AM

Analysis Target

---------------

Database version 12.2.0.1.0.

Activity During the Analysis Period

-----------------------------------

Total database time was 28494328 seconds.

The average number of active sessions was 1316.38.

Summary of Findings

-------------------

Description          Active Sessions      Recommendations

Percent of Activity

-------------------  -------------------  ---------------

1  Top SQL Statements   1310.09 | 99.52      1

2  Shared Pool Latches  1244.89 | 94.57      0

3  Soft Parse           238.14 | 18.09       2

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Findings and Recommendations

----------------------------

Finding 1: Top SQL Statements

Impact is 1310.09 active sessions, 99.52% 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 1310.09 active sessions, 99.52% of total activity.

-----------------------------------------------------------------------

Action

Investigate the SELECT statement with SQL_ID "fnf64buuadbm0" 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 fnf64buuadbm0.

select nvl(sum(stake_total_self), 0), nvl(sum(win_amount_self), 0),

nvl(sum(rake_off_self), 0),nvl(sum(rake_off_agent), 0) From

t_a_user_settle where user_id= :1  and settle_date =

to_char(sysdate,'yyyyMMdd')

Rationale

The SQL spent only 0% 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: 83% for SQL

execution, 17% for parsing, 0% for PL/SQL execution and 0% for Java

execution.

Rationale

At least 4 distinct execution plans were utilized for this SQL statement

during the analysis period.

Rationale

Waiting for event "cursor: mutex X" in wait class "Concurrency"

accounted for 98% of the database time spent in processing the SQL

statement with SQL_ID "fnf64buuadbm0".

Finding 2: Shared Pool Latches

Impact is 1244.89 active sessions, 94.57% of total activity.

------------------------------------------------------------

Contention for latches related to the shared pool was consuming significant

database time.

Waits for "cursor: mutex X" amounted to 93% of database time.

No recommendations are available.

Symptoms That Led to the Finding:

---------------------------------

Wait class "Concurrency" was consuming significant database time.

Impact is 1244.92 active sessions, 94.57% of total activity.

Finding 3: Soft Parse

Impact is 238.14 active sessions, 18.09% of total activity.

-----------------------------------------------------------

Soft parsing of SQL statements was consuming significant database time.

Recommendation 1: Application Analysis

Estimated benefit is 238.14 active sessions, 18.09% of total activity.

----------------------------------------------------------------------

Action

Investigate application logic to keep open the frequently used cursors.

Note that cursors are closed by both cursor close calls and session

disconnects.

Recommendation 2: Database Configuration

Estimated benefit is 238.14 active sessions, 18.09% of total activity.

----------------------------------------------------------------------

Action

Consider increasing the session cursor cache size by increasing the

value of parameter "session_cached_cursors".

Rationale

The value of parameter "session_cached_cursors" was "50" during the

analysis period.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Additional Information

----------------------

Miscellaneous Information

-------------------------

Wait class "Application" was not consuming significant database time.

Wait class "Commit" 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.

Wait class "User I/O" 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.

本文标签： 单条msORACLEthreadpool