Πώς κάνουμε force ένα πλάνο σε βάση δεδομένων της Oracle

• Σχετικά με
• τελευταίες δημοσιεύσεις

Stratos Matzouranis

Το ταξίδι άρχισε το 2008 όπου ξεκίνησα να εργάζομαι επίσημα στον τομέα της τεχνολογίας πληροφορικής (IT). Ξεκινώντας το πρώτο εξάμηνο της σχολής κατάλαβα μια ιδιαίτερη έλξη προς τις βάσεις δεδομένων και τους αυτοματισμούς.Έχω ασχοληθεί με βάσεις δεδομένων όπως Microsoft SQL Server / Oracle Database, ανάλυση δεδομένων και αυτοματισμούς με τη χρήση γραμμής εντολών (CLI), Visual Basic for Applications και Python. Μέσα από τα χρόνια εμπειρίας εξέλιξα αυτές τις δυνατότητες ώστε να μπορώ να κάνω τη ζωή μου πιο εύκολη. Για μένα ο σκοπός του κάθε IT guy αλλά και κάθε υπαλλήλου γραφείου είναι το να έχει τις γνώσεις ώστε μέσω εργαλείων να μπορεί να δουλεύει λίγο αλλά να παράγει πολύ. Μέσα από αυτό τον ιστότοπο του DataPlatform.gr προσπαθώ να προσφέρω γνώσεις και να προτείνω λύσεις σε καθημερινά προβλήματα.

Πιστοποιήσεις:

Τελευταίες δημοσιεύσεις από Stratos Matzouranis (Προβολή όλων)

• Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Linux - 1 Ιούλιος 2024
• Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
• Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 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;

Πηγές:

• Managing SQL Profiles
• Adaptive Cursor Sharing