CODEWITHSUNDEEP
oracle-databaseoracle-sqldevelopersql-execution-plan
user13353974
user13353974

Reputation: 145

How to collect more statistics in Oracle?

I'm new to Oracle and would like to know the way how to get more statistics in the case i'll describe below. I'm using SQL Developer.

First, I execute this:

SET autotrace on;

SELECT NAME
FROM PASSENGER
WHERE ID_PSG IN (
    SELECT ID_PSG
    FROM PASS_IN_TRIP PIT JOIN TRIP T on T.TRIP_NO = PIT.TRIP_NO
    WHERE UPPER(TOWN_FROM) = 'MOSCOW'
    )

In Script Output I can see info about the query from PLAN_TABLE table and after that there's the text:

Unable to gather statistics please ensure user has correct access. The statistic feature requires that the user is granted select on v_$sesstat, v_$statname and v_$session.

I've tried to find a solution here already, there's a link: SQL Developer : Unable to gather system statistics : insufficient privileges So I executed the same commands and the grants were all succeded:

GRANT CREATE session TO PRACTICE;
GRANT GATHER_SYSTEM_STATISTICS TO PRACTICE; 
GRANT CONNECT TO PRACTICE;

Then I disconnected, closed SQL dev, opened it, connected again as it was described in the solution from the link, ran

execute dbms_stats.gather_system_stats ('START');

and got this:

PL/SQL procedure successfully completed.

Then I thought everything is fine and tried to execute the code from the very beginning and its Script Output was still the same as before. Do I have to grant anything else or this statistics can be found in the other place or I just did everything wrong?

Upvotes: 1

Views: 632

Answers (1)

Andrew Sayer
Andrew Sayer

Reputation: 2336

That error is about sqlplus autotrace, it's not about gathering system statistics like your linked post seems to be about.

There is a role created specifically for these grants called plustrace https://docs.oracle.com/database/121/SQPUG/ch_eight.htm#SQPUG535

To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you. DBA privileges are required to grant the PLUSTRACE role. For information on how to grant a role and how to create the PLAN_TABLE table, see the Oracle Database SQL Language Reference.

Upvotes: 2

Related Questions