Forum Stats

  • 3,758,216 Users
  • 2,251,355 Discussions
  • 7,870,114 Comments

Discussions

Performance issues with a job, SQL ID in V$session not matching the query

Hello all,

First post here, so apologies if I step on something wrong. I know the heading sounds vague but I will try to explain my problem in detail -

I have a particular ETL job that triggers 4 queries to a single view definition at once to my Oracle database. The queries are in essence as below -

select * from view_name where mod(id) = 0;

select * from view_name where mod(id) = 1;

select * from view_name where mod(id) = 2;

select * from view_name where mod(id) = 3;

Now the performance of these queries has been fluctuating wildly as in sometimes they complete within an hour and sometimes they go north of a dozen hours. After doing the basics of gather stats and adding indices on the base tables of the view definition, I checked gv$session during the job was executing and was surprised to see that all 4 sessions were showing the same sql_id. Ideally I would have assumed that the sql_ids would be different since the query itself is different.

I then checked gv$sqltext for this particular sql_id and got a query that I am not familiar with. As in it does not seem directly related to my view definition or any of the objects used in the view definition.

So I am stumped as to 1) why this single sql_id is popping up for all my 4 different queries

2) How do I proceed with checking the performance issues in these cases since as per gv$session it's a different sql text that is actually executing.

Any help is appreciated, thank you!

Answers

  • evgenyg
    evgenyg Member Posts: 337 Bronze Badge

    Hi

    I would start by checking the execution plan for the queries.

    I am not sure why you gathered statistics and/or added indexes as well as why to check sql_id.

    There is a basic sequence of steps to check performance issues.

    Start with understanding what's going on, exe. plan, wait events etc. Only when you understand what's going on start to see how to resolve it.

    Regards

    Evgeny

  • GregV
    GregV Member Posts: 3,062 Gold Crown

    Hi,

    Did you have a look at the execution plan? Since you're using a function on the column in your query I guess a full scan is performed, unless you created a function-based index?

    It would nice to see the view's uderlying query, and the execution plan.

  • mtefft
    mtefft Member Posts: 836 Gold Badge

    Are you sure the queries are really

    select * from view_name where mod(id) = 0;

    select * from view_name where mod(id) = 1;

    select * from view_name where mod(id) = 2;

    select * from view_name where mod(id) = 3;

    and not

    select * from view_name where mod(id) = :bindvariable;

    If your queries are really being submitted with literals 0,1,2,3 then check parameter cursor_sharing. If it is SIMILAR or FORCE then that can cause the effect you are seeing.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,729 Black Diamond
    1. MOD requires two arguments.
    2. As others noted it is possible that ETL process uses bind variables.
    3. If that ETL process is indeed using literals check cursor sharing. It might be set (on system or session level) to force. Then oracle under the hood will replace literals with bind variables and all 4 SQL statements will be transformed to select * from view_name where mod(id) = :b1 and therefore will have same sql id.

    SY.

  • User_GSQTY
    User_GSQTY Member Posts: 53 Bronze Badge

    I then checked gv$sqltext for this particular sql_id and got a query that I am not familiar with

    It could mean that your ETL job executes additional querie(s) that is other than "select * from view_name where mod(id)..."