Re: Oracle SPA Use case

Hi Lok,

even in the shared pool you will not have executions stats for a single execution, but rather accumulated over all executions. An exception are statistics_level=all and you query the stats right after a statement was executed.
Don´t know if you can sample SPA like that.

Regards

Lothar

Am 26.02.2021 um 11:26 schrieb Lok P:
> 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
> <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
> <mailto: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