Como executar o Oracle 12c SQL Tuning Advisor
A Oracle 12c oferece um utilitário útil chamado o SQL Tuning Advisor. Você pode usar essa ferramenta built-in para fornecer sugestões ou recomendações sobre certas instruções SQL. Embora nem sempre pode dar conselhos perfeito, como qualquer outra coisa, tê-lo em sua caixa de ferramentas de técnicas de ajuste é benéfico.
Use PL / SQL eo DBMS_SQL_TUNE pacote interno para criar uma tarefa de afinação. Digite o seguinte:
DECLAREl_sqlVARCHAR2 (500) -l_sql_tune_task_id VARCHAR2 (100) -BEGINl_sql: = 'SELECT first_name, last_name, department_name' || 'FROM emp Cadastre departamentos utilizando (department_id)' || 'WHERE last_name =' 'Hopkins' '' - l_sql_tune_task_id: = DBMS_SQLTUNE.create_tuning_task (sql_text => l_sql, user_name => 'HR', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 60, task_name => 'emp_dept_tuning_task', descrição => 'tarefa ajuste para um EMP para o departamento de consulta de junção. ) -DBMS_OUTPUT.put_line ( 'l_sql_tune_task_id:' || l_sql_tune_task_id) -END- /
Você deve ver o seguinte:
Procedimento PL / SQL concluído com êxito.
No comando anterior, observe o TIME_LIMIT de 60. Isso limita o tempo de processamento para 60 segundos. Você não pode sempre quer executar algo parecido com isso por longos períodos em seu banco de dados, porque ele incorre em sobrecarga do sistema.
Executar o assessor de sintonia com a sua tarefa, digitando o seguinte:
EXEC DBMS_SQLTUNE.execute_tuning_task (task_name => 'emp_dept_tuning_task') -
Devido ao limite de 60 segundos fornecidas na criação da tarefa, esta etapa pode demorar até 60 segundos para ser concluído. Durante este tempo, seu prompt não vai voltar.
Quando ela for concluída, você deve ver o seguinte:
Procedimento PL / SQL concluído com êxito.
Se tiver definido um longo tempo e estão a ficar impacientes, você pode abrir outra janela SQL para se certificar de que a tarefa ainda está em execução, digitando
task_name SELECT, status, execution_startFROM dba_advisor_log ONDE proprietário = 'HR'-
Você vê algo como o seguinte:
TASK_NAME STATUS EXECUTION_START ------------------------------ --------------- - ------------------ emp_dept_tuning_task EXECUTING19-JUL-2013 15:35:42
Quando a execução estiver concluída, você pode ver os resultados através da execução do procedimento BMS_SQLTUNE.report_tuning_task. Digite o seguinte:
SELECTDBMS_SQLTUNE.report_tuning_task ( 'emp_dept_tuning_task') AS recommendationsFROM dual-
Por razões de espaço, que já cortou algumas seções da saída que se segue, mas você vê algo como isto:
RECOMMENDATIONS---------------------------------------------------------------------------------GENERAL INFORMAÇÃO SECÇÃO ------------------------------------------------ --------------------------------- Ajuste Nome da Tarefa: emp_dept_tuning_taskTuning Task proprietário: Tipo HRWorkload: Single SQL StatementScope: Limite COMPREHENSIVETime (segundos): 60Completion status: INTERRUPTEDStarted em: 2013/07/19 15: 21: 39Completed em: 2013/07/19 15:22:43 ------------------ -------------------------------------------------- ------------- erro: ORA-13639: A operação atual foi interrompida porque cronometrado out.------------------------------------------------------------------------------------------------------------------------------------------------------------------Schema Nome: HRSQL ID: 47uvvzcuu5mdgSQL Texto: first_name SELECT, last_name, department_name DE JOINdepartments emp USANDO (department_id) ONDE last_name = 'Hopkins'RECOMMENDATIONS------------------------------------------------------------------------------------------------------------------------------------------------------------------FINDINGS SECÇÃO (1 constatação) --------------------------------------------- ------------------------------------ 1- Índice Finding (veja explicar secção planos abaixo) --- ----------------------------------------------- O plano de execução desta declaração pode ser melhorada através da criação de um ou moreindices.Recommendation (benefício estimado: 99,98%) ------------------------------ ------------- Considere executar o Supervisor de acesso para melhorar o esquema físico designor criar o HR.IDX índice index.create recomendado $$ _ 03170001 em HR.EMP ( "LAST_NAME") - Rationale-- ------- Criando os índices recomendados melhora significativamente a execução planof esta declaração. No entanto, pode ser preferível correr "Access Advisor" usando uma carga de trabalho SQL representante em oposição a uma única instrução. Thiswill permitem obter recomendações índice abrangente que leva intoRECOMMENDATIONS--------------------------------------------------------------------------------account sobrecarga de manutenção do índice e consumo de espaço adicional .... saída cortou ...
A última parte do relatório mostra o antes e depois de planos de execução. Neste caso, você já viu o antes quando estavam gerando planos de execução. Vá em frente e adicionar o índice, regenerar o plano de execução, e veja se você fez uma melhoria.
Antes de adicionar o índice, note que as recomendações dar o SQL para adicionar o índice:
Recomendação (benefício estimado: 99,98%) ------------------------------------------ - Considere executar o Supervisor de acesso para melhorar o esquema físico designor criar o HR.IDX índice index.create recomendado $$ _ 03170001 em HR.EMP ( "LAST_NAME") -
Observe também que a Oracle dá um aviso:
RECOMMENDATIONS--------------------------------------------------------------------------------account sobrecarga de manutenção do índice e consumo de espaço adicional.
Adicionar o índice com seu próprio nome, digitando o seguinte:
CREATE INDEX emp_last_name_idx ON emp (last_name) -
Você deve ver algo como o seguinte:
Índice criado.
Dê uma olhada no plano de execução. Digite o seguinte:
explicar plano first_name forSELECT, last_name, department_nameFROM emp juntar departamentos usando (department_id) ONDE last_name = 'Hopkins-Explained.Elapsed: 00: 00: 00,09
E digite
@? Rdbms admin utlxpls
Você deverá ver uma saída como esta:
PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------Plan valor de hash: 1505300146 ---------------------------------------------- ----------------------------------- | Id | operação | Nome | linhas | bytes | Custo (% CPU) | Tempo | ------------------------------------------------ --------------------------------- | 0 | Instrução SELECT | | 1 | 34 | 5 (0) | 00:00:01 || 1 | Nested Loops | ||| ||| 2 | Nested Loops | | 1 | 34 | 5 (0) | 00:00:01 || 3 | Acessar a tabela pelo índice ROWID agrupadas | EMP | 1 | 18 | 4 (0) | 00:00:01 || * 4 | INDEX RANGE SCAN | EMP_LAST_NAME_IDX | 1 || 3 (0) | 00:00:01 || * 5 | UNIQUE INDEX SCAN | DEPT_ID_PK | 1 || 0 (0) | 00:00:01 || 6 | ACESSO tabela por ROWID INDEX | DEPARTAMENTOS | 1 | 16 | 1 (0) | 00:00:01 | -------------------------------------------- ------------------------------------- Informação predicado (identificado pelo código de operação): ---- ----------------------------------------------- 4 - acesso (. "EMP" "LAST_NAME" = 'Hopkins') 5 - acesso ( "EMP" "DEPARTMENT_ID" = "departamentos" "DEPARTMENT_ID"..)
Agora que você adicionou o índice, algumas coisas são evidentes:
O custo do plano caiu 40336-5.
Há agora seis etapas.
A varredura completa da tabela está desaparecido. Em vez disso você vê o uso de seu novo índice.
Muitas vezes uma das partes difíceis sobre ajustar um banco de dados é ter uma sólida compreensão do pedido e os dados. A questão pode não ser sempre óbvias. Às vezes envolver outros especialistas em aplicações e dados ajuda.
Explicar-lhes seus resultados e que se propõe. Eles podem ser capazes de ajudá-lo a chegar a uma conclusão. Além disso, se os dados são parte de um aplicativo de terceiros embalados, às vezes abrindo um ticket com o fornecedor é o caminho a percorrer.