Really "static" SQL based execution

SQL Optimizer SQL Optimizer for Oracle
1

Hi,

Whenever you choose execution type (dynamic or static) on a lower value it is basically “dynamic”.

Because it generates hint by which you lately make some statistic analyses for each execution. Here is brief case of mine words (taken while SQL Optimizer was executing):

SQL>@sql_id 1 72w638vps8zs1

SQL_FULLTEXT

select /+ Set 40_00000000624533 / NVL(TOTAL_POINTS_BALANCE, 0)
from CLUB_BILL_SUBSCRIBER
where MEMBER_ID = :b1
and CLUB_CODE = :b2
and BAN = :b3
and SUBSCRIBER_NO = :b4
and BEN = :b5
and BILL_SEQ_NO = (select /+ USE_MERGE(B,CBS) */ max(CBS.BILL_SEQ_NO)
from CLUB_BILL_SUBSCRIBER CBS,
BILL B
where CBS.MEMBER_ID = :b1
and CBS.CLUB_CODE = :b2
and CBS.BAN = :b3
and CBS.SUBSCRIBER_NO = :b4
and CBS.BEN = :b5
and CBS.BAN = B.BAN
and CBS.BILL_SEQ_NO = B.BILL_SEQ_NO
and CBS.BEN = B.BEN
and B.CLUB_CALCULATION_STATUS = ‘S’)

Current sessions run that sql

no rows selected

gv$sql data …

IID CH PLAN_HASH_V EXECS TIME [s] ROWS AVG TIME [s] AVG ROWS AVG LIO AVG CPU [s] LAST_LOAD LAST_ACTIVE SH SQL_PROFILE BA BS

1 0 2570093969 1 0.96700 1 0.96656 1.000 258.00000 0.0030000 06-23/14:59:19 06-23/14:59:20 Y N N

@sql_id [inst_id] [sql_id]
SQL>@sql_id 1 7pfurkfvyytrj

SQL_FULLTEXT

select /+ Set 43_00000000624610 / NVL(TOTAL_POINTS_BALANCE, 0)
from CLUB_BILL_SUBSCRIBER
where MEMBER_ID = :b1
and CLUB_CODE = :b2
and BAN = :b3
and SUBSCRIBER_NO = :b4
and BEN = :b5
and BILL_SEQ_NO = (select /+ USE_MERGE(B,CBS) */ max(CBS.BILL_SEQ_NO)
from CLUB_BILL_SUBSCRIBER CBS,
BILL B
where CBS.MEMBER_ID = :b1
and CBS.CLUB_CODE = :b2
and CBS.BAN = :b3
and CBS.SUBSCRIBER_NO = :b4
and CBS.BEN = :b5
and CBS.BAN = B.BAN
and CBS.BILL_SEQ_NO = B.BILL_SEQ_NO
and CBS.BEN = B.BEN
and B.CLUB_CALCULATION_STATUS = ‘S’)

Current sessions run that sql

no rows selected

gv$sql data …

IID CH PLAN_HASH_V EXECS TIME [s] ROWS AVG TIME [s] AVG ROWS AVG LIO AVG CPU [s] LAST_LOAD LAST_ACTIVE SH SQL_PROFILE BA BS

1 0 2570093969 1 1.56600 1 1.56574 1.000 360.00000 0.0040000 06-23/14:59:26 06-23/14:59:27 Y N N

@sql_id [inst_id] [sql_id]
SQL>

Mine question is to introduce “full static on server” execution type, which could be used ONLY in bind sets execution. This would mean to leave query as it is and just change binds sets in execution and make one sql calculation

Why?

In this case 100 of executions will have the one entry in gv$sql, this would be easy to find in ASH, AWR or parallel monitoring and on the end will be really summary for binds set use-like it is in real life-one sql_id for all executions.

Once again, stat for such a query would be only possible in binds set usage, will not have stat for each execution but for summary only.

Is that possible?

Thx

Damir

2

Hi Damir,

Sorry that I don’t follow the question here. Would you please explain your question in more details?

You mentioned the “static” and “dynamic” options. Note that when we test run a SQL, we construct a PL/SQL script to include the SQL to test run and measure its time. The way we embed the SQL into the PL/SQL script is different depending on the “static” and “dynamic” settings. I would help me better understand the issue if you can explain the meaning of “… on a lower value it is basically “dynamic””.

Thanks,

Alex

3

on a lower value it is basically "dynamic"
This should be bottom line it is basically dynamic. It is always dynamic

What I want to say is that you should introduce new option for running sql binds set which will not not change sql_id but have all test under one, songle sql_id and then later have only one stat result grid.

This is now not possible because you want to measure single run and you are adding your own hint which is changing sql_id, and this is not what a real life is...making harder to analyse impact on ASH, AWR, where all your single small execution are not recorded and in mine case it will be nicely recorded.

Hop now is more clear.

4

Hi Damir,

Thank you for explaining in more details.

It is actually intentional to add a different sql id everytime to make the SQL different in each bind set execution. The reason behind this is really to avoid Oracle from reusing the same plan. We want to be able to measure the performance of a SQL under a particular bind value set without dependency of what other bind value set was executed before. In other words, we want Oracle to “freely” (i.e. not reusing an existing plan) choose the best plan according to the current bind value set so the comparison between different alternatives will be fair and not affected by the execution order of the bind value sets.

Thanks,
Alex

5

Hi Alex,

Thx for your answer but I do know all that what you have said.
:slight_smile:

Mine proposal is to have real oracle based test-like it will be in real production-sherable plan to see how it reacts.

In your way I am not able to see that in any way!

And this is mine intention - to have one additional way in bind sets only-one that will not change sql_id.

Please take that very seriously as well what are benefits of that way in mine previous explanation.

Damir

6

My final:

Give us an option to test query with binds sets exactly as they will be execute tomorrow in real life-one sql_id.

Brg,
Damir

7

Hi Damir,

Thank you for the suggestion as always. I will create this request for future consideration.

Thanks,
Alex

8

Hi Alex,

from all mine suggestions, this one should have highest priority because it allows to test in real circumstances. All other options are really far from real life.
Please put some “importance” on this … please.
Brg

Damir

9

Noted and will include your emphasis of importance in the change request.

Thanks again,
Alex