CODEWITHSUNDEEP
oracle-databaseplsqlsqlplusplsqldeveloper
hc0re
hc0re

Reputation: 1986

Fetching SQL execution plan. PL/SQL Developer = strange behaviour. SQL*Plus = no rows selected

I have a problem with fetching SQL plans.

In the final form, I have to fetch them through PL*SQL.

So I have a bash script, a loop to iterate through sql plan names and parameters, and a code like this:

sqlplus -s /nolog > /dev/null 2>&1  <<EOF
CONNECT BLAH/BLAH@BLAH

*CLEAR AND SET COMMANDS HERE*

VARIABLE RC REFCURSOR;
SPOOL ${BERICHT}_${configArray[0]}.DATA
SET TIMING ON;
EXEC :RC := $aktuellesBericht;
SET TIMING OFF;
PRINT RC;
DISCONNECT
QUIT
EOF

And the second part: (logging in as SYS, but without the SYSDBA permissions, I dont have them and I dont think that I will have them...)

sqlplus -s /nolog > /dev/null 2>&1  <<EOF
CONNECT SYSBLAH/SYSBLAH@BLAH
SPOOL ${BERICHT}_${configArray[0]}.SQLPLAN.TXT
CLEAR BREAK
CLEAR COMP
CLEAR COL
select 
        sqlplan.operation, 
        sqlplan.options, 
        sqlplan.object_name, 
        sqlplan.cost, 
        sqlplan.depth   
  from v\$sqlarea sqlarea, 
       v\$session sesion, 
       v\$sql_plan sqlplan
where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sqlarea.plan_hash_value = sqlplan.plan_hash_value
   and sesion.username = 'BLAH' order by sqlplan.depth;
QUIT

EOF

All I can get from this is *.SQLPLAN.TXT files containing just one sentence: no rows selected

What is strange here, that when I do the same in PL/SQL Developer - i get the same results, BUT when I just click on the Auto Refresh timer button on both SQL windows, both queries are running in parallel, and SOMETIMES the second query (the one to fetch SQL plan) is giving me results. And sometimes it doesnt.

It seems that theese commands need to be run in parallel... or am I missing something?

Upvotes: 1

Views: 625

Answers (1)

hc0re
hc0re

Reputation: 1986

The sql query in my question was never needed.

All I needed is to write to the admin team to grant me the SELECT premissions on:

V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL

The answer to my question was found here.

I will need a deeper understanding of the problem in the future but for now, FWIK, looking deeper into the dbms_xplan package gave me the idea.

The approach to get the queries from the SYSACC account was the wrong idea. This acc has access to all the queries history and it is hard and painful (if even possible) to husk out the sql plan I needed.

Upvotes: 2

Related Questions