RE: Comparison of stats and parameters

> phvs

For most databases performing within their own “normal” boundaries, the expectation that PHVs are generally stable/constant is not a solid starting point, at least for most applications with any degree of complexity to the SQL.

It’s just not how the optimizer is designed to work. I always wished for a “max stability” mode...

https://orastory.wordpress.com/2015/05/01/strategies-for-minimising-sql-execution-plan-instability/

From: Pap<mailto:oracle.developer35_at_gmail.com> Sent: 01 November 2022 11:10
To: Lothar Flatz<mailto:l.flatz_at_bluewin.ch> Cc: Oracle L<mailto:oracle-l_at_freelists.org> Subject: Re: Comparison of stats and parameters

Thank you Lothar. So basically it would be manual comparison of those values like parameters, phvs etc, between two databases using db link and throw alerts based on the output. On Sun, 30 Oct, 2022, 7:19 pm Lothar Flatz, <l.flatz_at_bluewin.ch<mailto:l.flatz_at_bluewin.ch>> wrote: Hi,

I sent you a statement I used to compare cdb and pdb parameter. Maybe you can use it as a blueprint to compare your 2 dbs via dblink.

Thanks

Lothar

SELECT

     pdb_name,
     name,
     con_id,
     value
FROM ( SELECT
     pdb_name,
     name,
     p.con_id,
     value,
     COUNT(DISTINCT p.con_id) OVER(
         PARTITION BY name
     ) settings,
     MIN(value) OVER(
         PARTITION BY name
     ) min_value,
     MAX(value) OVER(
         PARTITION BY name
     ) max_value
FROM
     gv$system_parameter   p,
     cdb_pdbs              d

   AND inst_id = 3)
WHERE

     settings > 1
     AND max_value != min_value;

Am 30.10.2022 um 11:13 schrieb Pap:
> Hello Listers,
> Actually we have a customer application running on Oracle 19.15
> version databases with two sides(primary and Disaster recovery)
> active-active configuration i.e both read and write and each of them
> having in sync using golden gate replication. At any point in time the
> majority of the applications write activity is happening on one
> side(i.e we call the primary side for that time). and that same thing
> is replicated to the other side using GGS replication and vice versa.
>
> We have many times encountered issues while pointing
> application traffic from one side to other. Things running fine on one
> side behave badly when switched to the other side and it's because of
> differences in object statistics and also sometimes differences in
> object structures or DB parameters because the DBA team missed
> to apply changes to both sides. So I wanted to understand from experts
> here, is there any easy/quick way or existing oracle tool available ,
> to compare the basic optimization things like object statistics, DB
> parameters, difference in Sql execution paths etc between two
> sides/databases and based on that we can trigger some
> automatic alerts, so as to avoid potential issues beforehand?
>
> Regards
> Pap

--
http://www.freelists.org/webpage/oracle-l