MS SQL Server versus Oracle Database, tão diferentes e ao mesmo tempo tão próximos (1.ª Parte)

Este artigo pretende alertar para o cumprimento de boas práticas ao nível de programação SQL nos seus dialectos T-SQL para o caso do MS SQL Server e PL/SQL para o caso do Oracle Database, independentemente do SGBDR, estas práticas têm elevadíssimos benefícios.

 

Boas práticas de codificação em SQL, são muitas vezes simplesmente ignoradas, mas originam imensos custos para o projecto, aplicação ou sistema e até para a organização.  

 

Na minha opinião, um Programador de SQL,  possuir esta qualidade ou mesmo habilidade é uma atitude orientada aos objectivos, alinhamento com as necessidades do cliente ou negócio, profissionalismo e um óptimo estilo de programação em SQL e seus dialectos.

 

E refiro-me concretamente, no caso do Oracle à utilização de BIND VARIABLES, são fundamentais para o desempenho de um sistema.

 

Para se entender melhor passarei a explicar.

 

Imagine uma aplicação que é alvo de milhares de instruções de SQL numa tabela de Base de Dados, como as apresentadas a seguir:

 

SELECT u_nome, p_nome, c_postal FROM t_fornecedor WHERE id_fornecedor = 10735;

SELECT u_nome, p_nome, c_postal FROM t_fornecedor WHERE id_fornecedor = 10840;

SELECT u_nome, p_nome, c_postal FROM t_fornecedor WHERE id_fornecedor = 10987;

 

Cada vez que a consulta é executada, o SGBDR Oracle primeiro analisa a SHARED POOL (zona de memória) a verificar se a instrução de SQL já foi alguma vez executada. Caso tenha já tenha sido executada, o plano de execução utilizado na anterior instância é reutilizado, e a instrução de SQL é executada.

 

No caso da instrução de SQL não ser encontrada na SHARED POOL, o SGBDR Oracle terá executar o processo de “parsing” ou seja análise da instrução de SQL e posteriormente realizar o estudo dos vários planos de execução para encontrar o melhor plano de acordo com as suas parametrizações, antes de executar a instrução propriamente dita. Este processo denomina-se “HARD PARSE”, e para uma Base de Dados OLTP (Online Transaction Processing) poderá consumir mais tempo do que a própria instrução de SQL.

 

Quando é procurada na SHARED POOL a exacta correspondência de uma instrução de SQL, só instruções exactamente iguais são consideradas. Se como no exemplo das Instruções SQL acima colocadas, forem submetidas instruções  de SQL únicas, em que o predicado da cláusula WHERE é sempre diferente (id_fornecedor = 10735, id_fornecedor = 10840, etc.) nunca será encontrada uma correspondência e será necessário realizar um “HARD PARSE”. O “HARD PARSE” é muito intensivo na utilização de CPU e envolve operações de como “LATCH” de chaves nas áreas de memórias partilhadas. Esta situação poderá não originar problemas num sistema com poucos dados e pouco solicitado, mas num sistema com bastantes dados, multi-utilizador e com muitas transacções e muitas delas distribuídas, poderá simplesmente colocá-lo de rastos, com centenas ou milhares de instruções a necessitarem de ser analisadas “HARD PARSE” em simultâneo. Associado a esta situação surge um problema, a contenção que é o resultado dos inúmeros “HARD PARSE” realizados, o “HARD PARSE” é imune a medidas reactivas tais como aumento da memória física, incremento do n.º de CPU’s e muitas outras mediadas de reacção. O “HARD PARSE” é um problema encontrado em muitos sistemas e aplicações empresariais.

 

A solução para este problema é a reutilização dos planos de execução, e esta pode ser alcançada recorrendo a “BIND VARIABLES”. As “BIND VARIABLES” não são mais nem menos do que variáveis de substituição que são colocadas nas posições dos literais (10735, 10840, 10987). Exemplo da mesma instrução, desta vez recorrendo a “BIND VARIABLE”:

 

SELECT u_nome, p_nome, c_postal FROM t_fornecedor WHERE id_fornecedor =: fornecedor_numero;

 

Exemplo de SQL PLUS

 

SQL> variable fornecedor_numero number

SQL> exec :fornecedor_numero := 10987

SQL> SELECT u_nome, p_nome, c_postal FROM t_fornecedor WHERE id_fornecedor =: fornecedor_numero;

 

É muito fácil implementar a utilização de “BIND VARIABLES” em Java, C++ C# ou mesmo em VB.net. E como disse no início este problema não é só para o SGDBR Oracle, acontece ao mesmo ao MS SQL Server ou qualquer outro SGDBR. O MS SQL Server e SQL Bind Parameters serão abordados na 2.ª parte.

 

Continua…

 

 João Paulino

Terminar com o SELECT COUNT(*) FROM schema_name.table_name

Esta simples instrução ( SELECT COUNT(*) FROM schema_name.table_name) para tabelas de pequena dimensão não tem grande impacto, mas para tabelas grandes é muito prejudicial ao desempenho. Recomendo vivamente que não o utilizem nestas situações, porque para obter o número de linhas é realizado um full table scan à tabela em questão.

Apresento aqui algumas alternativas:

SQL Server BOX

/*******************************************************************************/

SQL Server 2000 e 2005
SP_MSTABLESPACE ‘schema_name.table_name’;

SQL Server 2005
SELECT TOTAL_ROWS = SUM(P_ST.ROW_COUNT)
FROM   SYS.DM_DB_PARTITION_STATS P_ST
WHERE  OBJECT_NAME(OBJECT_ID) = ‘table_name’
AND (INDEX_ID < 2);

/*******************************************************************************/

Oracle BOX

/*******************************************************************************/

SELECT OWNER,
TABLE_NAME,
NUM_ROWS
FROM DBA_TABLES
WHERE TABLE_NAME = ‘table_name’
AND OWNER = ‘schema_name; 

/*******************************************************************************/

Nota:É imprescindível que as tabelas possuam estatísticas actualizadas. Senão não há alternativa ao SELECT COUNT(*) FROM schema_name.table_name ou seus derivados. Ou há SELECT COUNT(1) FROM FROM schema_name.table_name ou seus derivados, escolha um campo que seja índex e not null.

João Paulino

Oracle Data Guard versus Microsoft SQL Server 2005 Database Mirroring

Coloco aqui um link para um artigo, que de um modo mais ou menos imparcial ( É da Oracle) compara duas propostas de funcionalidades de ”High Availability” distintas, mas como finalidades semelhantes. http://www.oracle.com/technology/deploy/availability/htdocs/DataGuardDatabaseMirroring.html

João Paulino

Base de Dados Oracle e Segurança

Este artigo tem como finalidade  colocar aqui alguns link’s para este tema sempre actual:

http://www.oracle.com/technology/getting-started/security.html

http://www.oracle.com/technology/deploy/security/database-security/pdf/twp_security_checklist_database.pdf

João Paulino

Oracle Database 11g – Baixa o custo de propriedade, ajuda a cumprir os níveis de Serviço e a obedecer à regulamentação

Com mais de 400 recursos adicionais, 15 milhões de horas de testes e 36.000 pessoa/meses de desenvolvimento, o SGBDR Oracle 11g destaca-se como o software mais inovador e da mais alta qualidade já lançado pela Oracle. “SGBDR 11g oferece aos clientes a capacidade de assumir o controlo das suas informações empresariais, ter melhor visão dos negócios e adaptar-se com rapidez e confiança a um ambiente competitivo que passa por grandes mudanças”, afirmou William Hardie, vice-presidente de Marketing de Produto de base de dados da Oracle. “Trabalhamos com os nossos clientes, incorporamos novas capacidades e recursos na nova versão, a fim de ajudar a minimizar o risco das mudanças, cumprir as expectativas do nível de serviços dos utilizadores e regulamentações, além de baixar custos de TI”, completa Hardie.

Com o SGBDR Oracle 11g, as organizações e empresas poderão assumir o controlo da informação/dados empresariais, obtendo assim uma melhor visão dos negócios e adaptar-se com rapidez e agilidade a um ambiente competitivo que passa por grandes mudanças. A nova versão aumenta a capacidade de cluster de Base Dados, além de acelerar a automação do data center e a gestão da carga de trabalho.

Testes com aplicações ajudam a reduzir tempo, risco e o custo da mudança

O SGBDR Oracle 11g apresenta recursos avançados de autogestão e automação para ajudar as organizações, empresas e fornecedores de serviços a cumprir acordos de nível de serviços. Por exemplo, como as organizações precisam fazer actualizações regulares do sistema Operativo e da Base de Dados, além de alterações no hardware, SGBDR Oracle 11g conta com o Oracle Real Application Testing. Ou seja, trata-se do primeiro SGBDR capaz de ajudar os clientes a testar e gerir alterações no seu ambiente de TI rapidamente, de maneira controlada e com baixos custos.

Maior retorno do investimento em soluções de recuperação de catástrofe

No SGBDR Oracle 11g, o Oracle Data Guard permite a utilização da Base de Dados em standby para melhorar o desempenho no ambiente de produção, além de fornecer protecção contra falhas do sistema e catástrofes. O Oracle Data Guard possibilita a leitura e a recuperação simultâneas de uma única da Base de Dados standby, tornando-a disponível para relatórios, backup, testes e actualizações para Base de Dados de produção. Ao aliviar a carga de trabalho de um sistema de produção para um de standby, o Oracle Data Guard ajuda a melhorar o desempenho dos sistemas de produção e fornece uma solução de baixo custo para recuperação de catástrofe.

Melhoramento na gestão do ciclo de vida das informações e do armazenamento

O SGBDR Oracle 11g conta com novos e significativos recursos de particionamento e compactação de dados para as gestões do ciclo de vida das informações e do armazenamento com menor custo. Automatiza muitas operações de particionamento de dados e amplia o particionamento por faixas, por função matemática e por listas (em inglês, range, hash e list), incluindo as novas extensões por intervalo, referência e por coluna virtual (em inglês, interval, REF e virtual columns). Além disso, oferece um conjunto completo de opções de particionamento composto, permitindo que o gestão do armazenamento seja orientado por regras de negócios.

Em complemento aos recursos tradicionais de compactação de dados, oferece ainda compactação avançada para dados estruturados e não-estruturados (em inglês, large objects ou LOBs) geridos em ambientes transaccionais, data warehousing e de gestão de conteúdos. É possível atingir índices de compactação de 2x a 3x ou até mais para todos os dados, com os novos recursos.

Registo total de todas as alterações nos dados

A nova versão também apresenta o “Oracle Total Recall”, que possibilita a consulta de dados em tabelas designadas a partir de pontos no passado. O recurso é uma maneira fácil e prática de acrescentar uma dimensão de tempo aos dados para acompanhamento de alterações, auditoria e cumprimento de regulamentações.

Máxima disponibilidade de informações

A Oracle tem sido líder no mercado em protecção para SGBDR contra indisponibilidade planeada ou imprevista. O Oracle 11g mantém essa liderança, pois mantémas expectativas de disponibilidade de seus clientes/utilizadores. Dentre os novos recursos encontramos o Oracle Flashback Transaction, que possibilita a reversão de uma transacção efectuada com erro, bem como de qualquer transacção dependente; Parallel Backup and Restore, que ajuda a melhorar o desempenho do backup e restauro de Grandes Base de Dados (VLDB); e ‘hot patching’, que melhora a disponibilidade do sistema ao permitir que as correcções sejam aplicadas sem a necessidade de interromper dos SGBDR. Além disso, um novo recurso de recomendação – Data Recovery Advisor – ajuda os administradores a reduzir significativamente paragens para recuperação, o que permite automatizar investigação de falhas, determinar planos de recuperação e lidar com várias situações de crise.

Oracle Secure Files

Esse recurso de última geração tem a função de armazenar grandes objectos como imagens, textos ou tipos de dados avançados – incluindo XML, imagens médicas e objectos tridimensionais – na base de dados. O Oracle Secure Files possui un desempenho comparável aos sistemas de ficheiros. Ao armazenar uma variedade mais ampla de informações empresariais e estratégicas e recuperá-las com rapidez e facilidade, as empresas podem saber mais sobre seus negócios e adaptar-se com agilidade.

XML mais rápido

O SGBDR Oracle 11g inclui melhoramentos significativos de desempenho no XML DB, um recurso que permite armazenar nativamente e manipular dados em XML. Acrescentou-se o suporte para XML binário, oferecendo aos clientes várias opções de armazenamento de XML que obedecem aos seus requisitos específicos de aplicação e desempenho. O XML DB também possibilita manipulação de dados em XML tilizando interfaces padrão de mercado com suporte para XQuery, Java Specification Requests (JSR)-170 e padrões SQL/XML.

Criptografia transparente

O SGBDR Oracle 11g adiciona aos seus recursos de segurança outros ainda mais significativos. A nova versão possui melhoramentos dos recursos do Oracle Transparent Data Encryption, além da criptografia ao nível das colunas, oferece ainda criptografia de tablespaces, que pode ser utilizada para criptografar tabelas inteiras, índices e outros armazenamentos de dados.

Cubos OLAP incorporados

O SGBDR Oracle 11g também oferece inovações em data warehousing. Os cubos OLAP foram melhorados e desenvolvidos para se comportarem como vistas materializadas na base de dados. Assim os programadores podem utilizar SQL standard para realizarem consultas aos dados, além do benefício da performance proporcionada por um cubo OLAP. Os novos recursos de notificação contínua de consultas permitem que as aplicações sejam notificadas imediatamente, sempre que forem feitas alterações importantes nas informações contidas na base de dados, sem sobrecarregá-lo com pesquisas constantes.

Pool de conexões e caches de resultados das consultas/pesquisas

Os recursos de desempenho e escalabilidade do SGBDR 11g possibilitam às empresas manter uma infra-estrutura de serviços de alta qualidade e desempenho. O novo produto consolida ainda mais a posição da Oracle como líder em desempenho e escalabilidade do mercado, com novos recursos como Query Result Caches, que melhoram o desempenho e a escalabilidade com o armazenamento em cache e a reutilização dos resultados de consultas mais frequentes e as funções do SGBDR. Oferece ainda o Database Resident Connection Pooling, que melhora a escalabilidade dos sistemas baseados na web ao fornecer pools de conexões para aplicações que não são multithread, ou seja, quando diferentes partes de um código são executadas concorrentemente ou em simultâneo.

Desenvolvimento de Aplicações

O SGBDR Oracle 11g disponibiliza várias ferramentas e um processo rápido de desenvolvimento de aplicações, que aproveita a totalidade dos principais recursos dessa versão. Destaco os novos recursos como cache no cliente, XML binário para melhor desempenho das aplicações, processamento de XML e armazenamento e recuperação de ficheiros. Além disso, o Oracle 11g também inclui um novo compilador Java “just-in-time” para executar rapidamente procedimentos Java na base de dados, sem a necessidade de um compilador de outro fornecedor; integração nativa com o Visual Studio 2005 para desenvolvimento de software .NET no Oracle; ferramentas de migração de Access com Oracle Application Express; e um recurso para fácil criação de consultas do SQL Developer e rápida codificação de “statements” SQL e PL/SQL.

Melhoramentos na autogestão/autoadministração e na automação

Os recursos de administração do SGBDR Oracle 11g foram desenvolvidos para facilitar o gestão dos grids empresariais, atendendo às expectativas dos utilizadores quanto ao nível dos serviços. Possui mais recursos de autoadministração e automação, que contribuem para a redução dos custos de gestão dos sistemas e o aumento do desempenho, da escalabilidade, da disponibilidade e da segurança das aplicações. Entre os novos recursos de administração presentes no Oracle 11g, destacam-se o ajuste automático de SQL e memória; o novo Partitioning Advisor que sugere aos administradores automaticamente como particionar tabelas e índices para melhorar o desempenho; e diagnóstico do desempenho para clusters de base de dados. Além disso, inclui um novo Support Workbench que fornece uma interface fácil de utilizar, apresenta incidentes relacionados à integridade da base de dados aos administradores, em conjunto com instruções e informação para a sua resolução.

 

João Paulino

Oracle Database 10g Express Edition – Free to develop, deploy, and distribute

Oracle Database 10g Express Edition, foi desenvolvido com base no SGBDR Oracle 10g Release 2 e é compatível com toda a família de SGBDR 10G, o que permite e simplifica a actualização e migração para as versões Oracle Standard Edition One, Oracle Standard Edition e Oracle Enterprise Edition.

 

É fornecido também o Oracle Application Express, que permite um rápido desenvolvimento e implementação de aplicações baseadas em interface WEB.

Encontra-se disponível para download versões para Linux (Debian, Mandriva, Novell, Red Hat and Ubuntu) e Windows em:

http://www.oracle.com/technology/software/products/database/xe/index.html

 

Existe um fórum comunitário grátis, mas é necessário registar-se, onde se podem discutir tópicos sobre o Oracle Database 10g Express Edition.

Atenção não é possível obter Service Request do Oracle Suporte, por isso não utilize esta versão em sistemas críticos!

http://www.oracle.com/technology/products/database/xe/forum.html

 

Notas: É uma excelente ferramenta para Administradores de Base de Dados poderem treinar, para instituições de ensino e estudantes que necessitem de um SGBDR gratuito, permitindo o desenvolvimento com .NET, XML, Java, PHP, etc.

FAQ em:

http://www.oracle.com/technology/products/database/xe/pdf/dbxe_faq.pdf

 

João Paulino 

Oracle Monitoring Specification – Topics (English)

• General Server Metrics
     Availability
     Physical Reads
     Physical Reads per Minute
     Logical Reads
     Logical Reads per Minute
     Physical Writes
     Physical Writes per Minute
     User Calls
     User Calls per Minute
     User Commits
     User Commits per Minute
     User Rollbacks
     User Rollbacks per Minute
     Connections Cumulative
     Connections Cumulative per Minute
     Connections Current
     Bytes Sent via SQL*Net
     Bytes Sent via SQL*Net per Minute
     Bytes Received via SQL*Net
     Bytes Received via SQL*Net per Minute
     Client Roundtrips
     Client Roundtrips per Minute
     Opened Cursors Cumulative
     Opened Cursors Cumulative per Minute
     Opened Cursors Current
     CPU Time Total
     CPU Time Total per Minute
     CPU Time Recursive
     CPU Time Recursive per Minute
     CPU Time Parse
     CPU Time Parse per Minute
     Cache Hit Ratio
     Up Time
     Block Changes
     Block Changes per Minute
     Redo Size Generated
     Redo Size Generated per Minute
     Redo Log Size
     Session PGA Memory
     Session UGA Memory
     Parse Count (total)
     Parse Count (total) per Minute
     Parse Count (hard)
     Parse Count (hard) per Minute
     Sorts Disk
     Sorts Disk per Minute
     Sorts Memory
     Sorts Memory per Minute
     Sorts Rows
     Sorts Rows per Minute
     Table Scans (long)
     Table Scans (long) per Minute
     Table Scans (short)
     Table Scans (short) per Minute
     Execute Count
     Execute Count per Minute
     Instance Used Space
     Instance Free Space
• Segment Metrics
     Availability
     Disk Space Used
     Number Of Rows
• TNS Ping Metrics
     Availability
     TNS Response Time
• Process Metrics Metrics
     Availability
     Process Virtual Memory Size
     Process Resident Memory Size
     Process Page Faults
     Process Page Faults per Minute
     Process CPU System Time
     Process CPU System Time per Minute
     Process CPU User Time
     Process CPU User Time per Minute
     Process CPU Total Time
     Process CPU Total Time per Minute
     Process CPU Usage
     Process Start Time
     Process Open File Descriptors
     Process Threads
• Tablespace Metrics
     Availability
     Free Space
     Free Extents
     Disk Space Used
     Number of Data Files
     Space Used Percent
• User Instance Metrics
     Availability
     Logons Current (active)
     Logons Current (inactive)
     Opened Cursors Current
     Used Space
• Oracle Control Specification
• Control Actions
• Analyze

João Paulino