Field search very slow compared to free text

[altink]

Dear Support

I make two searches – for the same time-period, ex last 7 days, and on the same data (index):

1. index=db_oracle sourcetype="oracle:audit:text" (OMEGACA OR OMEGA_CORE_AUDIT)

2. index=db_oracle sourcetype="oracle:audit:text" (ACTION=*OMEGACA* OR ACTION=*OMEGA_CORE_AUDIT*)

The idea is to look for SYSDBA actions on application objects.

Search 1 – a wide-search – completes very quickly in a reasonable time (few seconds)

Job Inspect:

This search has completed and has returned 28 results by scanning 85 events in 1.438 seconds

Search 2 – a field-search – has a big delay and it is very slow.

Job Inspect:

This search has completed and has returned 28 results by scanning 1,264,230 events in 156.1 seconds

Problem:

I was expecting the Search 2 to be faster (or at least equal) compared to Search 1.

I can see that the second search scans 1,264,230 events while Search 1 only 85

Why do I have such a big performance slowness in my second Search?

[hc_joycechen · Accepted solution]

index=db_oracle sourcetype="oracle:audit:text" (OMEGACA OR OMEGA_CORE_AUDIT)

| where like(ACTION, "%OMEGACA%") OR  like(ACTION, "%OMEGA_CORE_AUDIT%") 

[altink]

Hi @hc_joycechen 

and thanks for the reply. this works blaze fast and precise, but it has a problem.

It requires a repetition of information in the sense that I have two declare twice each string I wish to search for.
This is prone to error, and as a matter of general principles - duplication should not exist.

Is it a workaround for this?

best regards

Altin

[bowesmana]

@altink 

You can just remove the first and leave the criteria in the where clause. Does that give acceptable performance?

[hc_joycechen]

I don't quite understand your question.

Can you give sample information?

[altink]

The sole issue is that the searched strings ("OMEGACA" and "OMEGA_CORE_AUDIT") have to be declared twice in the statement

Is there a syntax that would do the same with a single declaration?

best regards

Altin

[richgalloway]

The leading wildcards in search 2 are killing performance.  To find matches, Splunk has to read every event that has an ACTION field to determine if it contains one of the matching substrings.

In search 1, Splunk can use metadata to avoid reading events that don't contain one of the target strings.

A key difference between the two searches is #1 may find events where the target string is not in the ACTION field.

If the two return the same results (not just the same count) then use #1.

[altink]

Thank You @richgalloway 

But what I am searching for (strings OMEGA and OMEGA_CORE_AUDIT) - are inside the ACTION field and in no other. In the first search it seems Splunk performs implicitely a wildcard search - cause the two strings above are part of wider strings.
I do not need to search in any other field other the ACTION. Furthermore, if casually, the strings above are to be found in a field other then ACTION - I do not want those records returned !

While in the second search, where things are logically as I want them to be - a field search - and I am forced by logic to use wild-carding - Splunk is as slow as one cannot work with that.

Any idea?
best

Altin

[gcusello]

Hi @altink,

when you use "*" your searches are always slower than fixed strings or words!

Ciao.

Giuseppe

[altink]

thank you @gcusello 

But the strings I am searching for:

1. are to be found only inside ACTION field - and if others not returned
2. they are not field values in whole, they are part of field value, and as such wild card is required

best

Altin