Oracle Sql Cheat Sheet



Home
E-mail Us
Oracle Articles
New Oracle Articles


Oracle Training
Oracle Tips

Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support


SQL Tuning
Security

Oracle UNIX
Oracle Linux
Monitoring
Remote s
upport
Remote plans
Remote
services
Application Server

Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support

Remote S
upport
Development

Implementation


Consulting Staff
Consulting Prices
Help Wanted!


Oracle Posters
Oracle Books

Oracle Scripts
Ion
Excel-DB



OraclePl/sql

Oracle Database Tips by Donald BurlesonFebruary 21, 2015

SQL Tuning Cheat sheet
by Donald K. Burleson


-- Create a function-based index
create index idx_rtrim_title on titles ( rtrim(title));


Gather stats
exec dbms_stats.gather_schema_stats('scott?);
exec dbms_stats.gather_table_stats ('scott?,?emp)
-- system statistics during peak workload period
execute dbms_stats.gather_system_stats('Start');
-- delay while you watch the workload
execute dbms_stats.gather_system_stats('Stop');

Autotrace commands
set autotrace on
set autotrace on explain
set autotrace traceonly
set autotrace traceonly explain
set autotrace traceonly statistics
Alter session commands
-- enable commands
alter system enable parallel query;
alter system enable parallel dml;
alter system enable parallel ddl;
-- change optimizer parms
alter session set optimizer_index_cost_adj=20 -- default is 100;
alter session set optimizer_index_caching=50 -- default is 0;
alter session set '_optimizer_cost_model'=io; -- default is cpu;
-- change optimizer modes
alter session set optimizer_mode=rule;
alter session set optimizer_mode=first_rows_1;
alter session set optimizer_mode=first_rows_10;
alter session set optimizer_mode=first_rows_100;
alter session set optimizer_mode=first_rows_1000;
alter session set optimizer_mode=all_rows;
Good Hints:
-- Table join order hints
select /*+ ordered */ -- join tables in order of FROM clause
select /*+ leading */ -- Driving table is 1st table in FROM clause
-- optimizer mode hints
select /*+ all_rows */ -- optimizer to minimize CPU resources
select /*+ first_rows_1 */ -- optimize to minimize response time
select /*+ first_rows_10 */ -- optimize to minimize response time
select /*+ first_rows_100 */ -- minimize response time
select /*+ first_rows_1000 */ -- minimize response time
insert /*+ append */ into. . . - use a empty data block for inserts
select /*+ dynamic_sampling(customer 4) */ - Use dynamic sampling
select /*+ cardinality( gtt, 500 ) */ -- estimate result set size
select /*+driving_site(huge_table)*/ -- In distributed SQL, largest table is the driving table
select /*+ no_cpu_costing */ -- use older i/o-based optimizer costing
-- table join exclusion hints
select /*+ no_use_hash(titles, sales) */ -- don't use a hash join
select /*+ no_use_nl(titles, sales) */ -- don't use nested loops
select /*+ no_use_merge(titles, sales) */ -- don't use merge join
select /*+ parallel (book 4) */
Bad Hints: (for testing only)
select /*+ rule */ - great for testing for bad metadata
-- table access Hints
select /*+ index (emp, emp_lname_idx) */ -- only use for testing
select /*+ full use_hash(emp, jobs) */
Table join hints:
select /*+ use_hash(titles, sales) */ -- force a hash join
select /*+ use_nl(titles, sales) */ -- force nested loops
select /*+ use_merge(titles, sales) */ -- force merge join

Get the Complete
Oracle SQL Tuning Information

The landmark book 'Advanced Oracle SQL Tuning The Definitive Reference' is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.
Get the Complete
Oracle SQL Tuning Information

The landmark book 'Advanced Oracle SQL Tuning The Definitive Reference' is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

Burleson is the American Team

Note:This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum.

Verify experience!Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just and include the URL for the page.




Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning


Copyright © 1996 - 2020

All rights reserved by Burleson

Oracle® is the registered trademark of Oracle Corporation.

SqlOracle Sql Cheat Sheet
  • Oracle SQL Tuning Quick Reference cheatsheet. Oracle Database Tips by Donald BurlesonFebruary 21, 2015. SQL Tuning Cheat sheet by Donald K.
  • SQL set timing on SQL set autotrace on SQL explain plan for select blah. Index Statistics. Check the time table statistics were last updated. Select tablename, lastanalyzed from usertables. Check the time index statistics were last updated. Select indexname, lastanalyzed from userindexes.
  • SQL AGGREGATE FUNCTIONS AVG returns the average of a list CREATE TEMPORARY VIEW v AS SELECT c1, c2 FROM t; Create a temporary view WHEN. BEFORE –invoke before the event occurs. AFTER –invoke after the event occurs EVENT. INSERT –invoke for INSERT. UPDATE –invoke for UPDATE. DELETE –invoke for DELETE TRIGGERTYPE. FOR.
Sheet

The SQL cheat sheet commands can be used in any IDE or tool where the user has connected to the database using the JAR file of the database type. The different databases existing in the market are Oracle, Microsoft SQL Server, IBM DB2, etc., which all these can be connected to by using their respective jars and tools to manage the data operations. Oracle 12c For Dummies Cheat Sheet By Chris Ruel, Michael Wessler Knowing the most common tasks an Oracle Database Administrator (DBA) encounters and what a DBA needs to monitor daily can help keep your database running and well-maintained. Master your common command-line executables to develop your DBA expertise.

Oracle Pl Sql Cheat Sheet



��