Hi Marko,
----- Original Message -----
> On 2016-05-24 01:52, Nathan Scott wrote:
> > ----- Original Message -----
> >> On 2016-05-23 08:52, Nathan Scott wrote:
> >>> ----- Original Message -----
> >>>> On 2016-05-20 09:46, Nathan Scott wrote:
> >>
> >>> This doesn't equate to that many metrics/instances, so perhaps some
> >>> cunning
> >>> query rewriting there could solve that aspect, and could get us most of
> >>> the
> >>> way home here. (would involve rewriting the object_cache_values()
> >>> function
> >>> in pmdaoracle.pl)
> >>
> >> The queries are like:
> >>
> >> echo 'select file#, phyrds, phywrts, phyblkrd, phyblkwrt, readtim,
> >> writetim from v$filestat;' | sqlplus scott/tiger@orcl
> >>
> >> Querying any of the columns individually takes pretty much the same time
> >> so I can't see how to improve this.
(oh, I didn't read that closely enough - thought you were still referring to
v$db_object_cache there)
> > There's a fair bit of post-processing done in the PMDA, as it iterates over
> > all the rows returned and performs aggregation. If that could be shifted
> > to
> > within Oracle (via more cunning queries) then there would be much less data
> > to be returned, which might get us back to the response time for a regular-
> > sized query.
>
> Hmm, both the above query and pminfo -f oracle.file take the same ~1.3s
> so looks like PMDA post-processing time is not that significant? (select
> file#, name from v$datafile is around 0.1s)
>
Yep, oracle.file.* is a totally separate beast to oracle.object_cache.* - we
do no post-processing for the former, all rows effectively become instances.
>
> >> Thus your suggestion to make these
> >> two clusters opt-in is probably the most feasible solution at least in
> >> the short-term. Here oracle.object_cache will be ignored but I can try
> >> to see how things work after enabling oracle.file.
> >
> > OK. How many rows are there in your v$file view OOC? (from the systems we
> > were testing earlier there were several thousand, IIRC, but that setup was
> > plagued by a very different latency problem, so might not be relevant
> > here).
>
> The v$filestat query return ~1k rows here:
>
> > - oracle.file takes ~1.3s with ~1k rows
>
OK, thanks. That's relatively poor, but not clear we can do anything about it.
And there must be significant latency coming in from oracle there & specific to
that view it seems, since we're able to query many-thousands-of-rows on other
tables/views.
cheers.
--
Nathan
|