AWR report

[Roshan]

Red Hat Linux 6
Oracle Database 12.1.0.2

Hi Erman,

I have checked you post on
http://ermanarslan.blogspot.com/2014/05/awr-analysis-part-1.html

I have a production system on which we are facing slowness.

I have generated AWR reports and compared the statistics as per the above link.

Grateful if you can help me with the analysis of the report to pin point the slowness

awrrpt_07March18_10_11.html
awrrpt_07March2018_11_12.html
awrrpt_07March2018_12_13.html
March2018CBS.docx

Thanks,
Roshan

[ErmanArslansOracleBlog]

What kind of slowness you mean?
your db  is working without stress.
Your DB time is less than or equal to Elapsed time. So your db doesn't do lots of work. It is not utilized heavily.

Please declare that "slowness" and get AWR reports for the related time intervals..
You can increase the frequency of AWR snapshots, thus you can get AWR reports for a 30 min interval or for 15 min interval etc..

Probably, you will need to tune your queries at the end..

[Roshan]

I see from 11-12 a.m and 12-1 p.m the elapsed time is greater than db time.

Also, I see db file scattered read parameter present. Could it mean full table scan is being performed? Maybe developers need to perform indexing

[ErmanArslansOracleBlog]

You don't have a significant load on Db.
Your waits are primarily on CPU, but this doesn't mean a bad thing. It means processing. So your DB is doing work, but it is not utilizing lots of resources for this.

If you are feeling slowness on your application layer/on frontend, I recommend you to tune your queries.
Especially the ones listed in awr sections : SQL ordered by CPU Time and Sql ordered by Elapsed Time.

Also concantrate on, the execution counts. For some of the sqls, you have lots of executions, which will trigger lots of parses (hard & soft) + which will get unnecessary cpu cycles (if these executions are unnecessary)

any tuning on sql layer, will decrease the slowness that you feel on your application layer.
But as I said, your db is running comfortably..

--db file scattered read is the same type of event as "db file sequential read", except that Oracle will read multiple data blocks. Multi-block reads are typically used on full table scans. The name "scattered read" refers to the fact that multiple blocks are read into database block buffers that are 'scattered' throughout memory.
It doesn't mean only full table scan.. It can also occur for a fast full scan of an index.

 But you don't have an issue about that. you only have significant wait on CPU.