Nir's questions

Minha atualização incremental de visualização materializada está começando a ficar cada vez mais lenta. Todos os dias há milhões de novos registros. Portanto, há muitas exclusões na tabela de log mv. Após investigação, vejo um sql específico demorando muito, o que pode levar à causa raiz. A seguir está um dos SQLs em execução como parte da atualização mv:

SQL> select dmltype$$, max(snaptime$$) from "S"."MLOG$_TABLE_202210" group by dmltype$$;

no rows selected

Elapsed: 00:00:12.68

Statistics
----------------------------------------------------------
      7  recursive calls
      0  db block gets
     678822  consistent gets
     678789  physical reads
      0  redo size
    438  bytes sent via SQL*Net to client
     41  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed

Como você pode ver, uma contagem nesta tabela de log mv vazia levou 12s! (temos muitos assim). O motivo são as altas leituras físicas. As estatísticas estão bloqueadas no propósito (conforme recomendação do oráculo)

SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE_FREELIST_BLOCKS,LAST_ANALYZED,STATTYPE_LOCKED,STALE_STATS 
     from dba_tab_statistics 
     where table_name='MLOG$_TABLE_202210';
  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_SPACE_FREELIST_BLOCKS LAST_ANAL STATT STALE_S
---------- ---------- ------------ ------------------------- --------- ----- -------
         0          0            0                         0 14-NOV-22 ALL   YES


SQL> SELECT EXTENTS, blocks, bytes
FROM dba_segments
WHERE SEGMENT_NAME = 'MLOG$_TABLE_202210';

  EXTENTS    BLOCKS     BYTES
---------- ---------- ---------- 
        18      384    3145728

Então se a tabela não estiver em SGA ela passa por cima de todos os blocos que está alto devido ao HWM das muitas exclusões. Como tudo isso é atualização de mv - se eu executar truncar, isso interromperá os MVs.

Eu apreciaria algumas outras sugestões.

[nir@dba etl]$ cat login.sql
col TAB# new_value TAB NOPRINT
select chr(9) TAB# from dual;
set markup csv on delimiter "&TAB" quote off
set heading off termout off echo off feedback off verify off numformat 99999999999.9999999999999 null '\N'
[nir@dba etl]$ cat a.sh
#!/bin/bash

export ORACLE_PATH="/home/nir/etl"

echo "select * from dms_u.test_etl_source;" | sqlplus -s ${USER}/${PASS}@${TNS}

[nir@dba etl]$ ./a.sh




aa  \N  aa
\N  aa  aa
aa  aa  \N
\N  \N  aa
\N  aa  \N
aa  \N  \N
\N  \N  \N
aa  aa  aa

Estou usando o login.sqlpara fazer a sqlplussaída como tsv. O que eu quero é rodar login.sqlsem nenhuma saída. Se eu colocasse glogin.sqlno -sfuncionaria. Mas preciso que o login do tsv aconteça apenas em casos específicos (por isso o ORACLE_PATH).

Então gostaria a.shde retornar:

[nir@dba etl]$ ./a.sh
aa  \N  aa
\N  aa  aa
aa  aa  \N
\N  \N  aa
\N  aa  \N
aa  \N  \N
\N  \N  \N
aa  aa  aa

Eu quero retornar sqlplusa saída como tsv. Para isso eu uso set markup csv. mas não aceita tabulação como delimitador, não \te tabulação real.

erro é

SP2-1660: Invalid option. Only a single character be specified.

em qualquer um de:

sqlplus -M "CSV on delimiter '\t' QUOTE OFF" -s user/pass@host
sqlplus -M "CSV on delimiter '    ' QUOTE OFF" -s user/pass@host

sqlplusversão é 21.0. A solução tem que ser sqlplusargumento ou setcomando.

tenho os seguintes dados

SQL> SELECT id, relatedIdInASet from tab;

+----+------------------+
| id | relatedIdInASet  |
+----+------------------+
| 1  | [12,34,56]       |
| 2  | [11,12,22,34]    |
+----+------------------+

relatedIdInASeté textdo tipo até 1024 caracteres, cada linha pode conter uma quantidade diferente de IDs. podemos usar a find_in_setfunção nele, mas não podemos indexá-lo... Eu quero normalizar os dados. Eu gostaria de criar uma tabela usando um SQL para desacoplar o conjunto para que resulte:

+----+------------+
| id | relatedId  |
|---+-------------+
| 1  | 12         |
| 1  | 34         |
| 1  | 56         |
| 2  | 11         |
| 2  | 12         |
| 2  | 22         |
| 2  | 34         |
+----+------------+

Eu quero escapar da barra invertida \bpara que eu possa processá-la mais tarde normalmente. Até agora eu uso um comando que retorna resultado separado por tab que converte nulos \Ntambém (para mysql load into):

PGPASSWORD=$PASS psql -qtAX -U $USER -h $HOST -p $PORT -d $DB -AF $'\t' -P 'null=\N'

Alguma ideia?

Estou tentando ter uma NVLfunção no postgres.

create or replace function nvl (anyelement, anyelement)
returns anyelement language sql as $$
    select coalesce(cast( $1 as decimal), cast( $2 as decimal))
$$;

no entanto, isso falha em mim para os seguintes exemplos:

testdb=> select nvl(1,2);
ERROR:  return type mismatch in function declared to return integer
DETAIL:  Actual return type is numeric.
CONTEXT:  SQL function "nvl" during inlining

testdb=> SELECT nvl( sum(balance), 0 ) as b FROM db.bank WHERE user = 123;
ERROR:  function nvl(numeric, integer) does not exist
LINE 1: SELECT nvl( sum(balance), 0 ) as b FROM db.bank...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Quando eu altero para:

create or replace function nvl (anyelement, anyelement)
returns anyelement language sql as $$
    select case when $1 is null then $2 else $1 END 
$$;

O primeiro exemplo funciona. Mas ainda tenho falhas com:

testdb=> SELECT nvl( sum(balance), 0 ) as b FROM db.bank WHERE user = 123;
ERROR:  function nvl(numeric, integer) does not exist
LINE 1: SELECT nvl( sum(balance), 0 ) as b FROM db.bank...
       

Gostaria de uma ajuda para consertar isso.

Eu tenho uma tabela no Aurora MySQL 5.7. table tem poucas partições com 800m de linhas e pesa 2tb. Recentemente eu deixei cair algumas colunas usando percona. Surpreendentemente, o tamanho da tabela não mudou (olhando em information_schema.tables.

A maneira como o percona faz uma mudança é usando a nova tabela _<table_name>_newcom gatilhos na tabela original. ele cria uma nova tabela vazia com o mesmo DDL, executa as alterações que desejamos e copia tudo para a nova tabela com os gatilhos para mantê-la atualizada. uma vez que os dados são sincronizados - percona renomeia as tabelas e descarta a antiga. Portanto, a tabela foi construída do zero (sem travamento).

No entanto, depois de executar alter table optimize partition, vi o tamanho reduzido para 250gb. Alguém tem explicação ou sabe o que fiz de errado?

comando pt:

pt-online-schema-change --user $MYSQL_DBA_USER --password $MYSQL_DBA_PASS --host $MYSQL_WRITER D=db,t=table_data --alter "drop column a1, drop column a2"  --execute --max-load Threads_running=18446744073709551606 --critical-load Threads_running=18446744073709551606 --recursion-method=none

comando de otimização:

MySQL [(db)]> select table_rows,data_length/power(1024,3), index_length/power(1024,3),DATA_FREE/power(1024,3),AVG_ROW_LENGTH  from information_schema.tables where table_name='table_data';
+------------+---------------------------+----------------------------+-------------------------+----------------+
| table_rows | data_length/power(1024,3) | index_length/power(1024,3) | DATA_FREE/power(1024,3) | AVG_ROW_LENGTH |
+------------+---------------------------+----------------------------+-------------------------+----------------+
|  610884663 |        1847.7273712158203 |         202.40484619140625 |            0.0322265625 |           3247 |
+------------+---------------------------+----------------------------+-------------------------+----------------+
1 row in set (0.00 sec)


MySQL [db]> ALTER TABLE table_data OPTIMIZE PARTITION p20210601;
+---------------+----------+----------+---------------------------------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                                                    |
+---------------+----------+----------+---------------------------------------------------------------------------------------------+
| db.table_data | optimize | note     | Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. |
| db.table_data | optimize | status   | OK                                                                                          |
+------------------------+----------+----------+---------------------------------------------------------------------------------------------+
2 rows in set (5 hours 39 min 40.95 sec)

MySQL [db]>
MySQL [db]> select table_rows,data_length/power(1024,3), index_length/power(1024,3),DATA_FREE/power(1024,3),AVG_ROW_LENGTH  from information_schema.tables where table_name='table_data';


+------------+---------------------------+----------------------------+-------------------------+----------------+
| table_rows | data_length/power(1024,3) | index_length/power(1024,3) | DATA_FREE/power(1024,3) | AVG_ROW_LENGTH |
+------------+---------------------------+----------------------------+-------------------------+----------------+
|  736965899 |        104.25639343261719 |         155.98052978515625 |            0.0244140625 |            151 |
+------------+---------------------------+----------------------------+-------------------------+----------------+

Estou procurando um possível índice para o seguinte:

select left(name,10), count(*)
from tab
group by left(name,10)

Esta é a configuração da tabela:

create table tab(
id int(11),
name text,
primary key(id),
key name(name(10)))

O otimizador não usa o índice para a seleção. Alguma idéia se isso é possível no MySQL? No Oracle, por exemplo, posso criar índice em funções, o que não é suportado no MySQL.

Eu quero encontrar todos os grupos em uma tabela que tenham a versão de grupo duplicada. Um grupo pode ter várias versões de grupo. Cada versão do grupo pode ter vários membros. Uma 'versão' de grupo é definida por grpide changeDate. Um grupo considera uma duplicata se TODOS os membros ( userid, pcte hobby) em uma versão de grupo corresponderem a outra versão de grupo dentro do mesmo grupo.

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=84eb81a1a71dcee9ad3d0bd91f56120a

A groupsmesa:

^{* Número exclusivo da versão do grupo apenas para visualização.}

Resultado deve ser:

grpid
1
4
7

Explicação:

• grpid 1 - existem 3 versões de grupo (de 1 membro) - 1 e 3 duplicadas porque userid, pct e hobby são iguais
• grpid 2 - existem 2 versões de grupo (de 2 membros) - não duplicado porque hobby não é igual entre 5 e 7
• grpid 3 - existem 2 versões de grupo (de 2 membros) - não duplicado porque o pct é diferente entre todos os membros
• grpid 4 - existem 2 versões de grupo (de 2 membros) - todos os membros são duplicados porque userid, pct e userid são iguais
• grpid 5 - existe apenas um grupo de 3 membros - não duplicado
• grpid 6 - existem 3 versões de grupo (de 3 membros) - não duplicado - o pct mudou para cada membro do grupo entre as versões
• grpid 7 - existem 3 versões de grupo (de 3 membros) - duplicado porque userid, pct e userid são iguais entre 28-30 e 31-33
• grpid 8 - existem 2 versões de grupo uma com um membro e outra com 2 membros - não duplicada porque há outro membro nesse grupo

Estou usando o MySQL 5.7.

Quando faço select * .. | mysql ... > /tmp/filea partir de uma tabela com texto, existem alguns caracteres problemáticos que me impedem de carregá-la em um db diferente usando copy(postgres) ou load into(mysql). Caractere como tab, nova linha é traduzida automaticamente para \ne \t, mas alguns dos caracteres problemáticos são escape ^[, CR ^M, ^U, ^Z, ^Fe ^Htalvez outros que eu não tenha visto antes.

Normalmente, eu apenas o substituiria como echo "select * .." | mysql .. | sed 's/\r/\\r/g', mas há muitos caracteres desconhecidos lá. Então, em vez de substituí-los após a seleção, eu gostaria de uma função que recuperasse o texto já escapado (acho que removê-los também é bom).

Como devo fazer?

Eu uso o seguinte para carregar dados no postgres

\copy tab FROM /tmp/file DELIMITER E'\t' QUOTE E'\b' NULL AS '\N' CSV

Normalmente eu seleciono os dados de um banco de dados de origem

echo "select * from tab" | mysql --batch --quick --skip-column-names .... > /tmp/file

Gera um arquivo com campos delimitadores de tabulação. Funciona bem na maior parte. Até eu tentar importar coluna de texto de várias linhas. O erro aparece:

ERROR:  unquoted carriage return found in data

Alguma recomendação de como superar isso?

Eu quero subtrair o valor na coluna lag_daysde current_date. Normalmente eu fariacurrent_date - interval '1 day'

Estou recebendo erro de sintaxe no exemplo a seguir para subtrair por dias:

dkarchive=> select current_date - interval lag_days day from example_tbl;
ERROR:  syntax error at or near "day"
LINE 1: select current_date - interval lag_days day from example_tb...
   

Eu tenho um índice que filtra 99% da tabela ou seja ix_magic_composite(para os argumentos dessa consulta). Quando eu adiciono outro orfiltro ele escolhe o índice errado, ou seja, fTSmesmo que eu crie um índice que comece com esse campo ele ainda escolhe o índice errado. Os tempos de execução são 20s vs 3s para o melhor índice. ix_magic_compositeindex retorna (filtro inicial) para ambos os SQLs em torno de 10 linhas de milhões, enquanto fTSretorna milhões de volta.

Meio sem noção. Parece-me que as estatísticas não estão dando ao mecanismo a imagem certa com todas essas colunas combinadas.

Simplifiquei a tabela, ela tem muito mais colunas e índices.

SQL com bom plano:

select *
from tblExample
where 1=1
and status = 'okay'
and textCol > ''
and insrBLN = 1
and (magic is NULL or magic = '')
and (itemId is NULL or itemId = '')
and fTS > '2020-01-01'
and fTS > '2020-01-01'
order by fTS
limit 50

+----+-------------+------------+------------+-------------+--------------------------------------------------+---------------------+---------+-------+---------+----------+----------------------------------------------------+
| id | select_type | table      | partitions | type        | possible_keys                                    | key                 | key_len | ref   | rows    | filtered | Extra                                              |
+----+-------------+------------+------------+-------------+--------------------------------------------------+---------------------+---------+-------+---------+----------+----------------------------------------------------+
|  1 | SIMPLE      | tblExample | NULL       | ref_or_null | textCol,status,textCol_4,ix_magic_composite,fTS  | ix_magic_composite  | 53      | const | 5892974 |     0.24 | Using index condition; Using where; Using filesort |
+----+-------------+------------+------------+-------------+--------------------------------------------------+---------------------+---------+-------+---------+----------+----------------------------------------------------+

SQL com plano ruim:

select *
from tblExample
where 1=1
and status = 'okay'
and textCol > ''
and insrBLN = 1
and (magic is NULL or magic = '' or magic = 'retry')
and (itemId is NULL or itemId = '' or itemId = 'retry')
and fTS > '2020-01-01'
and fTS > '2020-01-01'
order by fTS
limit 50

+----+-------------+------------+------------+-------+-------------------------------------------------+---------+---------+------+---------+----------+------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys                                   | key     | key_len | ref  | rows    | filtered | Extra                              |
+----+-------------+------------+------------+-------+-------------------------------------------------+---------+---------+------+---------+----------+------------------------------------+
|  1 | SIMPLE      | tblExample | NULL       | range | textCol,status,textCol_4,ix_magic_composite,fTS | fTS     | 5       | NULL | 6271587 |    0.18  | Using index condition; Using where |
+----+-------------+------------+------------+-------+-----------------------------------------    ----+---------+---------+------+---------+----------+------------------------------------+

Mesa:

CREATE TABLE `tblExample` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `fTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `status` varchar(50) NOT NULL DEFAULT 'new',
  `textCol` varchar(50) DEFAULT NULL,
  `insrBLN` tinyint(4) NOT NULL DEFAULT '0',
  `itemId` varchar(50) DEFAULT NULL ,
  `magic` varchar(50) DEFAULT NULL ,
  PRIMARY KEY (`id`),
  KEY `ix_magic_composite` (`itemId`,`magic`,`fTS`,`insrBLN`),
  KEY `fTS` (`fTS`)
) ENGINE=InnoDB AUTO_INCREMENT=14391289 DEFAULT CHARSET=latin1

EDITAR

Nós refatoramos o código para que a consulta se pareça com:

select *
from tblExample
where 1=1
and status = 'okay'
and textCol > ''
and insrBLN = 1
and (retry = '' or (retry='retry' and retryDT < now() - interval 1 day))
and fTS > '2020-01-01'
order by fTS
limit 50

O problema NÃO foi classificado (também tentei uma ordem de colunas diferente no índice). Parece que ele escolhe o índice certo apenas se eu remover o pedido.

No Oracle, há uma nota importante na planície de explicação onde diz que filter a.col1=...este informando que você col1primeiro é recuperado do disco e só então o filtro é feito. Esta informação importante permite que você saiba a coluna exata que foi usada em um índice específico e quais são os filtros depois e podem ser bons candidatos para índice.

No resultado da explicação do MySQL, vemos algo como:

+----+-------------+-------+------------+--------+--------------------+---------+-------+--------+----------+------------------------------------+
| id | select_type | table | partitions | type   | key                | key_len | ref   | rows   | filtered | Extra                              |
+----+-------------+-------+------------+--------+--------------------+---------+-------+--------+----------+------------------------------------+
|  1 | SIMPLE      | SD    | NULL       | range  | ix_store_composite | 64      | NULL  | 252978 |    60.00 | Using index condition; Using where |
+----+-------------+-------+------------+--------+--------------------+---------+-------+--------+----------+------------------------------------+

O que eu gostaria de saber quais dos dados das colunas estão vindo do índice e quais estão sendo filtrados após (o using where). Não encontrei nenhuma opção para obter detalhes adicionais sobre a explicação

Estou faltando algo na minha consulta. Eu quero selecionar todas as tabelas de dba_tables, exceto uma lista de tabelas.

Por exemplo, mostre todos, exceto HR.DEPT, SCHEMA.TAB

dba_tables:

OWNER    TABLE_NAME
------   ----------
HR       DEPT
HR       TEST
SCHEMA   TAB
SCHEMA   NAMES
TEM      TBA

Resultado após a consulta:

OWNER    TABLE_NAME
------   ----------
HR       TEST
SCHEMA   NAMES
TEM      TBA

Consulta que tentei:

select owner, table_name from dba_tables
where (owner != 'HR' and table_name != 'DEPT' )  OR (owner != 'SCHEMA' and table_name != 'TAB' )

Desejo executar DMLs usando a variável de ligação com scripts de shell. Por exemplo, algo como:

#!/bin/bash

SH_NUM=10

sqlplus -S test_user/test_pass <<EOD
var a number;
a:=${SH_NUM}
insert into test_table values(a);
commit;
EOD

Não tenho certeza se isso é possível, posso usar essa abordagem ao usar pl/sql, mas gostaria de saber se posso fazer esse método também.

pg_dump -F dresultará em vários <number>.dat.gzarquivos que representam o número do objeto no banco de dados. Desejo pg_dumpcriar esses arquivos no formato <table name>.dat.gzpara poder distinguir facilmente um arquivo/tabela em um caso de recuperação de tabela.

Quero gerenciar a cópia do arquivamento em um local remoto. Para isso, quero excluir todos os arquivos que copiei, a menos que sejam exigidos pelo standby.

Portanto, para executar pg_archivecleanup $ARCLOC $CHECKPOINT, preciso encontrar o ponto de verificação mais recente que pode ser excluído. Não vejo os .backuparquivos recentes, pois são muito antigos para minha capacidade de arquivamento. O que eu quero ver é o replay_location\ replay_lsn(depende da versão) de pg_stat_replication.

No entanto, não sei como converter de replay_locationvalor para valor de ponto de verificação. 7/2DBCED18Ou seja, de algo como 000000020000000700000029.

Responda:

Função é:pg_xlogfile_name

Vejo algumas discrepâncias entre duas visualizações no postgres quanto à replicação e ao tempo de atraso.

Por esta consulta, claramente a replicação está ativa:

9.5.6.11

postgres=# select client_addr, sent_location, replay_location from pg_stat_replication where application_name = 'walreceiver';
  client_addr   | sent_location | replay_location
----------------+---------------+-----------------
 172.yyy.yyy.213  | 1C/2B5732F8   | 1C/2B5732F8
 10.xxx.xxx.195 | 1C/2B5732F8   | 1C/2B5732F8
 10.xxx.xxx.196 | 1C/2B5732F8   | 1C/2B5732F8

10.1

postgres=# select client_addr, sent_lsn, replay_lsn from pg_stat_replication where application_name = 'walreceiver';
 client_addr  |  sent_lsn  | replay_lsn
--------------+------------+------------
 10.qqq.qqq.44 | 1/8C000140 | 1/8C000140

Mas quando olhar para o tempo de atraso:

9.5.6.11

postgres-# SELECT pg_last_xlog_receive_location() ,  pg_last_xlog_replay_location() ,  now() ,  pg_last_xact_replay_timestamp();
 pg_last_xlog_receive_location | pg_last_xlog_replay_location |              now              | pg_last_xact_replay_timestamp
-------------------------------+------------------------------+-------------------------------+-------------------------------
                               |                              | 2018-05-10 04:19:38.079161-04 |

10.1

postgres=# SELECT pg_last_wal_receive_lsn() ,  pg_last_wal_replay_lsn() ,  now() ,  pg_last_xact_replay_timestamp();
 pg_last_wal_receive_lsn | pg_last_wal_replay_lsn |              now              | pg_last_xact_replay_timestamp
-------------------------+------------------------+-------------------------------+-------------------------------
                         |                        | 2018-05-10 04:29:21.730089-04 |

Não há informações.. O que estou perdendo?

Eu quero capturar exceção na função DB2 e retornar 0 se houver uma exceção. Eu não sei como a sintaxe correta

create function is_decimal(c_data varchar(100))
RETURNS INTEGER
begin
    select cast(c_data as decimal(12,10)) from sysibm.sysdummy1;

    return 1;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
    return 0;
end