Tuesday, October 29, 2024
Something fun to do, a word search!!
Friday, September 13, 2024
Cloud World 24 - done!
I had a good time at Cloud World, except for leaving my iPhone in the Uber car Tuesday morning. That was not fun! Took quite awhile and a lot of effort to get it back. I don't recommend doing that.
The presentation went very well. There were over 350 folk register for the presentation, I'm not sure how many showed up. Likely approaching 200, defiantly serious overflow for the area we had for the presentation.
It was great to catch up with a few friends, I know more folks were there I wanted to see but it was such a huge event it was hard to find folks.
Here are a few fun photos.
Tuesday, August 13, 2024
Cloud World 2024
Hey! I’ll be presenting at Cloud World this year! Woot-woot! I think the last time I was at this event it was still called Open World and it was still in San Francisco. Yea been a while.
Improve Custom BIP Reports: from Hours to Minutes [THR2938]
Wednesday, Sep 11, 1:50 PM - 2:10 PM PDT
And yes, I’ll get to talk about my favorite topic, CTEs!!
If you happen to be going to this spectacular event, stop by and say Hi!
Friday, April 12, 2024
EXISTS vs EQUALITY - sand in the gears
The best thing about doing SQL Optimization is there is always something that needs attention.
FROM big_tab bigtab
WHERE 1 = ( SELECT 1 FROM allusers_tab alluserstab
WHERE bigtab.owner = alluserstab.username
and rownum = 1 );
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | TABLE ACCESS STORAGE FULL| BIG_TAB |
|* 3 | COUNT STOPKEY | |
|* 4 | INDEX UNIQUE SCAN | USERNAME_PK |
--------------------------------------------------
FROM big_tab bigtab
WHERE EXISTS ( SELECT 1 FROM allusers_tab alluserstab
WHERE bigtab.owner = alluserstab.username
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
| 2 | INDEX FULL SCAN | USERNAME_PK |
| 3 | TABLE ACCESS STORAGE FULL| BIG_TAB |
--------------------------------------------------
Doing the Equality check 1000 times:
Times in hundredths of a second
**** TIME - 25195
**** CPU - 24653
Doing the EXISTS (without ROWNUM=1) 1000 times:
**** TIME - 20090
**** CPU - 19790
= = = = = = = = = = = = = = = = = = = = = = = = = = = = =
DECLARE
l_start_time pls_integer;
l_start_cpu pls_integer;
Cursor test_cur01 is
select count(*) from (
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM big_tab bigtab
WHERE 1 = ( SELECT 1 FROM allusers_tab alluserstab
WHERE bigtab.owner = alluserstab.username
and rownum = 1 ) );
Cursor test_cur02 is
select count(*) from (SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM big_tab bigtab
WHERE Exists ( SELECT 1 FROM allusers_tab alluserstab
WHERE bigtab.owner = alluserstab.username));
cur01_rec test_cur01%rowtype;
cur02_rec test_cur02%rowtype;
BEGIN
l_start_time := DBMS_UTILITY.GET_TIME;
l_start_cpu := DBMS_UTILITY.GET_CPU_TIME;
for i in 1 .. 1000 loop
open test_cur01;
fetch test_cur01 into cur01_rec;
close test_cur01;
end loop;
DBMS_OUTPUT.put_line ('Times in hundredths of a second');
DBMS_OUTPUT.put_line ('**** TIME - '||to_char(DBMS_UTILITY.get_time - l_start_time));
DBMS_OUTPUT.put_line ('**** CPU - '||to_char(DBMS_UTILITY.GET_CPU_TIME - l_start_cpu));
l_start_time := DBMS_UTILITY.GET_TIME;
l_start_cpu := DBMS_UTILITY.GET_CPU_TIME;
for i in 1 .. 1000 loop
open test_cur02;
fetch test_cur02 into cur02_rec;
close test_cur02;
end loop;
DBMS_OUTPUT.put_line ('Times in hundredths of a second');
DBMS_OUTPUT.put_line ('**** TIME - '||to_char(DBMS_UTILITY.get_time - l_start_time));
DBMS_OUTPUT.put_line ('**** CPU - '||to_char(DBMS_UTILITY.GET_CPU_TIME - l_start_cpu));
END;
/
Wednesday, January 17, 2024
A misunderstanding about the Materialize hint
I think I have found out why some folks are resisting using this hint when defining a CTE (Common Table Expression). There apparently is a misunderstanding about what this hint does.
This doesn’t cause the CTE to be kept in memory (the PGA) after the query finishes. This only forces the optimizer to not “merge” it back into the main query. A materialized CTE will store the results set in a GTT (Global Temp Table) like structure during the run of the statement. This GTT is dropped once the query finishes.
I say again, the GTT is dropped and is not kept in the PGA once the query finishes.
So, what does the hint do and why do I recommend using it pretty much all the time?
What it does is force the optimizer to keep the CTE as a separate unit and not merge (refactor) it back into the main query. Generally merging the CTE back into the query is not what we want to have happen. We just spent all this time and effort to write the code with a CTE and then the optimizer negates all that work.
I can count on one hand the number of times that the materialize hint made a query worse. Sometimes it doesn’t matter, the optimizer will materialize a CTE with or without the hint. For example, if the CTE is referenced more than one time, the optimizer will automatically materialize the CTE.
One more time, with feeling, the GTT used during the run of a query for a materialized CTE is DROPPED after the statement finishes.
I have several blog posts raving about all the wonderful things a CTE can do to make queries run better faster and stronger. Please see these for ideas on how you can use them to speed up your code.
Tuesday, December 26, 2023
CTEs and UNIONs, Wrap it up!
CTEs (Common Table Expressions) can solve a lot of performance issues. Maybe not all of them, but a majority of them.
I ran into a situation recently that was quite frustrating. A customer I was working with had created a rather large and complex CTE that was 3 select statements with the UNION ALL operation pulling them together. I’m a huge fan of the /*+ MATERIALIZE */ hint for CTEs as this will keep the CTE as a separate entity. Using this hint more times than not is best for performance.
But this CTE wouldn’t get materialized. It was in effect ignoring the hint.
Turns out a CTE using UNION or UNION ALL will do this. I didn’t find any official documentation on this, given that the MATERIALIZE hint is really undocumented anyway, this is not a surprise.
A solution I found was to “wrap it” in another select.
Here is an example query to illustrate the technique. Yes, this is a silly query but it works for showing what I’m talking about.
WITH loc_cte AS (
SELECT /*+ MATERIALIZE */
ename, sal, dname
FROM
emp, dept
WHERE
emp.deptno = dept.deptno
AND dept.loc = 'NEW YORK'
UNION
SELECT
ename, sal, dname
FROM
emp, dept
WHERE
emp.deptno = dept.deptno
AND dept.loc = 'DALLAS'
UNION
SELECT
ename, sal, dname
FROM
emp, dept
WHERE
emp.deptno = dept.deptno
AND dept.loc = 'CHICAGO'
)
SELECT ename, sal, dname FROM loc_cte
ORDER BY sal, ename;
For this one the explain plan looks like below. Notice there is not a materialized object for the CTE, the main query and the CTE have been merged together. There are remnants of the CTE in here, but key is that the CTE is not being materialized. (The focus here is not what the optimizer did when it transformed the query. Maybe a topic for another day.)
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 348 | 00:00:01 |
| 1 | SORT ORDER BY | | 12 | 348 | 00:00:01 |
| 2 | VIEW | | 12 | 348 | 00:00:01 |
| 3 | SORT UNIQUE | | 12 | 414 | 00:00:01 |
| 4 | UNION-ALL | | | | |
| 5 | NESTED LOOPS | | 3 | 99 | 00:00:01 |
| 6 | NESTED LOOPS | | 5 | 99 | 00:00:01 |
|* 7 | TABLE ACCESS STORAGE FULL | DEPT | 1 | 20 | 00:00:01 |
|* 8 | INDEX RANGE SCAN | EMP_DEPT_IDX | 5 | | 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | EMP | 3 | 39 | 00:00:01 |
| 10 | NESTED LOOPS | | 9 | 315 | 00:00:01 |
| 11 | NESTED LOOPS | | 10 | 315 | 00:00:01 |
| 12 | VIEW | VW_JF_SET$5E11AB46 | 2 | 44 | 00:00:01 |
| 13 | SORT UNIQUE | | 2 | 40 | 00:00:01 |
| 14 | UNION-ALL | | | | |
|* 15 | TABLE ACCESS STORAGE FULL| DEPT | 1 | 20 | 00:00:01 |
|* 16 | TABLE ACCESS STORAGE FULL| DEPT | 1 | 20 | 00:00:01 |
|* 17 | INDEX RANGE SCAN | EMP_DEPT_IDX | 5 | | 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | EMP | 4 | 52 | 00:00:01 |
--------------------------------------------------------------------------------------------
Now the change to the plan is to wrap the CTE in a select. I’d recommend you don’t use SELECT * for the wrapping select. I’m not a fan of this syntax as it can cause problems.
WITH loc_cte AS (
select /*+ MATERIALIZE */ ename, sal, dname from (
SELECT
ename, sal, dname
FROM
emp, dept
WHERE
emp.deptno = dept.deptno
AND dept.loc = 'NEW YORK'
UNION
SELECT
ename, sal, dname
FROM
emp, dept
WHERE
emp.deptno = dept.deptno
AND dept.loc = 'DALLAS'
UNION
SELECT
ename, sal, dname
FROM
emp, dept
WHERE
emp.deptno = dept.deptno
AND dept.loc = 'CHICAGO'
))
SELECT ename, sal, dname FROM loc_cte
ORDER BY sal, ename;
And now the plan shows the CTE staying as a separate entity (it’s been materialized, Beam me up Scotty!).
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 348 | 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_3FD9E6966_7A9C9340 | | | |
| 3 | VIEW | | 12 | 348 | 00:00:01 |
| 4 | SORT UNIQUE | | 12 | 828 | 00:00:01 |
| 5 | UNION-ALL | | | | |
| 6 | NESTED LOOPS | | 3 | 99 | 00:00:01 |
| 7 | NESTED LOOPS | | 5 | 99 | 00:00:01 |
|* 8 | TABLE ACCESS STORAGE FULL | DEPT | 1 | 20 | 00:00:01 |
|* 9 | INDEX RANGE SCAN | EMP_DEPT_IDX | 5 | | 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | EMP | 3 | 39 | 00:00:01 |
| 11 | NESTED LOOPS | | 9 | 315 | 00:00:01 |
| 12 | NESTED LOOPS | | 10 | 315 | 00:00:01 |
| 13 | VIEW | VW_JF_SET$B1ADC4B5 | 2 | 44 | 00:00:01 |
| 14 | SORT UNIQUE | | 2 | 80 | 00:00:01 |
| 15 | UNION-ALL | | | | |
|* 16 | TABLE ACCESS STORAGE FULL | DEPT | 1 | 20 | 00:00:01 |
|* 17 | TABLE ACCESS STORAGE FULL | DEPT | 1 | 20 | 00:00:01 |
|* 18 | INDEX RANGE SCAN | EMP_DEPT_IDX | 5 | | 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID | EMP | 4 | 52 | 00:00:01 |
| 20 | SORT ORDER BY | | 12 | 348 | 00:00:01 |
| 21 | VIEW | | 12 | 348 | 00:00:01 |
| 22 | TABLE ACCESS STORAGE FULL | SYS_TEMP_3FD9E6966_7A9C9340 | 12 | 348 | 00:00:01 |
-----------------------------------------------------------------------------------------------------------
And there you have it! Here is the full script if you’d like to run it on your own. Enjoy!
explain plan for
WITH loc_cte AS (
SELECT /*+ MATERIALIZE */
ename, sal, dname
FROM
emp, dept
WHERE
emp.deptno = dept.deptno
AND dept.loc = 'NEW YORK'
UNION
SELECT
ename, sal, dname
FROM
emp, dept
WHERE
emp.deptno = dept.deptno
AND dept.loc = 'DALLAS'
UNION
SELECT
ename, sal, dname
FROM
emp, dept
WHERE
emp.deptno = dept.deptno
AND dept.loc = 'CHICAGO'
)
SELECT ename, sal, dname FROM loc_cte
ORDER BY sal, ename;
set lines 4000
set pages 5000
select * from table(DBMS_XPLAN.display(format=>'advanced -cost -projection'));
explain plan for
WITH loc_cte AS (
select /*+ MATERIALIZE */ ename, sal, dname from (
SELECT
ename, sal, dname
FROM
emp, dept
WHERE
emp.deptno = dept.deptno
AND dept.loc = 'NEW YORK'
UNION
SELECT
ename, sal, dname
FROM
emp, dept
WHERE
emp.deptno = dept.deptno
AND dept.loc = 'DALLAS'
UNION
SELECT
ename, sal, dname
FROM
emp, dept
WHERE
emp.deptno = dept.deptno
AND dept.loc = 'CHICAGO'
))
SELECT ename, sal, dname FROM loc_cte
ORDER BY sal, ename;
set lines 4000
set pages 5000
select * from table(DBMS_XPLAN.display(format=>'advanced -cost -projection'));
Thursday, May 18, 2023
Using the MATERIALIZE hint correctly
I see queries like this from time to time:
SELECT /*+ materialize */ * FROM (SELECT bla-bla-bla…);
I’m pretty sure that the MATERIALIZE hint is doing nothing when used in this way. The MATERIALIZE hint is for CTEs (common table expressions) crated using the WITH clause. I am unable to see what is does for any other use. If someone can send me a repeatable test case that I can run showing it does something in other cases I’m open to being proven wrong.
This is the type of statement where the hint works as expected. This creates a structure that is similar to a Global Temp Table, this structure is dropped after the statement completes.
WITH emp_dept_cte AS (
SELECT /*+ materialize */ empno, sal, dname
FROM
emp,dept
WHERE
emp.deptno = dept.deptno
)
SELECT * FROM emp_dept_cte;
But it doesn’t work when I write something like this:
Select /*+ materialize */ * from (SELECT empno, sal, dname
FROM
emp, dept
WHERE
emp.deptno = dept.deptno )
In this query no matter where I put the MATERIALIZE hint, no temp table like object is created and used.
In the plan for the first query above I can clearly see the temp table being created in the auto trace with the step LOAD AS SELECT and an object name that starts with SYS_TEMP_ followed by an apparent random combination of numbers and letters. (I’m pretty sure it’s a hexadecimal number for the internal object.)
Using the MATERLIZE hint when not using the WITH clause, I do not see this step in the plan. Hence no temp table like structure.
Have a great day!