Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Saving AWR report from SQL developer

BV2925Jun 22 2016 — edited Jun 22 2016

Hi Folks,

I am new to sql developer and have a situation where I would want to save the awr snapshots to my local machine. I can view the snapshots taken every one hour and can access them in sql developer.

Any ways that I can save them to my local OC?

Thanks.

BV

Comments

Scott Wesley

That's how I use it. (Though it's not the entire time the user may be waiting. Networking lag...)

And I marry this with frequency, and compared medians & averages to find those that are used often, occasionally slow, frequently slow, rarely used, etc.

Keegan_W

Thanks Scott!  Do you have an example of a query where you've married it with frequency?

Scott Wesley

Yes, I should have clarified further. I think I used an odd choice of words

Here I'm just looking at how often the page was used during the day.

select

to_char(view_date,'hh24') dt

,count(*) c

,min(elapsed_time) min

,round(median(elapsed_time),2) median

,round(avg(elapsed_time),2) avg

,max(elapsed_time) max

,round(avg(elapsed_time)-median(elapsed_time),2) diff

from apex_workspace_activity_log

where view_date > sysdate-2 -- last two days

and application_id = :APP_ID

and page_view_type = 'Rendering'

group by to_char(view_date,'hh24')

order by dt desc

pastedImage_8.png

I find it interesting how the outliers tend the average away from the median as the count drops.

And you could do what some of the app builder reports do and multiple them out to find weighted averages / medians

-- top pages

select r.application_id||':'||r.page_id page

,p.application_name||' - '||p.page_name page

,count(*) c

,count(distinct apeX_user) unique_users

,sum(elapsed_time) sum_elapsed

,count(*) * sum(elapsed_time) weighted_sum

,count(*) * median(elapsed_time) weighted_median

,max(elapsed_time) max_elapsed

,round(avg(elapsed_time),3) avg_elapsed

,median(elapsed_time) median_elapsed

,count(case when page_view_type = 'Rendering' then 1 end) render

,count(case when page_view_type = 'AJAX' then 1 end) ajax

from apex_workspace_activity_log r

join apx_application_Pages p

on p.page_id = r.page_id

and p.application_id = r.application_id

where r.application_id = :MY_APP

group by r.application_id||':'||r.page_id

,p.application_name||' - '||p.page_name

order by c desc

pastedImage_9.png

In this case, I might ask why page 5 has a higher median than 1 or 3 (particularly since I'm aware of what those pages do...)

I love this data.

Keegan_W

These are great examples - thank you so much Scott!

I'm not sure if this is a silly question but is each elapsed_time entry for initial page load only or also for page refresh (such as generating a new chart or report on the same page with new filters)?

Scott Wesley

I don't think it's a silly question. If I remember correctly, the page_view_type column was introduced in APEX 5.0 to help answer this question.

pastedImage_0.png

So you get separate records for each, and check out the request_value column to give it some more context.

1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 20 2016
Added on Jun 22 2016
5 comments
1,405 views