• Node name
  • User information
  • Session, transaction, and statement IDs
  • Plan information
  • Operator name
  • Counter name
  • Counter value

    • Counters change from one operator to another.

    Do: Update System Config (If needed)

    You might want to change some system parameters to improve performance. Do this with caution.

    Don’t: Underestimate Data Extraction

    If your query returns a large result set, moving data to the client can take a lot of time. Redirecting client output to /dev/null still implies moving data to the client. Consider instead storing the result set in a LOCAL TEMPORARY TABLE.

    Useful Queries

    The following query checks the data distribution for a given table. This is often useful to look into a plan when no statistics are available:
    select
    projection_name, node_name, sum(row_count) as row_count, sum(used_bytes) as used_bytes, sum(wos_row_count) as wos_row_count, sum(wos_used_bytes) as wos_used_bytes, sum(ros_row_count) as ros_row_count, sum(ros_used_bytes) as ros_used_bytes, sum(ros_count) as ros_count
    from
    projection_storage
    where
    anchor_table_schema = :schema and
    anchor_table_name = :table

    group by 1, 2
    order by 1, 2;

    The following query shows the non-default configuration parameters:
    SELECT
    parameter_name, current_value, default_value, description
    FROM v_monitor.configuration_parameters
    WHERE current_value <> default_value
    ORDER BY parameter_name;

    The following query checks encoding and compression for a given table:
    SELECT cs.projection_name, cs.column_name, sum(cs.row_count) as row_count, sum(cs.used_bytes) as used_bytes, max(pc.encoding_type) as encoding_type, max(cs.encodings) as encodings, max(cs.compressions) as compressions
    FROM
    column_storage cs
    inner join projection_columns pc
    on cs.column_id = pc.column_id
    WHERE
    anchor_table_schema = :schema and
    anchor_table_name = :table
    GROUP BY 1, 2
    ORDER BY 1, 2;

    The following will retrieve the EXPLAIN PLAN for a given query:
    SELECT
    path_line
    FROM v_internal.dc_explain_plans
    WHERE
    transaction_id=:trxid and
    statement_id=:stmtid
    ORDER BY
    path_id, path_line_index;

    The following shows the resource acquisition for a given query:
    SELECT
    a.node_name, a.queue_entry_timestamp, a.acquisition_timestamp,
    ( a.acquisition_timestamp - a.queue_entry_timestamp ) AS queue_wait_time, a.pool_name, a.memory_inuse_kb as mem_kb, (b.reserved_extra_memory_b/1000)::integer as emem_kb, (a.memory_inuse_kb-b.reserved_extra_memory_b/1000)::integer AS rmem_kb, a.open_file_handle_count as fhc, a.thread_count as threads
    FROM
    v_monitor.resource_acquisitions a
    inner join query_profiles b
    on a.transaction_id = b.transaction_id
    WHERE
    a.transaction_id=:trxid and
    a.statement_id=:stmtid
    ORDER BY 1, 2;

    The following gives query events for a given query:
    SELECT event_timestamp, node_name, event_category, event_type, event_description, operator_name, path_id, event_details, suggested_action
    FROM v_monitor.query_events
    WHERE
    transaction_id=:trxid and
    statement_id=:stmtid
    ORDER BY 1;

    The following query shows transaction locks:
    SELECT node_name,(time - start_time) as lock_wait, object_name, scope, result,description
    FROM v_internal.dc_lock_attempts
    WHERE
    transaction_id = :trxid
    ;

    The following query shows threads by profile operator:
    SELECT node_name, path_id, operator_name, activity_id::varchar || ',' || baseplan_id::varchar || ',' || localplan_id::varchar as abl_id, count(distinct(operator_id)) as '#Threads'
    FROM v_monitor.execution_engine_profiles
    WHERE
    transaction_id=:trxid and
    statement_id=:stmtid
    GROUP BY 1,2,3,4
    ORDER BY 1,2,3,4;

    The following query shows how you can retrieve the query execution report:
    SELECT node_name , operator_name, path_id, round(sum(case counter_name when 'execution time (us)' then counter_value else null end)/1000,3.0) as exec_time_ms,
    sum(case counter_name when 'estimated rows produced' then counter_value else null end ) as est_rows,
    sum ( case counter_name when 'rows processed' then counter_value else null end ) as proc_rows,
    sum ( case counter_name when 'rows produced' then counter_value else null end ) as prod_rows,
    sum ( case counter_name when 'rle rows produced' then counter_value else null end ) as rle_pr_rows,
    sum ( case counter_name when 'consumer stall (us)' then counter_value else null end ) as cstall_us,
    sum ( case counter_name when 'producer stall (us)' then counter_value else null end ) as pstall_us,
    round(sum(case counter_name when 'memory reserved (bytes)' then
    counter_value else null end)/1000000,1.0) as mem_res_mb,
    round(sum(case counter_name when 'memory allocated (bytes)' then
    counter_value else null end )/1000000,1.0) as mem_all_mb
    FROM v_monitor.execution_engine_profiles
    WHERE transaction_id = :trxid and statement_id = :stmtid and counter_value/1000000 > 0
    GROUP BY 1, 2, 3
    ORDER BY
    case when sum(case counter_name when 'execution time (us)' then counter_value else null end) is null then 1 else 0 end asc , 5 desc ;

    OpenText

    , ,
    , , ,

  • From identity provisioning to managing IoT ecosystems

    From identity provisioning to managing IoT ecosystems

    More and more enterprises have begun their journey towards digital transformation. They are creating entirely new types of digital ecosystems that include people, applications, systems and things – both inside and outside the organization. This is an exciting new world. At its heart lies a new generation of identity management technologies and mindsets.

    The pace at which organizations are embracing digital transformation is startling. The digital transformation market is estimated to reach $798.44 billion by 2025 – up from $177.27 billion in 2016. That’s an increase of over 450%. Recent research from OpenText™ into the UK Financial Services sector found that over 60% of companies were either about to or had already deployed digital transformation programs.

    The identity challenge

    Digital transformation represents a massive change in the way that companies operate and conduct business. In its report “2017: A ‘transformative’ year”, AIIM states that digital transformation means re-inventing the business “from the outside in” where customer, employee and partner experiences need to be central to digital transformation initiatives. The trade body suggests: “A new generation of customers and partners, too – requires a dramatically different approach to engagement, specifically one that is personalized, immediate, expressive, and immersive.”

    That sounds fantastic, but the stumbling block is obvious. To take advantage of the opportunities of digital transformation, you need to provide access to your digital ecosystem with the assurance that everyone is who they say they are and that they have the right access to information only when they should.

    Effective identity management becomes the key enabler for successful digital transformation. However, previous approaches to identity management have primarily delivered on helping the IT Help Desk, which is an inside-out approach to identity and access management (IAM). This traditional method applies a trade-off between application security and user convenience that cannot deliver the types of experience that AIIM suggests are necessary.

    The digital ecosystem – comprised of employees, customers, suppliers, partners and other stakeholders – involve too many applications and systems that are often not in the direct control of your IT department. On top of this, disruptive technologies such as IoT are adding new “things” to the ecosystem whose identity has to be provisioned and managed as well.

    Identity management: Responding to the challenge

    While reading a recent research report, I came across a recommendation from Gartner about how organizations should respond to the identity challenge in digital transformation: “Emphasize the benefits of risk-taking to Identity and Access Management innovators”. I’m not sure how many IT security professionals would be happy to take this approach – risk mitigation always seems better than risk taking for sensitive corporate data – and I’m not sure it’s necessary.

    Certainly, the business-to-employee (B2E) approach to identity management is fine if we limit ourselves to only employees and the systems and cloud applications that they need to connect to. I’ve written a previous blog about the need of an ‘outside-in’ model for identity. It requires a collaborative approach to delivering identity assurance – the trust that people are who they say they say are – based around a new generation and mindset of identity management. Such a platform enables you to manage the entire lifecycle of internal and external users as well as their access to all resources across your extended enterprise.

    OpenText IAM

    These platforms – like OpenText™ Core Secure Access – deliver a host of intelligent features, including digital identity management, authentication management, identity event streaming and identity analytics. You have the ability to create a single, central identity for everyone and every thing that can be synchronized across devices, applications, systems and resources. This increases convenience for the user while facilitating information governance and compliance.

    Identity of Things IIoT

    As importantly in the hyper-connected world of digital transformation, the platform goes beyond the establishment of trusted interaction between users and organizations within your digital ecosystem. It enables the secure interoperability of the different systems and things. You have an end-to-end identity infrastructure that manages access, relationships and lifecycle for every element of your digital ecosystem.

    5 key capabilities of an identity management platform

    These platforms are available today. In the case of OpenText™ Covisint, it’s the platform at the core of GM OnStar serving over 12 million people everyday. Key capabilities for an identity management platform include:

    1. Identity provisioning

    Centralizing the process of establishing digital identities for every actor on the digital ecosystem and assigning rights reduces administration and speeds up the onboarding of new users, organizations systems and devices. The most important factor in identity provisioning is the ability to move away from identity silos where each system or application has its own rights. This also make de-provisioning quicker and more effective by only having to deactivate one identity to ensure all access rights are revoked.

    2. Authentication management

    While single sign on (SSO) remains an important tool in identity management, it is no longer sufficient to meet the needs of a digital ecosystem. The vast majority of data breaches in 2017 were the result of credential based cyber attacks. The platform should be able to deliver multi-factor authentication as well as support emerging authentication technologies such as biometrics. The most advanced platform allows for adaptive and risk-based authentication as well as real-time provisioning.

    3. Identity federation

    Identity federation allows multiple organizations to provide access to users across systems and enterprises using the same identification data. The platform manages identity federation establishing a trust relationship between different parties in the ecosystem. As digital transformation progresses, identity federation capabilities grow increasingly important to establish secure and dynamic connections between people, systems, things and services.

    4. Identity governance

    It’s essential that the identity management platform you select contains integrated identity governance capabilities. It should include features such as user administration, privileged identity management, identity intelligence, role-based identity administration and analytics. You must be able to define, enforce, review and audit identity management policies and map your identity function to regulatory compliance requirements and records retention policies.

    5. IDaaS deployment

    As companies move more services to the Cloud, Identity as a Service (IDaaS) is becoming more attractive by delivering highly secure and scalable identity management services that let organizations concentrate on developing the benefits of digital transformation in a constantly evolving digital ecosystem. Recent research showed that 57% of respondents used IDaaS for single sign on and employee portals, while one third used the approach for mobility management and multi-factor authentication.

    With the new generation of identity management platforms, companies can choose to outsource their entire identity management capabilities to a trusted third-party service provider. With OpenText Core Secure Access, you can select on-premises, Cloud or hybrid Cloud deployment to suit your business and security requirements.

    If you’d like know more about how identity management underpins digital transformation, it’s a key topic at Enterprise World in Toronto in July. For a personalized and private meeting, please contact us through the website or email me directly.

    OpenText

    , , , , , ,
    , , , , , , ,

    • Stay in the loop!

    Get our most popular content delivered monthly to your inbox.

    How can we help?