Myth Buster - Number of rows in a table is not the issue

Overview

I have heard this myth so many times that we have issues "because we have too many rows in table x" - or in response to a support request we are told "this is because your purges are not set up properly" or over engineered solutions are created because "pckwrk has too many rows".

Note that number of rows in a table is never the issue for performance - it is the access path to the data.  Furthermore in a system like RedPrairie WMS (an On Line Transaction Processing System) - most of the data is coming from the database cache so it is absolutely not an issue!  This also goes into a tangent that on Oracle do not spend so much time in distributing your tablespaces to different disks - this is not 1995!

Current version of this article relies on data from an Oracle database but for this discussion, same concepts apply to SQLServer.

What is a table?

A table, from the point of view of the database, is not a monolith where you read it from top to bottom to see where your data is.  It is made of segments that are made of blocks.  You simply need to get to the right segment and block to read your data.  You get there through the use of data dictionary and indexes.

So from the point of view of the overall access path, if you divide your 10 million row table into 10 tables and you look at some "index" to see which table you should go to and then go to that table - your overall solution will be no better than going at the 10 million row table itself.

Data partitioning for extremely large tables is very useful and I am not disputing that.  There are other reasons to split the data - not performance in an OLTP system.  

Index Depth is the key

Oracle uses a variant of a B-Tree index.  The key concept here is the "height" of the index.  Height represents how many reads are needed in order to get at the desired data block.  Even if you have 10 million rows in a table, your height should be <= 3.  That is pretty good and should not cause any performance issues.  While we are at it - lets bust another myth - it is not necessary to rebuild the indexes due to performance issues.  This is because the B-Tree is balanced as the data is inserted - It is not 1985!  Every time you rebuild an index for gaining performance an engineer at Oracle/Microsoft gets heart burn.

To see the index height, analyze the index in oracle and then see the BLEVEL column in "ind" view.  Actual height is that number plus 1.  You will rarely see this value > 2.

Putting the concept to test

Following tests are on a development machine that has not been tuned for production use.  I am comparing the performance to access a table by PK between two tables that have very different sizes.

• dlytrn has 17,471 rows.  The BLEVEL is 1 for the primary key.
• usr_large_dlytrn has has 626,542 rows. The BLEVEL is 2 for the primary key.

Access Times for dlytrn (same PK accessed multiple times)

1. 1,000 - 2.4s
2. 10,000 - 14.29s
3. 100,000 - 127.88 s

Access Times for usr_large_dlytrn (same PK accessed multiple times)

1. 1,000 - 1.69s
2. 10,000 - 9.92s
3. 100,000 - 104.54 s

So in this test the larger table gave better performance - but that is not important.  It may have given better performance because of other reasons.  The key message is that number of rows in the table itself had no effect - especially in an OLTP system where our queries typically return a very small percentage of the rows in a table and that access is through indexes and cache.

But I swear - when we had performance issues last year and we deleted data from pckwrk - things improved!

I believe you - but unfortunately when someone faces acute performance issues, we often forget about basic troubleshooting strategies and start "trying out options" - sometimes expensive options without any basis for supporting them.  For example people would right away suggest "rebuild indexes" and "purge" without any evidence to support their decisions.  And when such actions are taken we often do not capture the results objectively.  For instance if we rebuild, we should see if IND view on oracle shows any improvement.

When you purged data in pckwrk, most likely the access path to pckwrk changed and that is what improved your performance - not the number of rows.  So in that case we should have identified the problem query and analyzed its query plan.  If we analyzed the same query after purging the data we would have noticed a different plan.  Had we used the improved plan on the larger table - we would have seen the same improvement.

Right Indexes vs More Indexes

In many cases dropping some indexes can improve performance as well.  We have several indexes delivered by standard product that simply do not make any sense.  On top of that several projects end up creating many more indexes without analyzing the need - and those indexes are often the culprit when we are seeing the performance issues.  For example if you add an index on wh_id column in pckwrk by itself - that will hurt performance.  We should add indexes only when they are selective enough to give less than 10% of the rows.

Bind Variables

MOCA "auto binds" the SQL statements, i.e. if you ran a query [select * from pckwrk where pcksts = 'P'], MOCA will run it as [select * from pckwrk where pcksts = :1].  So whereas with [P] Oracle had enough information to understand that an index on pcksts is good when Oracle does not know the value (as in running with :1) then Oracle may decide that 99% of rows in pckwrk have same pcksts - so maybe it is not a good idea to use pcksts index.  These are the types of situations where deleting rows from pckwrk may help because pcksts then becomes more selective - but as pckwrk will grow again the same issue will re-appear.  As of 2011, MOCA provides a "#nobind" hint.  If that were used for the query then Oracle will run it without bind variables and make the correct decision.

Adding more columns to an index

This is a less known strategy and often has extremely good results.  As tables grow organically, even for a selective index - database needs to access several rows in the base table in order to get additional information.  For example lets say we got 100 locations and we join to inventory tables and the query may need invlod.loducc.  In order to get that one column, database will need to access the invlod table in addition to the index on invlod.  The resulting overhead may be very low but often such queries are running 100s of times within a transaction so that number can add up.  An explain plan would have looked good but would include "TABLE ACCESS BY ROWID FOR INVLOD".  That can be an issue.  If you modified the index definition to include the columns that your query needed, then accessing index will be enough and the improvement will be quite significant.

Joining with views in general and poldat_view in particular

As indicated in Top 10 mistakes, policies are often over-used and we end up with unbalanced data distribution.  So even for a given polcod, polvar, polval - we may not gain good selectivity.  That coupled with the complexity of the poldat_view where it is trying to add rows for each wh is often a major problem.

If you are about to put a join to "poldat_view" in your query - stop, take a glass of water and try to think of some alternate strategies.  Even though joining is always faster than using "|" and do multiple queries, if poldat_view is involved - it may be better to not use join and go for "|" and a second query to get data from poldat_view.  Another option may be to read the poldat_view first into a recordset and then access that within the loop of your query.

If you must use poldat_view, then see if using base poldat is an option.  For example if your data is always expected to be warehouse specific, then you could use poldat.wh_id_tmpl of the actual warehouse.  

Any time you use views in the query - and especially poldat_view - always explain and also see if the database could run it incorrectly under certain scenarios.  I generally favor not joining to the view, especially poldat_view, and use one of the other strategies.

Changing Query Plans

Often when our symptom is "Performance was fine yesterday" - the issue is that some change in data distribution caused the database to start using a different plan.  In such situations, it helps to see the problem query and see its plans historically.  Then we should pin the plan that was running well.

Conclusion

Purging data is not a bad idea and we should have an appropriate purge and archive strategy but that falls in the domain of "data management" not "performance management".  Number of rows in a table is not a measure of performance.  When faced with performance issues, it is important to understand why we are facing the problem and then take corrective action.  Every corrective action should then be scrutinized to see if it really helped or had no effect.  An incorrect assumption can become a problem over time because then when we try to do things right - there is resistance that suggests that all of these changes were made to improve performance.  So key message is:

• Just number of rows in table is not a measure of performance
• Do not create complex solutions where you try to "not use pckwrk because it has so many rows".
• Do not worry that prtmst has 1M rows.
• Rebuilding indexes will rarely - if ever - help performance
• For RedPrairie WMS, data is accessed mostly by indexes and cache, so do not over engineer data distribution strategy where you try to put tables on different physical disks.  You have bought that expensive disk array - let it do its job!
• When faced with performance issues, always look at the query plan (and with bind variables).  See older plans and see if execution plan changed.
• Remove unnecessary indexes.
• Employ change management regime to control the changes done due to performance issues.  Capture the improvements objectively.

RedPrairie Implementation of Sampling in Workflows

Sometimes you come across an algorithm that is so elegant yet simple that you have to appreciate it.  It is a common requirement to apply some operation to a percentage of tasks for audit reasons.  For example you may want to divert 10% of the picked cartons to a special lane for audit.  Similarly you may want to apply some workflows to a certain percentage of items only.

When I considered the problem, I concluded that I would have to somehow mark the task, e.g. pckwrk, to indicate which cartons need to be audited.  RedPrairie has the concept of Workflow Rates:

Looking at this setup I was intrigued because the setup was independent of any workflow and was talking about probability.  Going deeper into the implementation, I was amazed at the elegance and simplicity of their implementation - rather than marking every nth row and deal with the associated complexities - simply roll a dice - if it says "1" - apply workflow - Genius!

Consider that if we have a work-station next to a conveyor where picked cartons are traveling and it is the job of the user to audit 50% of the cartons that pass next to him.  He could try to pick every other carton or he could simply flip a coin when he sees a carton - heads audit it, tails let it go.  Law of Large Numbers guarantees that  our results will approach the desired rate.

How do we toss a coin in software?  Generate random numbers.  For the random numbers generated by software  each number in a given random sequence had an equal chance of occurring, and that various other patterns in the data should be also distributed equiprobably.  So all we have to do is generate a number and check against defined probability to see if we should perform the operation.

To see it working - the command responsible for doing this is "get probability calculation".  You should pass in prob_qty and max_prob_qty.  For example for 10% probability you should pass in 10 and 100 respectively.

To test it, you can run it for large numbers and see the results.  So lets run it for 10, 100, 1000,and 10000 iterations and see how many workflows we will do.  Results were - as expected:

When you run the test, the exact counts will be different but they will approach the desired 10% result.  Simplicity of this solution is truly elegant.  The result should be acceptable for anyone.

For reference, the code to generate the above table is:

do loop where iterations = 4
|
{
   sl_power
   where num = 10
   and power = @i+1
   |
   publish data
   where max_iterations = @o_value
   |
   {
      do loop where iterations = @max_iterations
      |
      {
         get probability calculation result
         where prob_qty = 10
         and max_prob_qty = 100
         and invtid_num = 1
         |
         if ( @result = 1 )
            filter data where moca_filter_level = '1'   
      }
   } 
   >> res
   |
   publish data 
   where max_iterations = @max_iterations
   and cnt_work_done = rowcount(@res)
}

Dealing with very large trace files

Overview

In RedPrairie, we often have to analyze trace files.  The trace files are generally very helpful because nothing is missed when you trace everything - but that also implies that nothing is skipped.  This means that it is not a surprise to get a trace file that has several million lines.
Most new MOCA Clients have nice trace viewers but they all have a problem with extremely large trace files.  Also the analysis is quite difficult.

Solution.

One solution is to load the trace to the database.  With a proper schema that represents the trace the analysis can become manageable.  Following tables represent a possible schema:

usr_temp_cmd_trace_analysis

Column	Specification	Comments	Example
uc_trace_file	String(500)	Name of the trace file	/blah/trace.log
uc_cmd	String(4000)	Command Name	USRint/list order lines available for wave planning
uc_lvl	String(100)	Command Stack Level in trace	1
uc_start_ts	String(100)	Start time stamp from trace	17:15:32,571
uc_end_ts	String(100)	End time stamp from trace	17:15:34,059
uc_elapsed_ms	Float	Elapsed time of this command execution	1488
uc_start_line_num	Float	Starting Line Number in trace file	202
uc_end_line_num	Float	Ending Line Number in trace file	121208
uc_args	String(4000)	Pipe separated List of arguments and values	|wh_id=xyz(STRING)

usr_temp_sql_trace_analysis

Column	Specification	Comments	Example
uc_trace_file	String(400)	Name of trace file	/blah/trace.log
uc_bound_sql	String(4000)	SQL Statement with bind variables	select super_usr_flg from les_usr_ath where usr_id = :var0
uc_unbound_sql	String(4000)	SQL Statement with bind variables replaced with values	select super_usr_flg from les_usr_ath where usr_id = 'XXX'
uc_rows_returned	String(100)	# rows returned	1
uc_elapsed	String(100)	Elapsed Time in seconds	0.002135123
uc_line_num	Float	Line Number in trace file	377
uc_cmd	String(4000)	Command that called this SQL	MCSbase/list users
uc_lvl	String(100)	Command Stack Level In Trace	10
uc_cmd_start_line_num	Float	Starting Line number of this trace file	352

Loading the trace to these tables

Simple MOCA command can be developed to read the trace file and load these tables.  The latest version of Oracular MOCA Client will include this functionality as well.  Since the trace files can be very large, you should use groovy to read the file - as opposed to using "read file".  Additionally you should commit the data as it is being loaded into these tables.

Typical Analysis

Once data is loaded into these tables analyzing huge trace files becomes quite manageable, for example:

• Using usr_temp_cmd_trace_analysis, you can pin point which execution of a command took the longest, i.e. highest value of uc_elapsed_ms.
• usr_temp_sql_trace_analysis.uc_bound_sql has bind variables so it can help in analyzing SQL statements that are executed very frequently.  You can group by this column and sum uc_elapsed and take count(*).  This way it becomes easy to find SQLs that are executed very often.
• usr_temp_sql_trace_analysis.uc_bound_sql can be explained to see how the statement will be executed by the database.
• By comparing the total time in usr_temp_sql_trace_analysis and usr_temp_cmd_trace_analysis, we can find out if the performance issue may be external to the database.  For example if usr_temp_cmd_trace_analysis shows 2 minutes but the total time in usr_temp_sql_trace_analysis  is 1 minute, we know that 50% of the overhead is outside the database.
• If we see that a command is executed very frequently, according to usr_temp_cmd_trace_analysis, some caching techniques may be employed or the commands may be disabled.

We will add some of these features to future versions of Oracular MOCA client.

Conclusion

RedPrairie trace files can quickly reach millions of lines.  For example tracing of wave planning, pick release, allocation, etc. can often create huge trace files.  Analyzing these via typical tools can be very challenging.  By loading the data into a database table, we can pin point the problems very easily.  The script to load the trace into the database can be quite efficient.  I have loaded a 7 million line trace file in around 30 minutes into these two tables.  Once the data is loaded we can easily find the problem areas.

Dealing with characterset issues

Overview

Recently I was involved in a project where we had to call a web service (XML post over http).  The data in RedPrairie database was in UTF8.  The special characters in this case were Turkish characters.  In this post I will document the various issues we faced and how I addressed them.

Understanding the environment

When dealing with international charactersets, I have seen that most often the parties involved do not completely understand what they are talking about and that can often hinder progress.  Following are some simple scripts to get some basic data:

• What is the database characterset?
On Oracle execute:

[SELECT * FROM nls_database_parameters where parameter = 'NLS_CHARACTERSET']

Typically you will see something like AL32UTF8 implying UTF. Refer to oracle documentation for other charactersets.
• How is it translated within RedPrairie technologist stack?
When the string is read into a Java String, you can ignore what Java stores it internally as. You are basically trusting Java that it knows what the string really is. If the string goes from Java to C, C will see it as a UTF8 byte sequence.
• How does the other system want it?
For this project, the other system did not understand what characterset they expect. Something like this can become quite frustrating but in situations like this you may have to simply try the various encodings.

Solution Overview

Our scenario was as follows:

1. The data is fetched
2. An XML message is created from the data
3. The XML is posted to the other sever.
4. The other system responds.

When data is fetched and converted to XML, at that time I created a file using "write output file".  "write output file" is a C component and that showed the byte sequences as UTF-8.  When that data was posted to the URL, the other side was unable to parse it.  Even though network trace showed the correct byte sequences.  You can also use groovy to write files but in that case you should declare what characterset you want the file to be in.

The other system indicated that their native characterset was ISO-8859-9 so I tried converting the XML message to ISO-8859-9 before sending.

After that is resolved, we still need to understand what characterset their response comes in as.

So the code to write out to the URL and convert the characterset will be:

/* Note the iso-8859-9 here */
publish data
where uc_rp2outside_charset = "iso-8859-9"
|
publish data
where url = 'http://whatever.com'
and body = 'xml string that has international characters.  Dont care 

about characterset here because trusting Java to represent correctly'
and uc_content_type = "text/xml;charset=" || @uc_rp2outside_charset
|
[[
URL my_url = new URL(url);
HttpURLConnection conn = (HttpURLConnection) my_url.openConnection();
conn.setRequestMethod("POST");
conn.setRequestProperty("Content-Type", uc_content_type );
//
DataOutputStream wr = new DataOutputStream(conn.getOutputStream());
// This is where we are declaring that our writer should be writing in 

// a speciic characterset
PrintWriter out = new PrintWriter ( new OutputStreamWriter ( wr, 

                                    uc_rp2outside_charset ) );
//
// This println will convert body from internal java representation 

// to the target characterset
out.println(body);
out.close();

]]

The response from the webservice will be in a specific characterset as well and we need to interpret it as such.  In this situation, after some analysis I determined that the response from the webservice was sending the data in UTF-8.

publish data
where uc_outside2rp_charset = "utf-8"
|
[[
... code to write on the url ...
DataInputStream ir = new DataInputStream ( conn.getInputStream() );

//

// This declares that the response stream from the request will

// send UTF-8.  This causes the "ret" to properly convert from

// utf-8

//
BufferedReader in_reader = new BufferedReader( new InputStreamReader( ir, 

                               uc_outside2rp_charset ) );
//
while ((inputLine = in_reader.readLine()) != null)
{
   ret.append ( inputLine );
}


]]

Conclusion

Dealing with characterset issues often becomes a challenge because most people do not fully understand the details and underlying concepts.  In such situations you may have to try some approaches, for example:

• Understand that whenever you write out or read from a connection, data may need to be converted to a different characterset.  By having some policy or environment variables, you can easily try different conversions.
• Have the ability to create files with the request and response.  
• Look at the generated files in hex editor to see what bytes are being sent and received.
• A network level trace can help as well to ensure that what is on the wire is proper character sequence.
• Many technologies show "?" to represent characters that they do not understand; for example Oracle will show "?" and so would some java technologies.  MOCA trace will also show "?" - that can throw you off because the other side may be seeing "?" and so does MOCA trace on your side - but the raw data was correct from your side.  So having a raw file helps.  When creating such raw files same concepts apply, i.e. you will have to declare what characteset the file is in.
• I have seen that ISO-xxxx-y charactersets are used more often that UTF especially on windows based systems, so if other side is not making sense, try out the appropriate ISO characterset.