Home » RDBMS Server » Server Administration » Query runtime predictiction simulator in test like prod (Oracle 11g)
Query runtime predictiction simulator in test like prod [message #654325] Sat, 30 July 2016 10:40 Go to next message
swas_recall
Messages: 12
Registered: May 2012
Location: Bangalore
Junior Member

Hello All

I have a requirement as below.

We are in need to build a oracle data simulator on 11g and 12c databases.This needs to be a framework that can predict the query output timiing.

Case study is as below.

TEST Database which normaly has around 2 GB of data across
Production databases have 200 GB of source data

WE want to build a system that would allow the query to run on 2GB of data but predict the outout of 200 GB,simulating the production run stats whcih it should give from the test database itself without actually running the queries on production.

I hope i was able to explain the requirement,can you please advise what should be done to achieve this.

Can this be done by exporting the statistic and simulating the data output/load,Please help me on the approach for this soltion to build

thanks
Swastik

[Updated on: Sat, 30 July 2016 10:41]

Report message to a moderator

Re: Query runtime predictiction simulator in test like prod [message #654326 is a reply to message #654325] Sat, 30 July 2016 10:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
swas_recall wrote on Sat, 30 July 2016 08:40
Hello All

I have a requirement as below.

We are in need to build a oracle data simulator on 11g and 12c databases.This needs to be a framework that can predict the query output timiing.

Case study is as below.

TEST Database which normaly has around 2 GB of data across
Production databases have 200 GB of source data

WE want to build a system that would allow the query to run on 2GB of data but predict the outout of 200 GB,simulating the production run stats whcih it should give from the test database itself without actually running the queries on production.

I hope i was able to explain the requirement,can you please advise what should be done to achieve this.

Can this be done by exporting the statistic and simulating the data output/load,Please help me on the approach for this soltion to build

thanks
Swastik


In theory, theory and practice are identical. In practice, theory and practice are unrelated.

IMO, it will be faster & more accurate to run SQL against clone of Production, than the level of effort to construct any "simulator".
Nothing is impossible for the person who does not have to do it.
For many moons to come, I expect any simulator to be wrong more often then it is correct at predicting SQL performance.
Re: Query runtime predictiction simulator in test like prod [message #654327 is a reply to message #654325] Sat, 30 July 2016 11:21 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What you are suggesting is the technique I've been using just this week. I took a metadata only Data Pump export of the necessary schemas from the production system and imported it into a database on my PC. I could do a lot of tuning of execution plans in my own environment, but eventually of course I had to test on the live system. A no-data simulation can only get you so far.
One point: be sure to lock the statistics in your simulator DB, or you'll lose them all overnight. That was very annoying Confused
Re: Query runtime predictiction simulator in test like prod [message #654328 is a reply to message #654327] Sat, 30 July 2016 13:59 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
As part of this task you need to write a SQL parser, I hope that you'll place the source code onto SourceForge & in the public domain.
I wish you the best at recreating the CBO & keeping it current as Oracle improves it algorithms & adds new datatypes.
Previous Topic: manual creation of database
Next Topic: Oracle Universal installer stucks - GUI issue?
Goto Forum:
  


Current Time: Fri Mar 29 03:59:16 CDT 2024