RE: How To Find Reapeating Row Patterns

I think the reply that addressed the statistical nature of problem is best. So if A followed by 1000 recs and another A occurs 1000 times, the you have a pattern.

Let me give you a bit more about the problem I want to solve. We have an application that logs a lot of messages to a log table. Lets say the application is processing a batch of 100 records, the log table will likely have some sort of repeating pattern of messages that occur 100 times, but since there is so much messaging and the program execution path may be different for some records, "seeing" that pattern is a very manual process. I would like the ability to run a query or program that helps me see the pattern a bit more quickly. Lets assume that it turns out that each record results in ~40 messages with a variance of 5-10 values within the ~40 values. Patterns with a 75% or above match would be likely candidates. I would want to start with the longest patterns that repeat at close to 100 times.

Since we have the time the message was inserted we also want to report on the "anomalies" that result in longer elapsed times in the message pattern and thus may represent a poorly performing program path. Why not just trace? Well I can think of a few reasons.

1. PLSQL heavy application such as this is not always easy to trace unless there is good instrumentation. However, the instrumentation is really just too much application logging and was not designed with performance diagnosis in mind.
2. This particular application talks to other 3'rd party applications which may be the source of the problem. From the database standpoint this will only show up in the application logging intervals.

-----Original Message-----
From: Mercadante, Thomas F (LABOR)
[mailto:Thomas.Mercadante_at_labor.state.ny.us] Sent: Friday, July 22, 2005 12:23 PM
To: Post, Ethan; sol beach
Cc: oracle-l_at_freelists.org
Subject: RE: How To Find Reapeating Row Patterns

Ethan

What constitutes a pattern? What makes it a success?

Does A,A; AB,AB; ABC,ABC; ABCD,ABCGD?

How about A, followed by 1000 other records and then A again?

Seems to me the rules need to be defined a little better. I get the feeling that this is a science all in itself. I wonder if regular expressions could help here.

Tom

--
http://www.freelists.org/webpage/oracle-l