PostgreSQL/PostGIS، Oracle/PL-SQL و بهینه‌سازی کوئریPostgreSQL/PostGIS, Oracle/PL-SQL & Query Tuning

CREATE TABLE customers (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email        text        NOT NULL UNIQUE,
  country_code char(2)     NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint        NOT NULL REFERENCES customers(id),
  status      text          NOT NULL,   -- NEW / PAID / SHIPPED / CANCELLED
  total       numeric(12,2) NOT NULL,
  placed_at   timestamptz   NOT NULL DEFAULT now()
);

CREATE TABLE customers (
  id           NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email        VARCHAR2(320) NOT NULL UNIQUE,
  country_code CHAR(2)       NOT NULL,
  created_at   TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL
);

CREATE TABLE orders (
  id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id NUMBER        NOT NULL REFERENCES customers(id),
  status      VARCHAR2(16)  NOT NULL,   -- NEW / PAID / SHIPPED / CANCELLED
  total       NUMBER(12,2)  NOT NULL,
  placed_at   TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL
);

-- PostgreSQL: ANALYZE samples the table and refreshes pg_statistic.
ANALYZE orders;
VACUUM (ANALYZE, VERBOSE) orders;      -- cleanup + stats in one pass

-- Raise histogram resolution on a skewed column
-- (default_statistics_target is 100; per-column overrides it):
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

-- Make autoanalyze keep up on a large table:
ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.01);

-- Oracle: DBMS_STATS is the only supported way.
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'ORDERS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE,      -- index stats too
    degree           => 4);        -- parallel gather
END;
/

-- Force a big histogram on one skewed column:
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS',
    method_opt => 'FOR COLUMNS SIZE 254 STATUS');
END;
/

SELECT attname, n_distinct, null_frac,
       most_common_vals, most_common_freqs, correlation
FROM   pg_stats
WHERE  tablename = 'orders' AND attname IN ('status', 'placed_at');

SELECT relname, n_live_tup, last_analyze, last_autoanalyze
FROM   pg_stat_user_tables WHERE relname = 'orders';

SELECT column_name, num_distinct, num_nulls, density,
       histogram,          -- NONE / FREQUENCY / TOP-FREQUENCY / HEIGHT BALANCED / HYBRID
       num_buckets, last_analyzed
FROM   user_tab_col_statistics
WHERE  table_name = 'ORDERS' AND column_name IN ('STATUS', 'PLACED_AT');

SELECT table_name, num_rows, blocks, avg_row_len, last_analyzed, stale_stats
FROM   user_tab_statistics WHERE table_name = 'ORDERS';

-- PostgreSQL: an extended statistics object (PG 10+, MCV lists since PG 12)
CREATE STATISTICS customers_geo (ndistinct, dependencies, mcv)
  ON country_code, city FROM customers;
ANALYZE customers;

-- Expression statistics come free with an expression index:
CREATE INDEX customers_email_lower_idx ON customers (lower(email));

-- Oracle: a column group (extended statistics)
DECLARE cg VARCHAR2(64);
BEGIN
  cg := DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'CUSTOMERS', '(COUNTRY_CODE, CITY)');
END;
/
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'CUSTOMERS',
    method_opt => 'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS (COUNTRY_CODE, CITY)');
END;
/

-- Expression statistics WITHOUT building an index:
DECLARE e VARCHAR2(64);
BEGIN
  e := DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'CUSTOMERS', '(UPPER(EMAIL))');
END;
/

-- The command you should type by reflex (and wrap DML in a rollback!):
BEGIN;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT o.id, o.total, c.email
FROM   orders o JOIN customers c ON c.id = o.customer_id
WHERE  o.status = 'NEW'
  AND  o.placed_at >= now() - interval '7 days';
ROLLBACK;

-- Oracle: tag the statement, run it, then read the real cursor.
ALTER SESSION SET statistics_level = ALL;      -- or use the hint per statement

SELECT /*+ GATHER_PLAN_STATISTICS */ o.id, o.total, c.email
FROM   orders o JOIN customers c ON c.id = o.customer_id
WHERE  o.status = 'NEW'
  AND  o.placed_at >= SYSTIMESTAMP - INTERVAL '7' DAY;

SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST +COST +BYTES'));

Nested Loop  (cost=0.86..2451.07 rows=812 width=48)
             (actual time=0.041..913.220 rows=214883 loops=1)
  Buffers: shared hit=629104 read=18422
  ->  Index Scan using orders_status_placed_idx on orders o
        (cost=0.43..118.52 rows=812) (actual time=0.021..71.004 rows=214883 loops=1)
        Index Cond: ((status = 'NEW') AND (placed_at >= ...))
  ->  Index Scan using customers_pkey on customers c
        (cost=0.43..2.87 rows=1) (actual time=0.003..0.003 rows=1 loops=214883)
Planning Time: 0.211 ms
Execution Time: 940.882 ms

-- PostgreSQL's "estimate only" and "parameterised estimate" forms:
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'NEW';

EXPLAIN (GENERIC_PLAN)                     -- PG 16+
SELECT * FROM orders WHERE customer_id = $1 AND status = $2;

-- Oracle's estimate-only form, plus SQL*Plus AUTOTRACE for a quick look:
EXPLAIN PLAN SET STATEMENT_ID = 'q1' FOR
SELECT * FROM orders WHERE customer_id = :cid AND status = :st;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'q1', 'ALL +OUTLINE'));

-- AUTOTRACE (SQL*Plus): plan + real consistent gets/physical reads per run
SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
SELECT * FROM orders WHERE status = 'NEW';
SET AUTOTRACE OFF

---------------------------------------------------------------------------------------
| Id | Operation                    | Name        | Starts | E-Rows | A-Rows | Buffers |
---------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |             |      1 |        |    215K|    647K |
|  1 |  NESTED LOOPS                |             |      1 |    812 |    215K|    647K |
|  2 |   TABLE ACCESS BY INDEX ROWID| ORDERS      |      1 |    812 |    215K|     18K |
|* 3 |    INDEX RANGE SCAN          | ORD_ST_PL_I |      1 |    812 |    215K|    1421 |
|  4 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS   |   215K |      1 |    215K|    629K |
|* 5 |    INDEX UNIQUE SCAN         | CUSTOMERS_PK|   215K |      1 |    215K|    414K |
---------------------------------------------------------------------------------------

-- pg_stat_statements: add to shared_preload_libraries, restart, then:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT queryid, calls,
       ROUND(total_exec_time::numeric, 1) AS total_ms,
       ROUND(mean_exec_time::numeric, 3)  AS mean_ms,
       rows,
       shared_blks_hit + shared_blks_read AS logical_reads,
       left(query, 80) AS query
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
FETCH FIRST 10 ROWS ONLY;

SELECT pg_stat_statements_reset();     -- baseline before a load test

-- Oracle: the shared pool already tracks everything; no extension needed.
SELECT sql_id, plan_hash_value, executions,
       ROUND(elapsed_time/1e6, 1)                         AS total_s,
       ROUND(elapsed_time/GREATEST(executions,1)/1000, 3) AS mean_ms,
       rows_processed,
       buffer_gets                                        AS logical_reads,
       SUBSTR(sql_text, 1, 80)                            AS query
FROM   v$sql
ORDER  BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

-- Historical version (Diagnostics Pack):
SELECT sql_id, plan_hash_value, SUM(executions_delta) execs,
       ROUND(SUM(elapsed_time_delta)/1e6, 1) total_s
FROM   dba_hist_sqlstat WHERE snap_id BETWEEN :s1 AND :s2
GROUP  BY sql_id, plan_hash_value ORDER BY 4 DESC FETCH FIRST 10 ROWS ONLY;

-- auto_explain: log the plan of anything slower than 500 ms.
LOAD 'auto_explain';
SET auto_explain.log_min_duration      = '500ms';
SET auto_explain.log_analyze           = on;    -- real row counts
SET auto_explain.log_buffers           = on;
SET auto_explain.log_nested_statements = on;    -- inside functions too
SET auto_explain.log_timing            = off;   -- cheap: rows without per-node timing
SET auto_explain.sample_rate           = 0.05;

-- Where is time going right now? (PG samples waits in pg_stat_activity)
SELECT wait_event_type, wait_event, state, count(*)
FROM   pg_stat_activity WHERE backend_type = 'client backend'
GROUP  BY 1,2,3 ORDER BY 4 DESC;

-- Real-Time SQL Monitoring auto-captures anything over ~5 s of CPU/IO
-- or any parallel statement. No setup required.
SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
         sql_id => '7ftjftd9xdrsp', type => 'TEXT', report_level => 'ALL')
FROM dual;
-- Force it for a fast statement:  SELECT /*+ MONITOR */ ...

-- Where is time going? ASH samples every active session every second.
SELECT event, wait_class, COUNT(*) samples
FROM   v$active_session_history
WHERE  sample_time > SYSTIMESTAMP - INTERVAL '10' MINUTE
GROUP  BY event, wait_class ORDER BY samples DESC FETCH FIRST 10 ROWS ONLY;

-- PostgreSQL: log every statement with its duration, then aggregate with pgBadger.
ALTER ROLE app_user SET log_min_duration_statement = 0;
SET log_duration = on;
-- Reset when done:
ALTER ROLE app_user RESET log_min_duration_statement;

-- 10046 level-12 trace: waits + bind values, for one session.
BEGIN
  DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 123, serial_num => 4567,
    waits => TRUE, binds => TRUE, plan_stat => 'ALL_EXECUTIONS');
END;
/
-- ... workload runs ...
BEGIN DBMS_MONITOR.SESSION_TRACE_DISABLE(123, 4567); END;
/
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
-- From the OS shell:
--   tkprof orcl_ora_12345.trc out.txt sort=prsela,exeela,fchela

-- Compare access paths experimentally by disabling one:
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'CANCELLED';
RESET enable_seqscan;

-- Index-only scan needs a current visibility map:
VACUUM (ANALYZE) orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id FROM orders WHERE status = 'NEW';

-- Same experiment, driven by hints:
SELECT /*+ FULL(o) */ * FROM orders o WHERE status = 'CANCELLED';
SELECT /*+ INDEX(o orders_status_idx) */ * FROM orders o WHERE status = 'CANCELLED';

-- Index-only: Oracle simply omits TABLE ACCESS BY INDEX ROWID when the
-- index covers every referenced column. No visibility map involved.
SELECT /*+ INDEX(o orders_status_cust_idx) */ customer_id
FROM   orders o WHERE status = 'NEW';

CLUSTER orders USING orders_placed_at_idx;   -- ACCESS EXCLUSIVE lock
ANALYZE orders;
SELECT attname, correlation FROM pg_stats
WHERE tablename = 'orders' AND attname = 'placed_at';

ALTER TABLE orders MOVE ONLINE;                        -- 12c+ online rebuild
ALTER INDEX orders_placed_at_idx REBUILD ONLINE;
SELECT index_name, clustering_factor, num_rows, leaf_blocks
FROM   user_indexes WHERE table_name = 'ORDERS';

-- Diagnose by disabling a method, then fix the real cause.
SET enable_nestloop = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT /* ... */ 1;
RESET enable_nestloop;

SET work_mem = '256MB';        -- session-scoped; mind the multiplier below
SET join_collapse_limit = 8;   -- how hard the planner searches join orders
SET geqo_threshold = 12;       -- above this, genetic (approximate) search

-- Hints are directives in Oracle: honoured whenever legal.
SELECT /*+ LEADING(o c) USE_NL(c) INDEX(c customers_pk) */ o.id, c.email
FROM   orders o JOIN customers c ON c.id = o.customer_id;

SELECT /*+ LEADING(c o) USE_HASH(o) */ o.id, c.email
FROM   orders o JOIN customers c ON c.id = o.customer_id;

ALTER SESSION SET pga_aggregate_target = 2G;
-- One-off batch job that needs a huge workarea:
ALTER SESSION SET workarea_size_policy = MANUAL;
ALTER SESSION SET hash_area_size = 268435456;

-- Equality, then range, then covering payload (PG 11+ INCLUDE).
CREATE INDEX orders_status_placed_idx
  ON orders (status, placed_at) INCLUDE (total, customer_id);

-- Partial index: index only the rows you actually query.
CREATE INDEX orders_open_idx ON orders (placed_at)
  WHERE status IN ('NEW', 'PAID');

-- Expression index, and a descending index to match an ORDER BY:
CREATE INDEX customers_email_lower_idx ON customers (lower(email));
CREATE INDEX orders_recent_idx ON orders (placed_at DESC NULLS LAST);

-- Oracle has no INCLUDE: extra columns simply join the key.
CREATE INDEX orders_status_placed_idx
  ON orders (status, placed_at, total, customer_id);

-- Oracle has no partial index. The idiomatic equivalent is a function-based
-- index that stores NULL for rows you want excluded (NULL keys aren't indexed):
CREATE INDEX orders_open_idx
  ON orders (CASE WHEN status IN ('NEW','PAID') THEN placed_at END);
-- and the query must repeat the expression verbatim:
SELECT * FROM orders
WHERE  CASE WHEN status IN ('NEW','PAID') THEN placed_at END >= :d;

CREATE INDEX customers_email_lower_idx ON customers (LOWER(email));
CREATE INDEX orders_recent_idx ON orders (placed_at DESC);

-- BRIN: a 200 GB append-only table, an index measured in megabytes.
CREATE INDEX gps_pings_brin ON gps_pings USING brin (recorded_at)
  WITH (pages_per_range = 32);

CREATE INDEX orders_meta_gin ON orders USING gin (meta jsonb_path_ops);
SELECT * FROM orders WHERE meta @> '{"channel":"mobile"}';

-- PostgreSQL has NO bitmap INDEX. It builds a bitmap at RUNTIME from
-- ordinary btree indexes — a different thing entirely:
EXPLAIN SELECT * FROM orders WHERE status = 'NEW' AND country_code = 'IR';
--  -> BitmapAnd -> Bitmap Index Scan ... , Bitmap Index Scan ...

-- Oracle: a real, persistent bitmap index — one bitmap per distinct value.
CREATE BITMAP INDEX orders_status_bmx  ON orders (status);
CREATE BITMAP INDEX orders_country_bmx ON orders (country_code);
SELECT * FROM orders WHERE status = 'NEW' AND country_code = 'IR';

-- Index-Organized Table: the table IS its primary-key B-tree.
CREATE TABLE order_items (
  order_id NUMBER, line_no NUMBER, sku VARCHAR2(64), qty NUMBER,
  CONSTRAINT pk_oi PRIMARY KEY (order_id, line_no)
) ORGANIZATION INDEX PCTTHRESHOLD 20 OVERFLOW TABLESPACE users;

-- PostgreSQL has no invisible index. Two honest options:
CREATE EXTENSION IF NOT EXISTS hypopg;
SELECT * FROM hypopg_create_index('CREATE INDEX ON orders (customer_id, status)');
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'NEW';
SELECT hypopg_reset();

-- Or build it for real without locking writers, and drop it if it disappoints:
CREATE INDEX CONCURRENTLY orders_cust_status_idx ON orders (customer_id, status);
DROP INDEX CONCURRENTLY orders_cust_status_idx;

-- Oracle: build it INVISIBLE, test it in ONE session, then publish or drop.
CREATE INDEX orders_cust_status_idx ON orders (customer_id, status) INVISIBLE ONLINE;
ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;
SELECT * FROM orders WHERE customer_id = :c AND status = :s;

ALTER INDEX orders_cust_status_idx VISIBLE;   -- happy
-- DROP INDEX orders_cust_status_idx;         -- unhappy

-- Dead-weight indexes (reset counters first, then wait weeks):
SELECT s.relname AS table_name, s.indexrelname AS index_name, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM   pg_stat_user_indexes s
JOIN   pg_index i ON i.indexrelid = s.indexrelid
WHERE  s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary
ORDER  BY pg_relation_size(s.indexrelid) DESC;

-- Oracle 12.2+: always-on index usage tracking.
SELECT name, total_access_count, total_exec_count, last_used
FROM   dba_index_usage WHERE owner = USER ORDER BY total_access_count;

-- Legacy method, still useful on older releases:
ALTER INDEX orders_cust_status_idx MONITORING USAGE;
SELECT index_name, used, start_monitoring FROM v$object_usage;

-- PostgreSQL: parameters via PREPARE / the extended protocol (JDBC, psycopg).
PREPARE find_orders (bigint, text) AS
  SELECT * FROM orders WHERE customer_id = $1 AND status = $2;
EXECUTE find_orders (42, 'NEW');

SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;

-- Control the custom-vs-generic decision (PG 12+):
SET plan_cache_mode = 'force_custom_plan';   -- re-plan with real values, always

-- Oracle: bind variables are non-negotiable in OLTP.
VARIABLE cid NUMBER
VARIABLE st  VARCHAR2(16)
EXEC :cid := 42; :st := 'NEW';
SELECT * FROM orders WHERE customer_id = :cid AND status = :st;

-- What values did the optimizer PEEK at when it built this plan?
SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST +PEEKED_BINDS'));

-- Emergency band-aid for a literal-SQL legacy app (default and correct: EXACT):
ALTER SYSTEM SET cursor_sharing = FORCE SCOPE = MEMORY;

-- Was I hit by the generic plan? Compare the two explicitly:
SET plan_cache_mode = 'force_generic_plan';
EXPLAIN (ANALYZE) EXECUTE find_orders (42, 'SHIPPED');
SET plan_cache_mode = 'force_custom_plan';
EXPLAIN (ANALYZE) EXECUTE find_orders (42, 'SHIPPED');
RESET plan_cache_mode;

-- Did adaptive cursor sharing kick in? One row per child cursor:
SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware, is_shareable,
       executions, buffer_gets, plan_hash_value
FROM   v$sql WHERE sql_id = :sid ORDER BY child_number;

-- PostgreSQL: no baselines, no stored outlines, no in-SQL hints in core.
-- The honest levers, best first:

-- 1. Fix the estimate:
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
CREATE STATISTICS orders_stat (dependencies, mcv) ON status, customer_id FROM orders;
ANALYZE orders;

-- 2. Nudge the cost model to match the hardware:
SET random_page_cost = 1.1;          -- NVMe: seeks are nearly free
SET effective_cache_size = '48GB';   -- how much OS cache the planner may assume

-- 3. Structural barrier as a last resort (PG 12+):
WITH scoped AS MATERIALIZED (
  SELECT id FROM orders WHERE status = 'NEW'
)
SELECT * FROM scoped JOIN order_items USING (id);

-- 4. pg_hint_plan (third-party extension) for genuine hints.

-- Oracle: an entire subsystem exists for exactly this.

-- 1. Hints — directives, honoured whenever legal:
SELECT /*+ LEADING(o c) USE_HASH(c) INDEX(o orders_status_placed_idx) PARALLEL(o 4) */
       o.id, c.email
FROM   orders o JOIN customers c ON c.id = o.customer_id;

-- 2. SQL Plan Baseline: capture the good plan, forbid regressions.
DECLARE n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
         sql_id => '7ftjftd9xdrsp', plan_hash_value => 3956160932,
         fixed  => 'YES', enabled => 'YES');
END;
/
SELECT sql_handle, plan_name, enabled, accepted, fixed, origin
FROM   dba_sql_plan_baselines;

-- 3. SQL Profile from the Tuning Advisor (corrective cardinality factors):
DECLARE t VARCHAR2(64);
BEGIN
  t := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '7ftjftd9xdrsp');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(t);
  DBMS_OUTPUT.PUT_LINE(DBMS_SQLTUNE.REPORT_TUNING_TASK(t));
END;
/

-- Bloat and vacuum lag, plus the HOT ratio:
SELECT relname, n_live_tup, n_dead_tup,
       ROUND(100.0*n_dead_tup/NULLIF(n_live_tup+n_dead_tup,0), 1) AS dead_pct,
       n_tup_upd, n_tup_hot_upd,
       ROUND(100.0*n_tup_hot_upd/NULLIF(n_tup_upd,0), 1)          AS hot_pct,
       last_autovacuum
FROM   pg_stat_user_tables ORDER BY n_dead_tup DESC FETCH FIRST 15 ROWS ONLY;

-- Leave room on each page so updates can stay HOT:
ALTER TABLE orders SET (fillfactor = 85);

-- Make autovacuum aggressive on a hot table:
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02,
                        autovacuum_vacuum_cost_delay   = 0);

-- Oracle: undo headroom, row migration, and the high-water mark.
SELECT tablespace_name, status, ROUND(SUM(bytes)/1024/1024) mb
FROM   dba_undo_extents GROUP BY tablespace_name, status;
SHOW PARAMETER undo_retention;
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

-- Row migration: a row grew and no longer fits its block.
ANALYZE TABLE orders LIST CHAINED ROWS INTO chained_rows;   -- a legit use of ANALYZE
SELECT COUNT(*) FROM chained_rows;

-- Oracle's "fillfactor", then reclaim space and reset the HWM:
ALTER TABLE orders PCTFREE 20;
ALTER TABLE orders ENABLE ROW MOVEMENT;
ALTER TABLE orders SHRINK SPACE COMPACT;
ALTER TABLE orders SHRINK SPACE;

-- A sane starting point for a dedicated 64 GB server — then MEASURE.
ALTER SYSTEM SET shared_buffers            = '16GB';
ALTER SYSTEM SET effective_cache_size      = '48GB';
ALTER SYSTEM SET work_mem                  = '32MB';   -- low! raise per session
ALTER SYSTEM SET maintenance_work_mem      = '2GB';
ALTER SYSTEM SET random_page_cost          = 1.1;      -- SSD/NVMe
ALTER SYSTEM SET effective_io_concurrency  = 200;
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
SELECT pg_reload_conf();

-- Size the pools, then let Oracle's own advisors tell you if it helped.
ALTER SYSTEM SET sga_target           = 16G SCOPE = SPFILE;
ALTER SYSTEM SET pga_aggregate_target = 8G  SCOPE = SPFILE;
ALTER SYSTEM SET pga_aggregate_limit  = 16G SCOPE = SPFILE;

SELECT size_for_estimate AS cache_mb, estd_physical_read_factor
FROM   v$db_cache_advice WHERE name = 'DEFAULT' AND block_size = 8192
ORDER  BY size_for_estimate;

SELECT pga_target_for_estimate/1024/1024 AS pga_mb,
       estd_pga_cache_hit_percentage, estd_overalloc_count
FROM   v$pga_target_advice ORDER BY 1;

-- Teach the CBO what your storage really costs:
BEGIN DBMS_STATS.GATHER_SYSTEM_STATS('INTERVAL', interval => 60); END;
/

-- Declarative partitioning (PG 10+), range by month.
CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY,
  customer_id bigint        NOT NULL,
  status      text          NOT NULL,
  total       numeric(12,2) NOT NULL,
  placed_at   timestamptz   NOT NULL
) PARTITION BY RANGE (placed_at);

CREATE TABLE orders_2026_07 PARTITION OF orders
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

-- Define the index once on the parent; PostgreSQL creates one per partition.
CREATE INDEX ON orders (customer_id, status);

-- Drop a month instantly, no row-by-row DELETE:
ALTER TABLE orders DETACH PARTITION orders_2026_07 CONCURRENTLY;
DROP TABLE orders_2026_07;

SET enable_partitionwise_join = on;
SET enable_partitionwise_aggregate = on;

-- Oracle: INTERVAL partitioning creates the monthly partitions for you.
CREATE TABLE orders (
  id          NUMBER GENERATED ALWAYS AS IDENTITY,
  customer_id NUMBER       NOT NULL,
  status      VARCHAR2(16) NOT NULL,
  total       NUMBER(12,2) NOT NULL,
  placed_at   DATE         NOT NULL
)
PARTITION BY RANGE (placed_at)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION orders_p0 VALUES LESS THAN (DATE '2026-07-01') );

-- LOCAL index = one segment per partition (usually what you want).
CREATE INDEX orders_cust_status_idx ON orders (customer_id, status) LOCAL;
-- GLOBAL index for a unique key that excludes the partition key:
CREATE UNIQUE INDEX orders_pk ON orders (id) GLOBAL;

ALTER TABLE orders DROP PARTITION orders_p0 UPDATE GLOBAL INDEXES;

-- Swap a fully-loaded staging table in, in milliseconds:
ALTER TABLE orders EXCHANGE PARTITION orders_p1
  WITH TABLE orders_stage INCLUDING INDEXES WITHOUT VALIDATION;

-- PostgreSQL: the planner decides; you set the ceilings.
SET max_parallel_workers_per_gather = 4;
SET min_parallel_table_scan_size = '8MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT status, count(*), sum(total) FROM orders GROUP BY status;
--  Finalize GroupAggregate -> Gather Merge -> Partial GroupAggregate
--  Workers Planned: 4   Workers Launched: 4     <- compare these two!

ALTER TABLE orders SET (parallel_workers = 8);

-- Oracle: degree of parallelism by hint, by table, or automatic.
SELECT /*+ PARALLEL(o, 8) */ status, COUNT(*), SUM(total)
FROM   orders o GROUP BY status;

ALTER TABLE orders PARALLEL 8;
ALTER SESSION SET parallel_degree_policy = AUTO;
ALTER SESSION SET parallel_min_time_threshold = 10;   -- seconds

-- Did it go parallel, and was it downgraded?
SELECT sql_id, px_servers_requested, px_servers_allocated, status
FROM   v$sql_monitor WHERE px_servers_requested > 0;

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE gps_pings (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  vehicle_id  bigint      NOT NULL,
  recorded_at timestamptz NOT NULL,
  location    geography(Point, 4326) NOT NULL
);
CREATE INDEX gps_pings_loc_gix ON gps_pings USING gist (location);
CREATE INDEX gps_pings_veh_time_idx ON gps_pings (vehicle_id, recorded_at DESC);

-- SARGABLE proximity: ST_DWithin uses the GiST index.
SELECT vehicle_id, recorded_at
FROM   gps_pings
WHERE  ST_DWithin(location, ST_MakePoint(51.389, 35.689)::geography, 500)  -- metres
  AND  recorded_at > now() - interval '5 minutes';

-- NOT sargable: a distance function computed for every row.
-- WHERE ST_Distance(location, ST_MakePoint(51.389,35.689)::geography) < 500

-- K-nearest-neighbour via the index-assisted <-> operator:
SELECT vehicle_id, location <-> ST_MakePoint(51.389, 35.689)::geography AS m
FROM   gps_pings
ORDER  BY location <-> ST_MakePoint(51.389, 35.689)::geography
FETCH FIRST 5 ROWS ONLY;

CREATE TABLE gps_pings (
  id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  vehicle_id  NUMBER    NOT NULL,
  recorded_at TIMESTAMP NOT NULL,
  location    SDO_GEOMETRY NOT NULL
);

-- The metadata row is MANDATORY before the spatial index can be created:
INSERT INTO user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
VALUES ('GPS_PINGS', 'LOCATION',
        SDO_DIM_ARRAY(SDO_DIM_ELEMENT('LONG', -180, 180, 0.05),
                      SDO_DIM_ELEMENT('LAT',   -90,  90, 0.05)), 4326);
COMMIT;

CREATE INDEX gps_pings_loc_sidx ON gps_pings (location)
  INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;
CREATE INDEX gps_pings_veh_time_idx ON gps_pings (vehicle_id, recorded_at DESC);

-- SARGABLE proximity: the operator form goes through the spatial index.
SELECT vehicle_id, recorded_at
FROM   gps_pings
WHERE  SDO_WITHIN_DISTANCE(location,
         SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(51.389, 35.689, NULL), NULL, NULL),
         'distance=500 unit=M') = 'TRUE'
  AND  recorded_at > SYSTIMESTAMP - INTERVAL '5' MINUTE;

-- NOT sargable: SDO_GEOM.SDO_DISTANCE(...) < 500 in the WHERE clause.

-- K-nearest-neighbour:
SELECT vehicle_id, SDO_NN_DISTANCE(1) AS m
FROM   gps_pings
WHERE  SDO_NN(location,
         SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(51.389, 35.689, NULL), NULL, NULL),
         'sdo_num_res=5', 1) = 'TRUE'
ORDER  BY m;

-- PL/pgSQL runs inside the same backend process, so there is no SQL/PL
-- context switch to amortise. The win comes purely from being SET-BASED.
CREATE OR REPLACE FUNCTION mark_stale_orders(p_days int)
RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE n bigint;
BEGIN
  -- SLOW: FOR r IN SELECT id FROM orders ... LOOP UPDATE ... END LOOP;
  -- FAST: one statement, one plan, one pass.
  UPDATE orders
     SET status = 'STALE'
   WHERE status = 'NEW'
     AND placed_at < now() - make_interval(days => p_days);
  GET DIAGNOSTICS n = ROW_COUNT;
  RETURN n;
END;
$$;

-- Chunk huge deletes so one transaction does not bloat the table:
DELETE FROM orders
WHERE id IN (SELECT id FROM orders WHERE status = 'CANCELLED'
             FETCH FIRST 10000 ROWS ONLY);

-- PL/SQL runs in a separate engine: every loop iteration costs a context
-- switch. Best is still set-based; second best is BULK COLLECT + FORALL.
CREATE OR REPLACE FUNCTION mark_stale_orders(p_days NUMBER) RETURN NUMBER IS
  TYPE id_tab IS TABLE OF orders.id%TYPE;
  v_ids id_tab;
  v_cnt NUMBER := 0;
  CURSOR c IS SELECT id FROM orders
              WHERE status = 'NEW' AND placed_at < SYSDATE - p_days;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_ids LIMIT 1000;   -- LIMIT is mandatory: PGA!
    EXIT WHEN v_ids.COUNT = 0;
    FORALL i IN 1 .. v_ids.COUNT
      UPDATE orders SET status = 'STALE' WHERE id = v_ids(i);
    v_cnt := v_cnt + SQL%ROWCOUNT;
    COMMIT;
  END LOOP;
  CLOSE c;
  RETURN v_cnt;
END;
/

-- BAD: the index on placed_at is dead.
SELECT * FROM orders WHERE date_trunc('day', placed_at) = DATE '2026-07-29';

-- GOOD: a sargable half-open range.
SELECT * FROM orders
WHERE  placed_at >= DATE '2026-07-29' AND placed_at < DATE '2026-07-30';

-- Or, if the expression is unavoidable, index the expression:
CREATE INDEX orders_day_idx ON orders (date_trunc('day', placed_at));

-- BAD: TRUNC() hides the column from the index.
SELECT * FROM orders WHERE TRUNC(placed_at) = DATE '2026-07-29';

-- GOOD: a sargable half-open range.
SELECT * FROM orders
WHERE  placed_at >= DATE '2026-07-29' AND placed_at < DATE '2026-07-30';

-- Or a function-based index — and gather stats on the hidden column!
CREATE INDEX orders_day_idx ON orders (TRUNC(placed_at));
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS',
    method_opt => 'FOR ALL HIDDEN COLUMNS SIZE AUTO');
END;
/

-- BAD: page 5000 counts and discards 100,000 rows.
SELECT * FROM orders ORDER BY placed_at DESC, id DESC OFFSET 100000 LIMIT 20;

-- GOOD: keyset ("seek") pagination — constant time on any page.
SELECT * FROM orders
WHERE  (placed_at, id) < (:last_placed_at, :last_id)
ORDER  BY placed_at DESC, id DESC
FETCH FIRST 20 ROWS ONLY;

-- BAD: same problem with the row-limiting clause (12c+).
SELECT * FROM orders ORDER BY placed_at DESC, id DESC
OFFSET 100000 ROWS FETCH NEXT 20 ROWS ONLY;

-- GOOD: keyset pagination, written without row-value syntax for clarity.
SELECT * FROM orders
WHERE  placed_at < :last_placed_at
   OR (placed_at = :last_placed_at AND id < :last_id)
ORDER  BY placed_at DESC, id DESC
FETCH FIRST 20 ROWS ONLY;

-- TRAP: one NULL from the subquery makes the whole result empty.
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

-- SAFE and usually far faster (a real anti-join):
SELECT c.* FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Identical trap, identical three-valued-logic cause.
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

-- SAFE anti-join:
SELECT c.* FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);