Re: Oracle SPA Use case

Thank You Lothar. Yes, so basically we don't have option to capture the workload and replay it on the target X8 which would have been the real test.

In our case we have two different databases one is current production which is X5 and other is new database which is on X8 which is restored from one of the backup and is available to us in read/write mode. We want to test/compare the query performance using SPA. And in this case i believe it will run the SELECT part of the sql behind the scene even it will not return/throws the rows out.

So the part i am unable to understand is , if you see below URL it says the sql tuning set can be populated from cursor cache or AWR or both in the source database(for us its X5). Then we have to perform the create analysis task and execute analysis task on same database. So my question was as the AWR is already present or captured from same source database why do we have to again execute those sqls(or SELECT part of the sqls) again as part of SPA in same database? Should not we just export the captured tuning set from X5 and import it on X8 and execute those on X8 to see the difference between X5 and X8 performance?

Also i am seeing EXECUTE_FULLDML paramter is there which drives even using SPA if we want to test the DML performance but seems that is not in 11.2.

https://oracle-base.com/articles/11g/sql-performance-analyzer-11gr1

OR is there any other Doc/Scripts available which has the steps which will fit our situation or test scenario?

Regards
Lok

On Fri, Feb 26, 2021 at 3:32 PM Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> Hi Lok,
>
> I think you confusing RAT and database replay. RAT consists of Database
> Replay and SPA. Database Replay is seen as the "real thing" quite often
> and therefore it is often used synonym to RAT.
> You can not use the AWR data, because it is aggregated data . It does
> not necessarily reflect you tuning set accurately. In particular bind
> variables used will change things.
> To be able to compare apples to apples you have to run your set twice.
> SPA is more about plan changes than about hardware performance, although
> you can use it that way.
>
> Regards
>
> Lothar
>
>
> Am 26.02.2021 um 09:36 schrieb Lok P:
> > Hi Listers, We are on RDBMS version 11.2.0.4 of Oracle exadata and are
> > migrating from exadata X5 to X8 and want to perform a basic
> > performance test on the new X8. We don't currently have the time and
> > flexibility to perform a real application testing(RAT) test which
> > would have been a full fledged test of the exact behaviour of the
> > application on X8. So we want to go for the SPA(sql performance
> > analyzer) option as a quick alternate. I have few questions around SPA
> > testing.
> >
> > 1)In the SPA test I am seeing we don't have an option to have the
> > DML/DDl tested on the current 11.2 version.So is there any alternate
> > way to achieve/test this?
> > 2) And also after capturing the sql tuning sets from the AWR , it's
> > asking to run, create and analyze tasks on the current database which
> > is again going to take time as it will run the SELECT part of the
> > statements(DML/DDL) behind the scene. So my question was as we already
> > have the AWR data in the current database why i have to again run it
> > and that is going to take time and resources on the current production
> > database? is it simply possible to capture the sql tuning set from
> > current production for a certain period (say 3-4hrs) and then just
> > export and import it to the new X8 database and then run it there and
> > compare and see the difference in performance?
> >
> > Regards
> > Lok
>
>

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