Category Archives: fun

[FUN] How to tune select count(*) from t?

3 Replies

A user mentioned one call is faster in test than in prod. And the table has the same size. How could we tune the production to make it quick?


user007@PROD> select count(*) from t;
  COUNT(*)
----------
  19832501

Elapsed: 00:03:05.00

Let’s try in test :


user007@TEST> select count(*) from t
select count(*) from t
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.16

Wow, 00:00:00.16! This is a much quicker response time. Probably the ORA-942 was catched by the application and the user did not realise it…

Next time someone ask you to tune a count(*), just revoke access to the table to optimize the response time :twisted:

dealing with support

10 Replies

It is quite a long time I did shot the pianist in my blog… probably patience and perseverance are better than aggressive behavior, but today I have been transfixed by an answer from metalink.

Sometimes the issues I submit are a bit exotic but there it was a join with one view using one function and dual returning wrong results. Reproducible at will on any db 10.2.0.4 and below.

After 2 weeks the helpful engineer told me it is fixed with patch 6471020 and in 11g. I asked if he tried it but he said no. As the patch is not available on my platform I asked if could try it but then he came back with an amazing workaround he became from an ARE (advanced resolution engineer) :

Remove the function from the view.

Unreal

le compte est bon

20 Replies

I am back from my vacations, I was at nice places in Switzerland like Rhone Glacier, underground lake of Saint-Leonard, Salt Mines of Bex, Rhine Waterfalls and more …

To keep up with the fun, here is a little quiz :

You have the numbers 1-3-4-6 and you need to achieve the number 24. The allowed operations are +, -, * and /

If I try to achieve 49 it is easy :

SQL> /
Enter value for n1: 1
old  14:      (SELECT &n1 n
new  14:      (SELECT 1 n
Enter value for n2: 3
old  17:       SELECT &n2 n
new  17:       SELECT 3 n
Enter value for n3: 4
old  20:       SELECT &n3 n
new  20:       SELECT 4 n
Enter value for n4: 6
old  23:       SELECT &n4 n
new  23:       SELECT 6 n
Enter value for result: 49
old 143:              ) = &result
new 143:              ) = 49

result
------------------------------------
(4+3)*(6+1)
(3+4)*(6+1)
(6+1)*(4+3)
(1+6)*(4+3)
(6+1)*(3+4)
(1+6)*(3+4)
(4+3)*(1+6)
(3+4)*(1+6)

8 rows selected.

Elapsed: 00:00:11.28

But for 24 it is not that simple :-) at least for human !

Ok, in SQL I am using a plsql function to evaluate expression

CREATE OR REPLACE FUNCTION lsc_eval (expr VARCHAR2)
   RETURN NUMBER
IS
   x   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'begin :x := ' || expr || ';end;'
               USING OUT x;
   RETURN x;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END;
/

I will post the rest of the code as a comment later ;)

to SQL or to PLSQL

10 Replies

Iggy Fernandez posted a riddle to be solved without PLSQL

http://www.amazon.com/gp/blog/post/PLNKI2MYB0YCYAUL

I tend to second Steven Feuerstein argument :
Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens.

In my book I mentionned techniques that enhance the basis functionality of SQL. Actually, many of those techniques allow you to write “shorter” statements than before (by avoid redundant subqueries) and also to gain performance, occasionnaly.

However the downside is that you will end up writting some code that only you can read.

In my opinion a zero-plsql approach is wrong. If you have a problem similar to Iggy Fernandez, then no no no, do not even try to solve it with SQL unless you are really doing this for fun

In my humble opinion, plsql and sql and complementary. If you want to program, use plsql. If you need to retrieve data, use sql.

And thanks a lot to Iggy for the excellent challenge !!!

high cost

8 Replies

What’s wrong with this query ?


select
(t6.column_value).getstringval() t6
from
table(xmlsequence(extract(xmltype(‘<x/>’),’/x’))) t1,
table(xmlsequence(t1.column_value))t2,
table(xmlsequence(t2.column_value))t3,
table(xmlsequence(t3.column_value))t4,
table(xmlsequence(t4.column_value))t5,
table(xmlsequence(t5.column_value))t6;
T6
————————
<x/>

Elapsed: 00:00:00.01

Well, let’s check the plan :


--------------------------------------------------------------------
| Id  | Operation          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |    18E|    15E|    18E  (0)|999:59:59 |
|   1 |  NESTED LOOPS      |    18E|    15E|    18E  (0)|999:59:59 |
|   2 |   NESTED LOOPS     |    18E|    15E|    99P  (3)|999:59:59 |
|   3 |    NESTED LOOPS    |  4451T|    31P|    12T  (3)|999:59:59 |
|   4 |     NESTED LOOPS   |   544G|  3045G|  1490M  (3)|999:59:59 |
|   5 |      NESTED LOOPS  |    66M|   254M|   182K  (3)| 00:36:31 |
|   6 |       COLLECTION I |       |       |            |          |
|   7 |       COLLECTION I |       |       |            |          |
|   8 |      COLLECTION IT |       |       |            |          |
|   9 |     COLLECTION ITE |       |       |            |          |
|  10 |    COLLECTION ITER |       |       |            |          |
|  11 |   COLLECTION ITERA |       |       |            |          |
--------------------------------------------------------------------

It is returning 18 quadrillions of rows, 15 exabytes, the cost is 1.8E19 and the time is about one month :mrgreen:

Obfuscation contest

9 Replies

I have been challenged to participated (my script : lsc2.sql) and now challenged to explain my query by Chen
Chen Shapira

Ok, I give a try.

To make the query unreadable and unformatable I used no space, no new line, and I started by q’<'>‘ to confuses sqlinform.

Ok, I added undocumented constructs like

connect by .1 < 1.
connect by .25 > rownum/100.

with 2 connect by, the first is ignored. The second is buggy in 10gR1 and returns 25 rows in 10gR1 (but 24 rows in 9i, 10gR2, 11g).

and group by () which means only one group. Note this clause is mandatory, you cannot remove it when using an aggregate function and a scalar subquery like in
select (select 1 from dual), count(*) from dual group by ();

The utl_raw and to_char converts a number to the string Oracle Community. I will keep terse on the formula.
The scalar subquery inside TAN returns 1. The regexp removes some characters out of the extract tag. _x0032_ is the alias of the column “2″. The v$reservedwords use UNIQUE to sort the rows (UNIQUE was sorting in 10gR1 for UNIQUE/DISTINCT) and the MAX(rownum) retrieve the 316th row (FROM) and 845th row (SELECT). Remember ROWNUM is evaluated BEFORE aggregation, therefore ROWNUM 845 exists! Note the “+” is an alias and not a string!

With the SELECT and FROM keyword I build a new query that I dynamically evaluates with dbms_xmlgen.getxml. Rest of the first part is fancy calculation.

The dburi is also dynamic.

dburitype(
'/SYS/DBA_PROCEDURES/ROW[PROCEDURE_NAME=''GETKEY'']/OBJECT_NAME'
).getxml()

Well, I could have obfuscated the text a bit more but I was short of time… This simply return the name of the package that have a procedure called GETKEY. Which is DBMS_OBFUSCATION_TOOLKIT_FFI. This is not very clean as the query would fail if you have a PROC.GETKEY in your schema. Well, I wanted to add this not very well know mechanism to query the database.

Last part is in dict, I select the pattern ‘[COTTON+XE]{4,}’ in dict and return the Mode (or modal score), which is CONTEXT.

A bit formatting and I get

"NVL2"(Q'<'>',"UTL_RAW".CAST_TO_VARC
------------------------------------
Oracle Community_Obfuscation_Contest