TuningSQL.com Blog
34 FOLLOWERS
TuningSQL.com Blog provides the latest news, information, discussion tips & tricks on Oracle Database SQL, Tuning, Features, and Toubleshooting
TuningSQL.com Blog
4M ago
Here’s a fun little experiment (I’m using Oracle 19.0 for this). First, let’s start off by creating a couple of tables. Cool! Now that we got our tables, let’s join them in a query and look a the plan. Here’s… Read More
The post When Statistics Help with Filters appeared first on Official TuningSQL.com Blog ..read more
TuningSQL.com Blog
2y ago
This article details how to make a “god user” in the database. Creating a god user isn’t something you want to do with your production databases. Generally, you want to follow the principle of least privilege. In other words, only grant the permissions that a user needs. This prevents someone from doing accidental damage (e.g. accidentally dropping a column). Also, if someone compromises your account, it limits the damage an attacker can do.
The following script will create a user if the user doesn’t already exist. If the user does exist, this script will convert them into a “god user.” The sc ..read more
TuningSQL.com Blog
2y ago
Occasionally I need to view hidden parameters in Oracle.
There are plenty of queries and scripts that already do this out there. But these queries have to be run as SYS. So when I need to view hidden parameters, I often find myself having to use SQL*Plus to do this.
Running queries on SQL*Plus is a bit of an ordeal, because then you have to worry about formatting the results of your query to fit your console window.
Here is a query that formats everything to fit nicely in my full-screen PuTTy window in SQL*Plus:
set linesize 400
set pagesize 10000
col parameter_name for a64
col parameter_desc ..read more
TuningSQL.com Blog
2y ago
In this article, I’ll be discussing how to fix the “KUP-01005: syntax error: found "hash"” error when selecting from an external table. Here’s the error in its entirety:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "hash": expecting one of: "binary_double, binary_float, comma, char, date, defaultif, decimal, double, float, integer, (, lls, lls_compat, no, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw, varcharc ..read more
TuningSQL.com Blog
2y ago
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 318th power, mod 367).
SQL ought to be able to handle this computation, since the answer will only be between 1 and 366 inclusive.
But it runs into issues when we try to raise 2 to the 318th power:
Oracle starts losing precision with large numbers
Oracle is unable to handle really, really large numbers with precision, so we get a bunch of trailing zeros at the end of our number.
So if we t ..read more
TuningSQL.com Blog
2y ago
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 PL/SQL functions and try to re-write them as “plain SQL” expressions.
Example Exercise
I’m using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production for this example.
Let’s start with an EMPLOYEES table.
create table employees
(
employee_id constraint pk_employees primary key
, employee_name
, employee_salary
)
as
-- Using the "generator" techniqu ..read more
TuningSQL.com Blog
2y ago
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 schema “B” contains tables owned by schema “C.”
Schema “B” has SELECT privileges on the tables owned by schema “C” but not SELECT…WITH GRANT OPTION.
You can fix the error by using the query below to figure out which grants you need.
CAUTION: The below query is a bit like killing a fly with a sledgehammer, because it lists ALL the grants needed. Think befor ..read more
TuningSQL.com Blog
2y ago
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 the same mechanism exists for an unnested subquery that uses a FILTER operation.
My thought was this–If I have a query that uses 1 table within a FILTER operation, I’d have 1024 buckets (same as what we had with the scalar subquery cache size) but…occasionally, a FILTER operation will call multiple tables. My thought is–if I have more than one table in my scalar ..read more
TuningSQL.com Blog
2y ago
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 in prod, and it might be necessary to migrate a baseline for the good plan to prod.
That’s not the *only* reason why a plan might perform well in one environment vs another…for example, a dev database might have a smaller amount of data; therefore, queries might run faster. But if you want to try to get an apples-to-apples comparison, one way would be to capture a baseline from the environ ..read more
TuningSQL.com Blog
2y ago
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 plan is to do the following.
If you’re using SQL*Plus as your database client, you’ll first need to do a SET SERVEROUTPUT OFF. This is also true if you have a database client that emulates SQL*Plus, such as Toad or SQL Developer. By setting the server output off, it makes it easy to capture the last-run-query’s execution plan without serveroutput interferi ..read more