Query (id=6045edc0b518dc53:19095cd47ca50a4) Summary Session ID: 7749917ab6f47aa0:c9d690a6538e3a9d Session Type: HIVESERVER2 HiveServer2 Protocol Version: V6 Start Time: 2016-10-14 11:00:13.688354000 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:57944 Default Db: retpocd01 Sql Statement: SELECT `v_t_dc_acct_pos_procs_dt_fact`.`procs_dt_yyyymm_key`, `v_t_dc_acct_pos_procs_dt_fact`.`partcpnt_key`, `v_t_dc_acct_pos_procs_dt_fact`.`plan_asset_key`, `v_t_dc_acct_pos_procs_dt_fact`.`univl_plan_id`, `v_t_dc_acct_pos_procs_dt_fact`.`orig_bal` FROM `retpocd01`.`v_t_dc_acct_pos_procs_dt_fact` `v_t_dc_acct_pos_procs_dt_fact` Coordinator: paehowchq1d02.prudential.com:22000 Query Options (non default): Plan: ---------------- Estimated Per-Host Requirements: Memory=440.00MB VCores=1 WARNING: The following tables are missing relevant table and/or column statistics. retpocd01.v_t_dc_acct_pos_procs_dt_fact 01:EXCHANGE [UNPARTITIONED] | hosts=3 per-host-mem=unavailable | tuple-ids=0 row-size=36B cardinality=unavailable | 00:SCAN HDFS [retpocd01.v_t_dc_acct_pos_procs_dt_fact v_t_dc_acct_pos_procs_dt_fact, RANDOM] partitions=1/1 files=24 size=5.38GB table stats: unavailable column stats: unavailable hosts=3 per-host-mem=440.00MB tuple-ids=0 row-size=36B cardinality=unavailable ---------------- Estimated Per-Host Mem: 461373440 Estimated Per-Host VCores: 1 Tables Missing Stats: retpocd01.v_t_dc_acct_pos_procs_dt_fact Request Pool: root.retpocfad01 Admission result: Admitted immediately Planner Timeline Analysis finished: 1690696 Equivalence classes computed: 1892898 Single node plan created: 3256246 Runtime filters computed: 3357538 Distributed plan created: 4007868 Lineage info computed: 4606407 Planning finished: 5854262 Query Timeline Start execution: 69973 Planning finished: 7130617 Submit for admission: 7733173 Completed admission: 7839249 Ready to start 3 remote fragments: 8400998 All 3 remote fragments started: 12682376 Rows available: 61127733 ImpalaServer - ClientFetchWaitTimer: 0 - InactiveTotalTime: 0 - RowMaterializationTimer: 0 - TotalTime: 0 Execution Profile 6045edc0b518dc53:19095cd47ca50a4 Number of filters: 0 Filter routing table: ID Src. Node Tgt. Node Targets Type Partition filter ----------------------------------------------------------- Fragment start latencies: Count: 3, 25th %-ile: 1ms, 50th %-ile: 3ms, 75th %-ile: 3ms, 90th %-ile: 4ms, 95th %-ile: 4ms, 99.9th %-ile: 4ms - FiltersReceived: 0 - FinalizationTimer: 0 - InactiveTotalTime: 0 - TotalTime: 53015653 Coordinator Fragment F01 - AverageThreadTokens: 0.0 - BloomFilterBytes: 0 - InactiveTotalTime: 0 - PeakMemoryUsage: 22716416 - PerHostPeakMemUsage: 0 - PrepareTime: 49516 - RowsProduced: 0 - TotalCpuTime: 0 - TotalNetworkReceiveTime: 48009775 - TotalNetworkSendTime: 0 - TotalStorageWaitTime: 0 - TotalTime: 48347898 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: 10568792 - ConvertRowBatchTime: 0 - DeserializeRowBatchTimer: 66902840 - FirstBatchArrivalWaitTime: 48000334 - InactiveTotalTime: 48003733 - PeakMemoryUsage: 0 - RowsReturned: 0 - RowsReturnedRate: 0 - SendersBlockedTimer: 0 - SendersBlockedTotalTimer(*): 0 - TotalTime: 48012190 Averaged Fragment F00 split sizes: min: 1.74 GB, max: 1.88 GB, avg: 1.79 GB, stddev: 60.25 MB - AverageThreadTokens: 9.0 - BloomFilterBytes: 0 - InactiveTotalTime: 0 - PeakMemoryUsage: 762263096 - PerHostPeakMemUsage: 769835234 - PrepareTime: 385632 - RowsProduced: 159744 - TotalCpuTime: 0 - TotalNetworkReceiveTime: 0 - TotalNetworkSendTime: 112714860 - TotalStorageWaitTime: 1177632369 - TotalTime: 288129208 DataStreamSender (dst_id=1) - BytesSent: 3544890 - InactiveTotalTime: 0 - NetworkThroughput(*): 119946195 - OverallThroughput: 47449135 - PeakMemoryUsage: 3272 - RowsReturned: 158720 - SerializeBatchTime: 44945028 - TotalTime: 73503427 - TransmitDataRPCTime: 28618148 - UncompressedRowBatchSize: 7029360 HDFS_SCAN_NODE (id=0) - AverageHdfsReadThreadConcurrency: 9.534706331045003E-4 - AverageScannerThreadConcurrency: 8.0 - BytesRead: 724178349 - BytesReadDataNodeCache: 0 - BytesReadLocal: 0 - BytesReadRemoteUnexpected: 0 - BytesReadShortCircuit: 0 - DecompressionTime: 11611712 - InactiveTotalTime: 0 - MaxCompressedTextFileLength: 0 - NumColumns: 5 - NumDisksAccessed: 0 - NumRowGroups: 8 - NumScannerThreadsStarted: 8 - PeakMemoryUsage: 762210672 - PerReadThreadRawHdfsThroughput: 593827013 - RemoteScanRanges: 0 - RowsRead: 352256 - RowsReturned: 159744 - RowsReturnedRate: 2232578 - ScanRangesComplete: 0 - ScannerThreadsInvoluntaryContextSwitches: 0 - ScannerThreadsTotalWallClockTime: 0 - MaterializeTupleTime(*): 0 - ScannerThreadsSysTime: 0 - ScannerThreadsUserTime: 0 - ScannerThreadsVoluntaryContextSwitches: 0 - TotalRawHdfsReadTime(*): 1456604378 - TotalReadThroughput: 827292 - TotalTime: 127243103 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 Fragment F00 Instance 6045edc0b518dc53:19095cd47ca50a6 (host=paehowchq1d02.prudential.com:22000) Hdfs split stats (:<# splits>/): 11:1/254.60 MB 1:1/254.61 MB 6:1/25.63 MB 4:1/254.51 MB 0:1/254.61 MB 3:2/509.21 MB 9:1/254.50 MB - AverageThreadTokens: 9.0 - BloomFilterBytes: 0 - InactiveTotalTime: 0 - PeakMemoryUsage: 748291968 - PerHostPeakMemUsage: 771008384 - PrepareTime: 200033 - RowsProduced: 276480 - TotalCpuTime: 0 - TotalNetworkReceiveTime: 0 - TotalNetworkSendTime: 154069416 - TotalStorageWaitTime: 421661188 - TotalTime: 289846210 DataStreamSender (dst_id=1) - BytesSent: 6132838 - InactiveTotalTime: 0 - NetworkThroughput(*): 131941839 - OverallThroughput: 48531511 - PeakMemoryUsage: 3272 - RowsReturned: 275456 - SerializeBatchTime: 79837081 - TotalTime: 126368164 - TransmitDataRPCTime: 46481374 - UncompressedRowBatchSize: 12166200 HDFS_SCAN_NODE (id=0) ExecOption: Expr Evaluation Codegen Disabled Hdfs split stats (:<# splits>/): 11:1/254.60 MB 1:1/254.61 MB 6:1/25.63 MB 4:1/254.51 MB 0:1/254.61 MB 3:2/509.21 MB 9:1/254.50 MB - AverageHdfsReadThreadConcurrency: 0.0 - AverageScannerThreadConcurrency: 8.0 - BytesRead: 709368038 - BytesReadDataNodeCache: 0 - BytesReadLocal: 0 - BytesReadRemoteUnexpected: 0 - BytesReadShortCircuit: 0 - DecompressionTime: 13059991 - InactiveTotalTime: 0 - MaxCompressedTextFileLength: 0 - NumColumns: 5 - NumDisksAccessed: 0 - NumRowGroups: 8 - NumScannerThreadsStarted: 8 - PeakMemoryUsage: 748239544 - PerReadThreadRawHdfsThroughput: 919671196 - RemoteScanRanges: 0 - RowsRead: 468992 - RowsReturned: 276480 - RowsReturnedRate: 5346366 - ScanRangesComplete: 0 - ScannerThreadsInvoluntaryContextSwitches: 0 - ScannerThreadsTotalWallClockTime: 0 - MaterializeTupleTime(*): 0 - ScannerThreadsSysTime: 0 - ScannerThreadsUserTime: 0 - ScannerThreadsVoluntaryContextSwitches: 0 - TotalRawHdfsReadTime(*): 771327884 - TotalReadThroughput: 809780 - TotalTime: 51713631 Instance 6045edc0b518dc53:19095cd47ca50a7 (host=paehowchq1d03.prudential.com:22000) Hdfs split stats (:<# splits>/): 5:1/254.53 MB 0:1/254.56 MB 4:1/254.57 MB 7:1/254.65 MB 3:1/254.53 MB 8:1/254.58 MB 10:1/254.62 MB 11:1/2.66 MB 13:1/136.16 MB - AverageThreadTokens: 10.0 - BloomFilterBytes: 0 - InactiveTotalTime: 0 - PeakMemoryUsage: 808680664 - PerHostPeakMemUsage: 808680664 - PrepareTime: 447575 - RowsProduced: 72704 - TotalCpuTime: 0 - TotalNetworkReceiveTime: 0 - TotalNetworkSendTime: 60914537 - TotalStorageWaitTime: 2126509170 - TotalTime: 286762271 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: 1630379 - InactiveTotalTime: 0 - NetworkThroughput(*): 112471128 - OverallThroughput: 41145175 - PeakMemoryUsage: 3272 - RowsReturned: 71680 - SerializeBatchTime: 25220542 - TotalTime: 39625035 - TransmitDataRPCTime: 14495978 - UncompressedRowBatchSize: 3199260 HDFS_SCAN_NODE (id=0) ExecOption: Expr Evaluation Codegen Disabled Hdfs split stats (:<# splits>/): 5:1/254.53 MB 0:1/254.56 MB 4:1/254.57 MB 7:1/254.65 MB 3:1/254.53 MB 8:1/254.58 MB 10:1/254.62 MB 11:1/2.66 MB 13:1/136.16 MB - AverageHdfsReadThreadConcurrency: 0.0 - AverageScannerThreadConcurrency: 9.0 - BytesRead: 768356696 - BytesReadDataNodeCache: 0 - BytesReadLocal: 0 - BytesReadRemoteUnexpected: 0 - BytesReadShortCircuit: 0 - DecompressionTime: 12020150 - InactiveTotalTime: 0 - MaxCompressedTextFileLength: 0 - NumColumns: 5 - NumDisksAccessed: 0 - NumRowGroups: 9 - NumScannerThreadsStarted: 9 - PeakMemoryUsage: 808628240 - PerReadThreadRawHdfsThroughput: 338290166 - RemoteScanRanges: 0 - RowsRead: 266240 - RowsReturned: 72704 - RowsReturnedRate: 367495 - ScanRangesComplete: 0 - ScannerThreadsInvoluntaryContextSwitches: 0 - ScannerThreadsTotalWallClockTime: 0 - MaterializeTupleTime(*): 0 - ScannerThreadsSysTime: 0 - ScannerThreadsUserTime: 0 - ScannerThreadsVoluntaryContextSwitches: 0 - TotalRawHdfsReadTime(*): 2271294803 - TotalReadThroughput: 877119 - TotalTime: 197836279 Instance 6045edc0b518dc53:19095cd47ca50a8 (host=paehowchq1d01.prudential.com:22000) Hdfs split stats (:<# splits>/): 1:1/254.59 MB 9:1/254.63 MB 8:1/254.63 MB 7:1/254.59 MB 12:1/254.61 MB 4:1/254.65 MB 0:1/254.54 MB - AverageThreadTokens: 8.0 - BloomFilterBytes: 0 - InactiveTotalTime: 0 - PeakMemoryUsage: 729816656 - PerHostPeakMemUsage: 729816656 - PrepareTime: 509288 - RowsProduced: 130048 - TotalCpuTime: 0 - TotalNetworkReceiveTime: 0 - TotalNetworkSendTime: 123160627 - TotalStorageWaitTime: 984726749 - TotalTime: 287779144 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: 2871454 - InactiveTotalTime: 0 - NetworkThroughput(*): 115425620 - OverallThroughput: 52670720 - PeakMemoryUsage: 3272 - RowsReturned: 129024 - SerializeBatchTime: 29777463 - TotalTime: 54517082 - TransmitDataRPCTime: 24877094 - UncompressedRowBatchSize: 5722620 HDFS_SCAN_NODE (id=0) ExecOption: Expr Evaluation Codegen Disabled Hdfs split stats (:<# splits>/): 1:1/254.59 MB 9:1/254.63 MB 8:1/254.63 MB 7:1/254.59 MB 12:1/254.61 MB 4:1/254.65 MB 0:1/254.54 MB - AverageHdfsReadThreadConcurrency: 0.002860411899313501 - AverageScannerThreadConcurrency: 7.0 - BytesRead: 694810315 - BytesReadDataNodeCache: 0 - BytesReadLocal: 0 - BytesReadRemoteUnexpected: 0 - BytesReadShortCircuit: 0 - DecompressionTime: 9754997 - InactiveTotalTime: 0 - MaxCompressedTextFileLength: 0 - NumColumns: 5 - NumDisksAccessed: 0 - NumRowGroups: 7 - NumScannerThreadsStarted: 7 - PeakMemoryUsage: 729764232 - PerReadThreadRawHdfsThroughput: 523519677 - RemoteScanRanges: 0 - RowsRead: 321536 - RowsReturned: 130048 - RowsReturnedRate: 983874 - ScanRangesComplete: 0 - ScannerThreadsInvoluntaryContextSwitches: 0 - ScannerThreadsTotalWallClockTime: 0 - MaterializeTupleTime(*): 0 - ScannerThreadsSysTime: 0 - ScannerThreadsUserTime: 0 - ScannerThreadsVoluntaryContextSwitches: 0 - TotalRawHdfsReadTime(*): 1327190447 - TotalReadThroughput: 794977 - TotalTime: 132179401