Looks good already, doesn’t it? Actually, this is real world data and quite typical for an OLTP system with many reads and little changes. If just the tiny area of the “db block gets” would show up better… But hey, there’s an option in the report properties that helps! Change the scale to “logarithmic” and see for yourself: You could either mitigate this with SQL or, as shown here, by setting the minimum Y-Axis value to 0 (zero).įinally, let’s hit “Apply” an check out how our graph looks!
Furthermore, when the instance is restarted, many metrics are reset to zero, resulting in huge negative differences ruining our graph. So let’s choose a brighter colour for the “db block gets” column:Ī title for the Y-Axis would also be nice.
#Oracle sql developer 4.0.3 series#
I don’t like the default colour, especially when it comes to series that differ by orders of magnitude. Let’s give our report a self-explanatory title: In our case, the sum of “db block gets” and “consistent gets” equals “ session logical reads” – thus, a stacked area graph shows us three metrics by selecting only two. Now the SQL is entered, choose an appropriate graph type. restrict the selection to the current instance.ĪND ss.instance_number = sn.instance_numberĪND sn.instance_number = ( SELECT instance_number FROM v$instance ) WHERE ss.name IN ('db block gets', 'consistent gets') -, 'session logical reads') , ( select to_number(value) from v$parameter where name='db_block_size' ) blksz , ( LAG( ss.value, 2 ) OVER( ORDER BY ss.snap_id, ss.name ) - ss.value ) AS value_diff the LAG distance has to match the number of metrics collected DECODE( time_diff, 0, NULL, time_diff ) "MB/s" These are the columns SQL Developer needs for a graph:
, ( LAG( sn.snap_time, 1 ) OVER( ORDER BY sn.snap_id ) - sn.snap_time ) *24*3600 AS time_diff , LAG( sn.snap_id, 1 ) OVER( ORDER BY sn.snap_id ) AS prev_snap_id particularly the time difference between snaps in seconds Pre-select snapshot detail using a WITH-clause, Let me show you my way – your mileage may vary: There are many, many ways to query Statspack data and to visualize performance metrics. Okay, let’s fire up sqldev, go to the “Reports” tab an create a new user report:Īnd set the initial properties for the report – choose “Chart” as report type: Unfortunately, you cannot choose your preferred language from the preferences menu, so you have to add an entry to “/sqldeveloper/bin/nf”: # Set language to English This motivated me to start a series on leveraging SQL Developer Reports for DBA tasks, starting with visualizing logical I/O history.įirst things first: I like my SQL Developer menus in English.
#Oracle sql developer 4.0.3 free#
But wait – Oracle SQL Developer has a nice reporting feature built in, so why not build custom statspack reports for this great free tool? This is when Statspack, the predecessor of AWR, comes in handy to keep a history of database performance metrics.īut although Oracle still deliver Statspack with their recent DB releases (yes, even in 12c it’s not dead!), there are few tools that support it. If you run Oracle Standard Edition or haven’t licenced Diagnostics Pack for Enterprise Edition, then you don’t have AWR and ASH Data available.
0 kommentar(er)
