Πώς κάνουμε force ένα πλάνο σε βάση δεδομένων της Oracle
- Πώς ενεργοποιούμε το Unified Auditing σε Oracle Database - 7 Οκτώβριος 2024
- Τι είναι η PostgreSQL και πως κάνουμε μία πλήρης εγκατάσταση - 2 Σεπτέμβριος 2024
- Πώς ρυθμίζουμε το replication του GoldenGate ώστε να διαβάζει από το Standby του Oracle Data Guard - 2 Αύγουστος 2024
Σε προηγούμενο άρθρο είχαμε δει γιατί ένα query μπορεί να τρέχει με διαφορετικό πλάνο και πως κάνουμε force ένα στον SQL Server. Σε αυτό το άρθρο θα δούμε πως γίνεται force ένα πλάνο σε βάση δεδομένων της Oracle με χρήση SQL profile.
Γιατί το query μπορεί να τρέχει με διαφορετικό πλάνο
Όταν ο Query Optimizer διαλέγει ποιο πλάνο θα χρησιμοποιήσει εξαρτάται από πληροφορίες όπως τα στατιστικά, το πλήθος δεδομένων, των τιμών των μεταβλητών, των indexes που υπάρχουν και το SQL Profile.
Αν αλλάξει κάτι από τα δύο ο Optimizer θα δημιουργήσει ένα καινούργιο πλάνο.
Parameter sniffing και Adaptive Cursor Sharing
Όπως και στον SQL Server έτσι και στην Oracle έχουμε το πρόβλημα του parameter sniffing. Όταν τρέχουμε μια procedure ή ένα sql statement που δέχεται μία παράμετρο ανάλογα με την τιμή, μπορεί το query να επιστρέφει 1 γραμμή είτε 100.000. Για να χρησιμοποιεί ο Query Optimizer διαφορετικό πλάνο αν αυξάνει την απόδοση η Oracle έχει φέρει το Adaptive Cursor Sharing.
Πώς κάνουμε force ένα πλάνο με χρήση SQL profile
To SQL Profile παρέχει στατιστικά για ένα SQL Statement και μέσα από αυτά τα στατιστικά ο Query Optimizer θα διαλέξει ποιο πλάνο θα χρησιμοποιήσει.
Μπορούμε να δημιουργήσουμε ένα SQL Profile όμως ώστε να χρησιμοποιούμε κάποιο παρελθοντικό πλάνο που είτε βρίσκεται στην μνήμη (library_cache) είτε στο συστημικό view dba_hist_sqlplan.
Ο Carolos Sierra από το Oracle Support έχει φτιάξει ένα script με το όνομα coe_xfr_sql_profile.sql το οποίο κάνει εύκολη την δημιουργία SQL Profile που κάνει force ένα πλάνο σε ένα SQL Statement.
Παράδειγμα
Εκτελώντας ένα query δύο φορές, βλέπουμε στο συστημικό view gv$sqlarea ότι το ίδιο SQL_ID έχει πολύ διαφορετικό χρόνο εκτέλεσης ανά πλάνο. Από εκεί κρατάμε το SQL_ID και το PLAN_HASH_VALUE που είναι πιο γρήγορο και θέλουμε να κάνουμε force.
select inst_id,sql_text,sql_fulltext,sql_id,hash_value,plan_hash_value,elapsed_time,last_load_time from gv$sqlarea where sql_text like '%SELECT h.LOGON_TIME%' order by sql_id;
Εκτελώντας ένα query δύο φορές βλέπουμε το ίδιο query που έχει το ίδιο SQL_ID να έχει διαφορετικό PLAN_HASH_VALUE δηλαδή διαφορετικό πλάνο.
Σε περίπτωση που δεν ξέρουμε το SQL text αλλά έχουμε πληροφορίες για το session. Τότε σε αυτό το άρθρο μπορούμε να δούμε πως μέσα από το συστημικό view gv$session θα βρούμε εύκολα το SQL_ID με το PLAN_HASH_VALUE που έτρεξε την κάθε φορά.
Πριν ξεκινήσουμε θα πρέπει πρώτα να κατεβάσουμε το script coe_xfr_sql_profile.sql.
Μεταφέρουμε το script σε κάποιον φάκελο και το εκτελούμε βάζοντας το SQL_ID του και το PLAN_HASH_VALUE που θα θέλουμε να έχει για πάντα από εδώ και πέρα:
@coe_xfr_sql_profile.sql 4wtpa4vzh8rpb 1851898411;
Έπειτα αφού το εκτελέσουμε, στο path που βρίσκεται το script θα δημιουργηθεί ένα καινούργιο coe_xfr_sql_profile_sql_id.sql:
Αφού εκτελέσουμε και αυτό με sqlplus θα δημιουργηθεί το SQL Profile:
@coe_xfr_sql_profile_sql_id.sql
Μπορούμε να δούμε ότι SQL Profile έχει δημιουργηθεί από το view dba_sql_profiles.
select * from dba_sql_profiles where 1=1 and name like '%4wtpa4vzh8rpb%' --sqlID --and sql_text like '%h.LOGON_TIME%' --SQL TEXT
Πλέον όσες φορές εκτελέσουμε το query θα τρέχει με το ίδιο πλάνο.
Πως μπορούμε να δούμε τις αλλαγές πλάνου σε query μέσα στον χρόνο
Με το παρακάτω query μπορούμε να δούμε πόσες φορές άλλαξε το πλάνο ενός query σε ποιο και το πότε:
SELECT trunc(sample_time),h.session_id,h.SESSION_SERIAL#,h.sql_id, h.sql_plan_hash_value, SUM(10) ash_secs, 10*COUNT(h.sql_id) awr_secs,min(sample_time) FROM dba_hist_snapshot X, dba_hist_active_sess_history h LEFT OUTER JOIN dba_hist_sqltext t ON t.sql_id = h.sql_id WHERE 1=1 and h.sql_id='3c1kubcdjnppq' and x.snap_id=h.snap_id --and h.dbid=xxxxx and x.snap_id between (select max(snap_id) - 800 from dba_hist_snapshot) and (select max(snap_id) from dba_hist_snapshot) GROUP BY trunc(sample_time),h.session_id,h.SESSION_SERIAL#,h.sql_id, h.sql_plan_hash_value order by min(sample_time) desc;
* Σε περίπτωση όμως που δεν έχουμε Oracle DB enterprise edition αλλά standard δεν θα μπορούμε να βρούμε την πληροφορία από ιστορικά views οπότε μπορούμε να δούμε μόνο από την μνήμη library cache με το παρακάτω query.
select plan_hash_value,sql_id,timestamp from gv$sql_plan where SQL_ID='3c1kubcdjnppq' order by timestamp desc
Μπορούμε να δούμε ολόκληρο το SQL Statement έχοντας το SQL_ID με τα πλήρες πλάνα που έτρεξε:
SELECT * from table(dbms_xplan.display_awr('3c1kubcdjnppq',null,null,'ADVANCED'));
Για να σβήσουμε ένα κακό πλάνο από το shared pool της SGA
Για σβήσουμε από την μνήμη ένα κακό πλάνο από την SGA θα πρέπει να κάνουμε χρήση της procedure dbms_shared_pool.purge.
Πρώτα βρίσκουμε την τιμή που θέλουμε να σβήσουμε από το view sqlarea:
select address, hash_value from gv$sqlarea where sql_id='4wtpa4vzh8rpb';
Έχοντας την πληροφορία που χρειαζόμαστε μπορούμε να εκτελέσουμε την παρακάτω procedure:
begin sys.DBMS_SHARED_POOL.purge('000000006AE41DF0,4278476459', 'C'); end;
Για να σβήσουμε το SQL Profile
Αν θέλουμε να σβήσουμε ένα από τα SQL Profiles που έχουμε δημιουργήσει, τότε τρέχουμε το παρακάτω συμπληρώνοντας το ανάλογο όνομα του SQL Profile:
begin dbms_sqltune.drop_sql_profile('coe_4wtpa4vzh8rpb_185189411'); end;
How do you determine when an sql profile has become stale
If query performance suddenly declined.