Skip to content

Instantly share code, notes, and snippets.

View kleontev's full-sized avatar

Kirill Leontev kleontev

2 followers · 11 following
View GitHub Profile
@kleontev
kleontev / f_deterministic_arraysize.log
Last active July 17, 2023 10:48
setting ARRAYSIZE and/or ROWPREFETCHSIZE impacts deterministic function result caching. basically, the caching kicks in only within one fetch, so the bigger the fetch size - the more beneficial it is. as of 2023-7-17 I am not entirely sure why we see efficient caching even for arraysize=1 if order by is introduced
SQL>
SQL> create or replace function fd(i number) return number
2 deterministic
3 as
4 begin
5 dbms_output.put_line(i);
6 return i;
7 end;
8 /
@kleontev
kleontev / join_order.log
Created June 18, 2023 13:48
a quick demo that join order matters performance-wise
SQL>
SQL> select /*+leading(t3 t2 t1)*/ count(*)
2 from test_10_rows t1
3 join test_1m_rows t2 on t1.id = t2.id
4 join test_2m_rows t3 on t2.id = t3.id
5 /
COUNT(*)
----------
20
@kleontev
kleontev / session_1.log
Last active July 29, 2022 15:50
a snippet to demonstrate that sql -> plsql -> sql calls may break statement level consistency while querying actively updated tables.
SQL>
SQL> whenever sqlerror continue
SQL>
SQL> drop table test_1 purge;
Table dropped.
Elapsed: 00:00:00.04
SQL> drop table test_2 purge;
@kleontev
kleontev / drop_col_exch_part_issue.sql
Created July 20, 2022 14:17
exchange partition; drop col from compressed table; ORA-14097: column type or size mismatch
cl scr
set echo on
drop table test_part_tab purge;
drop table test_exch_tab purge;
drop table test_exch_tab_no_c2 purge;
create table test_part_tab (
pkey int,
@kleontev
kleontev / assert.sql
Created June 17, 2022 14:43
https://t.me/oracle_dbd/388 (тестовое Магнита)
create or replace procedure assert(
p_condition boolean,
p_error_message varchar2 := 'Assert violation!',
p_error_code int := -20100
) as
begin
if p_condition then
return;
end if;
@kleontev
kleontev / marketing_dept_query.sql
Last active June 8, 2022 11:11
https://telegra.ph/Zadacha-Otchet-dlya-otdela-marketinga-06-07
with
one_contact_per_client as (
select --+no_merge
client_id,
max(decode(c_type, 1, c_info)) keep (dense_rank first order by decode(c_type, 1, created) desc nulls last) as phone,
max(decode(c_type, 2, c_info)) keep (dense_rank first order by decode(c_type, 2, created) nulls last) as email
from contact
where active = 'Y'
group by client_id
),
@kleontev
kleontev / test_alter_table_enq.log
Last active June 6, 2022 05:14
trace[ksq] aka 10704 to determine which TM locks get acquired for certain dml/ddl operations on a partitioned table
SQL>
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
1 row selected.
SQL>
@kleontev
kleontev / test_append_locks_part_for_vs_pruning.log
Created June 4, 2022 14:43
direct path insert: does partition pruning syntax (as opposed to explicit partition specification) require exclusive TM lock?
21:42:42 SQL> set time off
SQL> set timi off
SQL>
SQL> col banner for a100
SQL> col object_name for a15
SQL> col payload for a150
SQL> col subobject_name for a15
SQL> col trace_filename for a40
SQL>
SQL> set lines 300
@kleontev
kleontev / test_last_ddl_time.log
Created June 1, 2022 04:47
does alter table exchange partition update LAST_DDL_TIME? (yes)
SQL>
SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'
2 /
Session altered.
SQL>
SQL> drop table test_last_ddl purge
2 /
@kleontev
kleontev / select_for_update_deadlock_one_session.log
Created May 18, 2022 07:16
deadlock, select for update - one session
SQL>
SQL> create table t (id, value) as select 1, 1 from dual
2 /
Table created.
Elapsed: 00:00:00.03
SQL>
SQL> var cur_1 refcursor
SQL> var cur_2 refcursor