Vishal desai’s Oracle Blog

March 10, 2015

SQL Plan Tree Visualization

Filed under: Performance Tuning, SQL, Tools, Troubleshooting — vishaldesai @ 8:22 pm

Real Time SQL Monitor Graphical plan from Cloud Control at times is not legible. Using google chart and visualization techniques that I learned from Accenture Enkitec’s and Iggy’s blog, I have create three scripts to quickly display plan in graphical Btree format. The one from real time sql monitoring displays few more metrics such as cardinality, IO and time statistics and I have added some color logic such that if time spend in one plan line id is more than 10% of total elapsed time then display Timeline in red otherwise green.

@getplan_cursor_tree.sql – Display graphical btree plan from cache based on SQL ID and plan_hash_value

@getplan_awr_tree.sql – Display graphical btree plan from AWR metadata based on SQL ID and plan_hash_value

@getplan_realsqlmonitor_tree.sql – Display graphical btree plan from Real Time SQL monitoring based on SQL ID, sql_exec_id etc.

Modify hard coded lines in script:

SPO logs\getplan_realsqlmonitor_tree_&tmenvs..html;

host start chrome C:\Users\U267399\Desktop\Tools\scripts\logs\getplan_realsqlmonitor_tree_&tmenvs..html

Sample output from OEM:

 image

 

Sample html output using getplan_realsqlmonitor_tree.sql: 

image

Full html file https://dl.dropboxusercontent.com/u/48791336/getplan_realsqlmonitor_tree_031615_170343.html

Download

References:

1) http://mauro-pagano.com/2015/03/10/trouble-reading-an-execution-plan-maybe-sqld360-can-help/

2) https://iggyfernandez.wordpress.com/2010/11/26/explaining-the-explain-plan-using-pictures/

November 5, 2014

SQL Performance changing over time

Filed under: Performance Tuning, SQL, Tools — vishaldesai @ 9:43 pm

Here is another visualization to analyze sql performance changing over period of time using Excel.

Input Screen:

image

Enter the details such as username, password, tns, Days, Interval and SQL ID and click on run button.

Output Screen:

image

As you can see from above, IO Wait times on 8/16 and 9/2 were higher compared to 8/19, 8/21 and 8/26. Number of executions were more or less same on those days. On 9/9 and 9/10, IO wait times were at peak but that could be due corresponding increase in number of executions.

image 

Data 2 worksheet will show more details about other SQL statistics. Units for other statistics are different so its difficult to show these statistics in two dimensional chart format.

Formatting for some combination by Day/Interval may not format excel chart properly. You may have to adjust chart manually to make it legible.

Download (Will be updated on 11/08)

October 3, 2014

Compare Oracle SQL Plan in Excel

Filed under: Performance Tuning, Tools — vishaldesai @ 2:36 pm

 

Input Screen:

image

 

 

Output Screen:

image

Download xlsm file, input source and target details, double click on list box to populate plan hash values and select one item and click on Run button to compare plan. The output screen will highlight first line (only first) where it finds difference in operation.

Download

Compare Oracle Database Parameters in Excel

Filed under: Performance Tuning, Tools — vishaldesai @ 2:13 pm

Input Screen:

image

Output Screen:

image

It will highlight differences in red. I have added logic to mark it blue if values are different for parameters instance_number, thread, undo_tablespace and local_listener. You can add more parameters to that logic for further customization.

Download xlsm file, add tns alias to tnsnames.ora file on your windows client and start comparing parameters.

Download

May 15, 2014

IO Waits, Average Wait Time and Histogram Heat maps using SQL Developer

Filed under: Tools, Troubleshooting — vishaldesai @ 11:00 pm

Jeff Smith pointed out David Mann’s blog in recent session on SQL Developer Reporting. Using David’s base code I have developed SQL developer heat map reports to visualize, troubleshoot and compare IO related waits.

 image

 

image

From above we can clearly visualize that on 11-MAY-2013 between 2:00 – 3:00 pm average wait time for log file parallel write event was 27.3 ms. The number of waits during same time slot was 10801 which is equivalent to 3 waits per second and that’s not significant. If intermittent slowness was reported by application team and if it was related to commit wait class, DBAs need to engage SA/SAN team to investigate this further.

Below are IO wait histogram reports for log file parallel write event on same database instance. For OLTP workload you want to see bright red boxes for log file parallel write between 1 and 16ms buckets for wait counts and average wait times. Such heat maps can also be used to do relative performance comparison by Day or Day/Hour.

image

 

image

 

Download (I will upload SQL developer report definition files early next week.)

Another example of vertical stacked bar chart.

IO Wait Histogram by Count – db file sequential read

image

 

IO Wait Histogram by Avg Wait Time(s) – db file sequential read

image

April 18, 2014

Database Time Viewer

Filed under: Tools — vishaldesai @ 2:31 pm

DBTimeViewer is a great standalone tool created by Dominic Giles to monitor Oracle Database Time. It requires simple configuration file update which includes database connection information such as username, password, hostname, port and service. In large enterprise environment with hundreds of thousands of databases it becomes little difficult to update database.xml file every time you have to connect to different database. I have created simple combination of batch and SQL script which will automatically create database.xml.

Scripts:

Create SQL and Batch scripts with proper path settings:

Script dbtimemonitor.sql

set head off
set feed off
set linesize 200
set pages 9999
set feedback off
set verify off
set pagesize 0


set term off

spool C:\<dbtime path>\databases.xml

select '<?xml version = ' || '''' || '1.0' || '''' || ' encoding = ' || '''' || 'UTF-8' || '''' || '?>'  from dual
union all
select '<WaitMonitor Title="Monitored Databases" xmlns="http://www.dominicgiles.com/waitmonitor">' from dual
union all
select  '<MonitoredDatabase><ConnectString>//' || a.host_name || '<domain>:<port>/' || b.name || '</ConnectString><Comment></Comment><Username>' || '&1' || '</Username><Password>' || '&2' || '</Password></MonitoredDatabase>'  from gv$instance a, v$database b
union all
select '</WaitMonitor>' from dual;

spool off
set term on
exit


Script dbtime.bat

@ECHO off
SET DBUser=%1
SET DBPass=%2
SET DBTNS=%3

sqlplus -s "%DBUser%/%DBPass%@%DBTNS%" @C:\<path>\dbtimemonitor.sql %1 %2

cd C:\<path>
dbtimemonitor.bat

Output:

C:\<path>>dbtime.bat <dbuser> <dbpassword> <dbtns>

Capture4

November 26, 2013

Compare IO outliers

Filed under: Performance Tuning, Tools, Troubleshooting — vishaldesai @ 9:47 pm

Oracle SQL Developer has nice reporting capability to plot charts. To compare IO outliers using db_hist_event_histogram between two periods, I have created couple of reports for quick comparison.

When you click on report, it will prompt you to select database and input event, period one start snap and end snap, period two start and end snap.

Compare IO wait count outliers by %

image

Compare IO wait count outliers by Value

image

Compare IO wait time outlier by %

image

IO latency for all snaps by Day

image

IO latency for last 24 hours

image

Download link for report definition file.

PS Current version of SQL developer supports only 5 different colors for pie charts so there is overlap of colors in first 3 charts.

October 7, 2013

SQL Developer Child Reports and Drill Down

Filed under: Tools, Troubleshooting — vishaldesai @ 10:36 pm

SQL Developer has some cool reporting capability. Using these features and some of my favorite scripts, I can now quickly troubleshoot Oracle Database related issue.

Example: For given database session, I can run Tanel’s asqlmon, look at historical SQL performance using Kerry’s AWR scripts, and drill down into IO latency with just point and click.

 

September 3, 2013

Oracle IO latency monitoring

Filed under: Performance Tuning, Tools — vishaldesai @ 6:13 pm

I have converted Kyle Hailey’s oramon, to SQL format so that it can be run from client sqlplus window.

Sample Screenshot:

image

Usage:

oraiomon.sql <interval> <samples>

Download

July 5, 2013

v$event_histogram – buckets of time in Nth/Snap interval

Filed under: Performance Tuning, Tools, Troubleshooting — vishaldesai @ 6:22 pm

v$event_histogram stores cumulative data since instance startup. To monitor real time events and under what latency bucket they fall under, I have written script called wait_histogram_wc_pct.sql. Script will display percentage of wait counts that falls into individual latency bucket.

Example: As shown below in last line, 25% of direct path read events waited for >32ms and <64ms (307 out of 1228  events).

SQL> @wait_histogram_wc_pct.sql 'direct path read' 10 10
---------------------------------------------------------------Percent Wait Count--------------------------------------------------------------------------|
Event               |Time                |<1    |<2    |<4    |<8    |<16   |<32   |<64   |<128  |<256  |<512  |<1024 |<2048 |<4096 |<8192 |Tot Wait Count |
-----------------------------------------------------------------------------------------------------------------------------------------------------------|
direct path read    |07/05/13 11:49:51   |     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              1|
direct path read    |07/05/13 11:50:01   |     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              1|
direct path read    |07/05/13 11:50:11   |     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              1|
direct path read    |07/05/13 11:50:21   |     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              1|
direct path read    |07/05/13 11:50:31   |     1|     2|     3|     7|    17|    38|    21|     7|     3|     0|     0|     0|     0|     0|           1212|
direct path read    |07/05/13 11:50:41   |     2|     2|     6|    11|    20|    33|    20|     5|     1|     0|     0|     0|     0|     0|           2696|
direct path read    |07/05/13 11:50:51   |     4|     3|     6|    11|    20|    29|    19|     7|     1|     0|     0|     0|     0|     0|           2492|
direct path read    |07/05/13 11:51:01   |     4|     3|     6|    11|    18|    24|    25|     9|     1|     0|     0|     0|     0|     0|           2164|
direct path read    |07/05/13 11:51:12   |     2|     2|     4|     9|    16|    23|    25|    16|     3|     0|     0|     0|     0|     0|           1588|
direct path read    |07/05/13 11:51:22   |     1|     2|     3|     7|    13|    19|    27|    21|     6|     0|     0|     0|     0|     0|           1228|

Total wait counts may not be 100% accurate if v$event_histogram gets updated between last and first sample.

To monitor historical events from AWR in similar fashion you can use awr_wait_hist_wc_pct.sql

SQL> @awr_wait_hist_wc_pct.sql
Please enter start_date(mm/dd/yy)    :07/05/13
Please enter end_date  (mm/dd/yy)    :07/05/13


     14757 05-JUL-13 09.00.57.689 AM
     14758 05-JUL-13 09.10.58.136 AM
     14759 05-JUL-13 09.20.58.580 AM
     14760 05-JUL-13 09.30.59.208 AM
     14761 05-JUL-13 09.40.59.867 AM
     14762 05-JUL-13 09.50.00.963 AM
     14763 05-JUL-13 10.00.02.200 AM
     14764 05-JUL-13 10.10.03.637 AM
     14765 05-JUL-13 10.20.04.821 AM
     14766 05-JUL-13 10.30.05.240 AM
     14767 05-JUL-13 10.40.05.723 AM
     14768 05-JUL-13 10.50.06.094 AM
     14769 05-JUL-13 11.00.06.516 AM
     14770 05-JUL-13 11.10.07.016 AM
     14771 05-JUL-13 11.20.07.402 AM
     14772 05-JUL-13 11.30.07.838 AM
     14773 05-JUL-13 11.40.08.306 AM
Enter value for start snap_id   :14757
Enter value for end snap_id     :14773
Enter wait event                :direct path read
Enter instance number for RAC   :1
---------------------------------------------------------------Percent Wait Count--------------------------------------------------------------------------|
Event               |Time                |<1    |<2    |<4    |<8    |<16   |<32   |<64   |<128  |<256  |<512  |<1024 |<2048 |<4096 |<8192 |Tot Wait Count |
-----------------------------------------------------------------------------------------------------------------------------------------------------------|
direct path read    |07/05/13 09:00      |     0|     0|    33|    33|    33|     0|     0|     0|     0|     0|     0|     0|     0|     0|              3|
direct path read    |07/05/13 09:10      |    50|     0|    50|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              2|
direct path read    |07/05/13 09:20      |     2|     3|     6|    11|    20|    36|    15|     5|     1|     0|     0|     0|     0|     0|           6002|
direct path read    |07/05/13 09:30      |     3|     3|     6|    10|    19|    33|    18|     6|     1|     0|     0|     0|     0|     0|         158867|
direct path read    |07/05/13 09:40      |     3|     3|     6|    11|    20|    30|    20|     7|     1|     0|     0|     0|     0|     0|          82122|
direct path read    |07/05/13 09:50      |     2|     3|     5|     9|    15|    22|    25|    15|     5|     0|     0|     0|     0|     0|          27698|
direct path read    |07/05/13 10:00      |     2|     2|     5|     9|    15|    24|    24|    15|     4|     0|     0|     0|     0|     0|          59854|
direct path read    |07/05/13 10:10      |     3|     3|     5|    10|    16|    24|    25|    14|     2|     0|     0|     0|     0|     0|          51421|
direct path read    |07/05/13 10:20      |     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              0|
direct path read    |07/05/13 10:30      |    20|    20|     0|    40|    20|     0|     0|     0|     0|     0|     0|     0|     0|     0|              5|
direct path read    |07/05/13 10:40      |     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              0|
direct path read    |07/05/13 10:50      |    60|     0|     0|     0|     0|    40|     0|     0|     0|     0|     0|     0|     0|     0|              5|
direct path read    |07/05/13 11:00      |    33|    33|     0|    33|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              3|
direct path read    |07/05/13 11:10      |     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              0|
direct path read    |07/05/13 11:20      |    50|     0|     0|    50|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              2|
direct path read    |07/05/13 11:30      |    50|     0|     0|    50|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              2|
direct path read    |07/05/13 11:40      |   100|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              1|

If you make any enhancements, find any bug with scripts or have any other better way to monitor this, please let me know.

Download Scripts

Older Posts »

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.