Hoje vou demonstrar o uso da package DBMS_SQLTUNE para gerar relatórios de monitoramento de SQL’s. Esse recurso foi adicionado na versão 11g, porém algumas funcionalidades entraram apenas na versão 11gR2.

CONFIGURAÇÃO DO AMBIENTE

Primeiramente, vamos conferir dois parâmetros obrigatórios para usar esse recurso:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBjb25uIC8gYXMgc3lzZGJhCkNvbmVjdGFkby4KClNRTD4gU0hPVyBQQVJBTUVURVIgc3RhdGlzdGljc19sZXZlbApOQU1FICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgVFlQRSAgICAgICAgVkFMVUUKLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tLS0tLS0tLS0Kc3RhdGlzdGljc19sZXZlbCAgICAgICAgICAgICAgICAgICAgIHN0cmluZyAgICAgIFRZUElDQUwKClNRTD4gU0hPVyBQQVJBTUVURVIgY29udHJvbF9tYW5hZ2VtZW50X3BhY2tfYWNjZXNzCk5BTUUgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBUWVBFICAgICAgICBWQUxVRQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gLS0tLS0tLS0tLS0gLS0tLS0tLS0tLS0tLS0tLS0tLQpjb250cm9sX21hbmFnZW1lbnRfcGFja19hY2Nlc3MgICAgICAgc3RyaW5nICAgICAgRElBR05PU1RJQytUVU5JTkc=” hover_enabled=”0″]U1FMPiBjb25uIC8gYXMgc3lzZGJhCkNvbmVjdGFkby4KClNRTD4gU0hPVyBQQVJBTUVURVIgc3RhdGlzdGljc19sZXZlbApOQU1FICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgVFlQRSAgICAgICAgVkFMVUUKLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tLS0tLS0tLS0Kc3RhdGlzdGljc19sZXZlbCAgICAgICAgICAgICAgICAgICAgIHN0cmluZyAgICAgIFRZUElDQUwKClNRTD4gU0hPVyBQQVJBTUVURVIgY29udHJvbF9tYW5hZ2VtZW50X3BhY2tfYWNjZXNzCk5BTUUgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBUWVBFICAgICAgICBWQUxVRQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gLS0tLS0tLS0tLS0gLS0tLS0tLS0tLS0tLS0tLS0tLQpjb250cm9sX21hbmFnZW1lbnRfcGFja19hY2Nlc3MgICAgICAgc3RyaW5nICAgICAgRElBR05PU1RJQytUVU5JTkc=[/et_pb_dmb_code_snippet]

O parâmetro statistics_level tem que estar com o valor “TYPICAL” ou “ALL”. O parâmetro
control_management_pack_access tem que estar com o valor “DIAGNOSTIC+TUNING”. No meu ambiente, conforme vimos acima, está tudo OK.

/*+ MONITOR */

Vamos usar o hint MONITOR no SQL que desejamos monitorar, conforme o exemplo abaixo.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBjb25uIGhyL2hyCkNvbmVjdGFkby4KU1FMPiBzZXQgbGluZXMgMTIwClNRTD4gc2V0IHBhZ2VzIDEwMApTUUw+IFNFTEVDVCAvKisgTU9OSVRPUiAqLyBlLmZpcnN0X25hbWV8fCcgJ3x8ZS5sYXN0X25hbWUgbm9tZSwKICAgICAgICAgICAgICAgICAgICAgICAgICAgai5qb2JfdGl0bGUsIGQuZGVwYXJ0bWVudF9uYW1lCiAgMiAgRlJPTSBlbXBsb3llZXMgZSwgam9icyBqLCBkZXBhcnRtZW50cyBkCiAgMyAgV0hFUkUgZS5kZXBhcnRtZW50X2lkID0gZC5kZXBhcnRtZW50X2lkCiAgNCAgQU5EIGUuam9iX2lkID0gai5qb2JfaWQ7″ hover_enabled=”0″]U1FMPiBjb25uIGhyL2hyCkNvbmVjdGFkby4KU1FMPiBzZXQgbGluZXMgMTIwClNRTD4gc2V0IHBhZ2VzIDEwMApTUUw+IFNFTEVDVCAvKisgTU9OSVRPUiAqLyBlLmZpcnN0X25hbWV8fCcgJ3x8ZS5sYXN0X25hbWUgbm9tZSwKICAgICAgICAgICAgICAgICAgICAgICAgICAgai5qb2JfdGl0bGUsIGQuZGVwYXJ0bWVudF9uYW1lCiAgMiAgRlJPTSBlbXBsb3llZXMgZSwgam9icyBqLCBkZXBhcnRtZW50cyBkCiAgMyAgV0hFUkUgZS5kZXBhcnRtZW50X2lkID0gZC5kZXBhcnRtZW50X2lkCiAgNCAgQU5EIGUuam9iX2lkID0gai5qb2JfaWQ7[/et_pb_dmb_code_snippet]

REPORT_SQL_MONITOR

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBjb25uIC8gYXMgc3lzZGJhCkNvbmVjdGFkby4KU1FMPiBTRUxFQ1Qgc3FsX2lkLCBzdGF0dXMsIHNxbF90ZXh0CiAgMiAgRlJPTSAgIHYkc3FsX21vbml0b3IKICAzICBXSEVSRSAgdXNlcm5hbWUgPSAnSFInOwoKU1FMX0lEICAgICAgICBTVEFUVVMKLS0tLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tClNRTF9URVhUCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQoKOTA4YmpncTR5NzF1NCBET05FIChBTEwgUk9XUykKU0VMRUNUIC8qKyBNT05JVE9SICovIGUuZmlyc3RfbmFtZXx8JyAnfHxlLmxhc3RfbmFtZSBub21lLApqLmpvYl90aXRsZSwgZC5kZXBhcnRtZW50X25hbWUKRlJPTSBlbXBsb3llZXMgZSwgam9icyBqLCBkZXBhcnRtZW50cyBkCldIRVJFIGUuZGVwYXJ0bWVudF9pZCA9IGQuZGVwYXJ0bWVudF9pZApBTkQgZS5qb2JfaWQgPSBqLmpvYl9pZApPIHNlbGVjdCBhY2ltYSBpZGVudGlmaWNvdSBvIFNRTCBxdWUgZXhlY3V0ZWkgYW50ZXJpb3JtZW50ZSBubyBzY2hlbWEgSFIuIENvbSBpc3NvIGV1IGNvbnNlZ3VpIG8gc2V1IElEIChjYW1wbyBTUUxfSUQpLg==” hover_enabled=”0″]U1FMPiBjb25uIC8gYXMgc3lzZGJhCkNvbmVjdGFkby4KU1FMPiBTRUxFQ1Qgc3FsX2lkLCBzdGF0dXMsIHNxbF90ZXh0CiAgMiAgRlJPTSAgIHYkc3FsX21vbml0b3IKICAzICBXSEVSRSAgdXNlcm5hbWUgPSAnSFInOwoKU1FMX0lEICAgICAgICBTVEFUVVMKLS0tLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tClNRTF9URVhUCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQoKOTA4YmpncTR5NzF1NCBET05FIChBTEwgUk9XUykKU0VMRUNUIC8qKyBNT05JVE9SICovIGUuZmlyc3RfbmFtZXx8JyAnfHxlLmxhc3RfbmFtZSBub21lLApqLmpvYl90aXRsZSwgZC5kZXBhcnRtZW50X25hbWUKRlJPTSBlbXBsb3llZXMgZSwgam9icyBqLCBkZXBhcnRtZW50cyBkCldIRVJFIGUuZGVwYXJ0bWVudF9pZCA9IGQuZGVwYXJ0bWVudF9pZApBTkQgZS5qb2JfaWQgPSBqLmpvYl9pZApPIHNlbGVjdCBhY2ltYSBpZGVudGlmaWNvdSBvIFNRTCBxdWUgZXhlY3V0ZWkgYW50ZXJpb3JtZW50ZSBubyBzY2hlbWEgSFIuIENvbSBpc3NvIGV1IGNvbnNlZ3VpIG8gc2V1IElEIChjYW1wbyBTUUxfSUQpLg==[/et_pb_dmb_code_snippet]

Com este ID eu consigo gerar o relatório usando o REPORT_SQL_MONITOR.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBTRVQgTE9ORyAxMDAwMApTUUw+IFNFVCBMT05HQ0hVTktTSVpFIDEwMDAwClNRTD4gU0VUIExJTkVTSVpFIDEwMDAKU1FMPiBTRVQgUEFHRVNJWkUgMApTUUw+IFNFVCBUUklNIE9OClNRTD4gU0VUIFRSSU1TUE9PTCBPTgpTUUw+IFNFVCBFQ0hPIE9GRgpTUUw+IFNFVCBGRUVEQkFDSyBPRkYKClNRTD4gU1BPT0wgRDpXb3JrdGVzdGVzcmVwb3J0X3NxbC5odG0KU1FMPiBTRUxFQ1QgREJNU19TUUxUVU5FLnJlcG9ydF9zcWxfbW9uaXRvcigKICAyICAgIHNxbF9pZCA9PiAnOTA4YmpncTR5NzF1NCcsCiAgMyAgdHlwZSA9PiAnSFRNTCcsCiAgNCAgICByZXBvcnRfbGV2ZWwgPT4gJ0FMTCcpIEFTIHJlcG9ydAogIDUgIEZST00gZHVhbDsKClNRTD4gU1BPT0wgT0ZG” hover_enabled=”0″]U1FMPiBTRVQgTE9ORyAxMDAwMApTUUw+IFNFVCBMT05HQ0hVTktTSVpFIDEwMDAwClNRTD4gU0VUIExJTkVTSVpFIDEwMDAKU1FMPiBTRVQgUEFHRVNJWkUgMApTUUw+IFNFVCBUUklNIE9OClNRTD4gU0VUIFRSSU1TUE9PTCBPTgpTUUw+IFNFVCBFQ0hPIE9GRgpTUUw+IFNFVCBGRUVEQkFDSyBPRkYKClNRTD4gU1BPT0wgRDpXb3JrdGVzdGVzcmVwb3J0X3NxbC5odG0KU1FMPiBTRUxFQ1QgREJNU19TUUxUVU5FLnJlcG9ydF9zcWxfbW9uaXRvcigKICAyICAgIHNxbF9pZCA9PiAnOTA4YmpncTR5NzF1NCcsCiAgMyAgdHlwZSA9PiAnSFRNTCcsCiAgNCAgICByZXBvcnRfbGV2ZWwgPT4gJ0FMTCcpIEFTIHJlcG9ydAogIDUgIEZST00gZHVhbDsKClNRTD4gU1BPT0wgT0ZG[/et_pb_dmb_code_snippet]

Pronto! Um arquivo em formato HTML foi criado no diretório especificado  –
D:Worktestesreport_sql.htm. O Arquivo mostra vários detalhes do SQL monitorado: plano de execução, plan hash, horários exatos de execução, duração, qual sessão executou, buffer gets, database time, entre outros.

REPORT_SQL_DETAIL

Essa função retorna detalhes do monitoramento, gerando um HTML da mesma maneira que o exemplo acima. Testem e aproveitem!

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBTUE9PTCBEOldvcmt0ZXN0ZXNyZXBvcnRfc3FsX2RldGFpbC5odG0KU1FMPiBTRUxFQ1QgREJNU19TUUxUVU5FLnJlcG9ydF9zcWxfZGV0YWlsKAogIDIgICAgc3FsX2lkID0+ICc5MDhiamdxNHk3MXU0JywKICAzICAgIHR5cGUgPT4gJ0FDVElWRScsCiAgNCAgICByZXBvcnRfbGV2ZWwgPT4gJ0FMTCcpIEFTIHJlcG9ydAogIDUgICAgICAgIEZST00gZHVhbDsKClNRTD4gU1BPT0wgT0ZG” hover_enabled=”0″]U1FMPiBTUE9PTCBEOldvcmt0ZXN0ZXNyZXBvcnRfc3FsX2RldGFpbC5odG0KU1FMPiBTRUxFQ1QgREJNU19TUUxUVU5FLnJlcG9ydF9zcWxfZGV0YWlsKAogIDIgICAgc3FsX2lkID0+ICc5MDhiamdxNHk3MXU0JywKICAzICAgIHR5cGUgPT4gJ0FDVElWRScsCiAgNCAgICByZXBvcnRfbGV2ZWwgPT4gJ0FMTCcpIEFTIHJlcG9ydAogIDUgICAgICAgIEZST00gZHVhbDsKClNRTD4gU1BPT0wgT0ZG[/et_pb_dmb_code_snippet]