When Statistics Help with Filters
TuningSQL.com Blog
by Kaley Crum
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
Visit website
How to Make a “God User” in Oracle
TuningSQL.com Blog
by Kaley Crum
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
Visit website
View Hidden Parameters in Oracle
TuningSQL.com Blog
by Kaley Crum
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
Visit website
KUP-01005: syntax error: found “hash” (How to fix on an External Table)
TuningSQL.com Blog
by Kaley Crum
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
Visit website
Finding mod(power(x,y)) for Larger Values
TuningSQL.com Blog
by Kaley Crum
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
Visit website
Writing Efficient Pl/SQL is Hard
TuningSQL.com Blog
by Kaley Crum
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
Visit website
Selecting From a View: ORA-01031: insufficient privileges
TuningSQL.com Blog
by Kaley Crum
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
Visit website
Calculating Scalar Subquery Cache Size, Part 2
TuningSQL.com Blog
by Kaley Crum
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
Visit website
How to Migrate a Baseline From One Database to Another
TuningSQL.com Blog
by Kaley Crum
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
Visit website
How to Get A Query’s Execution Plan (With All The Details)
TuningSQL.com Blog
by Kaley Crum
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
Visit website

Follow TuningSQL.com Blog on FeedSpot

Continue with Google
Continue with Apple
OR