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@xxxxxxxxxx <mailto:l.flatz@xxxxxxxxxx>> 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
