Oracle Index not working with CA SDM 12.9 AA

[Legacy User]

Hi guys,

I have a problem with my CA SDM 12.9 AA with Oracle 11g. It seems that SDM doesn't use the Oracle Indexes.

Eg.: 

STDLOG sample:

08/22 19:09:03.50 AS12K38BR sqlagt:select1415 12460 SIGNIFICANT orclclass.c 2169 A FETCH for the following statement took 77784 milliseconds: SELECT not_log.nlh_start, not_log.nlh_status, not_log.id FROM not_log WHERE not_log.cntxt_obj = N'cr:1689088' ORDER BY not_log.nlh_start DESC NULLS LAST

 

From Oracle developer, the same query return 0,016 seconds:

 

Can anyone saw that behavior?

 

Thanks,

 

Jorge Arcanjo de Souza

[Jon_Israel]

Hi Jorge,

In your nx.env file on the background and app servers, can you tell me the values you have set for the following items:

@NX_MIN_DBAGENT=
@NX_MAX_DBAGENT=

 

Thanks,
Jon I.

[Legacy User]

Both background and Application servers are configured with:

 

@NX_MIN_DBAGENT=6
@NX_MAX_DBAGENT=20

[Jon_Israel]

Thanks Jorge - does this happen every time that same query is run?  Or does it happen sometimes and not others?

I am wondering if you are running out of dbagents even with the 20 configured here.  The way to see that would be to turn on interval logging, and look at the vdbinfo after its been running for 24 hrs to see if there are any occurrences where the number of idle agents is zero.  That would be a key indicator that you are running out of db agents, and thus the queries are waiting and taking longer to return the data back to the virtdb.

If you dont find anything there, then I would say to open a case with CA Support and so an engineer can troubleshoot with you.

Thanks,

Jon

[Legacy User]

Hi Jon,

 

If your assumption is correct, if I restart one server and load the same information from Web Interface, so the informations must retrieved fast.

I'll try, as soon as I can.

 

Regards,

 

Jorge Arcanjo de Souza

[Jon_Israel]

Yeah I agree with Chi - there is something else going on that is causing the queries to get backlogged on the SDM side.  Those timings that you see in the logs are not just the running of the query but the whole time it takes to send the query, run the query, and get the data back.  So if there is a delay in sending it or being able to send it by way of a db agent being available, then these delays will be reflected in that time.

See if this occurs on a newly restarted system.

Thanks,

Jon I.

[Chi Chen]

Jorge, the stdlog showing the query took much longer time than running on the Oracle site does not mean SDM

does not use index. In fact, SDM can't control if it use or not use the index...that is totally up to the schema on the Oracle site and in this case, I believe the index was used when SDM submitted the query. So the question why it took this long

as showed in the stdlog. There could be a couple of reasons...for example, network delay, SDM machine resource overloaded etc. I think you would need to look from those, not why SDM not use Oracle index. My 2cent. Thanks _Chi 

[Legacy User]

Hi Chen,

 

You have a point. I think that CA SDM doesn't use Oracle Index because it seems obvius. However, I can execute the query from Oracle Developer inside the CA SDM machine and I can see if have the same behaviour. Related the resource overload, I don't think it's a problem, because CPU, Memory and Disk is running fine.

But ok, I will check the performance from the CA SDM Machine.

 

Regards,

[Legacy User]

Hi Chen,

 

I did the test from SQL Developer inside the server where CA SDM Application run and the same happens:

 

Just 0.047 seconds to return from SQL Developer

 

And from CA SDM Interface (running inside the CA SDM Application Server too) return takes 82297 miliseconds

 

[Raghu Rudraraju]

Hey Jorge,

We use Case IN-sensitive indices for SDM->Oracle usage.   So, try this approach in SQL developer:

 

alter session set NLS_COMP=ANSI;

alter session set NLS_SORT=BINARY_CI;

<< now run your query >>

 

It should take the same amount of time now as indicated in SDM logs.   I'm guessing we may need a case-insensitive index of some sort here (or maybe the one we have is not a case insenstive one)

 

Checkout this article for a bit more info too: 

CA Service Desk Manager (SDM) performance is poor when using Oracle Database Management System (DBMS) to host the MDB. 

 

Hope this helps

_R

[Michael Mueller]

Hi Jorge

 

If I take a look at  the explain plan for your query:

 

SELECT not_log.nlh_start, not_log.nlh_status, not_log.id FROM not_log WHERE not_log.cntxt_obj = N'cr:1689088'

ORDER BY not_log.nlh_start DESC NULLS LAST;

 

in my Environment I see that the index NOT_LOG_X2 gets used and so a performant query is executed.
when I change my session to case insensitive settings, using raghus suggestion (the same way how the SDM orcl_agents set their sesssion environment)

 

alter session set NLS_COMP=ANSI;
alter session set NLS_SORT=BINARY_CI;

the index is not used anymore, instead a full table scan is done (which is always bad), and therefore I have a slow execution, of course depending on the amount of records in the not_log table.

 

The DDL script of index NOT_LOG_X2 Looks like:

 

CREATE INDEX MDBADMIN.NOT_LOG_X2 ON MDBADMIN.NOT_LOG
(CNTXT_OBJ)

 

I created an analogue so called function based index which can be used by the DB in a case insensitive access situation

 

CREATE INDEX MDBADMIN.NOT_LOG_IDX_02_CI ON MDBADMIN.NOT_LOG
(NLSSORT("CNTXT_OBJ",'nls_sort=''BINARY_CI'''))
TABLESPACE YOUR_INDEX_TABLESPACE_NAME HERE;

 

after adding this index the query above uses the new index and runs fast again, even in a case insensitive access situation.

Give it a try , if you like, and let us know your results.

Hope that helps

Kind regards

.............Michael

[Legacy User]

Hi Michael,

 

Thanks for help, it works!!!!

 

Regards,

 

Jorge Arcanjo de Souza