Query (id=334462f3d1599444:711e12be8225b88a) Summary Session ID: b747226571af70c1:a0c7401f9610a182 Session Type: HIVESERVER2 HiveServer2 Protocol Version: V6 Start Time: 2016-10-14 11:00:13.277720000 End Time: Query Type: QUERY Query State: CREATED Query Status: OK Impala Version: impalad version 2.5.0-cdh5.7.1 RELEASE (build 27a4325c18c2a01c7a8097681a0eccf6d4335ea1) User: x194245 Connected User: x194245 Delegated User: Network Address: 48.24.145.193:57943 Default Db: retpocd01 Sql Statement: SELECT `v_t_partcpnt_no_test_dummy_dim`.`partcpnt_key`, `v_t_partcpnt_no_test_dummy_dim`.`yyyymm_key`, `v_t_partcpnt_no_test_dummy_dim`.`age_orig`, `v_t_partcpnt_no_test_dummy_dim`.`alt_ste_cd`, `v_t_partcpnt_no_test_dummy_dim`.`brth_dt_orig`, `v_t_partcpnt_no_test_dummy_dim`.`cntrbn_escltn_elect_cd`, `v_t_partcpnt_no_test_dummy_dim`.`cntrbn_escltn_incrmt_amt`, `v_t_partcpnt_no_test_dummy_dim`.`cntrbn_escltn_incrmt_pct`, `v_t_partcpnt_no_test_dummy_dim`.`dc_alt_vestg_dt`, `v_t_partcpnt_no_test_dummy_dim`.`dc_goalmkr_enrld_ind`, `v_t_partcpnt_no_test_dummy_dim`.`dc_goalmkr_portf_id`, `v_t_partcpnt_no_test_dummy_dim`.`dc_partcpnt_acct_ty_cd`, `v_t_partcpnt_no_test_dummy_dim`.`dc_partcpnt_acct_ty_desc`, `v_t_partcpnt_no_test_dummy_dim`.`dc_partcpnt_enrlmt_dt`, `v_t_partcpnt_no_test_dummy_dim`.`dc_partcpnt_stat_cd`, `v_t_partcpnt_no_test_dummy_dim`.`dc_partcpnt_stat_desc`, `v_t_partcpnt_no_test_dummy_dim`.`dc_partcpnt_ty_desc`, `v_t_partcpnt_no_test_dummy_dim`.`divsion_sub_id`, `v_t_partcpnt_no_test_dummy_dim`.`dth_dt`, `v_t_partcpnt_no_test_dummy_dim`.`ee_hire_dt_orig`, `v_t_partcpnt_no_test_dummy_dim`.`ee_num`, `v_t_partcpnt_no_test_dummy_dim`.`ee_rehire_dt`, `v_t_partcpnt_no_test_dummy_dim`.`ee_termn_dt`, `v_t_partcpnt_no_test_dummy_dim`.`ee_ty_cd`, `v_t_partcpnt_no_test_dummy_dim`.`gender_cd`, `v_t_partcpnt_no_test_dummy_dim`.`hce_ind`, `v_t_partcpnt_no_test_dummy_dim`.`home_city_nm`, `v_t_partcpnt_no_test_dummy_dim`.`home_frst_st_addr_ln`, `v_t_partcpnt_no_test_dummy_dim`.`home_sec_st_addr_ln`, `v_t_partcpnt_no_test_dummy_dim`.`home_ste_cd_desc`, `v_t_partcpnt_no_test_dummy_dim`.`home_zip_cd`, `v_t_partcpnt_no_test_dummy_dim`.`hrs_of_svc_amt`, `v_t_partcpnt_no_test_dummy_dim`.`orig_auto_enrlmt_dt`, `v_t_partcpnt_no_test_dummy_dim`.`partcpnt_dso_ind`, `v_t_partcpnt_no_test_dummy_dim`.`partcpnt_frst_nm`, `v_t_partcpnt_no_test_dummy_dim`.`partcpnt_last_nm`, `v_t_partcpnt_no_test_dummy_dim`.`partcpnt_mid_init`, `v_t_partcpnt_no_test_dummy_dim`.`partl_goalmkr_pct`, `v_t_partcpnt_no_test_dummy_dim`.`ppa_deflt_invst_ind`, `v_t_partcpnt_no_test_dummy_dim`.`ssn`, `v_t_partcpnt_no_test_dummy_dim`.`univl_cust_id` FROM `retpocd01`.`v_t_partcpnt_no_test_dummy_dim` `v_t_partcpnt_no_test_dummy_dim` Coordinator: paehowchq1d02.prudential.com:22000 Query Options (non default): Plan: ---------------- Estimated Per-Host Requirements: Memory=3.44GB VCores=1 WARNING: The following tables are missing relevant table and/or column statistics. retpocd01.v_t_partcpnt_no_test_dummy_dim 01:EXCHANGE [UNPARTITIONED] | hosts=3 per-host-mem=unavailable | tuple-ids=0 row-size=543B cardinality=unavailable | 00:SCAN HDFS [retpocd01.v_t_partcpnt_no_test_dummy_dim v_t_partcpnt_no_test_dummy_dim, RANDOM] partitions=24/24 files=24 size=4.63GB table stats: unavailable columns missing stats: partcpnt_key, age_orig, alt_ste_cd, brth_dt_orig, cntrbn_escltn_elect_cd, cntrbn_escltn_incrmt_amt, cntrbn_escltn_incrmt_pct, dc_alt_vestg_dt, dc_goalmkr_enrld_ind, dc_goalmkr_portf_id, dc_partcpnt_acct_ty_cd, dc_partcpnt_acct_ty_desc, dc_partcpnt_enrlmt_dt, dc_partcpnt_stat_cd, dc_partcpnt_stat_desc, dc_partcpnt_ty_desc, divsion_sub_id, dth_dt, ee_hire_dt_orig, ee_num, ee_rehire_dt, ee_termn_dt, ee_ty_cd, gender_cd, hce_ind, home_city_nm, home_frst_st_addr_ln, home_sec_st_addr_ln, home_ste_cd_desc, home_zip_cd, hrs_of_svc_amt, orig_auto_enrlmt_dt, partcpnt_dso_ind, partcpnt_frst_nm, partcpnt_last_nm, partcpnt_mid_init, partl_goalmkr_pct, ppa_deflt_invst_ind, ssn, univl_cust_id hosts=3 per-host-mem=3.44GB tuple-ids=0 row-size=543B cardinality=unavailable ---------------- Estimated Per-Host Mem: 3690987520 Estimated Per-Host VCores: 1 Tables Missing Stats: retpocd01.v_t_partcpnt_no_test_dummy_dim Request Pool: root.retpocfad01 Admission result: Admitted immediately Planner Timeline Analysis finished: 2563961 Equivalence classes computed: 2854196 Single node plan created: 4448867 Runtime filters computed: 4496697 Distributed plan created: 5606789 Lineage info computed: 6986324 Planning finished: 9092027 Query Timeline Start execution: 27883 Planning finished: 11490865 Submit for admission: 12486419 Completed admission: 12609251 Ready to start 3 remote fragments: 14000622 All 3 remote fragments started: 24078100 Rows available: 249076073 First row fetched: 4488110086 ImpalaServer - ClientFetchWaitTimer: 589057292204 - InactiveTotalTime: 0 - RowMaterializationTimer: 258545542203 - TotalTime: 0 Execution Profile 334462f3d1599444:711e12be8225b88a Number of filters: 0 Filter routing table: ID Src. Node Tgt. Node Targets Type Partition filter ----------------------------------------------------------- Fragment start latencies: Count: 3, 25th %-ile: 6ms, 50th %-ile: 7ms, 75th %-ile: 7ms, 90th %-ile: 10ms, 95th %-ile: 10ms, 99.9th %-ile: 10ms - FiltersReceived: 0 - FinalizationTimer: 0 - InactiveTotalTime: 0 - TotalTime: 620678803 Coordinator Fragment F01 - AverageThreadTokens: 0.0 - BloomFilterBytes: 0 - InactiveTotalTime: 0 - PeakMemoryUsage: 27684280 - PerHostPeakMemUsage: 0 - PrepareTime: 64810 - RowsProduced: 5808128 - TotalCpuTime: 0 - TotalNetworkReceiveTime: 310672518 - TotalNetworkSendTime: 0 - TotalStorageWaitTime: 0 - TotalTime: 556406623 BlockMgr - BlockWritesOutstanding: 0 - BlocksCreated: 0 - BlocksRecycled: 0 - BufferedPins: 0 - BytesWritten: 0 - InactiveTotalTime: 0 - MaxBlockSize: 8388608 - MemoryLimit: 150323855360 - PeakMemoryUsage: 0 - TotalBufferWaitTime: 0 - TotalEncryptionTime: 0 - TotalIntegrityCheckTime: 0 - TotalReadBlockTime: 0 - TotalTime: 0 EXCHANGE_NODE (id=1) - BytesReceived: 1642350569 - ConvertRowBatchTime: 201772192 - DeserializeRowBatchTimer: 7854413794 - FirstBatchArrivalWaitTime: 224622130 - InactiveTotalTime: 224624879 - PeakMemoryUsage: 0 - RowsReturned: 5808128 - RowsReturnedRate: 10758899 - SendersBlockedTimer: 843769758076 - SendersBlockedTotalTimer(*): 2516434628659 - TotalTime: 539844086 Averaged Fragment F00 split sizes: min: 1.52 GB, max: 1.56 GB, avg: 1.54 GB, stddev: 15.63 MB - AverageThreadTokens: 9.0 - BloomFilterBytes: 0 - InactiveTotalTime: 0 - PeakMemoryUsage: 1218468272 - PerHostPeakMemUsage: 1227676869 - PrepareTime: 287104 - RowsProduced: 1947989 - TotalCpuTime: 0 - TotalNetworkReceiveTime: 0 - TotalNetworkSendTime: 837030597288 - TotalStorageWaitTime: 2951927257 - TotalTime: 846717676143 BlockMgr - BlockWritesOutstanding: 0 - BlocksCreated: 0 - BlocksRecycled: 0 - BufferedPins: 0 - BytesWritten: 0 - InactiveTotalTime: 0 - MaxBlockSize: 8388608 - MemoryLimit: 150323855360 - PeakMemoryUsage: 0 - TotalBufferWaitTime: 0 - TotalEncryptionTime: 0 - TotalIntegrityCheckTime: 0 - TotalReadBlockTime: 0 - TotalTime: 0 DataStreamSender (dst_id=1) - BytesSent: 547061254 - InactiveTotalTime: 0 - NetworkThroughput(*): 173411359 - OverallThroughput: 43881874 - PeakMemoryUsage: 224 - RowsReturned: 1946965 - SerializeBatchTime: 9226584443 - TotalTime: 12484075711 - TransmitDataRPCTime: 3257864851 - UncompressedRowBatchSize: 1375403325 HDFS_SCAN_NODE (id=0) - AverageHdfsReadThreadConcurrency: 0.002364069496936571 - AverageScannerThreadConcurrency: 8.0 - BytesRead: 802879888 - BytesReadDataNodeCache: 0 - BytesReadLocal: 0 - BytesReadRemoteUnexpected: 0 - BytesReadShortCircuit: 0 - DecompressionTime: 515564708 - InactiveTotalTime: 0 - MaxCompressedTextFileLength: 0 - NumColumns: 40 - NumDisksAccessed: 0 - NumRowGroups: 8 - NumScannerThreadsStarted: 8 - PeakMemoryUsage: 1217810338 - PerReadThreadRawHdfsThroughput: 426393444 - RemoteScanRanges: 0 - RowsRead: 2140501 - RowsReturned: 1947989 - RowsReturnedRate: 4902760 - ScanRangesComplete: 0 - ScannerThreadsInvoluntaryContextSwitches: 0 - ScannerThreadsTotalWallClockTime: 0 - MaterializeTupleTime(*): 0 - ScannerThreadsSysTime: 0 - ScannerThreadsUserTime: 0 - ScannerThreadsVoluntaryContextSwitches: 0 - TotalRawHdfsReadTime(*): 2309638462 - TotalReadThroughput: 948293 - TotalTime: 423082074 Fragment F00 Instance 334462f3d1599444:711e12be8225b88c (host=paehowchq1d01.prudential.com:22000) Hdfs split stats (:<# splits>/): 13:1/223.56 MB 7:1/215.62 MB 1:1/211.10 MB 5:1/203.08 MB 6:1/189.73 MB 0:2/383.73 MB 2:1/171.96 MB - AverageThreadTokens: 9.0 - BloomFilterBytes: 0 - InactiveTotalTime: 0 - PeakMemoryUsage: 1225838200 - PerHostPeakMemUsage: 1225838200 - PrepareTime: 324466 - RowsProduced: 1849344 - TotalCpuTime: 0 - TotalNetworkReceiveTime: 0 - TotalNetworkSendTime: 835398523970 - TotalStorageWaitTime: 3229893573 - TotalTime: 844799284114 BlockMgr - BlockWritesOutstanding: 0 - BlocksCreated: 0 - BlocksRecycled: 0 - BufferedPins: 0 - BytesWritten: 0 - InactiveTotalTime: 0 - MaxBlockSize: 8388608 - MemoryLimit: 150323855360 - PeakMemoryUsage: 0 - TotalBufferWaitTime: 0 - TotalEncryptionTime: 0 - TotalIntegrityCheckTime: 0 - TotalReadBlockTime: 0 - TotalTime: 0 DataStreamSender (dst_id=1) - BytesSent: 518868276 - InactiveTotalTime: 0 - NetworkThroughput(*): 124436059 - OverallThroughput: 39602797 - PeakMemoryUsage: 224 - RowsReturned: 1848320 - SerializeBatchTime: 8933261127 - TotalTime: 13101808795 - TransmitDataRPCTime: 4169758181 - UncompressedRowBatchSize: 1306539084 HDFS_SCAN_NODE (id=0) ExecOption: Expr Evaluation Codegen Disabled Hdfs split stats (:<# splits>/): 13:1/223.56 MB 7:1/215.62 MB 1:1/211.10 MB 5:1/203.08 MB 6:1/189.73 MB 0:2/383.73 MB 2:1/171.96 MB - AverageHdfsReadThreadConcurrency: 0.0035502958579881655 - AverageScannerThreadConcurrency: 8.0 - BytesRead: 812086910 - BytesReadDataNodeCache: 0 - BytesReadLocal: 0 - BytesReadRemoteUnexpected: 0 - BytesReadShortCircuit: 0 - DecompressionTime: 497934339 - InactiveTotalTime: 0 - MaxCompressedTextFileLength: 0 - NumColumns: 40 - NumDisksAccessed: 0 - NumRowGroups: 8 - NumScannerThreadsStarted: 8 - PeakMemoryUsage: 1225115048 - PerReadThreadRawHdfsThroughput: 315903707 - RemoteScanRanges: 0 - RowsRead: 2041856 - RowsReturned: 1849344 - RowsReturnedRate: 4347346 - ScanRangesComplete: 0 - ScannerThreadsInvoluntaryContextSwitches: 0 - ScannerThreadsTotalWallClockTime: 0 - MaterializeTupleTime(*): 0 - ScannerThreadsSysTime: 0 - ScannerThreadsUserTime: 0 - ScannerThreadsVoluntaryContextSwitches: 0 - TotalRawHdfsReadTime(*): 2570678630 - TotalReadThroughput: 961049 - TotalTime: 425396049 Instance 334462f3d1599444:711e12be8225b88e (host=paehowchq1d03.prudential.com:22000) Hdfs split stats (:<# splits>/): 7:1/216.07 MB 2:1/211.05 MB 9:1/203.90 MB 8:1/202.31 MB 11:1/197.84 MB 0:1/185.56 MB 12:2/343.76 MB - AverageThreadTokens: 9.0 - BloomFilterBytes: 0 - InactiveTotalTime: 0 - PeakMemoryUsage: 1211175360 - PerHostPeakMemUsage: 1211175360 - PrepareTime: 270123 - RowsProduced: 2502656 - TotalCpuTime: 0 - TotalNetworkReceiveTime: 0 - TotalNetworkSendTime: 834960668364 - TotalStorageWaitTime: 4152439283 - TotalTime: 847450277525 BlockMgr - BlockWritesOutstanding: 0 - BlocksCreated: 0 - BlocksRecycled: 0 - BufferedPins: 0 - BytesWritten: 0 - InactiveTotalTime: 0 - MaxBlockSize: 8388608 - MemoryLimit: 150323855360 - PeakMemoryUsage: 0 - TotalBufferWaitTime: 0 - TotalEncryptionTime: 0 - TotalIntegrityCheckTime: 0 - TotalReadBlockTime: 0 - TotalTime: 0 DataStreamSender (dst_id=1) - BytesSent: 703306068 - InactiveTotalTime: 0 - NetworkThroughput(*): 210112774 - OverallThroughput: 46299136 - PeakMemoryUsage: 224 - RowsReturned: 2501632 - SerializeBatchTime: 11843234393 - TotalTime: 15190479316 - TransmitDataRPCTime: 3347278946 - UncompressedRowBatchSize: 1767227307 HDFS_SCAN_NODE (id=0) ExecOption: Expr Evaluation Codegen Disabled Hdfs split stats (:<# splits>/): 7:1/216.07 MB 2:1/211.05 MB 9:1/203.90 MB 8:1/202.31 MB 11:1/197.84 MB 0:1/185.56 MB 12:2/343.76 MB - AverageHdfsReadThreadConcurrency: 0.0035419126328217238 - AverageScannerThreadConcurrency: 8.0 - BytesRead: 792719618 - BytesReadDataNodeCache: 0 - BytesReadLocal: 0 - BytesReadRemoteUnexpected: 0 - BytesReadShortCircuit: 0 - DecompressionTime: 680003706 - InactiveTotalTime: 0 - MaxCompressedTextFileLength: 0 - NumColumns: 40 - NumDisksAccessed: 0 - NumRowGroups: 8 - NumScannerThreadsStarted: 8 - PeakMemoryUsage: 1210530144 - PerReadThreadRawHdfsThroughput: 244697149 - RemoteScanRanges: 0 - RowsRead: 2695168 - RowsReturned: 2502656 - RowsReturnedRate: 4144532 - ScanRangesComplete: 0 - ScannerThreadsInvoluntaryContextSwitches: 0 - ScannerThreadsTotalWallClockTime: 0 - MaterializeTupleTime(*): 0 - ScannerThreadsSysTime: 0 - ScannerThreadsUserTime: 0 - ScannerThreadsVoluntaryContextSwitches: 0 - TotalRawHdfsReadTime(*): 3239594824 - TotalReadThroughput: 935914 - TotalTime: 603845178 Instance 334462f3d1599444:711e12be8225b88d (host=paehowchq1d02.prudential.com:22000) Hdfs split stats (:<# splits>/): 12:1/220.81 MB 1:1/214.26 MB 7:1/206.89 MB 4:1/200.05 MB 11:1/195.98 MB 10:1/188.22 MB 13:1/183.24 MB 6:1/170.94 MB - AverageThreadTokens: 9.0 - BloomFilterBytes: 0 - InactiveTotalTime: 0 - PeakMemoryUsage: 1218391256 - PerHostPeakMemUsage: 1246017048 - PrepareTime: 266725 - RowsProduced: 1491968 - TotalCpuTime: 0 - TotalNetworkReceiveTime: 0 - TotalNetworkSendTime: 840732599532 - TotalStorageWaitTime: 1473448917 - TotalTime: 847903466792 DataStreamSender (dst_id=1) - BytesSent: 419009419 - InactiveTotalTime: 0 - NetworkThroughput(*): 185685245 - OverallThroughput: 45743690 - PeakMemoryUsage: 224 - RowsReturned: 1490944 - SerializeBatchTime: 6903257811 - TotalTime: 9159939023 - TransmitDataRPCTime: 2256557428 - UncompressedRowBatchSize: 1052443584 HDFS_SCAN_NODE (id=0) ExecOption: Expr Evaluation Codegen Disabled Hdfs split stats (:<# splits>/): 12:1/220.81 MB 1:1/214.26 MB 7:1/206.89 MB 4:1/200.05 MB 11:1/195.98 MB 10:1/188.22 MB 13:1/183.24 MB 6:1/170.94 MB - AverageHdfsReadThreadConcurrency: 0.0 - AverageScannerThreadConcurrency: 8.0 - BytesRead: 803833137 - BytesReadDataNodeCache: 0 - BytesReadLocal: 0 - BytesReadRemoteUnexpected: 0 - BytesReadShortCircuit: 0 - DecompressionTime: 368756079 - InactiveTotalTime: 0 - MaxCompressedTextFileLength: 0 - NumColumns: 40 - NumDisksAccessed: 0 - NumRowGroups: 8 - NumScannerThreadsStarted: 8 - PeakMemoryUsage: 1217785824 - PerReadThreadRawHdfsThroughput: 718579477 - RemoteScanRanges: 0 - RowsRead: 1684480 - RowsReturned: 1491968 - RowsReturnedRate: 6216403 - ScanRangesComplete: 0 - ScannerThreadsInvoluntaryContextSwitches: 0 - ScannerThreadsTotalWallClockTime: 0 - MaterializeTupleTime(*): 0 - ScannerThreadsSysTime: 0 - ScannerThreadsUserTime: 0 - ScannerThreadsVoluntaryContextSwitches: 0 - TotalRawHdfsReadTime(*): 1118641934 - TotalReadThroughput: 947916 - TotalTime: 240004996