Databases Reference
In-Depth Information
19. Query for
SPID
to identify the generated trace file:
SELECT s.sid, s.serial#, s.process, p.spid
FROM v$session s, v$process p
WHERE s.audsid = userenv('sessionid')
AND s.paddr = p.addr;
20. Use
TKPROF
to format the trace file generated earlier in a text file:
HOST
cd /u01/app/diag/rdbms/testdb/TESTDB/trace/
tkprof TESTDB_ora_2324.trc 2324.txt
21. Show the
TKPROF
report:
vi 2324.txt
22. Clean the database:
DROP TABLE sh.MY_TEST;
How it works...
In this recipe, from step 1 to step 3, we create a
MY_TEST
table containing more than 50000
rows. The values in the
ID
field are skewed, and the query executed in step 5 confirms this
situation. The results are shown in the following screenshot:
In step 4, we create an index on the
ID
field, and in step 6, we collect statistics for the
MY_TEST
table, generating histograms for the
ID
field with 10 buckets (more than the
distinct values in the
ID
field). We compute the statistics on all the rows, as specified by
the parameter
ESTIMATE_PERCENT
, set to 100.
