If we consider cost as a function of the number of distinct values (NDV), we
can clearly identify three different subsets of its domain:
1. NDV <= 0.1 * rows_in_table. A purely linear function, probably under the
assumption that *all* of the scalar subquery values are being cached.
2. 0.1 * rows_in_table < NDV <= 0.5 * rows_in_table. It's a
logarithmically-shaped function with the asymptote ~ f(rows_in_table). The
rational is probably: with large NDVs it will be much less likely to find its
scalar subquery result in the cache. A pitfall here is that the function seems
too steep at the beginning - a small increase in NDV will lead to a huge leap
in cost.
3. NDV > 0.5 * rows_in_table. f(NDV) = 267300. A possible assumption in this
case: no cache hits at all. Just the value seems a bit odd as the real cost
should be by around 10% higher if the results aren't cached.
Best regards,
Nenad
-----Original Message-----
From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
Sent: Dienstag, 28. Mai 2019 10:22
To: ORACLE-L (oracle-l@xxxxxxxxxxxxx) <oracle-l@xxxxxxxxxxxxx>; Noveljic Nenad
<nenad.noveljic@xxxxxxxxxxxx>
Subject: Re: scalar subquery costing
FYI - a quick test reporting costs for your query as I fake the column stats on
t_100K to vary Statement N corresponds to N * 1,000 distinct values, with
low_value 1 and high_value N * 1,000.
I've picked the cost, cpu_cost, and io_cost for the top line of the execution
plan in each case.
Interesting point(a) somewhere between 10 and 12 thousand distinct values, and
another when num_distinct = 0.5 * rows in table.
STATEMENT_ID IO_COST IO_DIFF CPU_COST CPU_DIFF
COST
------------------------------ ---------- ---------- ---------- ----------
----------
1 3068 252344831
3094
2 6068 3000 487952031 235607200
6119
3 9068 3000 723559231 235607200
9143
4 12068 3000 959166431 235607200
12167
5 15068 3000 1194773631 235607200
15192
6 18068 3000 1430380831 235607200
18216
7 21068 3000 1665988031 235607200
21240
8 24068 3000 1901595231 235607200
24265
9 27068 3000 2137202431 235607200
27289
10 30068 3000 2372809631 235607200
30314
11 34945 4877 2755835542 383025911
35230
12 59769 24824 4705425626 1949590084
60256
13 80774 21005 6355078773 1649653147
81432
14 98779 18005 7769067185 1413988412
99583
15 114383 15604 8994523809 1225456624
115314
16 128036 13653 1.0067E+10 1072274546
129078
17 140083 12047 1.1013E+10 946124599
141223
18 150792 10709 1.1854E+10 840999644
152019
19 160373 9581 1.2606E+10 752473365
161678
20 168996 8623 1.3284E+10 677226029
170371
21 176798 7802 1.3896E+10 612728312
178236
22 183891 7093 1.4453E+10 557025738
185387
23 190366 6475 1.4962E+10 508588717
191915
24 196303 5937 1.5428E+10 466206324
197900
25 201764 5461 1.5857E+10 428909819
203406
26 206805 5041 1.6253E+10 395916755
208488
27 211473 4668 1.6620E+10 366589588
213193
28 215807 4334 1.6960E+10 340404618
217563
29 219843 4036 1.7277E+10 316928437
221631
30 223609 3766 1.7573E+10 295799875
225428
31 227133 3524 1.7849E+10 276716012
228981
32 230436 3303 1.8109E+10 259421261
232311
33 233539 3103 1.8353E+10 243698760
235439
34 236460 2921 1.8582E+10 229363540
238383
35 239213 2753 1.8798E+10 216257051
241159
36 241814 2601 1.9002E+10 204242770
243781
37 244274 2460 1.9196E+10 193202621
246261
38 246604 2330 1.9379E+10 183034062
248610
39 248815 2211 1.9552E+10 173647700
250840
40 250916 2101 1.9717E+10 164965315
252957
41 252914 1998 1.9874E+10 156918226
254971
42 254817 1903 2.0024E+10 149445929
256890
43 256631 1814 2.0166E+10 142494957
258719
44 258363 1732 2.0302E+10 136017913
260465
45 260018 1655 2.0432E+10 129972672
262133
46 261601 1583 2.0556E+10 124321686
263729
47 263117 1516 2.0675E+10 119031402
265257
48 264569 1452 2.0790E+10 114071760
266721
49 265963 1394 2.0899E+10 109415770
268126
50 267300 1337 2.1004E+10 105039139
269474
51 267300 0 2.1004E+10 0
269474
52 267300 0 2.1004E+10 0
269474
...
98 267300 0 2.1004E+10 0
269474
99 267300 0 2.1004E+10 0
269474
100 267300 0 2.1004E+10 0
269474
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Noveljic Nenad <nenad.noveljic@xxxxxxxxxxxx>
Sent: 24 May 2019 16:46:30
To: ORACLE-L (oracle-l@xxxxxxxxxxxxx)
Subject: scalar subquery costing
The calculated cost for the following query is 262K (on Oracle 12.2):
select /*+ qb_name(QB_MAIN) */
(
select /*+ qb_name(QB_SUBQ) */ count(*)
from t_1k
where t_1k.n1 = t_100k.n1
)
from t_100k ;
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 262K | |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | TABLE ACCESS FULL | T_1K | 1 | 4 | 3 | 00:00:01 |
| 3 | TABLE ACCESS FULL | T_100K | 98K | 488K | 69 | 00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("T_1K"."N1"=:B1)
From the optimizer trace we can get more precise cardinalities and costs:
Final cost for query block QB_SUBQ (#0) - All Rows Plan:
Best join order: 1
Cost: 3.009811 Degree: 1 Card: 1.000000 Bytes: 4.000000
Final cost for query block QB_MAIN (#0) - All Rows Plan:
Best join order: 1
Cost: 268174.664510 Degree: 1 Card: 100000.000000 Bytes: 500000.000000
Table: T_100K Alias: T_100K
Card: Original: 100000.000000 Rounded: 100000 Computed: 100000.000000
Non Adjusted: 100000.000000 ...
Access Path: TableScan
Cost: 68.697001 Resp: 68.697001 Degree: 0
Cost_io: 68.000000 Cost_cpu: 16737631
In theory, the total cost should be caculated as follows.
cardinality(QB_MAIN) * cost(QB_SUBQ) + cost(full table scan T_100K) = 100000 *
3.009811 + 68.69701 = 301049.79701
How the optimizer came up with 268174.664510?
Best regards,
Nenad
____________________________________________________
Please consider the environment before printing this e-mail.
Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
Important Notice
This message is intended only for the individual named. It may contain
confidential or privileged information. If you are not the named addressee you
should in particular not disseminate, distribute, modify or copy this e-mail.
Please notify the sender immediately by e-mail, if you have received this
message by mistake and delete it from your system.
Without prejudice to any contractual agreements between you and us which shall
prevail in any case, we take it as your authorization to correspond with you by
e-mail if you send us messages by e-mail. However, we reserve the right not to
execute orders and instructions transmitted by e-mail at any time and without
further explanation.
E-mail transmission may not be secure or error-free as information could be
intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
processing of incoming e-mails cannot be guaranteed. All liability of Vontobel
Holding Ltd. and any of its affiliates (hereinafter collectively referred to as
"Vontobel Group") for any damages resulting from e-mail use is excluded. You
are advised that urgent and time sensitive messages should not be sent by
e-mail and if verification is required please request a printed version.
Please note that all e-mail communications to and from the Vontobel Group are
subject to electronic storage and review by Vontobel Group. Unless stated to
the contrary and without prejudice to any contractual agreements between you
and Vontobel Group which shall prevail in any case, e-mail-communication is for
informational purposes only and is not intended as an offer or solicitation for
the purchase or sale of any financial instrument or as an official confirmation
of any transaction.
The legal basis for the processing of your personal data is the legitimate
interest to develop a commercial relationship with you, as well as your consent
to forward you commercial communications. You can exercise, at any time and
under the terms established under current regulation, your rights. If you
prefer not to receive any further communications, please contact your client
relationship manager if you are a client of Vontobel Group or notify the
sender. Please note for an exact reference to the affected group entity the
corporate e-mail signature. For further information about data privacy at
Vontobel Group please consult www.vontobel.com<https://www.vontobel.com>.
Important Notice
This message is intended only for the individual named. It may contain
confidential or privileged information. If you are not the named addressee you
should in particular not disseminate, distribute, modify or copy this e-mail.
Please notify the sender immediately by e-mail, if you have received this
message by mistake and delete it from your system.
Without prejudice to any contractual agreements between you and us which shall
prevail in any case, we take it as your authorization to correspond with you by
e-mail if you send us messages by e-mail. However, we reserve the right not to
execute orders and instructions transmitted by e-mail at any time and without
further explanation.
E-mail transmission may not be secure or error-free as information could be
intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
processing of incoming e-mails cannot be guaranteed. All liability of Vontobel
Holding Ltd. and any of its affiliates (hereinafter collectively referred to as
"Vontobel Group") for any damages resulting from e-mail use is excluded. You
are advised that urgent and time sensitive messages should not be sent by
e-mail and if verification is required please request a printed version.
Please note that all e-mail communications to and from the Vontobel Group are
subject to electronic storage and review by Vontobel Group. Unless stated to
the contrary and without prejudice to any contractual agreements between you
and Vontobel Group which shall prevail in any case, e-mail-communication is for
informational purposes only and is not intended as an offer or solicitation for
the purchase or sale of any financial instrument or as an official confirmation
of any transaction.
The legal basis for the processing of your personal data is the legitimate
interest to develop a commercial relationship with you, as well as your consent
to forward you commercial communications. You can exercise, at any time and
under the terms established under current regulation, your rights. If you
prefer not to receive any further communications, please contact your client
relationship manager if you are a client of Vontobel Group or notify the sender.
Please note for an exact reference to the affected group entity the corporate
e-mail signature.
For further information about data privacy at Vontobel Group please consult
www.vontobel.com.
--
//www.freelists.org/webpage/oracle-l
