(Cat? OR feline) AND NOT dog?
Cat? W/5 behavior
(Cat? OR feline) AND traits
Cat AND charact*

This guide provides a more detailed description of the syntax that is supported along with examples.

This search box also supports the look-up of an IP.com Digital Signature (also referred to as Fingerprint); enter the 72-, 48-, or 32-character code to retrieve details of the associated file or submission.

Concept Search - What can I type?

For a concept search, you can enter phrases, sentences, or full paragraphs in English. For example, copy and paste the abstract of a patent application or paragraphs from an article.

Concept search eliminates the need for complex Boolean syntax to inform retrieval. Our Semantic Gist engine uses advanced cognitive semantic analysis to extract the meaning of data. This reduces the chances of missing valuable information, that may result from traditional keyword searching.

Method of detecting the most expensive statements within a stored procedure execution in database system/data warehousing system

Publishing Venue

The IP.com Prior Art Database

Abstract

When executing a stored procedure, one can see an overall execution time. There is, however, crucial to understand which nested statements introduce most of the overhead, i.e. which are the most expensive. The idea is to combine information from database/data warehouse system catalog, monitoring views and log files to detect stored procedure executions and list nested statements executions, sorted by its expensiveness.

Country

Undisclosed

Language

English (United States)

This text was extracted from a PDF file.

This is the abbreviated version, containing approximately
15% of the total text.

Page 01 of 5

Method of detecting the most expensive statements within a stored procedure execution in database system/data warehousing system

Before calling a stored procedure in a database system or a data warehousing system, the prerequisite is, obviously, to have one. Create [or replace] command creates a SP, i.e. it also inserts information about a stored procedure into a system catalog. This information, including the procedure body, can be extracted with show procedure

verbose command. In order to run a stored procedure, one need to use Call, Exec, Execute, Execute Procedure or just Select command followed by the procedure name and arguments.

Once a procedure is called, a corresponding entry is written into postgres log file: /nz/kit/log/postgres/pg.log, preceded by PID identifier. The log file contains also nested statements and they are prefixed with "STORED PROCEDURE EXEC:". That PID can be found also in dbos log file: /nz/kit/log/dbos/dbos.log, adding also information about session id (sid), user id (uid), and what's most important, identifiers of nested statement executions (identified by planid). This set of identifiers is what should be a filter for 2 Netezza monitoring views: _v_qryhist and _v_plan_resources. The first one provides a collection of recently executed plans, including information like statement text, database, user, start and end timestamps (i.e. execution time as well), number of snippets, resulting number of rows and bytes, estimates of cost, disk and memory. The second view is filled with data once a plan is finished and a set of attributes contains metrics about CPU, Fabric, Disk Read and Write on Host and SPU (avg and max), metrics related to SQB, GRA and more. All of that can be used to define query expensiveness, starting with as simple definition as elapse_time (_v_qryhist.TEND - _v_qryhist.TSUBMIT).