I got a colleague asking for help today, saying that there is a select query that runs for more than a minute on Oracle, and he hopes to produce results within 5s, as follows:
Select/*+ Parallel (SRC, 8) */distinct src.systemname as SystemName, src.databasename as DatabaseName, Src.tablename as TableName, src.username as username from <strong>meta_dbql_table_usage_exp_hst</stron g> src inner <strong>join dr_qry_log_exp_hst</strong> rl on <strong>src.acctstringdate = Rl.acct StringDate and Src.queryid = rl.queryid</strong> and Src.systemname = Rl.systemname and Src.acctstringda Te > Sysdate-30 and rl.acctstringdate > Sysdate-30 inner JOIN <STRONG>META_DR_QRY_LOG_TGT_ALL_HST </STRONG>TGT on upper (tgt.systemname) = Upper (' MOZART ') and Upper (TGT. Databasename) = Upper (' gdw_tables ') and Upper (TGT. TableName) = Upper (' ssa_slng_lstg_mtrc_sd ') <strong>and src.acctstringdate = Tgt.acctstringdate and Rl.state ment_id = tgt.statement_id</strong> and Rl.systemname = Tgt.systemname and tgt.acctstringdate > Sysdate- AnD Not (Upper (tgt.systemname) =upper (src.systemname) and Upper (tgt.databasename) = Upper (Src.databasename) and Upper (tgt.tablename) = Upper (Src.tablename)) and TGT. SystemName is not null and TGT. Databasename is not null and tgt.tablename are NOT null;
A simple analysis of SQL
In a way, this SQL is the inner JOIN of three tables (META_DBQL_TABLE_USAGE_EXP_HST,DR_QRY_LOG_EXP_HST,META_DR_QRY_LOG_TGT_ALL_HST), These three table data volumes are millions, and are partitioned tables (with Acctstringdate as the partitioning key), and the execution plan is as follows:
-----------------------------------------------------
---------------------------------------------------------- ---------
| Id | Operation |
Name | Rows | Bytes | Cost | Pstart|
Pstop | --------------------------------------------------------------------------------
------------------------------- ---------
| 0 | SELECT STATEMENT |
| 1 | 159 | 8654 | |
| | 1 | PX Coordinator |
| | | | |
| | 2 | PX SEND QC (RANDOM) |
: TQ10002 | 1 | 159 | 8654 | |
| | 3 | SORT UNIQUE |
| 1 | 159 | 8654 | |
| | 4 | PX RECEIVE |
| 1 | 36 | 3 | |
|| 5 | PX SEND HASH |
: TQ10001 | 1 | 36 | 3 | |
| |* 6 | TABLE ACCESS by local INDEX rowid|
Dr_qry_log_exp_hst | 1 | 36 | 3 | |
| | 7 | NESTED LOOPS |
| 1 | 159 | 8633 | |
| | 8 | NESTED LOOPS |
| 8959 | 1076k| 4900 | |
| | 9 | BUFFER SORT |
| | | | |
| | 10 | PX RECEIVE |
| | | | |
| | 11 | PX SEND Broadcast |
: TQ10000 | | | | |
| | 12 | PARTITION RANGE Iterator |
| 1 | 56 | 4746 | KEY |
14 ||* 13 | TABLE ACCESS Full |
Meta_dr_qry_log_tgt_all_hst | 1 | 56 | 4746 | KEY |
14 | | 14 | PX Block Iterator |
| 8959 | 586k| 154 | KEY |
KEY | |* 15 | TABLE ACCESS Full |
Meta_dbql_table_usage_exp_hst | 8959 | 586k| 154 | KEY |
KEY | | 16 | PARTITION RANGE Iterator |
| 1 | | 2 | KEY |
KEY | |* 17 | INDEX RANGE SCAN |
Dr_qry_log_exp_hst_idx | 1 | | 2 | KEY |
KEY | --------------------------------------------------------------------------------
-------------------------------
---------predicate information (identified by Operation ID):--------------------------------------------------- 6-filter ("RL".) statement_id "=" TGT "." statement_id "and" RL "." SystemName "=" TGT "." SystemName "and" SRC "." SystemName "=" RL "." SystemnaME ") 13-filter (UPPER (" TGT "). SystemName ") = ' MOZART ' and UPPER (" TGT ".) DATABASENAME ") = ' gdw_tables ' and UPPER (" TGT ".) TableName ") = ' ssa_slng_lstg_mtrc_sd ' and ' TGT '." Acctstringdate ">sysdate@!-30" and "TGT". SystemName ' is ' not NULL ' TGT '. DATABASENAME ' is ' not NULL and ' TGT '. TableName ' is not NULL ' 15-filter ("SRC".) Acctstringdate "=" TGT "." Acctstringdate "and" (UPPER ("TGT"). SystemName ") <>upper (" SRC ".) SystemName ") OR UPPER (" TGT ".) DATABASENAME ") <>upper (" SRC ".) DATABASENAME ") OR UPPER (" TGT ".) TableName ") <>upper (" SRC ".) TableName ")) and" SRC ". Acctstringdate ">sysdate@!-30" 17-access ("SRC".) Queryid "=" RL "." Queryid "and" SRC "." Acctstringdate "=" RL "." Acctstringdate ") filter (" RL ".) Acctstringdate ">sysdate@!-30)