Official TuningSQL.com Blog | Oracle Database SQL, Tuning, Features, and Toubleshooting
Skip to content
  • TuningSQL.com
  • TuningSQL.com
Official TuningSQL.com Blog

Official TuningSQL.com Blog

Oracle Database SQL, Tuning, Features, and Toubleshooting

Finding mod(power(x,y)) for Larger Values

May 27, 2022May 27, 2022 Kaley Crum

The following piece of Pl/SQL uses a clever bit of math to be able to raise a number to a very large power without having overflow problems. Suppose, for example, you wanted to calculate 2318 % 367 (2 to the… Read More

Oracle, Pl/SQLLeave a comment

Writing Efficient Pl/SQL is Hard

September 26, 2021September 26, 2021 Kaley Crum

In the databases that I currently manage, PL/SQL functions are used everywhere.  Honestly, one of the things I do when I try to get serious about tuning a SQL query is to (if it can be easily done) eliminate any… Read More

UncategorizedLeave a comment

Selecting From a View: ORA-01031: insufficient privileges

February 10, 2021February 10, 2021 Kaley Crum

If you’re selecting from a view in a different schema, and you’re encountering ORA-01031: insufficient privileges, this is probably why: You’re logged in under schema “A” You’re trying to select from a view owned by schema “B.” The view in… Read More

Oracle, PrivilegesLeave a comment

Calculating Scalar Subquery Cache Size, Part 2

January 23, 2021February 3, 2021 Kaley Crum

I’ve written earlier about how to calculate a query’s scalar subquery cache size, and I’ve had an interesting thought since the last blog entry. The last entry was all about scalar subqueries that appear in a query’s select clause, but… Read More

Caching, Oracle, Tuning/EfficiencyLeave a comment

How to Migrate a Baseline From One Database to Another

January 19, 2021January 19, 2021 Kaley Crum

If you have a query that performs well in in one environment (such as dev) but performs poorly in a different environment (such as prod), it might be because Oracle selects a good plan in dev, and a bad plan… Read More

Baselines, Oracle, SQL Plan ManagementLeave a comment

How to Get A Query’s Execution Plan (With All The Details)

January 10, 2021January 10, 2021 Kaley Crum

In Oracle, if you want to view an execution plan for a query, you use the DBMS_XPLAN.DISPLAY_CURSOR() procedure. NOTE: The execution plan is totally different than an explain plan; don’t confuse these two. The easiest way to get an execution… Read More

Execution Plans, Oracle, Tuning/Efficiencyexecution plan, execution plansLeave a comment

Using DBMS_UTILITY.EXPAND_SQL_TEXT

January 9, 2021January 9, 2021 Kaley Crum

If you have a query that contains views, or a query where the columns are not all qualified, the DBMS_UTILITY.EXPAND_SQL_TEXT function might be useful. The DBMS_UTILITY.EXPAND_SQL_TEXT function shows you what your query looks like without the views, and with all… Read More

DBMS_UTILITY, Oracle, Tuning/Efficiency, Writing Queries for Performancedbms_utility, expand_sql_textLeave a comment

How to Get Row Source Statistics In Oracle

January 7, 2021January 7, 2021 Kaley Crum

One great way to get time analysis information for a query in Oracle is to use row source statistics. Not everyone has the tuning and diagnostic pack, meaning not everyone can generate SQL Monitor reports. Row source statistics serves as… Read More

Execution Plans, Oracle, Row Source Statistics, Tuning/EfficiencyLeave a comment

How to Create a 10046 Trace File in Oracle

January 6, 2021January 6, 2021 Kaley Crum

This article will show you how to create a 10046 trace file in Oracle. There are multiple different ways of creating a SQL trace file, including doing an alter session set sql_trace = true but if you want the most… Read More

Oracle, Tuning/Efficiency10046 trace file, oracle trace file, wait event trace fileLeave a comment

Using PRAGMA AUTONOMOUS_TRANSACTION in a Function

November 19, 2020November 19, 2020 Kaley Crum

Here’s a quick example of how to use the AUTONOMOUS_TRANSACTION pragma in a function: If you use the AUTONOMOUS_TRANSACTION pragma in Pl/SQL, it creates a separate transaction from the transaction you are currently working in. Because we included it in… Read More

Oracle, Pl/SQL, PragmasLeave a comment

Posts navigation

Older posts

FREE Sql Tuning Tips!

Get FREE tips and tricks for FASTER Oracle SQL queries delivered directly to your email inbox!

Just a minute...

Awesome! You're subscribed!!

Recent Posts

  • Finding mod(power(x,y)) for Larger Values
  • Writing Efficient Pl/SQL is Hard
  • Selecting From a View: ORA-01031: insufficient privileges
  • Calculating Scalar Subquery Cache Size, Part 2
  • How to Migrate a Baseline From One Database to Another

Categories

  • Approximate Query Processing
  • Arrays
  • Autonomous
  • Baselines
  • Bind Variables
  • Caching
  • Certification
  • Creating Users
  • Cursors
  • Custom Aggregation
  • Data Dictionary Views
  • Data Types
  • Database Objects
  • Day to Second Interval
  • DBMS_METADATA
  • DBMS_UTILITY
  • Dirty Hacks
  • Execution Plans
  • Explain Plans
  • FORALL
  • Hash Clusters
  • Hash Joins
  • Hints
  • Keywords
  • Network Connections
  • NULL Values
  • NULL Values
  • Oracle
  • Oracle Error Messages
  • Oracle SQL Developer
  • Pl/SQL
  • Pl/SQL Compiler Flags
  • Pluggable Databases
  • Pragmas
  • Privileges
  • Problems and Troubleshooting
  • Regular Expressions
  • Row Level Security
  • Row Source Statistics
  • Scalar Subqueries
  • Scripts and Queries
  • SQL
  • SQL Monitor Reports
  • SQL Plan Management
  • Table Definition
  • Table Statistics
  • Tuning/Efficiency
  • Uncategorized
  • User Profiles
  • Users
  • Version
  • Virtual Columns
  • Wrapping Pl/SQL Code
  • Writing Queries for Performance
Copyright © TuningSQL.com All rights reserved.
Business Cast by Axle Themes