Could you please help me to optimize the below SQL statement and/or optimize database server configuration, this spent three days and still executing. I am thankful in advance. @"ToM_PL"
CREATE TABLE elmag_eqs_db
AS
SELECT /*+ PARALLEL(BIGDATA) */
EQ.EQ_NUM AS EQ_NUM,
SDO_GEOM.SDO_DISTANCE (GEOM1 => EQ.GEOM,
GEOM2 => D.GEOM,
TOL => 0.005,
UNIT => 'unit=KM') DISTANCE_KM,
EXTRACT (HOUR FROM (EQ.TIME_ - D.UT)) INTERVAL_H
FROM BIGDATA D
JOIN EQLISTS EQ
ON D.UT BETWEEN (EQ.TIME_ - (48 / 24)) AND EQ.TIME_
AND SDO_RELATE (
SDO_UTIL.CIRCLE_POLYGON (
POINT => EQ.GEOM,
RADIUS => POWER (10, (0.43 * EQ.MAG)) * 1000,
ARC_TOLERANCE => 0.05),
D.GEOM,
'mask=covers') =
'TRUE'
AND D.KP < 3
AND ( D.LOCAL_TIME BETWEEN 18 AND 24
OR D.LOCAL_TIME BETWEEN 0 AND 6);
SQL> select count(*) from EQLISTS;
COUNT(*)
----------
9328
SQL> select count(*) from BIGDATA ;
COUNT(*)
----------
27347148
The explain plan
Plan Hash Value : 860728825
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 26185014 | 13616207280 | 1653962 | 00:01:05 |
| 1 | LOAD AS SELECT | ELMAG_EQS_DB | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 26185014 | 13616207280 | 1304903 | 00:00:51 |
| 3 | MERGE JOIN | | 26185014 | 13616207280 | 1304903 | 00:00:51 |
| 4 | SORT JOIN | | 9328 | 1389872 | 382 | 00:00:01 |
| 5 | TABLE ACCESS FULL | EQLISTS | 9328 | 1389872 | 71 | 00:00:01 |
| * 6 | FILTER | | | | | |
| * 7 | SORT JOIN | | 11444797 | 4246019687 | 1278762 | 00:00:50 |
| * 8 | TABLE ACCESS FULL | BIGDATA | 11444797 | 4246019687 | 372374 | 00:00:15 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 6 - filter("D"."UT"<="EQ"."TIME_" AND
"MDSYS"."SDO_RELATE"("SDO_UTIL"."CIRCLE_POLYGON"("POINT"=>"EQ"."GEOM","RADIUS"=>POWER(10,0.43*"EQ"."MAG")*1000,"ARC_TOLERANCE"=>0.05),"D"."GEOM",'mask=covers')='TRUE')
* 7 - access("D"."UT">=INTERNAL_FUNCTION("EQ"."TIME_")-2)
* 7 - filter("D"."UT">=INTERNAL_FUNCTION("EQ"."TIME_")-2)
* 8 - filter(("D"."LOCAL_TIME">=18 AND "D"."LOCAL_TIME"<=24 OR "D"."LOCAL_TIME"<=6 AND "D"."LOCAL_TIME">=0) AND "D"."KP"<3)
The database server configuration parameters is as below:
Oracle Database 12c Enterprise Edition: 12.2.0.1.0 - 64bit Production
Server OS: Windows Server 2016
Server memory: 64G
SQL> show parameter pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep string
buffer_pool_recycle string
java_pool_size big integer 0
large_pool_size big integer 12G
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 96M
shared_pool_size big integer 0
streams_pool_size big integer 0
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 32G
sga_min_size big integer 0
sga_target big integer 32G
unified_audit_sga_queue_size integer 1048576
SQL> show parameter pga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 12G
pga_aggregate_target big integer 6G
SQL> show parameter workarea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
inmemory_adg_enabled boolean TRUE
inmemory_clause_default string
inmemory_expressions_usage string ENABLE
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0
inmemory_query string ENABLE
inmemory_size big integer 0
inmemory_trickle_repopulate_servers_percent integer 1
inmemory_virtual_columns string MANUAL
memory_max_target big integer 0
memory_target big integer 0
optimizer_inmemory_aware boolean TRUE
shared_memory_address integer 0