메뉴 건너뛰기

Korea Oracle User Group

Guru's Articles

Top 5 SQL Monitor Features

명품관 2015.12.01 11:28 조회 수 : 777

Top 5 SQL Monitor Features

SQL Monitor is one cool tool.  Via EM12c, it empowers everyone, (yes, that means the developer, too, so give them the access to view this data via Cloud Control, please!)  This is a top five list, but it doesn’t stop there, just remember, this is my favorite features when using Cloud Control with SQL Monitoring.

Once logged into the database, you can gain access to SQL Monitor by clicking on Performance –> SQL Monitoring.

1. High Level View

The main view of active and inactive processing, (just remember, SQL Monitor processes will age out, so keep that in mind that this tool isn’t for looking at historical processing.) is a great place to start when we talk about value to your day to day job.

sqlmon5

From the main view, you can easily see what sessions are active, the SQLID, if there is parallel and what the DOP, (Degree of Parallelism) granted, database time, (broken down by wait event percentage) and IO Requests.  There is a ton of pertinent data here, including plan hash values, start and stop time, plus the SQL Text, but everyone has to admit, this is what we all want and need to know.

2. SQL ID Details via SQL Monitor

By clicking on any of the SQLIDs displayed in the SQL Monitor main page, you can then dig into the details surrounding a SQL process.

sqlmon2.3

For the given SQLID, you can view the duration and type of wait event resource consumption.  You can quickly see what the cost is and in graphs added, quickly identify what performance challenges a particular execution plan is most hindered by.  There are arrows to help the DBA or developer navigate the plan to understand what pain points are most evident and where the plan connects from bottom to top as the process filters through data to come up with it’s results.

3.  Offload Percent

Yes, you can use a query to see the offload percent for a process, but SQL Monitor is nice enough to show you right in Cloud Control-

Picture1

If you’re working on an Exadata and want to make sure you’re using all those great engineered system features you paid for, it’s nice to know you’re offloading to a cell node vs. doing the work on your database node that should be used for other work than scanning objects.

4. Resource Usage Details for a Distinct SQLID

Knowing how many resources are going to a process are essential to knowing how to eliminate extra resource usage.

sqlmon3.1

Under Metrics for each SQLID captured by SQL Monitor, you’ll be able to view the amount of CPU, PGA and IO used by the process.  You’ll even be able to tell what kind of waits you’re experiencing if you don’t use Google Chrome, (I’m joking…OK, maybe I’m not, but you understand browser challenges, I’m sure… :))

5.  Session Details

By clicking on the User from the main view, I’m able to log into the session details.  The actual session ID won’t be displayed, (in this case, we just see SH for each session) but we can click on the link, there by displaying vital info about the session details.

sqlmon6

We can quickly link to the SQLID, view if there are any session blocking the one we are investigating and see pertinent wait information including the P1, P2 and P3 waits.

If I click on the bottom link in the WAIT section, it will take me to the object that is causing the most waits, which happens to be the SALES table.

sqlmon7

I can now view column and constraint info without having to go to the schema feature within Cloud Control.

I can also view information about indexes and statistics, which no longer is accessible from SQL Details in Cloud Control.

sqlmon8

6.  (Bonus) Viewing Waits and Pain Points

As a bonus, knowing how many of us like to see tabular vs. graphical execution plans, it may be worthwhile to also view them in graphical form.  Below you can see as I hover my cursor over the sections that are highlighted with yellow lines from the Sales table and then Red for the filtering, you can note that it clearly displays that there are 13 million rows having a nested loop performed out of a 26 million row table.  I think most of us know that a hash join would have performed much faster and this is why the section has been highlighted in red as the pain point for the SQL in question.

screenshot_Mon_Nov_30_16.12.30

Just a little bonus for those of you still as hooked on tabular execution plans as I am… :)

Have a great week after the long weekend and hope your Thanksgiving was wonderful!

번호 제목 글쓴이 날짜 조회 수
공지 Guru's Article 게시판 용도 ecrossoug 2015.11.18 598
24 How to Recover Data (Without a Backup!) 명품관 2016.05.11 1354
23 On ROWNUM and Limiting Results (오라클 매거진 : AskTom) 명품관 2016.04.28 384
22 DDL Logging - 12c (조나단 루이스) 명품관 2016.04.26 1497
21 11.1.0.6 부터 지원하는 Oracle Online Patching New Feature 명품관 2016.04.22 519
20 Quick tip on Function Based Indexes 명품관 2016.04.19 191
19 Oracle Linux 6.7 with Oracle 11.2.0.4 RAC 명품관 2016.04.15 9598
18 Parameter Recommendations for Oracle Database 12c - Part II 명품관 2016.03.18 352
17 Parameter Recommendations for Oracle Database 12c - Part I 명품관 2016.03.07 832
16 Hybrid Columnar Compression Common Questions 명품관 2016.03.04 191
15 What is an In-Memory Compression Unit (IMCU)? 명품관 2016.02.24 181
14 Why You Can Get ORA-00942 Errors with Flashback Query 명품관 2016.02.01 447
13 How do I capture a 10053 trace for a SQL statement called in a PL/SQL package? 명품관 2016.01.06 262
12 (유투브) KISS series on Analytics: Dealing with null - Connor McDonald 명품관 2016.01.05 155
11 Upgrade a Pluggable Database in Oracle 12c 명품관 2015.12.30 160
10 Oracle 12c SQL – Using JSON 명품관 2015.12.29 2560
9 Oracle Enterprise Manager Cloud Control 13c Release 1 (13.1.0.0) Installation on Oracle Linux 6 and 7 명품관 2015.12.23 198
» Top 5 SQL Monitor Features file 명품관 2015.12.01 777
7 On Invokers and Outs file 명품관 2015.11.23 102
6 Parallel Execution 12c New Features Overview file 명품관 2015.11.23 109
5 How many checkpoints in Oracle database ? [1] file 명품관 2015.11.20 282
위로