partition performance

[Roshan]

Oracle db 19c
Hello Erman,

the query below is taking 28s to execute. Kindly advise how I can improve the execution time?

query1.txt

ddl1.txt
tkprof.txt

Thanks,
Roshan

[Roshan]

I created index on sa_id and removed the lower function. It is taking 1s now. Is local index faster than global index?

[ErmanArslansOracleBlog]

It depends on your workload, queries and partition keys..
Global indexes may provide better performance in OLTP.. They may be faster when fewer index probes are required, when your query need to access data stored in the columns other than the column(s) used in partition key(s).

[Roshan]

If I create a functional index on the 'lower' function, I see the query execution time takes 28s instead of 1s. Why does the functional index not work?

[ErmanArslansOracleBlog]

It is a functional index, it needs computation power, it requires cpu for all the rows it is interested..

[ErmanArslansOracleBlog]

And probably, your execution plan changes because of the type of that index.. Compare the execution plans, they will tell you.

[Roshan]

Yes, the execution plan changes. I see the total fetch execution time from disk is higher.
explan2.txt

[ErmanArslansOracleBlog]

Elapsed times are low, what is the problematic SQL? Where is the execution plan comparison?

[Roshan]

the problematic sql is the ddl1.txt which is attached in the initial message. 'Explan2' shows the tkprof with 'lower' function in the query and index created using 'lower' function.  This takes 28s

below tkrprof is the output using normal index and 'lower' function removed in query from ddl1.txt. This takes around 1-4s

tkrpfo_normalindex.txt

[ErmanArslansOracleBlog]

Roshan,

Send me 2 explain plan for the same query. One with lower function in query and lower function in index, another without them.

I should see the explain plans of both of them and I should see the elapsed times as well.
Then, I will update this issue with my comments.
Multiple files, one with explain other one only query.. It is hard to manage, diagnose... Please send me what I request.

[Roshan]

Hi,

please find below

explain plan of query with lower function and index on lower function.
backdbpp1_ora_4100651_EXPLAN_LOWERFUNC.prf
explain plan of query without lower function and normal index.
backdbpp1_ora_4088817_EXPLAN_NORMALINDEX.prf

[Mustafa Keskin]

Hello ,

Dont use functions in where condition at left side , it cause to performance degradation ..

Could you upload actual execution plans in cache  ?

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>:sql_id ,format =>'ADVANCED'));

[Roshan]

Hello,

thanks for the update. It seems it is executing fast now.

Is the steps in the doc below fine to locate the SID?

http://dbaparadise.com/2018/04/display-any-execution-plan-with-ease/

[ErmanArslansOracleBlog]

Good. and yes -- supposing you already know the user running that sql.