Calma gente .. tem desenvolvedor na lista (eu) que gosta de escrever queries com qualidade (pelo menos tento).
kkkk 2014-02-17 20:14 GMT-03:00 Roland Martins <dadim...@yahoo.com.br>: > > > Rapaz, o que vemos por aí é a mesma coisa: o desenvolvedor escreve, quem > "tuna" é o DBA. Visão errada esta, vários desenvolvedores sabem ler um > plano, questionar estatísticas - de objetos e de sistema, histogramas, > influenciar o CBO com hints, mas a qualidade de muitos desenvolvedores é > bem ruim (eles ficam ali, escrevendo queries, mexendo nos celulares, sites > de compras, viagens internacionais... e o desempenho da query ? Ah, a gente > manda pros DBAs, aos 47 do segundo tempo, eles resolvem - e nem sempre do > melhor jeito), isso quando o problema já não aparece direto em produção > (tem lugares que funciona assim, todos sabemos) e aí é você que tem o > pepino pra descascar. > > Mas, como diz aquele provérbio chinês, é melhor acender uma vela que > queixar-se da escuridão. > > Chiappa está correto. Adiciono o que diz Thomas Kyte (na época do 8i > Release 3, mas ainda atual): > > "Here is a short extract from a book I am working on. The short answer is > if you want a 10 step guide to tuning a query, buy a piece of software. > You are not needed in this process, anyone can put a query in, get a query > out and run it to see if it is faster. > There are tons of these tools on the market. They work using rules > (heuristics) and can tune maybe 1% of the problem queries out there. They > APPEAR to be able to tune a much larger percent but that is only because > the people using these tools never look at the outcome -- hence they > continue to make the same basic mistakes over and over and over. > > If you want to really be aboe to tune the other 99% of the queries out > there, knowledge of lots of stuff -- physical storage mechanisms, access > paths, how the optimizer works - thats the only way. > > of course, read: > > > http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/toc.htm > > from cover to cover and > > > http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76992/toc.htm > > as well > > 1.1 Efficient SQL > > This was probably the hardest part of the book to write - this chapter. > That is not because the material is all that complex, rather because I > know what people want - and I know what can be delivered. What people > want: The 10 step process by which you can tune any query. What can be > delivered: Knowledge about how queries are processed, knowledge you can > use and apply day to day as you develop them. > > Think about it for a moment. If there were a 10 step or even 1,000,000 > step process by which any query can be tuned (or even X% of queries for > that matter), we would write a program to do it. Oh don't get me wrong, > there are many programs that actually try to do this - Oracle Enterprise > Manager with its tuning pack, SQL Navigator and others. What they do is > primarily recommend indexing schemes to tune a query, suggest materialized > views, offer to add hints to the query to try other access plans. They > show you different query plans for the same statement and allow you to pick > one. They offer "rules of thumb" (what I generally call ROT since the > acronym and the word is maps to are so appropriate for each other) SQL > optimizations - which if they were universally applicable - the optimizer > would do it as a matter of fact. In fact, the cost based optimizer does > that already - it rewrites our queries all of the time. These tuning tools > use a very limited set of rules that sometimes can suggest that index or > set of indexes you really should have thought of during your design. > > I'll close this idea out with this thought - if there were an N step > process to tuning a query, to writing efficient SQL - the optimizer would > incorporate it all and we would not be having a discussion about this topic > at all. It is like the search for the holy grail - maybe someday the > software will be sophisticated enough to be perfect in this regards, it > will be able to take our SQL, understand the question being asked and > process the question - rather then syntax. > > To me - writing efficient SQL requires a couple of things: > > o Knowledge of the physical organization of what I'm asked to query > against. That is - the schema. Knowledge that the physical organization > was actually designed in order to help me answer my frequently asked > questions (refer back to the chapter on designing an efficient schema for > advice in that arena) > > o Knowledge of what the database is capable of doing. If I did not > know about "skip scan indexes" and what they did (we'll cover them below) - > I might look at a schema and say "ah hah, we are missing an index" when in > fact we are not. > > o Knowledge of all of the intricacies of SQL - from the lowly "WHERE" > clause on up to analytics and psuedo columns. Knowledge of what using a > particular construct will do to my runtime processing. > > o And most importantly of all - a solid understanding of the goal, of > what the > question is. Tuning a query or process is really hard (impossible I would > say) - unless you understand the question in the first place. I cannot > tell you how many times I've not been able to tune a query until I had the > question in hand. Certainly you can derive a question from a query - > however, many times that derived question is much more confining then the > real question being asked. For example, many people use outer joins in all > queries - they are "afraid" of losing a row (perhaps they got "burned" in > some past experience and now use outer joins everywhere). If the objects > are related in a one > to one mandatory fashion - we don't need an outer join at all. The > question derived from the query is much more confining then reality. > > That last topic or point is so important, I'll close out this section with > it. In this > chapter we'll cover the topics of what the database is capable of doing in > general - looking at many of the access paths and join operations available > to us. We'll look at what SQL is capable of doing - not by discussing the > entire language, that in itself is a book. Rather, we'll look at a couple > of things that will whet you appetite - show you how powerful this language > can be, how much more than just "SELECT" "FROM" "WHERE" and "ORDER BY" > there is. Then we'll close up with a look at that most important topic - > why understanding the question is more important then having a query at > hand to tune. > > So, this section will not provide you with the N steps you need to follow > in order to tune a query or write the best queries in the world. For every > rule of thumb out there anyone has ever shown me regarding writing > "efficient SQL", I've been able to come up with a slew of common (not > esoteric) counter cases to prove that rule of thumb is wrong in as many > cases as it is right. I've talked to people who swear "NOT IN" is fatal, > never use it - always use NOT EXISTS. Then I show them NOT IN running a > query 10 times faster then NOT EXISTS. I talk with people who feel NOT > EXISTS is the worst construct on the planet - you must use IN. Then I do > the same - showing them how NOT EXISTS can > run many times faster then IN. > ..... > > > http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8764517459743 > " > > > > Em Segunda-feira, 17 de Fevereiro de 2014 19:24, "jlchia...@yahoo.com.br" > <jlchia...@yahoo.com.br> escreveu: > > Bem, eu conheço dois tipos principais de ferramentas que ajudam em > desenvolvimento de SQL : IDEs que listam quais são os campos indexados, > mostram tamanhos de tabelas e coisinhas do tipo , visando evitar > esquecimentos e erros principalmente na hora de se escrever os filtros, > mostram o plano de execução em forma gráfica ( > http://www.embarcadero.com/br/products/rapid-sql e > http://www.embarcadero.com/br/products/db-optimizer são exemplos, mas > certamente existem n+1 outras), e tools que tentam analisar o plano de > execução e dar sugestões (como > http://www.quest.com/sql-optimizer-for-oracle/ , os diversos Advisors > dentro do database - no caso principalmente o SQL Advisor - que normalmente > exigem Packs licenciados extras).... > > Essas tools podem ser de ajuda, sem dúvida, mas vc tem que ter em mente > que ** NENHUMA ** delas é restritiva, ie : se depois de mostrar os campos > indexados, ou avisar sobre tamanhos, ou mostrar um plano ineficiente o > usuário/desenvolvedor INSISTIR em prosseguir, OBVIAMENTE a tool não fará > nadica de nada, sim ???? Então vc é que vai fazer uma análise de > custoxbenefício pra ver se os custos desse "ajutório" pros desenvolvedores > se justificam.... > > IMHO o ** único ** caminho garantido para se obter uma melhoria FIRME na > qualidade dos SQLs é claro : Obrigatoriamente antes de entrar em produção, > o desenvolvedor de todo e qualquer SQL TEM que apresentar provas de > qualidade do SQL - nisso entraria tanto a execução do SQL num ambiente de > HOMOLOGAÇÂO ** decente **, o mais próximo possível à produção (executar o > SQL num banco teste ou desenv, onde tipicamente tem poucas centenas de > linhas nas tabelas e dois ou três gatos-pingados concorrendo é ÓBVIO que > não serve), quanto poderia se obter o plano de execução extendido (veja > https://blogs.oracle.com/optimizer/entry/how_do_i_know_if ) e estabelecer > limites tipo rejeitar sumariamente planos com steps retornando mais que x > linhas (baseie-se na sua experiência do ambiente para implementar um bom > valor para x), e/ou rejeitar planos que façam scan nas tabelas x, y ou z > que vc (por experiência no ambiente, mais uma vez) sabe que são muito > grandes e/ou muito acessadas, e/ou use mais que uma partição, digamos, num > ambiente DW... É por aí, é vc ** IMPOR ** regras e limites, EXIGIR um > mínimo de qualidade, senão simplesmente vc não alcança nadica de nada.... > Óbvio, vc VAI dar as tools que puder, vai automatizar/simplificar/GUIzar > o processo de obtenção das evidências de qualidade o mais possível pros > teus desenvolvedores, VAi dar treinamento interno pra eles, ok, mas um ** > mínimo esforço ** por parte deles é condição sine qua non, okdoc ? > > []s > > Chiappa > > > >
