Toad World® Forums

How to replay 10046 level 12 trace on RAC


#1

Hi ,

I have captured 10046 level 12 trace from 4 nodes of my RAC cluster.
Question 1: How do I replay those back to the RAC cluster.
Question 2: How do I replay those back to the same node of the RAC cluster? ie
the cluster on which the trace file was created has to be issued the same queries.

I imported the trace files, but when I run a benchmark with them, the database
server is showing no connections from the client, and no queries being run.

Thanks,
Prabhakaran.


#2

Check your profiles. You need to make sure that they are pointing to the correct location. There are some help topics that will help you with this. Look in the help file Quick Start section for the Real Application Clustering (RAC) Quick Start.

Tracey


#3

I did follow all the steps listed in the Quickstart. I didn’t do a level 4 trace though.
I did a level 12 trace assuming more information is better.

In the quickstart, it doesn’t explain how to send all the queries captured on that Node back to that particular node. With the default clustering option, it is going to send all the queries to all the nodes. Which is unfortunately not what I want.

I can try again with a level 4 trace.

With yesterday’s run, It ran for more than an hour. The Oracle database did not have any user connection from the BMF client. When I stopped it after an hour, it threw up this message.

** Job ‘TPC-C Toolkit Job’ started.
Started test ‘Oracle Trace Import’:
Agent(ORATCE-1950-01) Error: Unable to execute statement in ExecuteSQL(). ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘INIT_NO’
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Stopping test - Oracle Trace Import
Test cancelled by user.
** Job finished.
Total Time to perform the job:
Start Time: 6/19/2007 1:44:53 PM
End Time : 6/19/2007 3:01:41 PM
Elapsed : 0 Days 01:16:48.142

Thanks, Prabhakaran.


#4

A level 12 Oracle trace should be fine.

If all of the transactions need to execute one particular node, then you can create a profile pointing directly to that node and execute the transactions using that profile. If you point it to the cluster then the transactions will be redistributed to the cluster.

From the error message, it looks like one of the transactions is not valid for the database. You can go to the transaction list and use SQL preview to see if they will execute on the server. To do this, select the first transaction in the first user scenario of your replay test. When the properties display click on the green arrow icon just above and to the left of the SQL statement. Select the profile for your Oracle sever and click OK. If that statement executes correctly then you can continue with the next. If you have a lot of transactions you can narrow down your search by starting the agent and running the job again. When the job is running look at the Agent’s transactions tab, and take note of any transactions that have a number other then 0 in the Errors column. You can then check those transactions to find the source of the error.

Just a note, when you are running the transactions imported from a trace, the objects that the SQL Transactions use must be present in order for the transactions to successfully execute. For example, the transaction Select * from TableA will only execute properly if TableA exists. If TableA has been removed since the trace was created, then the transactions will no longer be valid.


#5

Those tips were very useful. I found out that it breaks at the first transaction itself.

  1. What are the PrepCursors and ExecuteCursors that are shown?

  2. The number of transactions listed seems very little compared to our expectation of what we would’ve expected.

  3. Some of the variables are replaced with $BFScripts, but others are not? and others are replaced with NULL. For example the first transaction that is imported init_tpcc.init_no (:idx1arr ) shows the value of NULL.

Thanks,
Prabhakaran.


#6
  1. The PrepCursors transactions prepare the SQL to be executed and the Execute Cursor transactions executes the prepared SQL with the same number. This is how Oracle executes the SQL, so BMF uses the same methodology.
  2. Are you sure you imported all of the trace files? Oracle creates multiple trace files when performing a single trace.
  3. The $BF Scripts are used when the same SQL executes with multiple bind parameter values so that the values are used in the same manor as originally executed. If only one bind parameter value is used for the SQL then no $BF Scripts are needed. The values used are pulled directly from the trace file, so if the original value was Null then the BMF will show a Null value as well.

One other note about trace file replay job executions. The transactions are executed in the same order and with the same timings as found in the trace file. So, if your original trace recorded transactions for 5 hours it will take 5 hours to play it back. If you wish to execute the transactions more quickly, then change the latency for the transactions. You can do this quickly using the Replace child latency feature on the right click menu.