Dashboards & Visualizations

Field search very slow compared to free text

altink
Contributor

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?

Labels (1)
Tags (2)
1 Solution

hc_joycechen
Explorer

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

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

View solution in original post

hc_joycechen
Explorer

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

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

View solution in original post

altink
Contributor

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
Super Champion

@altink 

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

0 Karma

hc_joycechen
Explorer

I don't quite understand your question.

Can you give sample information?

0 Karma

altink
Contributor

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.

altink
Contributor

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
SplunkTrust
SplunkTrust

Hi @altink,

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

Ciao.

Giuseppe

altink
Contributor

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

.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!