skills.rest
ZzZueszZ
ZzZueszZCOMMUNITY
Explore all skills by this author →
GitHub

Oracle Performance Tuning

Optimizes Oracle Database queries through indexing, execution plans, partitioning, and JPA tuning.

Updated May 12, 2026
Search Tags:#query optimization#indexing#oracle#partitioning#performance tuning#execution plan#jpa
One-click install
npx skills add https://github.com/ZzZueszZ/claude-kit --skill oracle-performance-tuning-zzzueszz
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: Oracle Performance Tuning
Source: https://github.com/ZzZueszZ/claude-kit/tree/main/.claude/skills/oracle-performance
Command: npx skills add https://github.com/ZzZueszZ/claude-kit --skill oracle-performance-tuning-zzzueszz

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Slow Oracle Database queries and poorly designed indexes degrade Spring Boot application performance, and diagnosing the root cause requires deep knowledge of execution plans, index strategies, and JPA behavior. ## Core Features & Use Cases - Indexing Strategy: Rules for B-Tree, composite, unique, function-based, and partial indexes, including column ordering for composite indexes (equality → range → order). - Execution Plan Analysis: Guidance on reading EXPLAIN PLAN output and identifying problematic operations like TABLE ACCESS FULL on large tables. - Query & JPA Optimization: Rewriting IN to EXISTS, avoiding functions on indexed columns, keyset pagination, Hibernate batch configuration, and eliminating N+1 problems with JOIN FETCH and @EntityGraph. - Use Case: A Spring Boot endpoint loading paginated citizen messages is slow; apply keyset pagination with a composite index on (postbox_id, received_at DESC, id DESC) and verify the fix via the execution plan. ## Quick Start Ask the AI to review a slow Oracle query or JPA repository method and propose indexes and query rewrites following this tuning guide.

Frequently Asked Questions about Oracle Performance Tuning

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I optimize a slow Oracle query in a Spring Boot application?▼

Start by running EXPLAIN PLAN and reading the output with DBMS_XPLAN.DISPLAY to find TABLE ACCESS FULL operations on large tables. Then add indexes on WHERE, JOIN, and ORDER BY columns, rewrite IN subqueries as EXISTS, and verify the plan uses INDEX RANGE SCAN.

How should I order columns in a composite Oracle index?▼

Place equality columns (=) first, range columns (>, <, BETWEEN) in the middle, and ORDER BY columns last. This ordering lets Oracle satisfy the most selective predicates first and can eliminate a separate sort operation.

How do I fix the N+1 problem in Hibernate with Oracle?▼

Use JOIN FETCH in JPQL queries, @EntityGraph on repository methods, or @BatchSize on collections to load associations in batches. Also configure hibernate.default_batch_fetch_size and jdbc batch_size in application.yaml.

When should I use keyset pagination instead of OFFSET in Oracle?▼

Use keyset pagination for large datasets or infinite scroll, because OFFSET forces Oracle to read and discard all skipped rows. Keyset pagination filters on the last seen row values and works with a matching composite index.

When should Oracle table partitioning be used?▼

Partition tables larger than about 10 million rows, typically by range on a timestamp column such as received_at. Oracle 12c and later supports interval partitioning to create new partitions automatically.

Are Oracle hints a good way to fix query performance?▼

Hints like INDEX, PARALLEL, and USE_HASH should be a last resort. Prefer creating correct indexes and writing well-structured queries first, since hints can become stale or harmful as data statistics change.

Related Skills

Explore More Software Engineering Skills→
yangqiongyangqiong

Database Schema Design Skill

Design normalized PostgreSQL schemas with indexes and constraints.

Community
Intermediate
OpenLaborOpenLabor
4

Database Optimization

OptimizeSlow database queries by tuning SQL performance and adding indexes.

Official
Intermediate
partme-aipartme-ai
610

oracle

Provide guidance for Oracle SQL, PL/SQL, and database administration.

Official
Intermediate
HainrixzHainrixz
18

design

Recommend database engines and generate initial schema migrations for greenfield projects.

Community
Advanced