Κάνοντας profiling σε εφαρμογές PL/SQL

Ένα από τα πιο ενδιαφέροντα και απαιτητικά έργα τα οποία έχω αναλάβει είναι αυτό της μεταφοράς (migration) μιας βάσης δεδομένων φτιαγμένη σε Oracle XML DB (document oriented) σε σχεσιακή (relational) μορφή. Ο αλγόριθμός αυτής της διαδικασίας κατασκευάστηκε σε PL/SQL και δεν άργησε να έλθει η ώρα για βελτιστοποιήσεις ώστε να μειωθεί ο χρόνος της μετάπτωσης των δεδομένων. Γι' αυτό το λόγο χρησιμοποιήσα το πακέτο DBMS_PROFILER που καταγράφει τους χρόνους εκτέλεσης των εντολών της PL/SQL σε ένα profiling session.

Η εγκατάσταση αυτής της δυνατότητας έγινε ως εξής:

1. Ως χρήστης SYS εκτελούμε το script $ORACLE_HOME/rdbms/admin/profload.sql
2. Δίνουμε δικαιώματα εκτέλεσης στο πακέτο dbms_profiler σε όλους τους χρήστες της βάσης: GRANT EXECUTE ON dbms_profiler to PUBLIC;
3. Ως ένας database user, κατασκευάζουμε στο σχήμα μας τους πίνακες καταγραφής του profiling εκτελώντας το script: $ORACLE_HOME/rdbms/admin/proftab.sql
4. Ως αποτέλεσμα θα δημιουργηθούν τρεις πίνακες με το πρόθεμα PLSQL_PROFILER στο σχήμα μας.
   

Τώρα είμαστε σε θέση να κάνουμε profiling τη PL/SQL μας, προσθέτοντας την ακόλουθη γραμμή στην αρχή (entry-point) του κώδικά μας:

dbms_profiler.start_profiler

και την αντίστοιχη εντολή κλεισίματος του profiling session στο τέλος του PL/SQL κώδικά μας:
dbms_profiler.stop_profiler

Τα αποτελέσματα του profiler καταγράφονται ανά database session και ένας αποτελεσματικός τρόπος να τα παρακολουθήσουμε είναι μέσω ενός custom report του SQL Developer της AMIS που βρίσκεται εδώ: http://technology.amis.nl/blog/2327/dbms_profiler-report-for-sql-developer To report εμφανίζει σε μορφή γραφήματος τον χρόνο εκτέλεσης ανά PL/SQL package και έπειτα σε αναλυτική μορφή τους min/max χρόνους εκτέλεσης ανά γραμμή κώδικα καθώς και τον συνολικό χρόνο και αριθμό των εκτελέσεων της γραμμής.



Μια πιο χρήσιμη γενίκευση αυτού του reporting, ειδικά εάν ο PL/SQL κώδικας μας ενεργοποιείται από πολλαπλά sessions μέσω connection pooling ή parallel execution, μπορεί να προκύψει τρέχοντας το script $ORACLE_HOME/plsql/demo/profsum.sql ως ο χρήστης που φτιάχτηκαν οι πίνακες του profiling. Αναφέρω ενδεικτικά τα reports που παράγονται κατά αυτόν τον τρόπο:

• Percentage of time in each module, for each run separately
• Percentage of time in each module, summarized across runs
• Lines taking more than 1% of the total time, each run separate
• Most popular lines (more than 1%), summarize across all runs
• ...