View/change optimizer / parallelism settings

Oracle

SHOW PARAMETER optimizer;
-- Session: ALTER SESSION SET optimizer_mode = FIRST_ROWS_10;
-- Parallelism: ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;

Settings such as optimizer_index_cost_adj and parallel_degree_policy affect plan selection; change them carefully.

PostgreSQL

SHOW random_page_cost;
SHOW max_parallel_workers_per_gather;
-- Session: SET enable_seqscan = off;

On SSDs, random_page_cost ~1.1 is recommended. enable_* flags are for diagnosis, not a permanent fix.

MySQL

SELECT @@optimizer_switch;
SET SESSION optimizer_switch='index_merge=off';

optimizer_switch turns individual optimizations on/off; test at session level.

SQL Server

-- Instance-level MAXDOP and cost threshold:
EXEC sp_configure 'max degree of parallelism', 8; RECONFIGURE;
-- Database level: ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;

MAXDOP=0 is unlimited; usually 4-8 for OLTP. The 'cost threshold for parallelism' default of 5 is too low for most.

MongoDB

# There are no optimizer parameters; indexes and query shape are decisive.
db.customer.find({...}).explain('allPlansExecution')

allPlansExecution shows all plans evaluated by the planner.

Redis

# Not applicable.

There is no planner; io-threads and data structure choice determine performance.

Elasticsearch

GET <index>/_settings?include_defaults=true
# search limits such as search.max_buckets, index.max_result_window

There is no optimizer; shard count, routing and query structure determine performance.