Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Indexes gone bad

GinolaMar 4 2018 — edited Mar 5 2018

Hi,

We have a couple of tables that are quite large (~200million rows), and each night we do delete and inserts of about 500k rows.

And after each insert we do rebuild index on all indexes on the table.

A lot of our batch jobs then run against these tables.

But for a couple of days now, the CBO has started doing a weird behaviour. On queries it used to do FTS, it now uses indexes and vice versa. Some of the indexes are obviously not that good to use since we are experiencing exceptional bad performance.

What can have happened? Could the table gone bad? And the indexes then look good for the CBO?

Thanks in advance!

Comments

Answer

Hi and thanks for the feedback!
This issue is fixed in the next release (version 19.3.4) which is coming out very soon. Please keep an eye out and give it a try and let me know here if it solves your problem.

Marked as Answer by User_6BTKC · Jun 24 2021
1 person found this helpful
User_6BTKC

<3 I am very excited for the new release thanks for the info

Hi,
We just released version 19.3.4 which should fix this issue. Could you please try it out and let me know?

User_6BTKC

Just did and it is absolutely working both in the tabular output as well as if i save to CSV. I'll check out the other updates as well. Nice one guys.

Ray007

Is there a way to set the default NLS so I don't need to alter my session each time?

No, not through these tools... you could set up a login trigger in the database.
We are looking into a "login.sql" sort of solution that would solve this problem.

1 person found this helpful
Ray007

Thanks. For now I created a bookmark that I can just run when I need it.

In version 21.5 we added support for a login script which should make changing NLS settings a lot easier :)

1 person found this helpful
Ray007

Thanks. Pointed to my instant client's glogin.sql file and works perfect. Next, someone will ask for this to be set per connection.... LOL (I'm good with this).

Actually, you can set it per connection. Check the "Show more Options" checkbox in the connection dialog and set the login script field that appears. (You can "Update" an existing connection to bring up the connection dialog.)

1 person found this helpful
1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 2 2018
Added on Mar 4 2018
20 comments
2,870 views