问:
Tensorflow: multi cpu inference using threadpool
I have lots of images that I want to process in parallel.

By default Tensorflow can use multiple cores, here is some info on this
https://stackoverflow.com/a/41233901/1179925

"Currently, this means that each thread pool will have one thread per
  CPU core in your machine."

By looking at htop I can see that not all cores are utilized at 100% in this default setting, so I want to set intra_op_parallelism_threads=1 and inter_op_parallelism_threads=1 and run n_cpu models in parallel, howewer it performs even worser.

On my notebook with 8 cores:

Single core sequential processing:

```<code>Model init time: 0.77 sec
Processing time: 37.58 sec
</code>```

Multi CPU default Tensorflow settings:

```<code>Model init time: 0.76 sec
Processing time: 20.16 sec
</code>```

This code using multiprocessing:

```<code>Model init time: 0.78 sec
Processing time: 39.14 sec
</code>```

Here is my code using multiprocessing, I'm missing something?:

```<code>import os
import glob
import time
import argparse
from multiprocessing.pool import ThreadPool
import multiprocessing
import itertools

import tensorflow as tf
import numpy as np
from tqdm import tqdm
import cv2

MODEL_FILEPATH = './tensorflow_example/inception_v3_2016_08_28_frozen.pb'

def get_image_filepaths(dataset_dir):
    if not os.path.isdir(dataset_dir):
        raise Exception(dataset_dir, 'not dir!')

    img_filepaths = []
    extensions = ['**/*.jpg', '**/*.png', '**/*.JPG', '**/*.PNG']
    for ext in extensions:
        img_filepaths.extend(glob.iglob(os.path.join(dataset_dir, ext), recursive=True))

    return img_filepaths

class ModelWrapper():
    def __init__(self, model_filepath):
        # TODO: estimate this from graph itself
        # Hardcoded for inception_v3_2016_08_28_frozen.pb
        self.input_node_names = ['input']
        self.output_node_names = ['InceptionV3/Predictions/Reshape_1']
        self.input_img_w = 299
        self.input_img_h = 299

        input_tensor_names = [name + ":0" for name in self.input_node_names]
        output_tensor_names = [name + ":0" for name in self.output_node_names]

        self.graph = self.load_graph(model_filepath)

        self.inputs = []
        for input_tensor_name in input_tensor_names:
            self.inputs.append(self.graph.get_tensor_by_name(input_tensor_name))

        self.outputs = []
        for output_tensor_name in output_tensor_names:
            self.outputs.append(self.graph.get_tensor_by_name(output_tensor_name))

        config_proto = tf.ConfigProto(device_count={'GPU': 0},
                                      intra_op_parallelism_threads=1,
                                      inter_op_parallelism_threads=1)
        self.sess = tf.Session(graph=self.graph, config=config_proto)

    def load_graph(self, model_filepath):
        # Expects frozen graph in .pb format
        with tf.gfile.GFile(model_filepath, "rb") as f:
            graph_def = tf.GraphDef()
            graph_def.ParseFromString(f.read())
        with tf.Graph().as_default() as graph:
            tf.import_graph_def(graph_def, name="")
        return graph

    def predict(self, img):
        h, w, c = img.shape
        if h != self.input_img_h or w != self.input_img_w:
            img = cv2.resize(img, (self.input_img_w, self.input_img_h))

        batch = img[np.newaxis, ...]
        feed_dict = {self.inputs[0] : batch}
        outputs = self.sess.run(self.outputs, feed_dict=feed_dict) # (1, 1001)

        return outputs

def process_single_file(args):
    model, img_filepath = args

    img = cv2.imread(img_filepath)
    output = model.predict(img)

def process_dataset(dataset_dir):
    img_filepaths = get_image_filepaths(dataset_dir)

    start = time.time()
    model = ModelWrapper(MODEL_FILEPATH)
    print('Model init time:', round(time.time() - start, 2), 'sec')

    start = time.time()
    n_cpu = multiprocessing.cpu_count()
    for _ in tqdm(ThreadPool(n_cpu).imap_unordered(process_single_file,
                                                   zip(itertools.repeat(model), img_filepaths)),
                                                   total=len(img_filepaths)):
        pass
    print('Processing time:', round(time.time() - start, 2), 'sec')

if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument(dest='dataset_dir')
    args = parser.parse_args()

    process_dataset(args.dataset_dir)
</code>```

Update:

After replacing multiprocessing.pool.ThreadPool with multiprocessing.Pool:

```<code>def process_dataset(dataset_dir):
    img_filepaths = get_image_filepaths(dataset_dir)

    start = time.time()
    model = ModelWrapper(MODEL_FILEPATH)
    print('Model init time:', round(time.time() - start, 2), 'sec')

    start = time.time()
    n_cpu = multiprocessing.cpu_count()
    pool = multiprocessing.Pool(n_cpu)

    it = pool.imap_unordered(process_single_file, zip(itertools.repeat(model), img_filepaths))
    for _ in tqdm(it, total=len(img_filepaths)):
        pass

    print('Processing time:', round(time.time() - start, 2), 'sec')
</code>```

I get an error:

```<code>Traceback (most recent call last):
  File "tensorflow_example/multi_core_cpu_inference_multiprocessing.py", line 110, in <module>
    process_dataset(args.dataset_dir)
  File "tensorflow_example/multi_core_cpu_inference_multiprocessing.py", line 99, in process_dataset
    for _ in tqdm(it, total=len(img_filepaths)):
  File "/usr/local/lib/python3.6/site-packages/tqdm/_tqdm.py", line 979, in __iter__
    for obj in iterable:
  File "/usr/local/Cellar/python/3.6.5_1/Frameworks/Python.framework/Versions/3.6/lib/python3.6/multiprocessing/pool.py", line 735, in next
    raise value
  File "/usr/local/Cellar/python/3.6.5_1/Frameworks/Python.framework/Versions/3.6/lib/python3.6/multiprocessing/pool.py", line 424, in _handle_tasks
    put(task)
  File "/usr/local/Cellar/python/3.6.5_1/Frameworks/Python.framework/Versions/3.6/lib/python3.6/multiprocessing/connection.py", line 206, in send
    self._send_bytes(_ForkingPickler.dumps(obj))
  File "/usr/local/Cellar/python/3.6.5_1/Frameworks/Python.framework/Versions/3.6/lib/python3.6/multiprocessing/reduction.py", line 51, in dumps
    cls(buf, protocol).dump(obj)
TypeError: can't pickle _thread.RLock objects
</code>```

答:
Based on this unswer: https://stackoverflow.com/a/46779776/1179925

It works but not much faster than default paralellism offered by tensorflow itself.

```<code>import os
import glob
import time
import argparse
import multiprocessing

import tensorflow as tf
import numpy as np
from tqdm import tqdm
import cv2

# Running N_PROCESSES processes using multiprocessing pool

N_PROCESSES = 2
N_CPU = multiprocessing.cpu_count()
INTRA_N_THREADS = max(1, N_CPU // N_PROCESSES)
INTER_N_THREADS = max(1, N_CPU // N_PROCESSES)

print('N_PROCESSES', N_PROCESSES)
print('N_CPU', N_CPU)
print('INTRA_N_THREADS', INTRA_N_THREADS)
print('INTER_N_THREADS', INTER_N_THREADS)

MODEL_FILEPATH = './tensorflow_example/inception_v3_2016_08_28_frozen.pb'

def get_image_filepaths(dataset_dir):
    if not os.path.isdir(dataset_dir):
        raise Exception(dataset_dir, 'not dir!')

    img_filepaths = []
    extensions = ['**/*.jpg', '**/*.png', '**/*.JPG', '**/*.PNG']
    for ext in extensions:
        img_filepaths.extend(glob.iglob(os.path.join(dataset_dir, ext), recursive=True))

    return img_filepaths

class ModelWrapper():
    def __init__(self, model_filepath):
        # TODO: estimate this from graph itself
        # Hardcoded for inception_v3_2016_08_28_frozen.pb
        self.input_node_names = ['input']
        self.output_node_names = ['InceptionV3/Predictions/Reshape_1']
        self.input_img_w = 299
        self.input_img_h = 299

        input_tensor_names = [name + ":0" for name in self.input_node_names]
        output_tensor_names = [name + ":0" for name in self.output_node_names]

        self.graph = self.load_graph(model_filepath)

        self.inputs = []
        for input_tensor_name in input_tensor_names:
            self.inputs.append(self.graph.get_tensor_by_name(input_tensor_name))

        self.outputs = []
        for output_tensor_name in output_tensor_names:
            self.outputs.append(self.graph.get_tensor_by_name(output_tensor_name))

        config_proto = tf.ConfigProto(device_count={'GPU': 0},
                                      intra_op_parallelism_threads=INTRA_N_THREADS,
                                      inter_op_parallelism_threads=INTER_N_THREADS)
        self.sess = tf.Session(graph=self.graph, config=config_proto)

    def load_graph(self, model_filepath):
        # Expects frozen graph in .pb format
        with tf.gfile.GFile(model_filepath, "rb") as f:
            graph_def = tf.GraphDef()
            graph_def.ParseFromString(f.read())
        with tf.Graph().as_default() as graph:
            tf.import_graph_def(graph_def, name="")
        return graph

    def predict(self, img):
        h, w, c = img.shape
        if h != self.input_img_h or w != self.input_img_w:
            img = cv2.resize(img, (self.input_img_w, self.input_img_h))

        batch = img[np.newaxis, ...]
        feed_dict = {self.inputs[0] : batch}
        outputs = self.sess.run(self.outputs, feed_dict=feed_dict) # (1, 1001)

        return outputs

def process_chunk(img_filepaths):
    start = time.time()
    model = ModelWrapper(MODEL_FILEPATH)
    print('Model init time:', round(time.time() - start, 2), 'sec')

    for img_filepath in img_filepaths:
        img = cv2.imread(img_filepath)
        output = model.predict(img)

def process_dataset(dataset_dir):
    img_filepaths = get_image_filepaths(dataset_dir)

    start = time.time()
    pool = multiprocessing.Pool(N_PROCESSES)

    chunks = []
    n = len(img_filepaths) // N_PROCESSES
    for i in range(0, len(img_filepaths), n):
        chunk = img_filepaths[i:i+n]
        chunks.append(chunk)

    it = pool.imap_unordered(process_chunk, chunks)
    for _ in tqdm(it, total=len(img_filepaths)):
        pass

    print('Processing time:', round(time.time() - start, 2), 'sec')

if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument(dest='dataset_dir')
    args = parser.parse_args()

    process_dataset(args.dataset_dir)
</code>```

Body:
I have lots of images that I want to process in parallel.

By default Tensorflow can use multiple cores, here is some info on this
https://stackoverflow.com/a/41233901/1179925

"Currently, this means that each thread pool will have one thread per
  CPU core in your machine."

By looking at htop I can see that not all cores are utilized at 100% in this default setting, so I want to set intra_op_parallelism_threads=1 and inter_op_parallelism_threads=1 and run n_cpu models in parallel, howewer it performs even worser.

On my notebook with 8 cores:

Single core sequential processing:

Model init time: 0.77 sec
Processing time: 37.58 sec

Multi CPU default Tensorflow settings:

Model init time: 0.76 sec
Processing time: 20.16 sec

This code using multiprocessing:

Model init time: 0.78 sec
Processing time: 39.14 sec

Here is my code using multiprocessing, I'm missing something?:

import os
import glob
import time
import argparse
from multiprocessing.pool import ThreadPool
import multiprocessing
import itertools

import tensorflow as tf
import numpy as np
from tqdm import tqdm
import cv2

MODEL_FILEPATH = './tensorflow_example/inception_v3_2016_08_28_frozen.pb'

def get_image_filepaths(dataset_dir):
    if not os.path.isdir(dataset_dir):
        raise Exception(dataset_dir, 'not dir!')

    img_filepaths = []
    extensions = ['**/*.jpg', '**/*.png', '**/*.JPG', '**/*.PNG']
    for ext in extensions:
        img_filepaths.extend(glob.iglob(os.path.join(dataset_dir, ext), recursive=True))

    return img_filepaths

class ModelWrapper():
    def __init__(self, model_filepath):
        # TODO: estimate this from graph itself
        # Hardcoded for inception_v3_2016_08_28_frozen.pb
        self.input_node_names = ['input']
        self.output_node_names = ['InceptionV3/Predictions/Reshape_1']
        self.input_img_w = 299
        self.input_img_h = 299

        input_tensor_names = [name + ":0" for name in self.input_node_names]
        output_tensor_names = [name + ":0" for name in self.output_node_names]

        self.graph = self.load_graph(model_filepath)

        self.inputs = []
        for input_tensor_name in input_tensor_names:
            self.inputs.append(self.graph.get_tensor_by_name(input_tensor_name))

        self.outputs = []
        for output_tensor_name in output_tensor_names:
            self.outputs.append(self.graph.get_tensor_by_name(output_tensor_name))

        config_proto = tf.ConfigProto(device_count={'GPU': 0},
                                      intra_op_parallelism_threads=1,
                                      inter_op_parallelism_threads=1)
        self.sess = tf.Session(graph=self.graph, config=config_proto)

    def load_graph(self, model_filepath):
        # Expects frozen graph in .pb format
        with tf.gfile.GFile(model_filepath, "rb") as f:
            graph_def = tf.GraphDef()
            graph_def.ParseFromString(f.read())
        with tf.Graph().as_default() as graph:
            tf.import_graph_def(graph_def, name="")
        return graph

    def predict(self, img):
        h, w, c = img.shape
        if h != self.input_img_h or w != self.input_img_w:
            img = cv2.resize(img, (self.input_img_w, self.input_img_h))

        batch = img[np.newaxis, ...]
        feed_dict = {self.inputs[0] : batch}
        outputs = self.sess.run(self.outputs, feed_dict=feed_dict) # (1, 1001)

        return outputs

def process_single_file(args):
    model, img_filepath = args

    img = cv2.imread(img_filepath)
    output = model.predict(img)

def process_dataset(dataset_dir):
    img_filepaths = get_image_filepaths(dataset_dir)

    start = time.time()
    model = ModelWrapper(MODEL_FILEPATH)
    print('Model init time:', round(time.time() - start, 2), 'sec')

    start = time.time()
    n_cpu = multiprocessing.cpu_count()
    for _ in tqdm(ThreadPool(n_cpu).imap_unordered(process_single_file,
                                                   zip(itertools.repeat(model), img_filepaths)),
                                                   total=len(img_filepaths)):
        pass
    print('Processing time:', round(time.time() - start, 2), 'sec')

if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument(dest='dataset_dir')
    args = parser.parse_args()

    process_dataset(args.dataset_dir)

Update:

After replacing multiprocessing.pool.ThreadPool with multiprocessing.Pool:

def process_dataset(dataset_dir):
    img_filepaths = get_image_filepaths(dataset_dir)

    start = time.time()
    model = ModelWrapper(MODEL_FILEPATH)
    print('Model init time:', round(time.time() - start, 2), 'sec')

    start = time.time()
    n_cpu = multiprocessing.cpu_count()
    pool = multiprocessing.Pool(n_cpu)

    it = pool.imap_unordered(process_single_file, zip(itertools.repeat(model), img_filepaths))
    for _ in tqdm(it, total=len(img_filepaths)):
        pass

    print('Processing time:', round(time.time() - start, 2), 'sec')

I get an error:

Traceback (most recent call last):
  File "tensorflow_example/multi_core_cpu_inference_multiprocessing.py", line 110, in <module>
    process_dataset(args.dataset_dir)
  File "tensorflow_example/multi_core_cpu_inference_multiprocessing.py", line 99, in process_dataset
    for _ in tqdm(it, total=len(img_filepaths)):
  File "/usr/local/lib/python3.6/site-packages/tqdm/_tqdm.py", line 979, in __iter__
    for obj in iterable:
  File "/usr/local/Cellar/python/3.6.5_1/Frameworks/Python.framework/Versions/3.6/lib/python3.6/multiprocessing/pool.py", line 735, in next
    raise value
  File "/usr/local/Cellar/python/3.6.5_1/Frameworks/Python.framework/Versions/3.6/lib/python3.6/multiprocessing/pool.py", line 424, in _handle_tasks
    put(task)
  File "/usr/local/Cellar/python/3.6.5_1/Frameworks/Python.framework/Versions/3.6/lib/python3.6/multiprocessing/connection.py", line 206, in send
    self._send_bytes(_ForkingPickler.dumps(obj))
  File "/usr/local/Cellar/python/3.6.5_1/Frameworks/Python.framework/Versions/3.6/lib/python3.6/multiprocessing/reduction.py", line 51, in dumps
    cls(buf, protocol).dump(obj)
TypeError: can't pickle _thread.RLock objects

Body:
Based on this unswer: https://stackoverflow.com/a/46779776/1179925

It works but not much faster than default paralellism offered by tensorflow itself.

import os
import glob
import time
import argparse
import multiprocessing

import tensorflow as tf
import numpy as np
from tqdm import tqdm
import cv2

# Running N_PROCESSES processes using multiprocessing pool

N_PROCESSES = 2
N_CPU = multiprocessing.cpu_count()
INTRA_N_THREADS = max(1, N_CPU // N_PROCESSES)
INTER_N_THREADS = max(1, N_CPU // N_PROCESSES)

print('N_PROCESSES', N_PROCESSES)
print('N_CPU', N_CPU)
print('INTRA_N_THREADS', INTRA_N_THREADS)
print('INTER_N_THREADS', INTER_N_THREADS)

MODEL_FILEPATH = './tensorflow_example/inception_v3_2016_08_28_frozen.pb'

def get_image_filepaths(dataset_dir):
    if not os.path.isdir(dataset_dir):
        raise Exception(dataset_dir, 'not dir!')

    img_filepaths = []
    extensions = ['**/*.jpg', '**/*.png', '**/*.JPG', '**/*.PNG']
    for ext in extensions:
        img_filepaths.extend(glob.iglob(os.path.join(dataset_dir, ext), recursive=True))

    return img_filepaths

class ModelWrapper():
    def __init__(self, model_filepath):
        # TODO: estimate this from graph itself
        # Hardcoded for inception_v3_2016_08_28_frozen.pb
        self.input_node_names = ['input']
        self.output_node_names = ['InceptionV3/Predictions/Reshape_1']
        self.input_img_w = 299
        self.input_img_h = 299

        input_tensor_names = [name + ":0" for name in self.input_node_names]
        output_tensor_names = [name + ":0" for name in self.output_node_names]

        self.graph = self.load_graph(model_filepath)

        self.inputs = []
        for input_tensor_name in input_tensor_names:
            self.inputs.append(self.graph.get_tensor_by_name(input_tensor_name))

        self.outputs = []
        for output_tensor_name in output_tensor_names:
            self.outputs.append(self.graph.get_tensor_by_name(output_tensor_name))

        config_proto = tf.ConfigProto(device_count={'GPU': 0},
                                      intra_op_parallelism_threads=INTRA_N_THREADS,
                                      inter_op_parallelism_threads=INTER_N_THREADS)
        self.sess = tf.Session(graph=self.graph, config=config_proto)

    def load_graph(self, model_filepath):
        # Expects frozen graph in .pb format
        with tf.gfile.GFile(model_filepath, "rb") as f:
            graph_def = tf.GraphDef()
            graph_def.ParseFromString(f.read())
        with tf.Graph().as_default() as graph:
            tf.import_graph_def(graph_def, name="")
        return graph

    def predict(self, img):
        h, w, c = img.shape
        if h != self.input_img_h or w != self.input_img_w:
            img = cv2.resize(img, (self.input_img_w, self.input_img_h))

        batch = img[np.newaxis, ...]
        feed_dict = {self.inputs[0] : batch}
        outputs = self.sess.run(self.outputs, feed_dict=feed_dict) # (1, 1001)

        return outputs

def process_chunk(img_filepaths):
    start = time.time()
    model = ModelWrapper(MODEL_FILEPATH)
    print('Model init time:', round(time.time() - start, 2), 'sec')

    for img_filepath in img_filepaths:
        img = cv2.imread(img_filepath)
        output = model.predict(img)

def process_dataset(dataset_dir):
    img_filepaths = get_image_filepaths(dataset_dir)

    start = time.time()
    pool = multiprocessing.Pool(N_PROCESSES)

    chunks = []
    n = len(img_filepaths) // N_PROCESSES
    for i in range(0, len(img_filepaths), n):
        chunk = img_filepaths[i:i+n]
        chunks.append(chunk)

    it = pool.imap_unordered(process_chunk, chunks)
    for _ in tqdm(it, total=len(img_filepaths)):
        pass

    print('Processing time:', round(time.time() - start, 2), 'sec')

if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument(dest='dataset_dir')
    args = parser.parse_args()

    process_dataset(args.dataset_dir)


来源: stackexchange

问:
TypeScript is referencing non-static method
I need to load a module from the root, so I am using require.main.require() to do so. Since import cannot access the root, I need to use require. However, this is referencing the wrong connect() method:

```<code>export class MongoClient extends EventEmitter {
  connect(callback: MongoCallback<MongoClient>): void;
}
</code>```

What I need is for it to reference the static method:

```<code>export class MongoClient extends EventEmitter {
  static connect(uri: string, options?: MongoClientOptions): Promise<MongoClient>;
}
</code>```

The code runs fine when compiled and executed with node, it's just that it is showing an error in the editor. This is what I am doing:

```<code>declare type MongoClient = import('mongodb').MongoClient

const mongo = require.main && require.main.require('mongodb')
const MongoClient: MongoClient = mongo.MongoClient

async function connect() {
  let url = 'mongodb://....'
  await MongoClient.connect(url, { useNewUrlParser: true })
}
</code>```

Expected 0-1 arguments, but got 2.

答:
Looks like the fix was to just use the typeof keyword along with a union type when declaring the type.

```<code>declare type MongoClient = typeof import('mongodb').MongoClient | import('mongodb').MongoClient
</code>```

ContentLicense: CC BY-SA 4.0

Body:
I need to load a module from the root, so I am using require.main.require() to do so. Since import cannot access the root, I need to use require. However, this is referencing the wrong connect() method:

export class MongoClient extends EventEmitter {
  connect(callback: MongoCallback<MongoClient>): void;
}

What I need is for it to reference the static method:

export class MongoClient extends EventEmitter {
  static connect(uri: string, options?: MongoClientOptions): Promise<MongoClient>;
}

The code runs fine when compiled and executed with node, it's just that it is showing an error in the editor. This is what I am doing:

declare type MongoClient = import('mongodb').MongoClient

const mongo = require.main && require.main.require('mongodb')
const MongoClient: MongoClient = mongo.MongoClient

async function connect() {
  let url = 'mongodb://....'
  await MongoClient.connect(url, { useNewUrlParser: true })
}

Expected 0-1 arguments, but got 2.

Body:
Looks like the fix was to just use the typeof keyword along with a union type when declaring the type.

declare type MongoClient = typeof import('mongodb').MongoClient | import('mongodb').MongoClient


问:
In ASP.NET Core Razor Pages, how to get view engine path for a page outside that page's context?
Say I have an Index page directly under Pages. In the context of that page, I can access PageContext.ActionDescriptor.ViewEnginePath that stores the page's path, and get /Index.

How do I get view engine path for any particular page outside a page's context? Does ASP.NET Core maintain a collection with view engine paths for all available pages/views that I can access?

This is an ASP.NET Core 2.2 application.

答:
I have the following razor page that I use for debugging all of the route info. You can use as is or grab the _actionDescriptorCollectionProvider.ActionDescriptors.Items and look for the specific value you are looking for.

.cs code:

```<code>using Microsoft.AspNetCore.Mvc.Infrastructure;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Newtonsoft.Json;
using System.Collections.Generic;
using System.Linq;

namespace RouteDebugging.Pages {
public class RoutesModel : PageModel {
    private readonly IActionDescriptorCollectionProvider _actionDescriptorCollectionProvider;

    public RoutesModel(IActionDescriptorCollectionProvider actionDescriptorCollectionProvider) {
        this._actionDescriptorCollectionProvider = actionDescriptorCollectionProvider;
    }

    public List<RouteInfo> Routes { get; set; }

    public void OnGet() {
        Routes = _actionDescriptorCollectionProvider.ActionDescriptors.Items
                .Select(x => new RouteInfo {
                    Action = x.RouteValues["Action"],
                    Controller = x.RouteValues["Controller"],
                    Name = x.AttributeRouteInfo?.Name,
                    Template = x.AttributeRouteInfo?.Template,
                    Constraint = x.ActionConstraints == null ? "" : JsonConvert.SerializeObject(x.ActionConstraints)
                })
            .OrderBy(r => r.Template)
            .ToList();
    }

    public class RouteInfo {
        public string Template { get; set; }
        public string Name { get; set; }
        public string Controller { get; set; }
        public string Action { get; set; }
        public string Constraint { get; set; }
    }
}
}
</code>```

With a cshtml page to view it nicely in a table:

```<code>@page
@model RouteDebugging.Pages.RoutesModel
@{
    ViewData["Title"] = "Routes";
}

<h2>@ViewData["Title"]</h2>
<h3>Route Debug Info</h3>

<table class="table table-striped table-bordered">
    <thead>
        <tr>
            <th>Route Template</th>
            <th>Controller</th>
            <th>Action</th>
            <th>Constraints/Verbs</th>
            <th>Name</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var route in Model.Routes) {
            @if (!String.IsNullOrEmpty(route.Template)) {
                <tr>
                    <td>@route.Template</td>
                    <td>@route.Controller</td>
                    <td>@route.Action</td>
                    <td>@route.Constraint</td>
                    <td>@route.Name</td>
                </tr>
            }
        }
    </tbody>
</table>
</code>```

Body:
I have the following razor page that I use for debugging all of the route info. You can use as is or grab the _actionDescriptorCollectionProvider.ActionDescriptors.Items and look for the specific value you are looking for.

.cs code:

using Microsoft.AspNetCore.Mvc.Infrastructure;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Newtonsoft.Json;
using System.Collections.Generic;
using System.Linq;

namespace RouteDebugging.Pages {
public class RoutesModel : PageModel {
    private readonly IActionDescriptorCollectionProvider _actionDescriptorCollectionProvider;

    public RoutesModel(IActionDescriptorCollectionProvider actionDescriptorCollectionProvider) {
        this._actionDescriptorCollectionProvider = actionDescriptorCollectionProvider;
    }

    public List<RouteInfo> Routes { get; set; }

    public void OnGet() {
        Routes = _actionDescriptorCollectionProvider.ActionDescriptors.Items
                .Select(x => new RouteInfo {
                    Action = x.RouteValues["Action"],
                    Controller = x.RouteValues["Controller"],
                    Name = x.AttributeRouteInfo?.Name,
                    Template = x.AttributeRouteInfo?.Template,
                    Constraint = x.ActionConstraints == null ? "" : JsonConvert.SerializeObject(x.ActionConstraints)
                })
            .OrderBy(r => r.Template)
            .ToList();
    }

    public class RouteInfo {
        public string Template { get; set; }
        public string Name { get; set; }
        public string Controller { get; set; }
        public string Action { get; set; }
        public string Constraint { get; set; }
    }
}
}

With a cshtml page to view it nicely in a table:

@page
@model RouteDebugging.Pages.RoutesModel
@{
    ViewData["Title"] = "Routes";
}

<h2>@ViewData["Title"]</h2>
<h3>Route Debug Info</h3>

<table class="table table-striped table-bordered">
    <thead>
        <tr>
            <th>Route Template</th>
            <th>Controller</th>
            <th>Action</th>
            <th>Constraints/Verbs</th>
            <th>Name</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var route in Model.Routes) {
            @if (!String.IsNullOrEmpty(route.Template)) {
                <tr>
                    <td>@route.Template</td>
                    <td>@route.Controller</td>
                    <td>@route.Action</td>
                    <td>@route.Constraint</td>
                    <td>@route.Name</td>
                </tr>
            }
        }
    </tbody>
</table>


问:
Go MSSQL Connection
How do I provide an MSSQL connection? It says it's always connected with the code. Even if the information is incorrect, it does not give errors.</p>

```<code>package main

import (
    "database/sql"
    "fmt"
    _ "github.com/denisenkom/go-mssqldb"
    "log"
)

var server = "DESKTOP\\SQLEXPRESS"
var port = 1433
var user = "loginx"
var password = "1"
var database = "test"

var db *sql.DB

func main() {
    var err error

    connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;",
        server, user, password, port, database)
    fmt.Println(server, user, password, database)
    conn, err := sql.Open("mssql", connString)
    if err != nil {
        log.Fatal("Open connection failed:", err.Error())
    }
    fmt.Printf("Connected!\n")
    defer conn.Close()

}

</code>```

答:
mssql in Open() should be sqlserver

Try change this.

db, err = sql.Open("sqlserver", connString)

So your main() would look like..

```<code>var db *sql.DB

func main() {
    var err error

    // Create connection string
    connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d",
        server, user, password, port)

    // Create connection pool
    db, err = sql.Open("sqlserver", connString)
    if err != nil {
        log.Fatal("Error creating connection pool: " + err.Error())
    }
    log.Printf("Connected!\n")

    // Close the database connection pool after program executes
    defer db.Close()
}

</code>```

Title: Go MSSQL Connection

Body:
How do I provide an MSSQL connection? It says it's always connected with the code. Even if the information is incorrect, it does not give errors.

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/denisenkom/go-mssqldb"
    "log"
)

var server = "DESKTOP\\SQLEXPRESS"
var port = 1433
var user = "loginx"
var password = "1"
var database = "test"

var db *sql.DB

func main() {
    var err error

    connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;",
        server, user, password, port, database)
    fmt.Println(server, user, password, database)
    conn, err := sql.Open("mssql", connString)
    if err != nil {
        log.Fatal("Open connection failed:", err.Error())
    }
    fmt.Printf("Connected!\n")
    defer conn.Close()

}

Body:
mssql in Open() should be sqlserver

Try change this.

db, err = sql.Open("sqlserver", connString)

So your main() would look like..

var db *sql.DB

func main() {
    var err error

    // Create connection string
    connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d",
        server, user, password, port)

    // Create connection pool
    db, err = sql.Open("sqlserver", connString)
    if err != nil {
        log.Fatal("Error creating connection pool: " + err.Error())
    }
    log.Printf("Connected!\n")

    // Close the database connection pool after program executes
    defer db.Close()
}


问:
Performance Tuning Oracle SQL involving huge tables
I have an Oracle SQL query that involves joins with 4 large tables along with few small tables.
Large tables are TBL_1, TBL_2, TBL_3, TBL4 each having approx 8M records.  Rest are small tables having less than 10K records.

The problem: Query takes more than 3 minutes even if there is no data to return.

The table and index stats are up-to-date.  There are no stale stats on these tables.

I've tried using hints but it didn't work.

Please see my observations below:

Query:

```<code>    SELECT a.*, ROWNUM AS rnm
      FROM (  SELECT c.idntfr,
             pr.program_name AS "Program",
             e.case_number,
             (SELECT DECODE (s.status_name,
                     'EA', 'A',
                     'ED', 'D',
                     'EU', 'U',
                     s.status_name)
                FROM TBL_5 ms, status s
               WHERE     ms.status_type_cid = 7
                 AND mbr_sid = c.mbr_sid
                 AND ms.status_type_cid = s.status_type_cid
                 AND s.status_cid = ms.status_cid
                 AND ms.oprtnl_flag = 'A'
                 AND SYSDATE BETWEEN ms.from_date AND ms.TO_DATE),
             DECODE (
                LENGTH (TRIM (e.social_security_nmbr)),
                NULL, 'Not Available',
                (   SUBSTR (e.social_security_nmbr, 1, 3)
                 || '-'
                 || SUBSTR (e.social_security_nmbr, 4, 2)
                 || '-'
                 || SUBSTR (e.social_security_nmbr, 6, 4)))
                AS "SSN",
             e.last_name || ',' || e.first_name || ' ' || e.middle_name,
             TO_CHAR (e.injury_date, 'MM/dd/yyyy'),
             DECODE (e.gender_lkpcd,
                 'M', 'Male',
                 'F', 'Female',
                 'U', 'Unknown'),
             e.mbr_sid,
             pr.program_cid,
             e.last_name,
             e.social_security_nmbr,
             e.first_name AS
            FROM TBL_1 c,
             program pr,
             TBL_2 e,
             TBL_3 mai,
             TBL_4 uaxou
           WHERE     c.mbr_sid = e.mbr_sid
             AND c.mbr_sid = mai.mbr_sid
             AND c.oprtnl_flag = 'A'
             AND c.idntfr_type_cid = 423
             AND TRUNC (SYSDATE) BETWEEN c.from_date AND c.TO_DATE
             AND TRUNC (SYSDATE) BETWEEN e.from_date AND e.TO_DATE
             AND e.oprtnl_flag = 'A'
             AND e.status_cid = 2
             AND mai.oprtnl_flag = 'A'
             AND mai.status_cid = 2
             AND TRUNC (SYSDATE) BETWEEN mai.from_date AND mai.TO_DATE
             AND e.program_code = pr.program_code
             AND pr.oprtnl_flag = 'A'
             AND uaxou.user_acct_sid = 1
             AND uaxou.oprtnl_flag = 'A'
             AND SYSDATE BETWEEN uaxou.from_date AND uaxou.TO_DATE
             AND uaxou.org_unit_sid = mai.org_unit_sid
        ORDER BY "Program" ASC) a
     WHERE ROWNUM < 102;
</code>```

There is no data for the following condition

```<code>    AND uaxou.user_acct_sid = 1
</code>```

Expected Result: Response time should be under 4 seconds if NO data is returned.

Explain Plan:

```<code>Plan hash value: 2272581586

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                             |   101 | 22220 |  1361   (1)| 00:00:01 |
|   1 |  NESTED LOOPS                         |                             |     1 |    58 |     7   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                             |     1 |    58 |     7   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TBL_5                       |     1 |    31 |     6   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | XIF1TBL_5                   |     8 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN                  | XPKSTATUS                   |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | STATUS                      |     1 |    27 |     1   (0)| 00:00:01 |
|*  7 |  COUNT STOPKEY                        |                             |       |       |            |          |
|   8 |   VIEW                                |                             |   169 | 37180 |  1361   (1)| 00:00:01 |
|   9 |    NESTED LOOPS                       |                             |   169 | 36166 |   767   (0)| 00:00:01 |
|  10 |     NESTED LOOPS                      |                             | 11904 | 36166 |   767   (0)| 00:00:01 |
|  11 |      NESTED LOOPS                     |                             |    62 | 11284 |   333   (0)| 00:00:01 |
|  12 |       NESTED LOOPS                    |                             |    45 |  6660 |   108   (0)| 00:00:01 |
|  13 |        NESTED LOOPS                   |                             |    33 |  3564 |     9   (0)| 00:00:01 |
|* 14 |         TABLE ACCESS BY INDEX ROWID   | PROGRAM                     |     5 |    70 |     2   (0)| 00:00:01 |
|  15 |          INDEX FULL SCAN              | XAK1OWCP_PROGRAM            |     2 |       |     1   (0)| 00:00:01 |
|* 16 |         TABLE ACCESS FULL             | TBL_2                       |    20 |  1880 |     4   (0)| 00:00:01 |
|* 17 |        TABLE ACCESS BY INDEX ROWID    | TBL_1                       |     1 |    40 |     3   (0)| 00:00:01 |
|* 18 |         INDEX RANGE SCAN              | TUNE_WS_19NOV10_X2          |     1 |       |     2   (0)| 00:00:01 |
|* 19 |       TABLE ACCESS BY INDEX ROWID     | TBL_3                       |     1 |    34 |     5   (0)| 00:00:01 |
|* 20 |        INDEX RANGE SCAN               | XIE2_TBL_3                  |     3 |       |     2   (0)| 00:00:01 |
|* 21 |      INDEX RANGE SCAN                 | XIF3TBL_4                   |   192 |       |     1   (0)| 00:00:01 |
|* 22 |     TABLE ACCESS BY INDEX ROWID       | TBL_4                       |     3 |    96 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("MS"."STATUS_TYPE_CID"=7 AND "MS"."OPRTNL_FLAG"='A' AND "MS"."TO_DATE">=SYSDATE@! AND
          "MS"."FROM_DATE"<=SYSDATE@!)
   4 - access("MBR_SID"=:B1)
   5 - access("S"."STATUS_TYPE_CID"=7 AND "S"."STATUS_CID"="MS"."STATUS_CID")
   7 - filter(ROWNUM<102)
  14 - filter("PR"."OPRTNL_FLAG"='A')
  16 - filter("E"."PROGRAM_CODE"="PR"."PROGRAM_CODE" AND "E"."OPRTNL_FLAG"='A' AND "E"."STATUS_CID"=2 AND
          "E"."FROM_DATE"<=TRUNC(SYSDATE@!) AND TRUNC(INTERNAL_FUNCTION("FROM_DATE"))<=TRUNC(TRUNC(SYSDATE@!)) AND
          "E"."TO_DATE">=TRUNC(SYSDATE@!) AND TRUNC(INTERNAL_FUNCTION("TO_DATE"))>=TRUNC(TRUNC(SYSDATE@!)))
  17 - filter("C"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "C"."TO_DATE">=TRUNC(SYSDATE@!))
  18 - access("C"."MBR_SID"="E"."MBR_SID" AND "C"."IDNTFR_TYPE_CID"=423 AND "C"."OPRTNL_FLAG"='A')
  19 - filter("MAI"."OPRTNL_FLAG"='A' AND "MAI"."STATUS_CID"=2 AND "MAI"."FROM_DATE"<=TRUNC(SYSDATE@!) AND
          "MAI"."TO_DATE">=TRUNC(SYSDATE@!))
  20 - access("C"."MBR_SID"="MAI"."MBR_SID")
  21 - access("UAXOU"."USER_ACCT_SID"=1)
  22 - filter("UAXOU"."ORG_UNIT_SID"="MAI"."ORG_UNIT_SID" AND "UAXOU"."OPRTNL_FLAG"='A' AND
          "UAXOU"."FROM_DATE"<=SYSDATE@! AND "UAXOU"."TO_DATE">=SYSDATE@!)
</code>```

This is output of query from v$parameter

```<code>    NAME                                |   VALUE
    compatible                          |   12.2.0
    optimizer_adaptive_plans            |   TRUE
    optimizer_adaptive_reporting_only   |   FALSE
    optimizer_features_enable           |   12.2.0.1
</code>```

This is the explain plan after adding GATHER_PLAN_STATISTICS showing the actual cardinality values:

```<code>    Plan hash value: 2272581586

    -------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    -------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                      |                             |      1 |        |      0 |00:00:00.01 |       0 |
    |   1 |  NESTED LOOPS                         |                             |      0 |      1 |      0 |00:00:00.01 |       0 |
    |   2 |   NESTED LOOPS                        |                             |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TBL_5                       |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  4 |     INDEX RANGE SCAN                  | XIF1TBL_5                   |      0 |      8 |      0 |00:00:00.01 |       0 |
    |*  5 |    INDEX UNIQUE SCAN                  | XPKSTATUS                   |      0 |      1 |      0 |00:00:00.01 |       0 |
    |   6 |   TABLE ACCESS BY INDEX ROWID         | STATUS                      |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  7 |  COUNT STOPKEY                        |                             |      1 |        |      0 |00:00:00.01 |       0 |
    |   8 |   VIEW                                |                             |      1 |    169 |      0 |00:00:00.01 |       0 |
    |   9 |    NESTED LOOPS                       |                             |      1 |    169 |      0 |00:00:00.01 |       0 |
    |  10 |     NESTED LOOPS                      |                             |      1 |  11904 |      0 |00:00:00.01 |       0 |
    |  11 |      NESTED LOOPS                     |                             |      1 |     62 |      0 |00:00:00.01 |       0 |
    |  12 |       NESTED LOOPS                    |                             |      1 |     45 |      0 |00:00:00.01 |       0 |
    |  13 |        NESTED LOOPS                   |                             |      1 |     33 |      0 |00:00:00.01 |       0 |
    |* 14 |         TABLE ACCESS BY INDEX ROWID   | PROGRAM                     |      1 |      5 |      1 |00:00:00.01 |       2 |
    |  15 |          INDEX FULL SCAN              | XAK1OWCP_PROGRAM            |      1 |      2 |      2 |00:00:00.01 |       1 |
    |* 16 |         TABLE ACCESS FULL             | TBL_2                       |      1 |     20 |      0 |00:00:00.01 |       0 |
    |* 17 |        TABLE ACCESS BY INDEX ROWID    | TBL_1                   |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 18 |         INDEX RANGE SCAN              | TUNE_WS_19NOV10_X2          |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 19 |       TABLE ACCESS BY INDEX ROWID     | TBL_3                       |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 20 |        INDEX RANGE SCAN               | XIE2_TBL_3                  |      0 |      3 |      0 |00:00:00.01 |       0 |
    |* 21 |      INDEX RANGE SCAN                 | XIF3TBL_4           |      0 |    192 |      0 |00:00:00.01 |       0 |
    |* 22 |     TABLE ACCESS BY INDEX ROWID       | TBL_4                   |      0 |      3 |      0 |00:00:00.01 |       0 |
    -------------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       3 - filter(("MS"."STATUS_TYPE_CID"=7 AND "MS"."OPRTNL_FLAG"='A' AND "MS"."TO_DATE">=SYSDATE@! AND
              "MS"."FROM_DATE"<=SYSDATE@!))
       4 - access("MBR_SID"=:B1)
       5 - access("S"."STATUS_TYPE_CID"=7 AND "S"."STATUS_CID"="MS"."STATUS_CID")
       7 - filter(ROWNUM<102)
      14 - filter("PR"."OPRTNL_FLAG"='A')
      16 - filter(("E"."PROGRAM_CODE"="PR"."PROGRAM_CODE" AND "E"."OPRTNL_FLAG"='A' AND "E"."STATUS_CID"=2 AND
              "E"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "E"."TO_DATE">=TRUNC(SYSDATE@!)))
      17 - filter(("C"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "C"."TO_DATE">=TRUNC(SYSDATE@!)))
      18 - access("C"."MBR_SID"="E"."MBR_SID" AND "C"."IDNTFR_TYPE_CID"=423 AND "C"."OPRTNL_FLAG"='A')
      19 - filter(("MAI"."OPRTNL_FLAG"='A' AND "MAI"."STATUS_CID"=2 AND "MAI"."FROM_DATE"<=TRUNC(SYSDATE@!) AND
              "MAI"."TO_DATE">=TRUNC(SYSDATE@!)))
      20 - access("C"."MBR_SID"="MAI"."MBR_SID")
      21 - access("UAXOU"."USER_ACCT_SID"=1)
      22 - filter(("UAXOU"."ORG_UNIT_SID"="MAI"."ORG_UNIT_SID" AND "UAXOU"."OPRTNL_FLAG"='A' AND
              "UAXOU"."FROM_DATE"<=SYSDATE@! AND "UAXOU"."TO_DATE">=SYSDATE@!))
</code>```

I've tried various hints USE_HASH(c e) and various other combinations, none worked.

One interesting observation, if I comment the condition:

```<code>    --AND uaxou.user_acct_sid = 1
</code>```

The results came in 7 seconds.  (Obviously, data is returned in this case).

So, what's causing the query to take so long when no data is returned? (i.e. this condition is not commented AND uaxou.user_acct_sid = 1)

I let the slow query to finish, it took 10 min 46 seconds.  No data is returned

Here is the explain plan.  I don't know why A-Time doesn't match with actual execution time.

```<code>    Plan hash value: 2272581586

    -------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    -------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                      |                             |      1 |        |      0 |00:00:00.01 |       0 |
    |   1 |  NESTED LOOPS                         |                             |      0 |      1 |      0 |00:00:00.01 |       0 |
    |   2 |   NESTED LOOPS                        |                             |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TBL_5                       |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  4 |     INDEX RANGE SCAN                  | XIF1TBL_5                   |      0 |      8 |      0 |00:00:00.01 |       0 |
    |*  5 |    INDEX UNIQUE SCAN                  | XPKSTATUS                   |      0 |      1 |      0 |00:00:00.01 |       0 |
    |   6 |   TABLE ACCESS BY INDEX ROWID         | STATUS                      |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  7 |  COUNT STOPKEY                        |                             |      1 |        |      0 |00:00:00.01 |       0 |
    |   8 |   VIEW                                |                             |      1 |    169 |      0 |00:00:00.01 |       0 |
    |   9 |    NESTED LOOPS                       |                             |      1 |    169 |      0 |00:00:00.01 |       0 |
    |  10 |     NESTED LOOPS                      |                             |      1 |  11904 |      0 |00:00:00.01 |       0 |
    |  11 |      NESTED LOOPS                     |                             |      1 |     62 |      0 |00:00:00.01 |       0 |
    |  12 |       NESTED LOOPS                    |                             |      1 |     45 |      0 |00:00:00.01 |       0 |
    |  13 |        NESTED LOOPS                   |                             |      1 |     33 |      0 |00:00:00.01 |       0 |
    |* 14 |         TABLE ACCESS BY INDEX ROWID   | PROGRAM                     |      1 |      5 |      1 |00:00:00.01 |       2 |
    |  15 |          INDEX FULL SCAN              | XAK1OWCP_PROGRAM            |      1 |      2 |      2 |00:00:00.01 |       1 |
    |* 16 |         TABLE ACCESS FULL             | TBL_2                       |      1 |     20 |      0 |00:00:00.01 |       0 |
    |* 17 |        TABLE ACCESS BY INDEX ROWID    | TBL_1                       |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 18 |         INDEX RANGE SCAN              | TUNE_WS_19NOV10_X2          |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 19 |       TABLE ACCESS BY INDEX ROWID     | TBL_3                       |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 20 |        INDEX RANGE SCAN               | XIE2_TBL_3                  |      0 |      3 |      0 |00:00:00.01 |       0 |
    |* 21 |      INDEX RANGE SCAN                 | XIF3TBL_4                   |      0 |    192 |      0 |00:00:00.01 |       0 |
    |* 22 |     TABLE ACCESS BY INDEX ROWID       | TBL_4                       |      0 |      3 |      0 |00:00:00.01 |       0 |
    -------------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       3 - filter(("MS"."STATUS_TYPE_CID"=7 AND "MS"."OPRTNL_FLAG"='A' AND "MS"."TO_DATE">=SYSDATE@! AND
              "MS"."FROM_DATE"<=SYSDATE@!))
       4 - access("MBR_SID"=:B1)
       5 - access("S"."STATUS_TYPE_CID"=7 AND "S"."STATUS_CID"="MS"."STATUS_CID")
       7 - filter(ROWNUM<102)
      14 - filter("PR"."OPRTNL_FLAG"='A')
      16 - filter(("E"."PROGRAM_CODE"="PR"."PROGRAM_CODE" AND "E"."OPRTNL_FLAG"='A' AND "E"."STATUS_CID"=2 AND
              "E"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "E"."TO_DATE">=TRUNC(SYSDATE@!)))
      17 - filter(("C"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "C"."TO_DATE">=TRUNC(SYSDATE@!)))
      18 - access("C"."MBR_SID"="E"."MBR_SID" AND "C"."IDNTFR_TYPE_CID"=423 AND "C"."OPRTNL_FLAG"='A')
      19 - filter(("MAI"."OPRTNL_FLAG"='A' AND "MAI"."STATUS_CID"=2 AND "MAI"."FROM_DATE"<=TRUNC(SYSDATE@!) AND
              "MAI"."TO_DATE">=TRUNC(SYSDATE@!)))
      20 - access("C"."MBR_SID"="MAI"."MBR_SID")
      21 - access("UAXOU"."USER_ACCT_SID"=1)
      22 - filter(("UAXOU"."ORG_UNIT_SID"="MAI"."ORG_UNIT_SID" AND "UAXOU"."OPRTNL_FLAG"='A' AND
              "UAXOU"."FROM_DATE"<=SYSDATE@! AND "UAXOU"."TO_DATE">=SYSDATE@!))
</code>```

答:
Adaptive plans may improve the execution plan.

You tagged the question as 12c but it looks like the execution plan is not using adaptive plans for some reason.  Adaptive plans let Oracle change operations at run time, such as switching between NESTED LOOPS and HASH JOINS.

NESTED LOOPS are good for a small percentage of rows, and HASH JOINS are good for a large percentage of rows.  Since the ROWS estimates are all small, but the query runs for three minutes, I would guess the optimizer is significantly underestimating the cardinality of the expressions and joins, and is using too many NESTED LOOPs.

If adaptive plans were enabled the execution plan should have this at the bottom:

```<code>Note
-----
   - this is an adaptive plan
</code>```

Since that Note is missing, I would guess there is a parameter issue on your database preventing adaptive plans.  Run the below query and see if one of the features is turned off, or if the features are set to a version lower than 12:

```<code>select name, value
from v$parameter
where name in (
    'optimizer_adaptive_features', --12.1 only
    'optimizer_adaptive_plans', --12.2+
    'optimizer_adaptive_reporting_only',
    'optimizer_features_enable',
    'compatible'
    )
order by 1;
</code>```

EDIT 1

I'm not sure why adaptive plans aren't working for you.  If nobody can figure that out, then we'll need to investigate the execution plan actual values to find out precisely which operations are slow.

There are at least two ways to get the actual numbers.  If you can change and re-run the offending query, you can use the hint GATHER_PLAN_STATISTICS.

```<code>--Run slow query and wait for it to finish:
select /*+ gather_plan_statistics */ * from dual;

--Find the SQL_ID of the query using some distinctive text:
select *
from v$sql
where lower(sql_fulltext) like '%gather_plan_statistics%';

--Generate execution plan with actual values.
select *
from table(dbms_xplan.display_cursor(sql_id => 'SQL_ID from above', format=>'allstats last'));
</code>```

If you cannot alter the query, you can use SQL Monitor reports to find the actual values.  (This feature requires Enterprise Edition and a tuning pack license.)

```<code>--Generate SQL Monitoring Report:
select dbms_sqltune.report_sql_monitor(sql_id => 'SQL_ID from above') from dual;
</code>```

EDIT 2

Are you 100% sure you've found the right SQL_ID?  You might want to double check GV$SQL.  Sometimes the case is switched, if the SQL is submitted from an application or PL/SQL block.  And rarely the real SQL statement will age out of GV$SQL, if someone runs alter system flush shared_pool;, or stats are gathered, or if you wait too long.

If that is truly the right execution plan, no time is spent on the query.  Normally this means that time must be spent by the network sending the results, or the application processing the results.  But since there are no rows returned a network or application issue doesn't sound likely.

If the time is spent on the database, but not on that query, then I would guess it's either a parsing issue or a recursive query issue.  Parsing issues can be found by tracing, but those are caused by extremely unusual problems or queries, and likely isn't the case here.

Perhaps one of the queries Oracle uses to gather metadata is taking too long.  For many queries, Oracle needs to run other queries that check privileges, dynamic sampling, etc.  You may need to tune one of those other queries, and the below statements can help with that painful process:

```<code>--Clear existing run times (be careful running this on production).
--(This won't flush queries that are actively running.)
alter system flush shared_pool;

--Run your slow SQL statement here.
--...

--Now look for anything "weird" that has taken up most of the time.
select elapsed_time/1000000 seconds, gv$sql.*
from gv$sql
order by seconds desc;
</code>```

Body:
I have an Oracle SQL query that involves joins with 4 large tables along with few small tables.
Large tables are TBL_1, TBL_2, TBL_3, TBL4 each having approx 8M records.  Rest are small tables having less than 10K records.

The problem: Query takes more than 3 minutes even if there is no data to return.

The table and index stats are up-to-date.  There are no stale stats on these tables.

I've tried using hints but it didn't work.

Please see my observations below:

Query:

    SELECT a.*, ROWNUM AS rnm
      FROM (  SELECT c.idntfr,
             pr.program_name AS "Program",
             e.case_number,
             (SELECT DECODE (s.status_name,
                     'EA', 'A',
                     'ED', 'D',
                     'EU', 'U',
                     s.status_name)
                FROM TBL_5 ms, status s
               WHERE     ms.status_type_cid = 7
                 AND mbr_sid = c.mbr_sid
                 AND ms.status_type_cid = s.status_type_cid
                 AND s.status_cid = ms.status_cid
                 AND ms.oprtnl_flag = 'A'
                 AND SYSDATE BETWEEN ms.from_date AND ms.TO_DATE),
             DECODE (
                LENGTH (TRIM (e.social_security_nmbr)),
                NULL, 'Not Available',
                (   SUBSTR (e.social_security_nmbr, 1, 3)
                 || '-'
                 || SUBSTR (e.social_security_nmbr, 4, 2)
                 || '-'
                 || SUBSTR (e.social_security_nmbr, 6, 4)))
                AS "SSN",
             e.last_name || ',' || e.first_name || ' ' || e.middle_name,
             TO_CHAR (e.injury_date, 'MM/dd/yyyy'),
             DECODE (e.gender_lkpcd,
                 'M', 'Male',
                 'F', 'Female',
                 'U', 'Unknown'),
             e.mbr_sid,
             pr.program_cid,
             e.last_name,
             e.social_security_nmbr,
             e.first_name AS
            FROM TBL_1 c,
             program pr,
             TBL_2 e,
             TBL_3 mai,
             TBL_4 uaxou
           WHERE     c.mbr_sid = e.mbr_sid
             AND c.mbr_sid = mai.mbr_sid
             AND c.oprtnl_flag = 'A'
             AND c.idntfr_type_cid = 423
             AND TRUNC (SYSDATE) BETWEEN c.from_date AND c.TO_DATE
             AND TRUNC (SYSDATE) BETWEEN e.from_date AND e.TO_DATE
             AND e.oprtnl_flag = 'A'
             AND e.status_cid = 2
             AND mai.oprtnl_flag = 'A'
             AND mai.status_cid = 2
             AND TRUNC (SYSDATE) BETWEEN mai.from_date AND mai.TO_DATE
             AND e.program_code = pr.program_code
             AND pr.oprtnl_flag = 'A'
             AND uaxou.user_acct_sid = 1
             AND uaxou.oprtnl_flag = 'A'
             AND SYSDATE BETWEEN uaxou.from_date AND uaxou.TO_DATE
             AND uaxou.org_unit_sid = mai.org_unit_sid
        ORDER BY "Program" ASC) a
     WHERE ROWNUM < 102;

There is no data for the following condition

    AND uaxou.user_acct_sid = 1

Expected Result: Response time should be under 4 seconds if NO data is returned.

Explain Plan:

Plan hash value: 2272581586

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                             |   101 | 22220 |  1361   (1)| 00:00:01 |
|   1 |  NESTED LOOPS                         |                             |     1 |    58 |     7   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                             |     1 |    58 |     7   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TBL_5                       |     1 |    31 |     6   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | XIF1TBL_5                   |     8 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN                  | XPKSTATUS                   |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | STATUS                      |     1 |    27 |     1   (0)| 00:00:01 |
|*  7 |  COUNT STOPKEY                        |                             |       |       |            |          |
|   8 |   VIEW                                |                             |   169 | 37180 |  1361   (1)| 00:00:01 |
|   9 |    NESTED LOOPS                       |                             |   169 | 36166 |   767   (0)| 00:00:01 |
|  10 |     NESTED LOOPS                      |                             | 11904 | 36166 |   767   (0)| 00:00:01 |
|  11 |      NESTED LOOPS                     |                             |    62 | 11284 |   333   (0)| 00:00:01 |
|  12 |       NESTED LOOPS                    |                             |    45 |  6660 |   108   (0)| 00:00:01 |
|  13 |        NESTED LOOPS                   |                             |    33 |  3564 |     9   (0)| 00:00:01 |
|* 14 |         TABLE ACCESS BY INDEX ROWID   | PROGRAM                     |     5 |    70 |     2   (0)| 00:00:01 |
|  15 |          INDEX FULL SCAN              | XAK1OWCP_PROGRAM            |     2 |       |     1   (0)| 00:00:01 |
|* 16 |         TABLE ACCESS FULL             | TBL_2                       |    20 |  1880 |     4   (0)| 00:00:01 |
|* 17 |        TABLE ACCESS BY INDEX ROWID    | TBL_1                       |     1 |    40 |     3   (0)| 00:00:01 |
|* 18 |         INDEX RANGE SCAN              | TUNE_WS_19NOV10_X2          |     1 |       |     2   (0)| 00:00:01 |
|* 19 |       TABLE ACCESS BY INDEX ROWID     | TBL_3                       |     1 |    34 |     5   (0)| 00:00:01 |
|* 20 |        INDEX RANGE SCAN               | XIE2_TBL_3                  |     3 |       |     2   (0)| 00:00:01 |
|* 21 |      INDEX RANGE SCAN                 | XIF3TBL_4                   |   192 |       |     1   (0)| 00:00:01 |
|* 22 |     TABLE ACCESS BY INDEX ROWID       | TBL_4                       |     3 |    96 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("MS"."STATUS_TYPE_CID"=7 AND "MS"."OPRTNL_FLAG"='A' AND "MS"."TO_DATE">=SYSDATE@! AND
          "MS"."FROM_DATE"<=SYSDATE@!)
   4 - access("MBR_SID"=:B1)
   5 - access("S"."STATUS_TYPE_CID"=7 AND "S"."STATUS_CID"="MS"."STATUS_CID")
   7 - filter(ROWNUM<102)
  14 - filter("PR"."OPRTNL_FLAG"='A')
  16 - filter("E"."PROGRAM_CODE"="PR"."PROGRAM_CODE" AND "E"."OPRTNL_FLAG"='A' AND "E"."STATUS_CID"=2 AND
          "E"."FROM_DATE"<=TRUNC(SYSDATE@!) AND TRUNC(INTERNAL_FUNCTION("FROM_DATE"))<=TRUNC(TRUNC(SYSDATE@!)) AND
          "E"."TO_DATE">=TRUNC(SYSDATE@!) AND TRUNC(INTERNAL_FUNCTION("TO_DATE"))>=TRUNC(TRUNC(SYSDATE@!)))
  17 - filter("C"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "C"."TO_DATE">=TRUNC(SYSDATE@!))
  18 - access("C"."MBR_SID"="E"."MBR_SID" AND "C"."IDNTFR_TYPE_CID"=423 AND "C"."OPRTNL_FLAG"='A')
  19 - filter("MAI"."OPRTNL_FLAG"='A' AND "MAI"."STATUS_CID"=2 AND "MAI"."FROM_DATE"<=TRUNC(SYSDATE@!) AND
          "MAI"."TO_DATE">=TRUNC(SYSDATE@!))
  20 - access("C"."MBR_SID"="MAI"."MBR_SID")
  21 - access("UAXOU"."USER_ACCT_SID"=1)
  22 - filter("UAXOU"."ORG_UNIT_SID"="MAI"."ORG_UNIT_SID" AND "UAXOU"."OPRTNL_FLAG"='A' AND
          "UAXOU"."FROM_DATE"<=SYSDATE@! AND "UAXOU"."TO_DATE">=SYSDATE@!)

This is output of query from v$parameter

    NAME                                |   VALUE
    compatible                          |   12.2.0
    optimizer_adaptive_plans            |   TRUE
    optimizer_adaptive_reporting_only   |   FALSE
    optimizer_features_enable           |   12.2.0.1

This is the explain plan after adding GATHER_PLAN_STATISTICS showing the actual cardinality values:

    Plan hash value: 2272581586

    -------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    -------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                      |                             |      1 |        |      0 |00:00:00.01 |       0 |
    |   1 |  NESTED LOOPS                         |                             |      0 |      1 |      0 |00:00:00.01 |       0 |
    |   2 |   NESTED LOOPS                        |                             |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TBL_5                       |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  4 |     INDEX RANGE SCAN                  | XIF1TBL_5                   |      0 |      8 |      0 |00:00:00.01 |       0 |
    |*  5 |    INDEX UNIQUE SCAN                  | XPKSTATUS                   |      0 |      1 |      0 |00:00:00.01 |       0 |
    |   6 |   TABLE ACCESS BY INDEX ROWID         | STATUS                      |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  7 |  COUNT STOPKEY                        |                             |      1 |        |      0 |00:00:00.01 |       0 |
    |   8 |   VIEW                                |                             |      1 |    169 |      0 |00:00:00.01 |       0 |
    |   9 |    NESTED LOOPS                       |                             |      1 |    169 |      0 |00:00:00.01 |       0 |
    |  10 |     NESTED LOOPS                      |                             |      1 |  11904 |      0 |00:00:00.01 |       0 |
    |  11 |      NESTED LOOPS                     |                             |      1 |     62 |      0 |00:00:00.01 |       0 |
    |  12 |       NESTED LOOPS                    |                             |      1 |     45 |      0 |00:00:00.01 |       0 |
    |  13 |        NESTED LOOPS                   |                             |      1 |     33 |      0 |00:00:00.01 |       0 |
    |* 14 |         TABLE ACCESS BY INDEX ROWID   | PROGRAM                     |      1 |      5 |      1 |00:00:00.01 |       2 |
    |  15 |          INDEX FULL SCAN              | XAK1OWCP_PROGRAM            |      1 |      2 |      2 |00:00:00.01 |       1 |
    |* 16 |         TABLE ACCESS FULL             | TBL_2                       |      1 |     20 |      0 |00:00:00.01 |       0 |
    |* 17 |        TABLE ACCESS BY INDEX ROWID    | TBL_1                   |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 18 |         INDEX RANGE SCAN              | TUNE_WS_19NOV10_X2          |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 19 |       TABLE ACCESS BY INDEX ROWID     | TBL_3                       |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 20 |        INDEX RANGE SCAN               | XIE2_TBL_3                  |      0 |      3 |      0 |00:00:00.01 |       0 |
    |* 21 |      INDEX RANGE SCAN                 | XIF3TBL_4           |      0 |    192 |      0 |00:00:00.01 |       0 |
    |* 22 |     TABLE ACCESS BY INDEX ROWID       | TBL_4                   |      0 |      3 |      0 |00:00:00.01 |       0 |
    -------------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       3 - filter(("MS"."STATUS_TYPE_CID"=7 AND "MS"."OPRTNL_FLAG"='A' AND "MS"."TO_DATE">=SYSDATE@! AND
              "MS"."FROM_DATE"<=SYSDATE@!))
       4 - access("MBR_SID"=:B1)
       5 - access("S"."STATUS_TYPE_CID"=7 AND "S"."STATUS_CID"="MS"."STATUS_CID")
       7 - filter(ROWNUM<102)
      14 - filter("PR"."OPRTNL_FLAG"='A')
      16 - filter(("E"."PROGRAM_CODE"="PR"."PROGRAM_CODE" AND "E"."OPRTNL_FLAG"='A' AND "E"."STATUS_CID"=2 AND
              "E"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "E"."TO_DATE">=TRUNC(SYSDATE@!)))
      17 - filter(("C"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "C"."TO_DATE">=TRUNC(SYSDATE@!)))
      18 - access("C"."MBR_SID"="E"."MBR_SID" AND "C"."IDNTFR_TYPE_CID"=423 AND "C"."OPRTNL_FLAG"='A')
      19 - filter(("MAI"."OPRTNL_FLAG"='A' AND "MAI"."STATUS_CID"=2 AND "MAI"."FROM_DATE"<=TRUNC(SYSDATE@!) AND
              "MAI"."TO_DATE">=TRUNC(SYSDATE@!)))
      20 - access("C"."MBR_SID"="MAI"."MBR_SID")
      21 - access("UAXOU"."USER_ACCT_SID"=1)
      22 - filter(("UAXOU"."ORG_UNIT_SID"="MAI"."ORG_UNIT_SID" AND "UAXOU"."OPRTNL_FLAG"='A' AND
              "UAXOU"."FROM_DATE"<=SYSDATE@! AND "UAXOU"."TO_DATE">=SYSDATE@!))

I've tried various hints USE_HASH(c e) and various other combinations, none worked.

One interesting observation, if I comment the condition:

    --AND uaxou.user_acct_sid = 1

The results came in 7 seconds.  (Obviously, data is returned in this case).

So, what's causing the query to take so long when no data is returned? (i.e. this condition is not commented AND uaxou.user_acct_sid = 1)

I let the slow query to finish, it took 10 min 46 seconds.  No data is returned

Here is the explain plan.  I don't know why A-Time doesn't match with actual execution time.

    Plan hash value: 2272581586

    -------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    -------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                      |                             |      1 |        |      0 |00:00:00.01 |       0 |
    |   1 |  NESTED LOOPS                         |                             |      0 |      1 |      0 |00:00:00.01 |       0 |
    |   2 |   NESTED LOOPS                        |                             |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TBL_5                       |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  4 |     INDEX RANGE SCAN                  | XIF1TBL_5                   |      0 |      8 |      0 |00:00:00.01 |       0 |
    |*  5 |    INDEX UNIQUE SCAN                  | XPKSTATUS                   |      0 |      1 |      0 |00:00:00.01 |       0 |
    |   6 |   TABLE ACCESS BY INDEX ROWID         | STATUS                      |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  7 |  COUNT STOPKEY                        |                             |      1 |        |      0 |00:00:00.01 |       0 |
    |   8 |   VIEW                                |                             |      1 |    169 |      0 |00:00:00.01 |       0 |
    |   9 |    NESTED LOOPS                       |                             |      1 |    169 |      0 |00:00:00.01 |       0 |
    |  10 |     NESTED LOOPS                      |                             |      1 |  11904 |      0 |00:00:00.01 |       0 |
    |  11 |      NESTED LOOPS                     |                             |      1 |     62 |      0 |00:00:00.01 |       0 |
    |  12 |       NESTED LOOPS                    |                             |      1 |     45 |      0 |00:00:00.01 |       0 |
    |  13 |        NESTED LOOPS                   |                             |      1 |     33 |      0 |00:00:00.01 |       0 |
    |* 14 |         TABLE ACCESS BY INDEX ROWID   | PROGRAM                     |      1 |      5 |      1 |00:00:00.01 |       2 |
    |  15 |          INDEX FULL SCAN              | XAK1OWCP_PROGRAM            |      1 |      2 |      2 |00:00:00.01 |       1 |
    |* 16 |         TABLE ACCESS FULL             | TBL_2                       |      1 |     20 |      0 |00:00:00.01 |       0 |
    |* 17 |        TABLE ACCESS BY INDEX ROWID    | TBL_1                       |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 18 |         INDEX RANGE SCAN              | TUNE_WS_19NOV10_X2          |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 19 |       TABLE ACCESS BY INDEX ROWID     | TBL_3                       |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 20 |        INDEX RANGE SCAN               | XIE2_TBL_3                  |      0 |      3 |      0 |00:00:00.01 |       0 |
    |* 21 |      INDEX RANGE SCAN                 | XIF3TBL_4                   |      0 |    192 |      0 |00:00:00.01 |       0 |
    |* 22 |     TABLE ACCESS BY INDEX ROWID       | TBL_4                       |      0 |      3 |      0 |00:00:00.01 |       0 |
    -------------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       3 - filter(("MS"."STATUS_TYPE_CID"=7 AND "MS"."OPRTNL_FLAG"='A' AND "MS"."TO_DATE">=SYSDATE@! AND
              "MS"."FROM_DATE"<=SYSDATE@!))
       4 - access("MBR_SID"=:B1)
       5 - access("S"."STATUS_TYPE_CID"=7 AND "S"."STATUS_CID"="MS"."STATUS_CID")
       7 - filter(ROWNUM<102)
      14 - filter("PR"."OPRTNL_FLAG"='A')
      16 - filter(("E"."PROGRAM_CODE"="PR"."PROGRAM_CODE" AND "E"."OPRTNL_FLAG"='A' AND "E"."STATUS_CID"=2 AND
              "E"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "E"."TO_DATE">=TRUNC(SYSDATE@!)))
      17 - filter(("C"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "C"."TO_DATE">=TRUNC(SYSDATE@!)))
      18 - access("C"."MBR_SID"="E"."MBR_SID" AND "C"."IDNTFR_TYPE_CID"=423 AND "C"."OPRTNL_FLAG"='A')
      19 - filter(("MAI"."OPRTNL_FLAG"='A' AND "MAI"."STATUS_CID"=2 AND "MAI"."FROM_DATE"<=TRUNC(SYSDATE@!) AND
              "MAI"."TO_DATE">=TRUNC(SYSDATE@!)))
      20 - access("C"."MBR_SID"="MAI"."MBR_SID")
      21 - access("UAXOU"."USER_ACCT_SID"=1)
      22 - filter(("UAXOU"."ORG_UNIT_SID"="MAI"."ORG_UNIT_SID" AND "UAXOU"."OPRTNL_FLAG"='A' AND
              "UAXOU"."FROM_DATE"<=SYSDATE@! AND "UAXOU"."TO_DATE">=SYSDATE@!))

Body:
Adaptive plans may improve the execution plan.

You tagged the question as 12c but it looks like the execution plan is not using adaptive plans for some reason.  Adaptive plans let Oracle change operations at run time, such as switching between NESTED LOOPS and HASH JOINS.

NESTED LOOPS are good for a small percentage of rows, and HASH JOINS are good for a large percentage of rows.  Since the ROWS estimates are all small, but the query runs for three minutes, I would guess the optimizer is significantly underestimating the cardinality of the expressions and joins, and is using too many NESTED LOOPs.

If adaptive plans were enabled the execution plan should have this at the bottom:

Note
-----
   - this is an adaptive plan

Since that Note is missing, I would guess there is a parameter issue on your database preventing adaptive plans.  Run the below query and see if one of the features is turned off, or if the features are set to a version lower than 12:

select name, value
from v$parameter
where name in (
    'optimizer_adaptive_features', --12.1 only
    'optimizer_adaptive_plans', --12.2+
    'optimizer_adaptive_reporting_only',
    'optimizer_features_enable',
    'compatible'
    )
order by 1;

EDIT 1

I'm not sure why adaptive plans aren't working for you.  If nobody can figure that out, then we'll need to investigate the execution plan actual values to find out precisely which operations are slow.

There are at least two ways to get the actual numbers.  If you can change and re-run the offending query, you can use the hint GATHER_PLAN_STATISTICS.

--Run slow query and wait for it to finish:
select /*+ gather_plan_statistics */ * from dual;

--Find the SQL_ID of the query using some distinctive text:
select *
from v$sql
where lower(sql_fulltext) like '%gather_plan_statistics%';

--Generate execution plan with actual values.
select *
from table(dbms_xplan.display_cursor(sql_id => 'SQL_ID from above', format=>'allstats last'));

If you cannot alter the query, you can use SQL Monitor reports to find the actual values.  (This feature requires Enterprise Edition and a tuning pack license.)

--Generate SQL Monitoring Report:
select dbms_sqltune.report_sql_monitor(sql_id => 'SQL_ID from above') from dual;

EDIT 2

Are you 100% sure you've found the right SQL_ID?  You might want to double check GV$SQL.  Sometimes the case is switched, if the SQL is submitted from an application or PL/SQL block.  And rarely the real SQL statement will age out of GV$SQL, if someone runs alter system flush shared_pool;, or stats are gathered, or if you wait too long.

If that is truly the right execution plan, no time is spent on the query.  Normally this means that time must be spent by the network sending the results, or the application processing the results.  But since there are no rows returned a network or application issue doesn't sound likely.

If the time is spent on the database, but not on that query, then I would guess it's either a parsing issue or a recursive query issue.  Parsing issues can be found by tracing, but those are caused by extremely unusual problems or queries, and likely isn't the case here.

Perhaps one of the queries Oracle uses to gather metadata is taking too long.  For many queries, Oracle needs to run other queries that check privileges, dynamic sampling, etc.  You may need to tune one of those other queries, and the below statements can help with that painful process:

--Clear existing run times (be careful running this on production).
--(This won't flush queries that are actively running.)
alter system flush shared_pool;

--Run your slow SQL statement here.
--...

--Now look for anything "weird" that has taken up most of the time.
select elapsed_time/1000000 seconds, gv$sql.*
from gv$sql
order by seconds desc;


问:
Accessing protected const in method defined in parent
I have an abstract base class that extends into a child class. In the base class, I have

protected CONST TYPE = '';
```

<p>which in the child class gets overwritten by</p>

<pre class="lang-php prettyprint-override"><code>protected CONST TYPE = 'mysql';
</code>```

In the parent, I defined

public function getTableName() {
    /*
    Code here
    */
    var_dump(self::TYPE); exit;
}
```

Even though I call $this->getTableName() in the child class, it dumps an empty string. I'm guessing I'm not understanding scoping here; given it's called by the child, isn't the scope relative to the child? Or since it's defined by the parent, will it always be in the scope of the parent? Is this a problem with constants? I'm running PHP 7.3.

答:
Because once a constant is defined with a value, it remains that value. Which is intended because the value is meant to be "constant"

You should instead use protected static $type = ''

Body:
I have an abstract base class that extends into a child class. In the base class, I have

protected CONST TYPE = '';

which in the child class gets overwritten by

protected CONST TYPE = 'mysql';

In the parent, I defined

public function getTableName() {
    /*
    Code here
    */
    var_dump(self::TYPE); exit;
}

Even though I call $this->getTableName() in the child class, it dumps an empty string. I'm guessing I'm not understanding scoping here; given it's called by the child, isn't the scope relative to the child? Or since it's defined by the parent, will it always be in the scope of the parent? Is this a problem with constants? I'm running PHP 7.3.


问:
Using an IF statement to control the return statement?
```<code>public static int seqSearch(int numRecords, String[] stuName,
      double[] stuGpa, String nameKey, double gpaKey)

        for(int i = 0; i < numRecords; i++)
           if(stuName[i] == nameKey && stuGpa[i] == gpaKey)
              return i;
        return -1;
</code>```

So, how would I used an if statement to control this? I'm doing sequential search to find if the name is found in the array and if the gpa is in the array, then it should return the position it was found in (i). But, all it does do is return -1 and print out that none were found.

答: if(stuName[i] == nameKey is unlikely to be right, you are comparing object identities not string content. Try if(stuName[i].equals(nameKey)

ContentLicense: CC BY-SA 3.0

Body:
public static int seqSearch(int numRecords, String[] stuName,
      double[] stuGpa, String nameKey, double gpaKey)

        for(int i = 0; i < numRecords; i++)
           if(stuName[i] == nameKey && stuGpa[i] == gpaKey)
              return i;
        return -1;

So, how would I used an if statement to control this? I'm doing sequential search to find if the name is found in the array and if the gpa is in the array, then it should return the position it was found in (i). But, all it does do is return -1 and print out that none were found.


问:
Async load modules via knockoutjs with webpack
I am currently setting up a knockoutjs project with webpack. One of the main aspects I want to achieve is lazy loading of bundles. While I have been able to achieve code splitting it currently requires the app to specifically contain code for this and I would like to get rid of this specific code as I believe knockoutjs already comes with the necessary infrastructure.

During app setup, components are registered in knockoutjs like this:

```<code>ko.components.register('form-page', { require: 'components/form-page/form' });
</code>```

Knockout will load the referenced module with an async require only of the component form-page is used.

With my webpack setup, this will fail because

Webpack does not detect that there is a dependency on components/form-page/form

Module names are mangled by webpack to some short identifier, so the components/form-page/form is unknown at runtime

Ideally I would get webpack to detect this as an async module dependency, but in a first step it would be sufficient to declare the modules I want to have available in the webpack config while preserving the module names, letting ko require its dependencies as it needs it.

Any idea how this could be done?

答:
You can use a custom component loader https://knockoutjs.com/documentation/component-loaders.html

```<code>var myloader =  {
    loadComponent: function (name, componentConfig, callback) {
        if (!componentConfig.import) {
            callback(null);
            return;
        }

        componentConfig.import()
            // Using the defaultLoader so we can support the different component configs knockout supports by default
            .then(config => ko.components.defaultLoader.loadComponent(name, config, callback));
    }
}
</code>```

And add it to the knockout loaders. Using unshift so it's the first loader hit

```<code>ko.components.loaders.unshift(myloader);
</code>```

Now your component registration is changed to

```<code>ko.components.register('form-page', { import: () => import(/* webpackChunkName: "form-page" */'components/form-page/form') });
</code>```

This takes advantage of Webpack's lazy loading. Though be aware that dynamic imports need some additional babel plugins.

The webpackChunkName comment tells webpack what to name the js file it outputs.

Body:
I am currently setting up a knockoutjs project with webpack. One of the main aspects I want to achieve is lazy loading of bundles. While I have been able to achieve code splitting it currently requires the app to specifically contain code for this and I would like to get rid of this specific code as I believe knockoutjs already comes with the necessary infrastructure.

During app setup, components are registered in knockoutjs like this:

ko.components.register('form-page', { require: 'components/form-page/form' });

Knockout will load the referenced module with an async require only of the component form-page is used.

With my webpack setup, this will fail because

Webpack does not detect that there is a dependency on components/form-page/form

Module names are mangled by webpack to some short identifier, so the components/form-page/form is unknown at runtime

Ideally I would get webpack to detect this as an async module dependency, but in a first step it would be sufficient to declare the modules I want to have available in the webpack config while preserving the module names, letting ko require its dependencies as it needs it.

Any idea how this could be done?

Body:
You can use a custom component loader https://knockoutjs.com/documentation/component-loaders.html

var myloader =  {
    loadComponent: function (name, componentConfig, callback) {
        if (!componentConfig.import) {
            callback(null);
            return;
        }

        componentConfig.import()
            // Using the defaultLoader so we can support the different component configs knockout supports by default
            .then(config => ko.components.defaultLoader.loadComponent(name, config, callback));
    }
}

And add it to the knockout loaders. Using unshift so it's the first loader hit

ko.components.loaders.unshift(myloader);

Now your component registration is changed to

ko.components.register('form-page', { import: () => import(/* webpackChunkName: "form-page" */'components/form-page/form') });

This takes advantage of Webpack's lazy loading. Though be aware that dynamic imports need some additional babel plugins.

The webpackChunkName comment tells webpack what to name the js file it outputs.


问:
Visual Studio forms designer
I have an annoying problem. Sometimes Visual Studio does not show option "View designer" for some of my forms in solution explorer. It does not show design mode errors, just doesn't show option to use the designer. I cannot figure out exact cases when this happens.. Does anybody have some ideas about what causes such behavior of solution explorer?</p>

答:
Experienced same issue with Visual Studio 2017

I found the following solution:

(root cause): Added custom class to top of the form1.cs file

(symptom): Design view now missing (right click the form class in solution explorer, "view designer" not shown)

(solution): Move the Form class back to the top of the file. "view designer" now properly showing

Doing the work around recommended by user2205930 might allow you to keep your custom class to the top of form.

ContentLicense: CC BY-SA 2.5

Title: Visual Studio forms designer


问:
purrr accumulate function strange output
I guess this is a combination of two questions.

I'm trying to understand the purrr::accumulate function but having a hard time figuring out the interplay between .x and .y. I've been reading the purrr vignette but it's not explaining much. I don't have a programming background so a lot of it is over my head.

First thing, why do these have different outputs when they are the same call? The only difference is the () after paste.

```<code>accumulate(letters[1:5], paste, sep = ".")
[1] "a"         "a.b"       "a.b.c"     "a.b.c.d"   "a.b.c.d.e"

accumulate(letters[1:5], paste(), sep = ".")
[1] "a" "a" "a" "a" "a"
</code>```

Second, what is going on here?

```<code>2:11 %>% accumulate(~ .x)
[1] 2 2 2 2 2 2 2 2 2 2

accumulate(1:5, `+`)
[1]  1  3  6 10 15

2:11 %>% accumulate(~ .y)
[1]  2  3  4  5  6  7  8  9 10 11

2:11 %>% accumulate(~ .x + .y)
[1]  2  5  9 14 20 27 35 44 54 65
</code>```

.x is the accumulating value, but I guess it's not accumulating anything? It makes sense for 1:5 as cumsum. .y is the element in the list. Am I correct in interpreting .y as essentially print?

But wouldn't the first output of .x + .y be 4?

Some insight would be very welcome.

答:
It would be easier to understand when there are print statements

```<code>2:11 %>%
   accumulate(~ {
     print("---step----")
     print(paste0(".x: ", .x))
     print(paste0(".y: ", .y))
     print(.x + .y)
    })
#[1] "---step----"
#[1] ".x: 2"  # .init or first value of the vector (as `.init` not specified)
#[1] ".y: 3"  # second value
#[1] 5    # sum of .x + .y
#[1] "---step----"
#[1] ".x: 5"   # note .x gets updated with the sum
#[1] ".y: 4"   # .y gets the next element
#[1] 9
#[1] "---step----"
#[1] ".x: 9"   # similarly in all the below steps
#[1] ".y: 5"
#[1] 14
#[1] "---step----"
#[1] ".x: 14"
#[1] ".y: 6"
#[1] 20
#[1] "---step----"
#[1] ".x: 20"
#[1] ".y: 7"
#[1] 27
#[1] "---step----"
#[1] ".x: 27"
#[1] ".y: 8"
#[1] 35
#[1] "---step----"
#[1] ".x: 35"
#[1] ".y: 9"
#[1] 44
#[1] "---step----"
#[1] ".x: 44"
#[1] ".y: 10"
#[1] 54
#[1] "---step----"
#[1] ".x: 54"
#[1] ".y: 11"
#[1] 65
# [1]  2  5  9 14 20 27 35 44 54 65
</code>```

Here, .x is the one getting updated in each iteration and that value is getting passed into .x + .y

It is essentially similar to

```<code>cumsum(2:11)
#[1]  2  5  9 14 20 27 35 44 54 65
</code>```

When we only pass .x, it is the .init value i.e the first element which is not getting updated as the .f is not doing anything

```<code>2:11 %>%
    accumulate(~ print(.x))
#[1] 2
#[1] 2
#[1] 2
#[1] 2
#[1] 2
#[1] 2
#[1] 2
#[1] 2
#[1] 2
# [1] 2 2 2 2 2 2 2 2 2 2
</code>```

Now, we pass .init a different value

```<code>2:11 %>%
    accumulate(~ print(.x), .init = 5)
#[1] 5
#[1] 5
#[1] 5
#[1] 5
#[1] 5
#[1] 5
#[1] 5
#[1] 5
#[1] 5
#[1] 5
#[1] 5 5 5 5 5 5 5 5 5 5 5
</code>```

Also, the difference in the first two calls, is the difference in passing the arguments.  In the first case with paste, .x and .y are implicitly passed, while in second paste(), it is only the .x i.e. going in to it

```<code>accumulate(letters[1:5], ~paste(.x, .y, sep = "."))
#[1] "a"         "a.b"       "a.b.c"     "a.b.c.d"   "a.b.c.d.e"
accumulate(letters[1:5], ~paste(.x,  sep = "."))
#[1] "a" "a" "a" "a" "a"
</code>```

Body:
I guess this is a combination of two questions.

I'm trying to understand the purrr::accumulate function but having a hard time figuring out the interplay between .x and .y. I've been reading the purrr vignette but it's not explaining much. I don't have a programming background so a lot of it is over my head.

First thing, why do these have different outputs when they are the same call? The only difference is the () after paste.

accumulate(letters[1:5], paste, sep = ".")
[1] "a"         "a.b"       "a.b.c"     "a.b.c.d"   "a.b.c.d.e"

accumulate(letters[1:5], paste(), sep = ".")
[1] "a" "a" "a" "a" "a"

Second, what is going on here?

2:11 %>% accumulate(~ .x)
[1] 2 2 2 2 2 2 2 2 2 2

accumulate(1:5, `+`)
[1]  1  3  6 10 15

2:11 %>% accumulate(~ .y)
[1]  2  3  4  5  6  7  8  9 10 11

2:11 %>% accumulate(~ .x + .y)
[1]  2  5  9 14 20 27 35 44 54 65

.x is the accumulating value, but I guess it's not accumulating anything? It makes sense for 1:5 as cumsum. .y is the element in the list. Am I correct in interpreting .y as essentially print?

But wouldn't the first output of .x + .y be 4?

Some insight would be very welcome.

Body:
It would be easier to understand when there are print statements

2:11 %>%
   accumulate(~ {
     print("---step----")
     print(paste0(".x: ", .x))
     print(paste0(".y: ", .y))
     print(.x + .y)
    })
#[1] "---step----"
#[1] ".x: 2"  # .init or first value of the vector (as `.init` not specified)
#[1] ".y: 3"  # second value
#[1] 5    # sum of .x + .y
#[1] "---step----"
#[1] ".x: 5"   # note .x gets updated with the sum
#[1] ".y: 4"   # .y gets the next element
#[1] 9
#[1] "---step----"
#[1] ".x: 9"   # similarly in all the below steps
#[1] ".y: 5"
#[1] 14
#[1] "---step----"
#[1] ".x: 14"
#[1] ".y: 6"
#[1] 20
#[1] "---step----"
#[1] ".x: 20"
#[1] ".y: 7"
#[1] 27
#[1] "---step----"
#[1] ".x: 27"
#[1] ".y: 8"
#[1] 35
#[1] "---step----"
#[1] ".x: 35"
#[1] ".y: 9"
#[1] 44
#[1] "---step----"
#[1] ".x: 44"
#[1] ".y: 10"
#[1] 54
#[1] "---step----"
#[1] ".x: 54"
#[1] ".y: 11"
#[1] 65
# [1]  2  5  9 14 20 27 35 44 54 65

Here, .x is the one getting updated in each iteration and that value is getting passed into .x + .y

It is essentially similar to

cumsum(2:11)
#[1]  2  5  9 14 20 27 35 44 54 65

When we only pass .x, it is the .init value i.e the first element which is not getting updated as the .f is not doing anything

2:11 %>%
    accumulate(~ print(.x))
#[1] 2
#[1] 2
#[1] 2
#[1] 2
#[1] 2
#[1] 2
#[1] 2
#[1] 2
#[1] 2
# [1] 2 2 2 2 2 2 2 2 2 2

Now, we pass .init a different value

2:11 %>%
    accumulate(~ print(.x), .init = 5)
#[1] 5
#[1] 5
#[1] 5
#[1] 5
#[1] 5
#[1] 5
#[1] 5
#[1] 5
#[1] 5
#[1] 5
#[1] 5 5 5 5 5 5 5 5 5 5 5

Also, the difference in the first two calls, is the difference in passing the arguments.  In the first case with paste, .x and .y are implicitly passed, while in second paste(), it is only the .x i.e. going in to it

accumulate(letters[1:5], ~paste(.x, .y, sep = "."))
#[1] "a"         "a.b"       "a.b.c"     "a.b.c.d"   "a.b.c.d.e"
accumulate(letters[1:5], ~paste(.x,  sep = "."))
#[1] "a" "a" "a" "a" "a"


问:
How to fix bad_typeid exception
I was asked to create a simple program. See below:

"1. Write a simple program that uses recursion to compute the factorial of a number – make sure to throw an exception if the user enters a negative integer or a non integer data type from which to compute the factorial."

I have created the program to compute the factorial of a number into a while block, allowing the user to have unlimited attempts. My try-catch block works well for the negative exception, however, the non-integer exception is not working.

```<code>int Factorial(int input)
{
    if (input > 1)
        return input * Factorial(input - 1);
    else if (input < 0)
        throw NegativeException(input);
    else
        return 1;
}

int main()
{
    using std::cout;
    using std::cin;
    int input;

    while (1)
    {
        try
        {
            cout << "Please enter an integer to find its factorial: ";
            cin >> input;
            cout << "The factorial of " << input << " is " << Factorial(input) << endl;
        }
        catch (const std::bad_typeid & ex)
        {
            cout << "Exception: " << ex.what();
            cout << "Try another integer\n" << endl;
        }
        catch (NegativeException& ex1)
        {
            cout << ex1.what();
            cout << "Try another integer\n" << endl;
        }
    }

    return 0;
}
</code>```

When I enter a letter, I expect the output to state "Exception: bad_typeid Try another integer". However, when I enter a letter instead of an int, the bad_typeid is not caught. It seems to return 0 and outputs "The factorial of 0 is 1". The while(1) block is then triggered and creates a never ending output of the above message.

I appreciate your time and assistance.

答:
bad_typeid doesn't work like this. It is thrown when a typeid is applied to a dereferenced null pointer. Completely irrelevant in this case.

If, as an example, the user entered

```<code>5.5
</code>```

then std::cin >> input would attempt to input an integer (5 in this case). If the user entered a letter like

```<code>foo
</code>```

then std::cin >> input would set failbit and not throw an exception.

Title: How to fix bad_typeid exception

Body:
I was asked to create a simple program. See below:

"1. Write a simple program that uses recursion to compute the factorial of a number – make sure to throw an exception if the user enters a negative integer or a non integer data type from which to compute the factorial."

I have created the program to compute the factorial of a number into a while block, allowing the user to have unlimited attempts. My try-catch block works well for the negative exception, however, the non-integer exception is not working.

int Factorial(int input)
{
    if (input > 1)
        return input * Factorial(input - 1);
    else if (input < 0)
        throw NegativeException(input);
    else
        return 1;
}

int main()
{
    using std::cout;
    using std::cin;
    int input;

    while (1)
    {
        try
        {
            cout << "Please enter an integer to find its factorial: ";
            cin >> input;
            cout << "The factorial of " << input << " is " << Factorial(input) << endl;
        }
        catch (const std::bad_typeid & ex)
        {
            cout << "Exception: " << ex.what();
            cout << "Try another integer\n" << endl;
        }
        catch (NegativeException& ex1)
        {
            cout << ex1.what();
            cout << "Try another integer\n" << endl;
        }
    }

    return 0;
}

When I enter a letter, I expect the output to state "Exception: bad_typeid Try another integer". However, when I enter a letter instead of an int, the bad_typeid is not caught. It seems to return 0 and outputs "The factorial of 0 is 1". The while(1) block is then triggered and creates a never ending output of the above message.

I appreciate your time and assistance.

Body:
bad_typeid doesn't work like this. It is thrown when a typeid is applied to a dereferenced null pointer. Completely irrelevant in this case.

If, as an example, the user entered

5.5

then std::cin >> input would attempt to input an integer (5 in this case). If the user entered a letter like

foo

then std::cin >> input would set failbit and not throw an exception.


答:
You are comparing two Strings.
Strings are immutable.
Please use "equalsIgnoreCase()" or "equals()" to compare Strings

See examples here
http://www.java-samples.com/showtutorial.php?tutorialid=224

Title: Using an IF statement to control the return statement?


问:
Finding probability of unsafe lift simulation

I can find this using Excel but I'd like to know how to do it using Python.

Each column under Person is randomly generated with normal distribution of a standard deviation 15 and mean of 80.

Number of people is randomly generated between 1 to 6.

Total weight is the sum of person with the number of people. e.g. the first one is the sum of first 4 person.

Unsafe is a boolean. If the total weight is more than 500 then 1 otherwise 0.

Trials is 3997. The sum of unsafe is 148. Then the probability is calculated by number over weight divided by trials.

I can create a normal distribution using numpy. I create a random number with 80 mean and 15 sd and a random number between 0 and 7 (1 to 6).

```<code>import numpy as np
from decimal import *
total_num = 0
i=1
trial = 100
while i < trial:
    rand_num =np.random.randint(1,7)
    person = np.random.normal(80, 15)
    total_weight = int(rand_num)*Decimal(person)
    # print(total_weight)
    if total_weight > int(500):
        total_num += total_num
    i += 1;
total_num/trial
</code>```

But I am getting 0 in my output.
What am I doing wrong here?

答:
I just needed to modify total_num += 1.

```<code>import numpy as np

count = 0
i=int(1)
trial = int(10000)
limit = int(500)
while i < trial:
    rand_num =np.random.randint(1,7)
    person = np.random.normal(80, 15)
    total_weight = int(rand_num)*person
    # print(total_weight)
    if total_weight > limit:
        #print('yes')
        count += 1
        count
    i += 1;
print("Probability is: ")
print(count/trial)
</code>```

Body:
I can find this using Excel but I'd like to know how to do it using Python.

Each column under Person is randomly generated with normal distribution of a standard deviation 15 and mean of 80.

Number of people is randomly generated between 1 to 6.

Total weight is the sum of person with the number of people. e.g. the first one is the sum of first 4 person.

Unsafe is a boolean. If the total weight is more than 500 then 1 otherwise 0.

Trials is 3997. The sum of unsafe is 148. Then the probability is calculated by number over weight divided by trials.

I can create a normal distribution using numpy. I create a random number with 80 mean and 15 sd and a random number between 0 and 7 (1 to 6).

import numpy as np
from decimal import *
total_num = 0
i=1
trial = 100
while i < trial:
    rand_num =np.random.randint(1,7)
    person = np.random.normal(80, 15)
    total_weight = int(rand_num)*Decimal(person)
    # print(total_weight)
    if total_weight > int(500):
        total_num += total_num
    i += 1;
total_num/trial

But I am getting 0 in my output.
What am I doing wrong here?

Body:
I just needed to modify total_num += 1.

import numpy as np

count = 0
i=int(1)
trial = int(10000)
limit = int(500)
while i < trial:
    rand_num =np.random.randint(1,7)
    person = np.random.normal(80, 15)
    total_weight = int(rand_num)*person
    # print(total_weight)
    if total_weight > limit:
        #print('yes')
        count += 1
        count
    i += 1;
print("Probability is: ")
print(count/trial)